D. Richard Hipp wrote:
> Consider a table:
>
> CREATE TABLE t1(a INTEGER, b INTEGER);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(9,8);
>
> I am reworking (read: trying to fix) the name resolution algorithm in
> SQLite and I am wondering what is the correct behavior of the
> following queries? Can anybody tell me what PostgreSQL or MySQL or
> SQL Server or Oracle do with these?
>
> SELECT a AS b, b AS a FROM t1 ORDER BY a;
> SELECT b AS a, a AS b FROM t1 ORDER BY a;
> SELECT a, b AS a FROM t1 ORDER BY a;
> SELECT a AS x, b AS x ORDER BY x;
>
> SELECT a AS b, b AS a WHERE a=1;
> SELECT a AS b, b AS a WHERE a=2;
> SELECT a AS x, b AS x WHERE x=1;
>
The following are results from PostgreSQL 8.3.3 on the tests. I changed
the inserted values as suggested by Robert Simpson to better indicate
the effects of ordering (and changed the a=2 in the subsequent tests to
a=6 to match the inserted values). I also duplicated the last 4 tests
with a FROM clause added to indicate the table the columns should be from.
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES(1,6);
INSERT INTO t1 VALUES(9,5);
SELECT a AS b, b AS a FROM t1 ORDER BY a;
b;a
9;5
1;6
SELECT b AS a, a AS b FROM t1 ORDER BY a;
a;b
5;9
6;1
SELECT a, b AS a FROM t1 ORDER BY a;
ERROR: ORDER BY "a" is ambiguous
LINE 1: SELECT a, b AS a FROM t1 ORDER BY a;
^
SELECT a AS x, b AS x ORDER BY x;
ERROR: column "a" does not exist
LINE 1: SELECT a AS x, b AS x ORDER BY x;
^
SELECT a AS b, b AS a WHERE a=1;
ERROR: column "a" does not exist
LINE 1: SELECT a AS b, b AS a WHERE a=1;
^
SELECT a AS b, b AS a WHERE a=6;
ERROR: column "a" does not exist
LINE 1: SELECT a AS b, b AS a WHERE a=6;
^
SELECT a AS x, b AS x WHERE x=1;
ERROR: column "a" does not exist
LINE 1: SELECT a AS x, b AS x WHERE x=1;
^
SELECT a AS x, b AS x FROM t1 ORDER BY x;
ERROR: ORDER BY "x" is ambiguous
LINE 1: SELECT a AS x, b AS x FROM t1 ORDER BY x;
^
SELECT a AS x, b AS x FROM t1 ORDER BY x;
ERROR: ORDER BY "x" is ambiguous
LINE 1: SELECT a AS x, b AS x FROM t1 ORDER BY x;
^
SELECT a AS b, b AS a FROM t1 WHERE a=1;
b;a
1;6
SELECT a AS b, b AS a FROM t1 WHERE a=6;
b;a
SELECT a AS x, b AS x FROM t1 WHERE x=1;
ERROR: column "x" does not exist
LINE 1: SELECT a AS x, b AS x FROM t1 WHERE x=1;
^
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
