jeudi 27 août 2015

Sqlite Group By of subquery returns only one row

We observed that Sqlite returns always only one row if we apply group on a subquery and do not use an aggregation operation such as count or sum.

Here is a toy example:

Given table

CREATE TABLE ExampleTable (
 id INT PRIMARY KEY,
 rank INT NOT NULL
);

with data

INSERT INTO ExampleTable(id, rank) VALUES (1, 1);
INSERT INTO ExampleTable(id, rank) VALUES (2, 2);
INSERT INTO ExampleTable(id, rank) VALUES (3, 2);

the query

SELECT rank, COUNT(*) FROM (select id, rank from ExampleTable) GROUP BY rank;

returns

rank|count
2|2
1|1

However, without the COUNT operation Sqlite returns only 1 row.

SELECT rank FROM (select id, rank from ExampleTable) GROUP BY rank;

=>

rank
1

Is this is a bug or an expected behavior?

Aucun commentaire:

Enregistrer un commentaire