--- [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
postgres=> select a+b AS "c" from t1 union select b from t1 order by c;
c
---
2
3
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?
____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
