Curious... even it does not make sense, it pass also on oracle 11g (sql server actively refused to run)
SQL Server 2008: SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_outer.c) FROM T t_outer -- Msg 164, Level 15, State 1, Line 1 -- Each GROUP BY expression must contain at least one column that is not an outer reference. SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_inner.c) FROM T t_outer ----------- (0 row(s) affected) Oracle 11g: SQL> create table t (c int); Table created SQL> select (select count(*) from t t_inner group by t_inner.c) from t t_outer; No rows selected SQL> select (select count(*) from t t_inner group by t_outer.c) from t t_outer; No rows selected -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: quinta-feira, 28 de maio de 2009 11:30 To: General Discussion of SQLite Database Subject: Re: [sqlite] GROUPY BY alias backward incompatibility 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? MySQL (5.0.21) reports no erros on either of both queries: select (select count(*) from t t_inner group by t_outer.c) -- t_outer !!! from t t_outer; select (select count(*) from t t_inner group by t_inner.c) -- t_inner !!! from t t_outer; I do not have access to PostgreSQL right now. The SQLite help [1] says: "The expressions in the GROUP BY clause do not have to be expressions that appear in the result." Reading this, I'd expect that both queries should run - even if the 1st one does not make much sense. Opinions? Ralf [1] http://www.sqlite.org/lang_select.html _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
