Re: [sqlite] BUG: Aggregate functions in subqueries

[Petite Abeille]

On Sep 15, 2014, at 4:48 PM, Richard Hipp  wrote:

> On Sun, Sep 14, 2014 at 12:18 AM, Lea Verou  wrote:
> 
>> Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM
>> table return the value of y from the same row that contains the maximum x
>> value. However, this:
>> 
>> select y from (SELECT max(x), y FROM table);
>> 
>> would not return the same y rows. This would work as expected:
>> 
>> select m, y from (SELECT max(x) as m, y FROM table);
>> 
> 
> I'm not sure if this qualifies as a "bug" or not, since the behavior is
> unspecified in the official documentation.  Nevertheless, it is now fixed
> on trunk.

Considering that the original query is non-sensical to start with, not quite 
sure what’s there to fix in the first place. Aside, of course, from raising an 
exception.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Aggregate functions in subqueries

[Richard Hipp]

On Sun, Sep 14, 2014 at 12:18 AM, Lea Verou  wrote:

> Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM
> table return the value of y from the same row that contains the maximum x
> value. However, this:
>
> select y from (SELECT max(x), y FROM table);
>
> would not return the same y rows. This would work as expected:
>
> select m, y from (SELECT max(x) as m, y FROM table);
>

I'm not sure if this qualifies as a "bug" or not, since the behavior is
unspecified in the official documentation.  Nevertheless, it is now fixed
on trunk.


-- 
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: Aggregate functions in subqueries

[Richard Hipp]

On Sun, Sep 14, 2014 at 11:47 AM, Keith Medcalf  wrote:

>
> Yes, the max(y) operation is flattened out of the query probably because
> it is not referenced anywhere and the optimizer does not see that it is
> performing any useful function.  There is still only one row returned,
> however, because the same accumulator is used to collect the result, it
> just operates on all rows instead of only those which set the max() return
> value.
>

An accurate and succinct summary of the issue.  The question now: what to
do about it.  I'll save that question for a workday...

-- 
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: Aggregate functions in subqueries

[Keith Medcalf]

Yes, the max(y) operation is flattened out of the query probably because it is 
not referenced anywhere and the optimizer does not see that it is performing 
any useful function.  There is still only one row returned, however, because 
the same accumulator is used to collect the result, it just operates on all 
rows instead of only those which set the max() return value.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Lea Verou
>Sent: Saturday, 13 September, 2014 22:19
>To: sqlite-users@sqlite.org
>Subject: [sqlite] BUG: Aggregate functions in subqueries
>
>Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM
>table return the value of y from the same row that contains the maximum x
>value. However, this:
>
>select y from (SELECT max(x), y FROM table);
>
>would not return the same y rows. This would work as expected:
>
>select m, y from (SELECT max(x) as m, y FROM table);
>
>[1]: http://www.sqlite.org/changes.html#version_3_7_11
>___
>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] BUG: Aggregate functions in subqueries

[Zsbán Ambrus]

On 9/14/14, Lea Verou  wrote:
> Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM
> table return the value of y from the same row that contains the maximum x
> value. However, this:

Hello!  I don't think this is a bug.  The documentation for the SELECT
statement at "http://sqlite.org/lang_select.html; says:

> If the SELECT statement is an aggregate query without a GROUP BY clause, then 
> [...] Each non-aggregate expression in the result-set is evaluated once for 
> an arbitrarily selected row of the dataset. The same arbitrarily selected row 
> is used for each non-aggregate expression.

This applies to your query, so y will return the value y from any row.
If it happens to return the value where x is maximal, that is an
accident, and might depend on what indexes you have or how sqlite
chooses to optimize your query.  You have no reason to trust sqlite to
choose any particular column.

If you would definitely like to get the value of y from the row where
x is maximal, I recommend a query like this:

  SELECT x, y FROM sometable ORDER BY x DESC LIMIT 1;

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

Re: [sqlite] BUG: Aggregate functions in subqueries

[Kees Nuyt]

On Sun, 14 Sep 2014 00:18:34 -0400, Lea Verou 
wrote:

> Per the 3.7.11 changelog [1], queries of the form 
> SELECT max(x), y FROM table return the value of y
> from the same row that contains the maximum x value.
> However, this:
> select y from (SELECT max(x), y FROM table);
> would not return the same y rows. This would work as expected:
> select m, y from (SELECT max(x) as m, y FROM table);
>
> [1]: http://www.sqlite.org/changes.html#version_3_7_11

Confirmed.
However, SELECT max(x), y FROM t1 is not proper SQL, as a
non-aggregate expression (in this case column y) is used that
doesn't appear in a GROUP BY clause. SQLite allows it, and in
the simplest of statements it returns a value of y from one of
the rows that matches x=max(x), as promised.
Apparently, in SELECT y FROM (SELECT max(x), y FROM t1); it is
optimized out somehow.

Workaround:
SELECT y FROM t1 WHERE x=(SELECT max(x) FROM t1);
which may return multiple rows, so you'd have to use 
LIMIT 1 or max(y) or min(y), whichever is most appropriate for
your use case.

I agree that the result 
is quite unexpected.

Test script and results on 
3.8.7 2014-09-06 17:06:13
ad7063aa1a0db32cdbe71815545b2edca57d3bcc and 
3.8.7 2014-09-12 20:30:59
b332a84d5154f70f3197537df4af243eaebbb011:

CREATE TABLE t1 (x INTEGER, y INTEGER PRIMARY KEY);
INSERT INTO t1 (x,y) VALUES (1,1);
INSERT INTO t1 (x,y) VALUES (2,2);
INSERT INTO t1 (x,y) VALUES (3,3);
INSERT INTO t1 (x,y) VALUES (3,4);
INSERT INTO t1 (x,y) VALUES (2,5);
SELECT * FROM t1;
1   1
2   2
3   3
3   4
2   5

SELECT max(x), y FROM t1;
3   3

SELECT y FROM (SELECT max(x), y FROM t1);
5

SELECT m, y FROM (SELECT max(x) as m, y FROM t1);
3   3

SELECT y FROM t1 WHERE x=(SELECT max(x) FROM t1);
3
4

-- 
Groet,

Kees Nuyt

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

[sqlite] BUG: Aggregate functions in subqueries

[Lea Verou]

Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM table 
return the value of y from the same row that contains the maximum x value. 
However, this:

select y from (SELECT max(x), y FROM table);

would not return the same y rows. This would work as expected:

select m, y from (SELECT max(x) as m, y FROM table);

[1]: http://www.sqlite.org/changes.html#version_3_7_11
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users