On 3/18/2018 5:31 AM, Moritz Bruder wrote:
I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d). Consider the 
following test case:

     CREATE TABLE test (name varchar);
     INSERT INTO test VALUES ("foo"),("bar");

-- Returns a single row with a single column: 'foo!'
SELECT (test.name || '!') AS tname
FROM test
WHERE tname = 'foo!'

     --Returns an empty result.
     SELECT (test.name || '!') AS name
     FROM test
     WHERE name = 'foo!';

What happens is that the identifier "name", defined in the SELECT-clause, gets shadowed 
by the table's column "name".

If I recall correctly, SQL standard doesn't allow aliases from SELECT to be 
used in WHERE clause, only in ORDER BY and, possibly, HAVING (I'm not sure of 
the latter). SQLite allows aliases in WHERE as an extension, but prefers the 
real column name in case of conflict, so as to match the behavior of other DBMS.
--
Igor Tandetnik

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

Reply via email to