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

Reply via email to