[sqlite] Expected behaviour of COUNT with no GROUP BY?

[Mark Lawrence]

I try to remember to define GROUP BY values when using aggregate
functions (and I wish SQLite considered it an error otherwise) but I
forget once and the result surprised me.

    CREATE TABLE x(
        id INTEGER
    );

    CREATE TABLE y(
        id INTEGER
    );

    SELECT
        x.id,
        count(y.id)
    FROM
        x
    INNER JOIN
        y
    ON
        y.id = x.id
    ORDER BY
        x.id
    ;

Result:

    id          count(y.id)
    ----------  -----------
    NULL        0          

It is expected behaviour that a row is returned in this situation even
through the tables are empty?  Adding a "GROUP BY x.id" returned the
expected empty set.

-- 
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users