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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users