Hi James, Clemens, Thanks for your reply, I will examine your idea of using a join. I normally work incrementally using subqueries and even unions, until I get to refactor the query and start using joins, but I'll jump to using joins sooner this time! Your examples will get me started faster! thanks!
> The problem appears to be using the aggregate result in a subquery in > the SELECT clause: > > > select m from (select min(x) as m from (select 1 as x)); > 1 > > select (select m) from (select min(x) as m from (select 1 as x)); > Error: misuse of aggregate: min() > > As far as I can see, this should be legal. > > This is my conclusion as well. Thanks a lot for pinpointing the shortest example to reproduce the strange behavior! I wouldn't be a good judge of how sql-compliant your own earlier examples are, but I believe that my own queries are sql-correct (maybe not clean and optimal, but not illegal). The idea behind whether it is legal or not, for me, is reduced to the fact it all works if you insert the result of the inner query into a temp table, then do the outer query on that temp table. The bug does show there is some strange interaction going on between the outer query and the inner one. I'll try a few things, but my experience is limited and my guesses won't be very educated! ;) I'll report any useful findings here, if any. Thanks again for sharing, Simon Lemieux _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
