> Not in principle. But I think changes that break backwards
> compatibility would be more trouble than they're worth for
> something like this. In the absence of clearer guidance
> from sql-92, it's probably more important to be compatible
> with earlier sqlite versions than with mysql and friends.
>
> Maybe it would be better to document the current behaviour
> and move on.
>
> Dan.
It would be tricky to document the current behavior accurately.
Compound queries with ORDER BY without aliases have never really worked
in a uniform way in sqlite. I don't think fixing the issue will trouble
people, as most must use column aliases and subqueries as a workaround
for these problems anyway.
I think at the very least, the inconsistency of the column names in
the result set should be resolved:
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> create table foo(a);
sqlite> insert into foo values(1);
sqlite> .header on
sqlite> select foo.a from foo;
a
1
sqlite> select foo.a from foo union all select foo.a from foo;
a
1
1
sqlite> select foo.a from foo union all select foo.a from foo order by 1;
foo.a
1
1
sqlite> select foo.a from foo union all select foo.a from foo group by 1;
a
1
1
Notice the column headings.
Why does the column name change in the result set because of the
addition of an ORDER BY but not with a GROUP BY clause or with a
regular non-compound query?
And should regular queries support expressions in ORDER BY, while
compound statements not?
sqlite> select a from foo order by a*a-3*a;
1
sqlite> select a from foo union all select a+5 as a from foo order by a*a-3*a;
SQL error: ORDER BY term number 1 does not match any result column
The only way to get this query to work is to use this workaround:
sqlite> select * from (select a from foo union all select a+5 from foo) order
by a*a-3*a;
1
6
Other databases allow expressions in compound SELECT/ORDER BY without the
subquery:
mysql> select a from foo union all select a+5 as a from foo order by a*a-3*a;
+------+
| a |
+------+
| 1 |
| 6 |
+------+
I only have MySQL to test with here. I'm fairly certain it works on
most other open source and popular commercial databases.
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
