Re: [sqlite] GROUPY BY alias backward incompatibility

[Nicolas Williams]

On Thu, May 28, 2009 at 04:30:14PM +0200, Ralf Junker wrote:
>   select
> (select count(*) from t t_inner
>  group by t_outer.c) -- t_outer !!!
>   from t t_outer;
> 
>   select
> (select count(*) from t t_inner
>  group by t_inner.c) -- t_inner !!!
>   from t t_outer;
> 
> The SQLite help [1] says: "The expressions in the GROUP BY clause do
> not have to be expressions that appear in the result." Reading this,
> I'd expect that both queries should run - even if the 1st one does not
> make much sense. Opinions?

"do not have to be expressions that appear in the result" != "do not
have to be expressions that appear in the query".  The first relates to
expressions appearing to the left of WHERE and the seconds relates to
expressions appearing to the right of WHERE.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUPY BY alias backward incompatibility

[Pavel Ivanov]

>  * The query that works on SQLite all versions fails on Oracle.

False conclusion. Did you try to make only one row in t?

>  * Behaviour is inconsistent between MySQL and Oracle.

I believe this conclusion is also false. Did you try several rows in t
on MySQL? If it worked I wonder how it showed you the results?

>  * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.

Regardless of that my opinion is that the query is nonsense and any
effort should not be taken to make it work.


Pavel

On Sat, May 30, 2009 at 4:30 AM, Ralf Junker  wrote:
> At 15:37 28.05.2009, D. Richard Hipp wrote:
>
>>Have you tried these two queries on other SQL database engines besides
>>SQLite?  What do PostgreSQL and MySQL make of them?
>
> I could now run the queries on Oracle Database 10g Express Edition Release 
> 10.2.0.1.0.
>
> Prepare the table:
>
>  create table t (c integer);
>
>  insert into t values (1);
>  insert into t values (2);
>
> Query with t_outer (the one that fails SQLite > 3.5.3) runs without error:
>
>  select
>    (select count(*) from t t_inner
>     group by t_outer.c)             -- t_outer !!!
>  from t t_outer;
>
> Query with t_inner (which works on all SQLite versions) fails with error 
> "ORA-01427: single-row subquery returns more than one row":
>
>  select
>    (select count(*) from t t_inner
>     group by t_inner.c)             -- t_inner !!!
>  from t t_outer;
>
> Preliminary conclusion:
>
>  * Behaviour is inconsistent between MySQL and Oracle.
>
>  * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.
>
>  * The query that works on SQLite all versions fails on Oracle.
>
> Additional findings from other DB engines would be helpful. Anyone?
>
> Other than that, I believe it would be desirable if SQLite would support the 
> t_outer query as it did up to 3.5.3.
>
> Ralf
>
> ___
> 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] GROUPY BY alias backward incompatibility

[Ralf Junker]

At 15:37 28.05.2009, D. Richard Hipp wrote:

>Have you tried these two queries on other SQL database engines besides  
>SQLite?  What do PostgreSQL and MySQL make of them?

I could now run the queries on Oracle Database 10g Express Edition Release 
10.2.0.1.0.

Prepare the table:

  create table t (c integer);

  insert into t values (1);
  insert into t values (2);

Query with t_outer (the one that fails SQLite > 3.5.3) runs without error:

  select
(select count(*) from t t_inner
 group by t_outer.c) -- t_outer !!!
  from t t_outer;

Query with t_inner (which works on all SQLite versions) fails with error 
"ORA-01427: single-row subquery returns more than one row":

  select
(select count(*) from t t_inner
 group by t_inner.c) -- t_inner !!!
  from t t_outer;

Preliminary conclusion:

  * Behaviour is inconsistent between MySQL and Oracle.

  * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle.

  * The query that works on SQLite all versions fails on Oracle.

Additional findings from other DB engines would be helpful. Anyone?

Other than that, I believe it would be desirable if SQLite would support the 
t_outer query as it did up to 3.5.3.

Ralf 

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

Re: [sqlite] GROUPY BY alias backward incompatibility

[Virgilio Alexandre Fornazin]

Curious... even it does not make sense, it pass also on oracle 11g (sql
server actively refused to run)

SQL Server 2008:

SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_outer.c) FROM T t_outer
-- Msg 164, Level 15, State 1, Line 1
-- Each GROUP BY expression must contain at least one column that is not an
outer reference.

SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_inner.c) FROM T t_outer
---
(0 row(s) affected)


Oracle 11g:

SQL> create table t (c int);
Table created

SQL> select (select count(*) from t t_inner group by t_inner.c) from t
t_outer;
No rows selected

