On May 28, 2009, at 7:01 AM, Ralf Junker wrote: > > drop table if exists t; > create table t (c); > select > (select count() from t as t_inner > group by t_outer.c) > from t as t_outer; > > This behaviour changed in version 3.5.4. From then on, SQLite issues > an "SQL error near line 4: no such column: t_outer.c". This also > shows in the most recent version (3.6.14).
I missed the space in between "t" and "t_inner" in your first post, which is why I did not understand the query. I edited above to insert the AS keyword for readability. But the query still makes no sense to me. How can you GROUP BY something that is not part of the query? The t_outer.c value is a constant within the scope of the inner query, so the GROUP BY is meaningless. If this worked in 3.5.3 and earlier, I guess that is a bug in 3.5.3 and earlier that was fixed in 3.5.4. > > Questions: > > * Does the ORDER BY change also apply to GROUP BY? GROUP BY and ORDER BY are mostly the same thing, just applied at different times during processing. So most of the code for the two is in common. Changes to one tend to effect the other. But I am still not seeing a "change" here - unless you call better detection of an error condition a change. > > > * Are there any test cases for the new behaviour? > I failed to find any in 3.6.14 test suite. There are test cases for everything. But they can often take a good bit of effort to locate. > > > * Post 3.5.3, the query works fine if I GROUP BY the inner table: > > select > (select count() from t as t_inner > group by t_inner.c) > from t as t_outer; > > Is this the intended behaviour and should users update > their SQL accordingly? This query makes sense because now the GROUP BY is against a column in the table that is being queried. And so one would expect this to work. Have you tried these two queries on other SQL database engines besides SQLite? What do PostgreSQL and MySQL make of them? D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
