[EMAIL PROTECTED] wrote:
Can you please tell me what
other databases do with this:
CREATE TABLE t1(a,b,c);
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b AS c FROM t1 WHERE c==4;
In the WHERE clause, should the "c" resolve to
the column "c" or to the "a+b" expression? I'm
guessing the "a+b" expression. But SQLite is
currently resolving the name to the column "c"
in table t1. Thus SQLite currently answers
"3" to the SELECT statement, when I think it
should give an empty set. Or maybe it should give
an error?
Opinions, anyone?
Richard,
According to the where clause definition in the SQL:1999 standard the
"c" in the where clause should refer to the column in table "t1" which
is the result of the preceding from clause. To conform to the standard
SQLite should return 3.
*7.8 <where clause>*
*Function*
Specify a table derived by the application of a <search condition> to
the result of the preceding
<from clause>.
*Format*
<where clause> ::= WHERE <search condition>
*Syntax Rules*
1) Let /T /be the result of the preceding <from clause>. Each column
reference directly contained in
the <search condition> shall unambiguously reference a column of /T /or
be an outer reference.
NOTE 98 – /Outer reference /is defined in Subclause 6.6, ‘‘<column
reference>’’.
My reading of the outer reference definition is that is applies to
triggers and SQL procedures, and correlated subqueries.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