SQL> select (select count(*) from t t_inner group by t_outer.c) from t
t_outer;
No rows selected


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker
Sent: quinta-feira, 28 de maio de 2009 11:30
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GROUPY BY alias backward incompatibility

At 15:37 28.05.2009, D. Richard Hipp wrote:

>Have you tried these two queries on other SQL database engines besides  
>SQLite?  What do PostgreSQL and MySQL make of them?

MySQL (5.0.21) reports no erros on either of both queries:

  select
(select count(*) from t t_inner
 group by t_outer.c) -- t_outer !!!
  from t t_outer;

  select
(select count(*) from t t_inner
 group by t_inner.c) -- t_inner !!!
  from t t_outer;

I do not have access to PostgreSQL right now.

The SQLite help [1] says: "The expressions in the GROUP BY clause do not
have to be expressions that appear in the result." Reading this, I'd expect
that both queries should run - even if the 1st one does not make much sense.
Opinions?

Ralf

[1] http://www.sqlite.org/lang_select.html  

___
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] GROUPY BY alias backward incompatibility

[Ralf Junker]

At 15:37 28.05.2009, D. Richard Hipp wrote:

>Have you tried these two queries on other SQL database engines besides  
>SQLite?  What do PostgreSQL and MySQL make of them?

MySQL (5.0.21) reports no erros on either of both queries:

  select
(select count(*) from t t_inner
 group by t_outer.c) -- t_outer !!!
  from t t_outer;

  select
(select count(*) from t t_inner
 group by t_inner.c) -- t_inner !!!
  from t t_outer;

I do not have access to PostgreSQL right now.

The SQLite help [1] says: "The expressions in the GROUP BY clause do not have 
to be expressions that appear in the result." Reading this, I'd expect that 
both queries should run - even if the 1st one does not make much sense. 
Opinions?

Ralf

[1] http://www.sqlite.org/lang_select.html  

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

Re: [sqlite] GROUPY BY alias backward incompatibility

[D. Richard Hipp]

On May 28, 2009, at 7:01 AM, Ralf Junker wrote:
>
>  drop table if exists t;
>  create table t (c);
>  select
>(select count() from t  as t_inner
> group by t_outer.c)
>  from t as t_outer;
>
> This behaviour changed in version 3.5.4. From then on, SQLite issues  
> an "SQL error near line 4: no such column: t_outer.c". This also  
> shows in the most recent version (3.6.14).

I missed the space in between "t" and "t_inner" in your first post,  
which is why I did not understand the query.  I edited above to insert  
the AS keyword for readability.

But the query still makes no sense to me.  How can you GROUP BY  
something that is not part of the query?  The t_outer.c value is a  
constant within the scope of the inner query, so the GROUP BY is  
meaningless.

If this worked in 3.5.3 and earlier, I guess that is a bug in 3.5.3  
and earlier that was fixed in 3.5.4.
>
> Questions:
>
> * Does the ORDER BY change also apply to GROUP BY?

GROUP BY and ORDER BY are mostly the same thing, just applied at  
different times during processing.  So most of the code for the two is  
in common.  Changes to one tend to effect the other.  But I am still  
not seeing a "change" here - unless you call better detection of an  
error condition a change.
>
>
> * Are there any test cases for the new behaviour?
>  I failed to find any in 3.6.14 test suite.

There are test cases for everything.  But they can often take a good  
bit of effort to locate.
>
>
> * Post 3.5.3, the query works fine if I GROUP BY the inner table:
>
>  select
>(select count() from t as t_inner
> group by t_inner.c)
>  from t as t_outer;
>
>  Is this the intended behaviour and should users update
>  their SQL accordingly?

This query makes sense because now the GROUP BY is against a column in  
the table that is being queried.  And so one would expect this to work.

Have you tried these two queries on other SQL database engines besides  
SQLite?  What do PostgreSQL and MySQL make of them?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] GROUPY BY alias backward incompatibility

[Ralf Junker]

At 13:27 28.05.2009, D. Richard Hipp wrote:

>On May 28, 2009, at 7:01 AM, Ralf Junker wrote:
>
>> Hello!
>>
>> Up to version 3.5.3, SQLite would happily execute this SQL without  
>> complaints:
>>
>>  drop table if exists t;
>>
>>  create table t (c);
>>
>>  select
>>(select count() from t t_inner
>> group by t_outer.c)
>>  from t t_outer;
>>
>
>This query does not make any sense.  What are you trying to do?

The above query is just a shortened version to demonstrate the backward 
incompatibilty. The original is far longer and calculates counts and average 
counts:

