Re: [sqlite] Different result set of query in examples of SQLite book

[Yongil Jang]

Thank you, Igor. :)


2013/6/13 Igor Tandetnik 

> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different result set of query in examples of SQLite book

[Igor Tandetnik]

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

[sqlite] Different result set of query in examples of SQLite book

[Yongil Jang]

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.

I've got this examples from following links of "The Definitive Guide to
SQLite"

http://www.apress.com/9781430232254

Regards,
Yongil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users