Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

[Mike Owens]

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  wrote:



On Thu, Jun 13, 2013 at 12:24 AM, Yongil Jang  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

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

[Darren Duncan]

On 2013.06.13 7:22 PM, Yongil Jang wrote:

Thank you, Richard and James.

2013/6/14 James K. Lowden 


Why not simply

SELECT   f.name, count(e.food_id) as 'episodes'
FROM foods as f
OUTER
  JOINfoods_episodes as e
ON   f.id = e.food_id
GROUP BY f.name
ORDER BY episodes DESC LIMIT 10;


In my opinion,
That example is used in beginning of SQL chapter, therefore, JOIN and GROUP
BY is not explained yet.


That seems kind of backwards.  Joining is a simpler operation than subqueries, 
or at least is no more complicated.  It seems strange to be talking about 
subqueries before you talk about joins or grouping.  On the other hand, I 
suppose from an explanation point of view, a subquery in the SELECT list could 
actually be a simpler thing to explain to a SQL newbie, so maybe that's why it 
is first. -- Darren Duncan


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

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

[Yongil Jang]

Thank you, Richard and James.

2013/6/14 James K. Lowden 
>
>
> Why not simply
>
> SELECT   f.name, count(e.food_id) as 'episodes'
> FROM foods as f
> OUTER
>  JOINfoods_episodes as e
> ON   f.id = e.food_id
> GROUP BY f.name
> ORDER BY episodes DESC LIMIT 10;
>
>
In my opinion,
That example is used in beginning of SQL chapter, therefore, JOIN and GROUP
BY is not explained yet.

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

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

[James K. Lowden]

On Thu, 13 Jun 2013 08:13:29 -0400
Richard Hipp  wrote:

> SELECT
>   name,
>   (SELECT COUNT(food_id) FROM foods_episodes WHERE food_id=f.id) count
> FROM
>   foods f
> ORDER BY count DESC LIMIT 10;
...
> SELECT
>   name,
>   (SELECT COUNT(*) FROM foods_episodes WHERE food_id=f.id) count
> FROM
>   foods f
> ORDER BY count DESC LIMIT 10;

Why not simply

SELECT   f.name, count(e.food_id) as 'episodes'
FROM foods as f 
OUTER
 JOINfoods_episodes as e
ON   f.id = e.food_id
GROUP BY f.name
ORDER BY episodes DESC LIMIT 10;

This form expresses the query more algebraically and IMO is much
easier to understand. Last I checked, the use of a subquery in the
SELECT clause isn't even stardard SQL.  

(FWIW stylistically, I avoid "count" as a column name, both because
it's a keyword, and because it's vague.  A concrete noun in plural form
is more expressive.)  

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