DROP TABLE IF EXISTS RemSymptoms;
DROP TABLE IF EXISTS SymCategory;

CREATE TABLE RemSymptoms (SymId);
CREATE TABLE SymCategory (CatID, SymId);

SELECT SymCategory.CatID, round(1000*COUNT(*) /
  (SELECT COUNT(*) 
 FROM SymCategory TempSymCategory 
 WHERE TempSymCategory.CatId=SymCategory.CatID 
 GROUP BY SymCategory.CatID))
 AS CatAvgRemCount, 
  (SELECT COUNT(*) 
 FROM SymCategory TempSymCategory 
 WHERE TempSymCategory.CatId=SymCategory.CatID 
 GROUP BY SymCategory.CatID) 
 AS CatSymCount 
  FROM RemSymptoms 
INNER JOIN SymCategory ON RemSymptoms.SymId=SymCategory.SymId
  GROUP BY SymCategory.CatID;

A user found that this query no longer works after upgrading to SQLite 3.5.4 or 
later.

Ralf 

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

Re: [sqlite] GROUPY BY alias backward incompatibility

[Ralf Junker]

At 13:27 28.05.2009, D. Richard Hipp wrote:

>On May 28, 2009, at 7:01 AM, Ralf Junker wrote:
>
>> Hello!
>>
>> Up to version 3.5.3, SQLite would happily execute this SQL without  
>> complaints:
>>
>>  drop table if exists t;
>>
>>  create table t (c);
>>
>>  select
>>(select count() from t t_inner
>> group by t_outer.c)
>>  from t t_outer;
>>
>
>This query does not make any sense.  What are you trying to do?

The above query is just a shortened version to demonstrate the backward 
incompatibilty. The original is far longer and calculates counts and average 
counts:

DROP TABLE IF EXISTS RemSymptoms;
DROP TABLE IF EXISTS SymCategory;

CREATE TABLE RemSymptoms (SymId);
CREATE TABLE SymCategory (CatID, SymId);

SELECT SymCategory.CatID, round(1000*COUNT(*) /
  (SELECT COUNT(*) 
 FROM SymCategory TempSymCategory 
 WHERE TempSymCategory.CatId=SymCategory.CatID 
 GROUP BY SymCategory.CatID))
 AS CatAvgRemCount, 
  (SELECT COUNT(*) 
 FROM SymCategory TempSymCategory 
 WHERE TempSymCategory.CatId=SymCategory.CatID 
 GROUP BY SymCategory.CatID) 
 AS CatSymCount 
  FROM RemSymptoms 
INNER JOIN SymCategory ON RemSymptoms.SymId=SymCategory.SymId
  GROUP BY SymCategory.CatID;

A user found that this query no longer works after upgrading to SQLite 3.5.4 or 
later.

Ralf 

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

Re: [sqlite] GROUPY BY alias backward incompatibility

[D. Richard Hipp]

On May 28, 2009, at 7:01 AM, Ralf Junker wrote:

> Hello!
>
> Up to version 3.5.3, SQLite would happily execute this SQL without  
> complaints:
>
>  drop table if exists t;
>
>  create table t (c);
>
>  select
>(select count() from t t_inner
> group by t_outer.c)
>  from t t_outer;
>

This query does not make any sense.  What are you trying to do?


D. Richard Hipp
d...@hwaci.com



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

[sqlite] GROUPY BY alias backward incompatibility

[Ralf Junker]

Hello!

Up to version 3.5.3, SQLite would happily execute this SQL without complaints:

  drop table if exists t;

  create table t (c);

  select 
(select count() from t t_inner
 group by t_outer.c)
  from t t_outer;

This behaviour changed in version 3.5.4. From then on, SQLite issues an "SQL 
error near line 4: no such column: t_outer.c". This also shows in the most 
recent version (3.6.14).

I searched both the change log and the timeline for 3.5.4 but could not find 
any explicit mention of GROUP BY.

I did note, however, that http://www.sqlite.org/releaselog/3_5_4.html talks 
about bringing "the processing of ORDER BY into compliance with the SQL 
standard".

Questions:

* Does the ORDER BY change also apply to GROUP BY?

* Are there any test cases for the new behaviour?
  I failed to find any in 3.6.14 test suite.

* Post 3.5.3, the query works fine if I GROUP BY the inner table:

  select 
(select count() from t t_inner
 group by t_inner.c)
  from t t_outer;

  Is this the intended behaviour and should users update 
  their SQL accordingly?

Thanks for any answers,

Ralf

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