On 13/04/18 14:12, Simon Slavin wrote:
On 13 Apr 2018, at 8:40am, Mark Brand <mabr...@mabrand.nl> wrote:
It also occurs to me that COUNT() should work (but doesn't) over sets of row
values:
sqlite> select count((1,2));
Error: row value misused
I would expect it to return the number of non-NULL row values in the set.
What should this do, and why ?
CREATE TABLE MyTable (a INTEGER, b, INTEGER, c INTEGER);
INSERT INTO MyTable VALUES (1, 1, 1);
INSERT INTO MyTable VALUES (2, 2, 2);
...
INSERT INTO MyTable VALUES (10, 10, 10);
SELECT COUNT(a, b, c) FROM MyTable;
Hi Simon,
I would expect COUNT() to count row values just as it counts normal
values. In your example, it should return 10 because there are 10 rows
in the MyTable.
When counting normal values, COUNT() excludes NULLs. If there is a such
as thing as a NULL row value, COUNT() should exclude it too.
Sqlite doesn't seem to distinguish between a row value made up of only
NULLs and a NULL row value, at least in this context:
sqlite> select (NULL, NULL) IS (SELECT 1, 2 WHERE 0); -- The right
side would probably satisfy anybody's idea of what "NULL row value" means.
1
sqlite> select (NULL, NULL) IS (SELECT NULL, NULL WHERE 1); -- The
right side is a row containing all NULLs.
1
Therefore, probably COUNT() should exclude row values made up of only
NULLs on the grounds that these qualify as NULL row values.
You wrote
COUNT(a, b, c)
but I would have expected
COUNT((a, b, c))
to make it clear that COUNT() has one argument which is a row value. For
aggregate MIN() and MAX(), the "extra" parenthesis would have the
additional motivation of distinguishing the aggregate functions from the
non-aggregate MIN() and MAX() which have 2 arguments.
Mark
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
