On 12/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Dennis Cote <[EMAIL PROTECTED]> wrote: > > > [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? > > > > > > 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. > > > > I never would have guessed things worked that way. But then > > again, SQL is not noted for making a whole lot of sense. > > I figure if you get agreement between many different databases, they > probably follow the standard. Or is it vice versa? > > It doesn't appear to be possible to use column aliases in the WHERE > clause of postgres and MySQL. So they seem to have interpreted the > standard in the same way. I thought sqlite's useful WHERE clause column > alias extension was common. Perhaps not. > > postgres=> select a AS foo from t1 where foo=1; > ERROR: column "foo" does not exist > postgres=> select a AS foo from t1 where "foo"=1; > ERROR: column "foo" does not exist > postgres=> select a AS "foo" from t1 where "foo"=1; > ERROR: column "foo" does not exist > > mysql> select a AS foo from t9 where foo=1; > ERROR 1054 (42S22): Unknown column 'foo' in 'where clause' > mysql> select a AS foo from t9 where "foo"=1; > Empty set, 1 warning (0.00 sec) > > MySQL seems to interpret the WHERE clause "foo" as the string literal 'foo'. > > But ORDER BY is a different story: > > mysql> select a AS "foo" from t1 union select b from t1 order by foo; > +------+ > | foo | > +------+ > | 1 | > | 2 | > +------+ > > postgres=> select a AS "foo" from t1 union select b from t1 order by foo; > foo > ----- > 1 > 2 >
IBM DB2 9.5 select a AS "foo" from t1 union select b from t1 order by foo SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 > postgres=> select a+b AS "c" from t1 union select b from t1 order by c; > c > --- > 2 > 3 > IBM DB2 9.5 select a+b AS "c" from t1 union select b from t1 order by c SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703 > Let's add another row to table t1... > > postgres=> insert into t1 values(2, -1000, 5); > INSERT 0 1 > test=> select * from t1; > a | b | c > ---+-------+--- > 1 | 2 | 4 > 2 | -1000 | 5 > > postgres=> select a, a+b AS "c" from t1 order by c; > a | c > ---+------ > 2 | -998 > 1 | 3 > > > mysql> select * from t1; > +------+-------+------+ > | a | b | c | > +------+-------+------+ > | 1 | 2 | 4 | > | 2 | -1000 | 5 | > +------+-------+------+ > > mysql> select a, a+b AS "c" from t1 order by c; > +------+------+ > | a | c | > +------+------+ > | 2 | -998 | > | 1 | 3 | > +------+------+ > > which differs from: > > SQLite version 3.5.1 > sqlite> select * from t1; > a b c > ---------- ---------- ---------- > 1 2 4 > 2 -1000 5 > > sqlite> select a, a+b AS "c" from t1 order by c; > a c > ---------- ---------- > 1 3 > 2 -998 > > Which database is correct? > IBM DB2 9.5 select * from t1; a b c ---------- ---------- ---------- 1 2 4 2 -1000 5 select a, a+b AS "c" from t1 order by c; a c ---------- ---------- 1 3 2 -998 Robert Wishlaw ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
