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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
