It seems to me that this should be invalid as COUNT(id) does not refer to a valid field in the subquery's column list. I would vote for throwing an error as it seems that wrong. Since its been over six years since I wrote that query, I don't know what I was thinking at the time.
Sent from my iPhone On Jun 13, 2013, at 2:13 PM, Richard Hipp <d...@sqlite.org> wrote: On Thu, Jun 13, 2013 at 12:24 AM, Yongil Jang <yongilj...@gmail.com> wrote: > Dear all, > > Following select query returns different result data between v3.7.11 and > v3.7.13~. > > CREATE TABLE foods( > id integer primary key, > type_id integer, > name text ); > CREATE TABLE foods_episodes( > food_id integer, > episode_id integer ); > > [Insert some data] > > SELECT > name, > (SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count > FROM > foods f > ORDER BY count DESC LIMIT 10; > > > Result from v3.7.6 and v3.7.11. > > Hot Dog|5 > Kasha|4 > Ketchup|4 > ...... > > > Result from v3.7.13 and v3.7.17. > > Wax Beans (Generic brand)|412 > > > Which result set is correct? > When I changed statement from COUNT(id) to COUNT(food_id) or COUNT(*), it > shows same result with older version. > There were two behavior changes. 3.7.12 and earlier gives the behavior as shown in the book. Check-in http://www.sqlite.org/src/info/430bb59d79 (for version 3.7.13) causes the query to return an error. This is the same behavior demonstrated by PostgreSQL. Check-in http://www.sqlite.org/src/info/d4cd6017c9 (for version 3.7.14) gives the current behavior. Since both Igor and PostgreSQL believes that the query is wrong, I'm going to say that this is a bug in the book. The query should be either: SELECT name, (SELECT COUNT(food_id) FROM foods_episodes WHERE food_id=f.id) count FROM foods f ORDER BY count DESC LIMIT 10; or SELECT name, (SELECT COUNT(*) FROM foods_episodes WHERE food_id=f.id) count FROM foods f ORDER BY count DESC LIMIT 10; Both of these alternative queries give the desired answer in all versions of SQLite and in PostgreSQL. As to whether or not SQLite should return an error for this query (as it did for 3.7.12), or give it its best go (as it does for 3.7.13 and later), I'll have to ponder more closely. The legacy behavior (as shown in the book) seems to be wrong, however. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
