On 6/13/2013 12:24 AM, Yongil Jang wrote:
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.
Looks like a bug in the newer versions. id resolves to f.id, which
within the context of the sub-select is a constant. So COUNT(id) should
work no differently than, say, COUNT(1), which in turn should be
equivalent to COUNT(*).
Of course, there is really no good reason to write COUNT(id) here.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users