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