At 15:37 28.05.2009, D. Richard Hipp wrote:
>Have you tried these two queries on other SQL database engines besides
>SQLite? What do PostgreSQL and MySQL make of them?
I could now run the queries on Oracle Database 10g Express Edition Release
10.2.0.1.0.
Prepare the table:
create table t (c integer);
insert into t values (1);
insert into t values (2);
Query with t_outer (the one that fails SQLite > 3.5.3) runs without error:
select
(select count(*) from t t_inner
group by t_outer.c) -- t_outer !!!
from t t_outer;
Query with t_inner (which works on all SQLite versions) fails with error
"ORA-01427: single-row subquery returns more than one row":
select
(select count(*) from t t_inner
group by t_inner.c) -- t_inner !!!
from t t_outer;
Preliminary conclusion:
* Behaviour is inconsistent between MySQL and Oracle.
* The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.
* The query that works on SQLite all versions fails on Oracle.
Additional findings from other DB engines would be helpful. Anyone?
Other than that, I believe it would be desirable if SQLite would support the
t_outer query as it did up to 3.5.3.
Ralf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
