Re: [sqlite] possible bug: select clause column alias shadowing
Jean-Christophe Deschamps wrote: > At 23:36 18/03/2018, you wrote: >> In other words, aliases in the SELECT clause are evaluated _after_ the >> FROM and WHERE clauses are done. > > I must be misinterpreting: I was talking about the SQL standard. (I might have mentioned that somewhere ...) > select a int, printf('<%5i>', a) fmt from t where fmt like '%>'; > > Here WHERE understands what fmt refers to. SQLite tries to be helpful. But when in doubt (i.e., when an alias tries to shadow a real column), it chooses the standard-conforming interpretation. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible bug: select clause column alias shadowing
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 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
Re: [sqlite] possible bug: select clause column alias shadowing
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
Re: [sqlite] possible bug: select clause column alias shadowing
At 23:36 18/03/2018, you wrote: In other words, aliases in the SELECT clause are evaluated _after_ the FROM and WHERE clauses are done. The order of the SELECT/WHERE clauses in the SQL syntax is misleading; the actual behaviour would be better represented by something like this: ( FROM test WHERE name = 'foo!' ) SELECT test.name || '!' AS name; I must be misinterpreting: create temp table t (a int); insert into t values (1), (2), (6); select a int, printf('<%5i>', a) fmt from t where fmt like '%>'; int fmt 1 <1> 2 <2> 6 <6> Here WHERE understands what fmt refers to. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible bug: select clause column alias shadowing
Simon Slavin wrote: > As best I can find, SQL92 does not specify what happens when you choose > an AS clause giving a value name the same as a column. | 7.3 | | Function | |Specify a table or a grouped table. | | Format | | ::= | | [ ] | [ ] | [ ] | | [...] | General Rules | |1) If all optional clauses are omitted, then the result of the is the same as the result of the . | Otherwise, each specified clause is applied to the result of | the previously specified clause and the result of the is the result of the application of the last specified | clause. | | [...] | | 7.9 | | Function | |Specify a table derived from the result of a . | | Format | | ::= | SELECT [ ] | | ::= | | | [ { }... ] | | ::= | | | | | ::= [ ] | | Syntax Rules | |1) Let T be the result of the . |[...] | |6) Each directly contained in each ... shall unambiguously reference a column of T. | | [...] | General Rules | | 1) a) ii) [...] each is applied to each row of T |yielding a table of M rows, where M is the cardinality of T. |The i-th column of the table contains the values derived by |the evaluation of the i-th . In other words, aliases in the SELECT clause are evaluated _after_ the FROM and WHERE clauses are done. The order of the SELECT/WHERE clauses in the SQL syntax is misleading; the actual behaviour would be better represented by something like this: ( FROM test WHERE name = 'foo!' ) SELECT test.name || '!' AS name; Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible bug: select clause column alias shadowing
On 18 Mar 2018, at 9:31am, Moritz Bruder wrote: > I'm not exactly sure what the SQL standard says As best I can find, SQL92 does not specify what happens when you choose an AS clause giving a value name the same as a column. It doesn't go into much detail at all about applying "AS" to a value. It does go into more detail about applying "AS" to a table name. Section 5.4 paragraph 12 it says An that is a is associated with a table within a particular scope. The scope of a is either a , , or (see Subclause 6.3, ""). Scopes may be nested. In different scopes, the same may be associated with different tables or with the same table. We can apply this to a 'correlation name' for a value rather than a table. If I read this right, it suggests you're right: the use of 'name' should equate to your definition '(test.name || '!')', not to 'test.name'. However if I see code where the programmer picked an alias the same as a real column name I'd question the quality of that programmer's thinking. I get a "Do not do this." feeling. Cue comment from the development team about backward compatibility. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users