Compared to PostgreSQL, SQLite does a better job here when there is no
input column collision.
The column collision case below returns no rows in both SQLite and
PostgreSQL:
WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS a FROM t WHERE a='foo!';
But the following edit with intermediating alias column b produces 'ERROR:
column "b" does not exist' in PostgreSQL:
sqlite> WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS b FROM t WHERE
b='foo!';
b
foo!
A safer coding style would be to use an intermediating query/view/cte when
any input column's meaning is being modified:
sqlite> WITH t(a) AS (VALUES ('foo')), u AS (SELECT a||'!' AS a FROM t)
SELECT a FROM u WHERE a='foo!';
a
foo!
Peter
On Sun, Mar 18, 2018 at 2:31 AM, Moritz Bruder <mues...@googlemail.com>
wrote:
> Hi,
>
> 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". I'm not exactly sure what the
> SQL standard says but it is wrong in my opinion. I expect it to be the
> other way round.Let me know whether you consider it a bug.
>
>
> Best wishes,
>
> Moritz
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
