possible bug: select clause column alias shadowing

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

possible bug: select clause column alias shadowing

Moritz Bruder
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug: select clause column alias shadowing

Simon Slavin-3
On 18 Mar 2018, at 9:31am, Moritz Bruder <[hidden email]> 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 <identifier> that is a <correlation name> is associated with
            a table within a particular scope. The scope of a <correlation
            name> is either a <select statement: single row>, <subquery>, or
            <query specification> (see Subclause 6.3, "<table reference>").
            Scopes may be nested. In different scopes, the same <correlation
            name> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug: select clause column alias shadowing

Clemens Ladisch
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  <table expression>
|
|  Function
|
|    Specify a table or a grouped table.
|
|  Format
|
|    <table expression> ::=
|         <from clause>
|         [ <where clause> ]
|         [ <group by clause> ]
|         [ <having clause> ]
|
|  [...]
|  General Rules
|
|    1) If all optional clauses are omitted, then the result of the <ta-
|       ble expression> is the same as the result of the <from clause>.
|       Otherwise, each specified clause is applied to the result of
|       the previously specified clause and the result of the <table ex-
|       pression> is the result of the application of the last specified
|       clause.
|
| [...]
|
| 7.9 <query specification>
|
|  Function
|
|    Specify a table derived from the result of a <table expression>.
|
|  Format
|
|    <query specification> ::=
|         SELECT [ <set quantifier> ] <select list> <table expression>
|
|    <select list> ::=
|           <asterisk>
|         | <select sublist> [ { <comma> <select sublist> }... ]
|
|    <select sublist> ::=
|           <derived column>
|         | <qualifier> <period> <asterisk>
|
|    <derived column> ::= <value expression> [ <as clause> ]
|
|  Syntax Rules
|
|    1) Let T be the result of the <table expression>.
|    [...]
|
|    6) Each <column reference> directly contained in each <value ex-
|       pression> ... shall unambiguously reference a column of T.
|
|  [...]
|  General Rules
|
|  1) a) ii) [...] each <value expression> 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 <value expression>.

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug: select clause column alias shadowing

Jean-Christophe Deschamps-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug: select clause column alias shadowing

Igor Tandetnik-2
In reply to this post by Moritz Bruder
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug: select clause column alias shadowing

petern
In reply to this post by Moritz Bruder
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible bug: select clause column alias shadowing

Clemens Ladisch
In reply to this post by Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users