[sqlite] GROUP BY: ambiguous column name?

[Mark Lawrence]

Don't know if this is a bug or intended behaviour. Given the following
schema:

    CREATE TABLE x(
        id INTEGER
    );

    CREATE TABLE y(
        id INTEGER,
        fk INTEGER REFERENCES x(id)
    );

ORDER BY on a result column name is allowed:

    SELECT
        COALESCE(x.id, y.id) AS id
    FROM
        y
    LEFT JOIN
        x
    ON
        x.id = y.fk
    ORDER BY
        id
    ;

GROUP BY on a result column fails with "ambiguous column name":

    SELECT
        COALESCE(x.id, y.id) AS id
    FROM
        y
    LEFT JOIN
        x
    ON
        x.id = y.fk
    ORDER BY
        id
    ;

I would have expected the group to work the same as the order, given
that I think of the group as happening on the result set before any
joins. The syntax diagrams on the web page show the first as an
"ordering-term" and the second as an "expr" which doesn't enlighten me
much.

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