[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?
From:       "Robert Simpson" <sqlite_list () bcsft ! com>
Date:       2008-08-19 21:18:06
Message-ID: 002501c90241$12a8c530$37fa4f90$ () com
[Download RAW message or body]

Slight change so you can see the order by results more clearly:

(Sql Server 2005)
CREATE TABLE #t1(a int, b int);
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;
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'a'.

SELECT a AS x, b AS x FROM #t1 ORDER BY x;
Msg 209, Level 16, State 1, Line 2
Ambiguous column name '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;
Msg 207, Level 16, State 1, Line 1
Invalid column name 'x'.




-----Original Message-----
From: sqlite-users-bounces@sqlite.org
[mailto:sqlite-users-bounces@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Tuesday, August 19, 2008 2:00 PM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Correct SQL name resolution on AS clauses in a
SELECT?

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;




D. Richard Hipp
drh@hwaci.com



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


_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]