Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-22 Thread Marco Bambini 
I am sorry, but it was sent twice for a mistake.
--
Marco Bambini
http://www.sqlabs.com


On Jun 21, 2012, at 2:41 PM, Marco Bambini wrote:

> Consider the following example:
> 
> CREATE TABLE t1(x);
> CREATE TABLE t2(y);
> SELECT max((SELECT avg(x) FROM t2)) FROM t1;
> 
> With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error 
> "Misuse of aggregate: avg()" is returned.
> Any thought?
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
> 
> 
> 
> 
> 
> 
> 
> ___
> 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

[sqlite] Difference between 3.7.11 and 3.7.13

2012-06-22 Thread Marco Bambini 
Consider the following example:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;

With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse 
of aggregate: avg()" is returned.
Any thought?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs







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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Keith Medcalf 

Either both of the following should execute, or neither should.  And they 
should both produce the same error message if they are not executable.

SELECT max((select avg(x) FROM t2)) FROM t1;
SELECT max((SELECT x FROM t2 limit 1)) FROM t1;

In other words, either you can access columns from the outer query inside a 
correlated subquery, or you cannot.  Since the latter is acceptable but not the 
former, there is a bug.

Similarly, these two:

sqlite> SELECT max((SELECT avg(x+y) FROM t2)) FROM t1;
Error: misuse of aggregate: avg()
sqlite> SELECT max((SELECT x+y FROM t2)) FROM t1;

sqlite> insert into t1 values (1), (2), (3);
sqlite> insert into t2 values (1), (2), (3);
sqlite> SELECT max((SELECT x+y FROM t2)) FROM t1;
4
sqlite> SELECT max((SELECT avg(x+y) FROM t2)) FROM t1;
Error: misuse of aggregate: avg()

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Thursday, 21 June, 2012 14:10
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Difference between 3.7.11 and 3.7.13
> 
> On 6/21/2012 3:52 PM, Pavel Ivanov wrote:
> >>>> CREATE TABLE t1(x);
> >>>> CREATE TABLE t2(y);
> >>>> SELECT max((SELECT avg(x) FROM t2)) FROM t1;
> >
> > So you are saying that behavior of such query should be equivalent to
> > "SELECT max(x) FROM t1"?
> 
> Not quite, but you get the idea. I think "select avg(SomeConstExpr) from
> t2" would return NULL rather than SomeConstExpr if t2 is empty. So it
> should be equivalent to
> 
> select max(case when (select count(*) from t2) == 0 then null else x
> end) from t1;
> --
> 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] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Igor Tandetnik 

On 6/21/2012 3:52 PM, Pavel Ivanov wrote:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;


So you are saying that behavior of such query should be equivalent to
"SELECT max(x) FROM t1"?


Not quite, but you get the idea. I think "select avg(SomeConstExpr) from 
t2" would return NULL rather than SomeConstExpr if t2 is empty. So it 
should be equivalent to


select max(case when (select count(*) from t2) == 0 then null else x 
end) from t1;

--
Igor Tandetnik

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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Pavel Ivanov 
On Thu, Jun 21, 2012 at 3:41 PM, Igor Tandetnik  wrote:
> On 6/21/2012 12:17 PM, Pavel Ivanov wrote:
>>
>> On Thu, Jun 21, 2012 at 11:47 AM, Marco Bambini  wrote:
>>>
>>> Consider the following example:
>>>
>>> CREATE TABLE t1(x);
>>> CREATE TABLE t2(y);
>>> SELECT max((SELECT avg(x) FROM t2)) FROM t1;
>>>
>>> With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error
>>> "Misuse of aggregate: avg()" is returned.
>>> Any thought?
>>
>>
>> 3.7.11 had a bug, 3.7.13 fixed it. Your query is invalid, t2 doesn't
>> have column named x.
>
>
> But t1 does, and a nested select should be able to access it. I don't see
> why an aggregate function can't be applied to any expression, even one that
> happens to be constant across all rows of the table.
>
> I don't understand what makes this query invalid. Pointless, yes, but why
> invalid?

So you are saying that behavior of such query should be equivalent to
"SELECT max(x) FROM t1"? I didn't think about it like that... Probably
you are right.

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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Igor Tandetnik 

On 6/21/2012 12:17 PM, Pavel Ivanov wrote:

On Thu, Jun 21, 2012 at 11:47 AM, Marco Bambini  wrote:

Consider the following example:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;

With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse of 
aggregate: avg()" is returned.
Any thought?


3.7.11 had a bug, 3.7.13 fixed it. Your query is invalid, t2 doesn't
have column named x.


But t1 does, and a nested select should be able to access it. I don't 
see why an aggregate function can't be applied to any expression, even 
one that happens to be constant across all rows of the table.


I don't understand what makes this query invalid. Pointless, yes, but 
why invalid?

--
Igor Tandetnik

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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Pavel Ivanov 
On Thu, Jun 21, 2012 at 11:47 AM, Marco Bambini  wrote:
> Consider the following example:
>
> CREATE TABLE t1(x);
> CREATE TABLE t2(y);
> SELECT max((SELECT avg(x) FROM t2)) FROM t1;
>
> With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error 
> "Misuse of aggregate: avg()" is returned.
> Any thought?

3.7.11 had a bug, 3.7.13 fixed it. Your query is invalid, t2 doesn't
have column named x.


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

[sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Marco Bambini 
Consider the following example:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;

With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse 
of aggregate: avg()" is returned.
Any thought?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs

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