Re: [sqlite] special aggregate feature vs query optimization

[Igor Tandetnik]

On 1/10/2013 12:04 PM, Eduardo Morras wrote:

Select max(x), y from t will return 2 colums and n rows


Not true. The query uses an aggregate function, so it will return one 
row per group. There's no GROUP BY clause, so the whole table is one 
group. Ergo, this query will always return exactly one row. Try it, see 
for yourself.

--
Igor Tandetnik

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

Re: [sqlite] special aggregate feature vs query optimization

[Simon Slavin]

On 10 Jan 2013, at 5:39pm, Simon Davies  wrote:

> SQLite version 3.7.11 2012-03-20 11:35:50

Your test is good but the way this worked changed recently.  Can you download 
an up-to-date version of the shell tool from



and try on that version ?

Simon.

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

Re: [sqlite] special aggregate feature vs query optimization

[Simon Davies]

On 10 January 2013 17:04, Eduardo Morras  wrote:
> On Thu, 10 Jan 2013 14:53:52 +0100
> E.Pasma  wrote:
>
>> Hello,
>>
>> A query of the form: "SELECT max(x), y FROM table" returns the value
>> of y on the same row that contains the maximum x value.
>
> True
>
>> I just want to point to a construction where one would expect this to
>> work however it does not. I tried a query that returns only the value
>> of y and intuitively wrote:
>>
>> select y from (select max(x), y from t);
>>
>
> Select max(x), y from t will return 2 colums and n rows, the first column 
> with the same value, the maximum of x in table t, the second column all t.y 
> values. Something like this:
>
> max(x) |   y
> -
> 500|  5
> 500|  3
> 500|  9
> 500|  2
> 500|  31
> 500|  1
> 500|  86
> 500|  64

Not what I see...

>
> From this result table, you are doing select y from (result table) and 
> getting only the y values as you expected
>
>   y
> -
>   5
>   3
>   9
>   2
>   31
>   1
>   86
>   64
>
>> This however no longer returns the value of y corresponding to the
>> maximum x.
>
> For me it works, it shows all y from t. Perhaps i have misunderstood something

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t( x integer, y integer );
sqlite>
sqlite>
sqlite> insert into t values( 1, 2 );
sqlite> insert into t values( 4, 3 );
sqlite> insert into t values( 10, 5 );
sqlite> insert into t values( 2, 6 );
sqlite>
sqlite> select max(x),y from t;
10|5
sqlite> select x,y from (select max(x) x, y from t);
10|5
sqlite> select y from (select max(x) x, y from t);
6

This does not look right...

>
>>
>> It looks a consequence of query optimization. The query satisfies all
>> conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html
>>   . The max(x) column is then eliminated.
>>
>> Hope this is useful to know for who is using the feature.
>>
>> EPasma
>>
>
> ---   ---
> Eduardo Morras 

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

Re: [sqlite] special aggregate feature vs query optimization

[Eduardo Morras]

On Thu, 10 Jan 2013 14:53:52 +0100
E.Pasma  wrote:

> Hello,
> 
> this mail is about the aggregate feature that was unveiled in the  
> release log of version 3.17.11, http://www.sqlite.org/releaselog/3_7_11.html 
>   :
> 
> A query of the form: "SELECT max(x), y FROM table" returns the value  
> of y on the same row that contains the maximum x value.

True

> I just want to point to a construction where one would expect this to  
> work however it does not. I tried a query that returns only the value  
> of y and intuitively wrote:
> 
> select y from (select max(x), y from t);
> 

Select max(x), y from t will return 2 colums and n rows, the first column with 
the same value, the maximum of x in table t, the second column all t.y values. 
Something like this:

max(x) |   y 
-
500|  5
500|  3
500|  9
500|  2
500|  31
500|  1
500|  86
500|  64


>From this result table, you are doing select y from (result table) and getting 
>only the y values as you expected

  y
-
  5
  3
  9
  2
  31
  1
  86
  64

> This however no longer returns the value of y corresponding to the  
> maximum x.

For me it works, it shows all y from t. Perhaps i have misunderstood something

> 
> It looks a consequence of query optimization. The query satisfies all  
> conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html 
>   . The max(x) column is then eliminated.
> 
> Hope this is useful to know for who is using the feature.
> 
> EPasma
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] special aggregate feature vs query optimization

[E . Pasma]

Hello,

this mail is about the aggregate feature that was unveiled in the  
release log of version 3.17.11, http://www.sqlite.org/releaselog/3_7_11.html 
 :


A query of the form: "SELECT max(x), y FROM table" returns the value  
of y on the same row that contains the maximum x value.


I just want to point to a construction where one would expect this to  
work however it does not. I tried a query that returns only the value  
of y and intuitively wrote:


select y from (select max(x), y from t);

This however no longer returns the value of y corresponding to the  
maximum x.


It looks a consequence of query optimization. The query satisfies all  
conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html 
 . The max(x) column is then eliminated.


Hope this is useful to know for who is using the feature.

EPasma

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