The SQLite documentation (http://www.sqlite.org/lang_select.html) says the
GROUP BY clause accepts [expr]. And [expr] can be composed of a literal.
What I cannot find is what SQLite does (or should do) with a literal in the
GROUP BY clause.
In the simple case of table T1 with two columns C1,C2
CREATE TABLE [T1](
[c1] text COLLATE NOCASE,
[c2] text COLLATE NOCASE);
INSERT INTO T1 VALUES ('a0','b8'),('a1','b7'),('a2','b8'),('a1','b5');
These queries give the same results:
SELECT c1,c2 from T1 GROUP BY c1;
SELECT c1,c2 from T1 GROUP BY 1;
SELECT c2,c1 from T1 GROUP BY 2;
and these two give the same result:
SELECT c1,c2 from T1 GROUP BY c2;
SELECT c1,c2 from T1 GROUP BY 2;
and this produces an error:
SELECT c1,c2 from T1 GROUP BY 3;
This seems to show in the case of an integer literal, it substitutes the
integer for that number of the selected columns. Maybe this is obvious and
documented somewhere, but I was not able to find it and was not obvious to
me initially.
But when the literal GROUP BY is a string:
These all produce a single row of output (and it happens to be the last row
inserted [a1,b5]):
select c1,c2 from t1 group by '1';
select c1,c2 from t1 group by '2';
select c1,c2 from t1 group by '3';
select c1,c2 from t1 group by 'c1';
In the case of string literals, I would have expected to see all rows
returned and in these cases the group by become effectively a no-op. But I
cannot find documentation to justify my expectation, or to validate the
actual output.
I did compare to SQL Server to see if behavior was similar and SQL Server
simply does not allow this and complains "Each GROUP BY expression must
contain at least one column that is not an outer reference."
So I think my questions are simply:
1) should literals be allowed in the group by?
2) if so, is SQLite doing what it should with them?
Thanks,
Denis
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
