Quantcast

Version 3.19.0 about to enter testing

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Version 3.19.0 about to enter testing

Richard Hipp-3
SQLite version 3.19.0 will soon enter testing with a target release
date of 2017-05-25.  If you have any issues or concerns, please bring
them up now.

   Change log:  https://sqlite.org/draft/releaselog/3_19_0.html
   Snapshot: https://sqlite.org/download.html
   Checklist: https://sqlite.org/checklists/3190000/index

The release will occur when the checklist goes all-green.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Version 3.19.0 about to enter testing

David Raymond
My brain might just not be working right today. Would you be so kind as to give an example for:

"Transfer any terms of the HAVING clause that use only columns mentioned in the GROUP BY clause over to the WHERE clause for faster processing."

?

Thanks


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Thursday, May 11, 2017 2:55 PM
To: sqlite-dev; General Discussion of SQLite Database
Subject: [sqlite] Version 3.19.0 about to enter testing

SQLite version 3.19.0 will soon enter testing with a target release
date of 2017-05-25.  If you have any issues or concerns, please bring
them up now.

   Change log:  https://sqlite.org/draft/releaselog/3_19_0.html
   Snapshot: https://sqlite.org/download.html
   Checklist: https://sqlite.org/checklists/3190000/index

The release will occur when the checklist goes all-green.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Version 3.19.0 about to enter testing

Dan Kennedy-4
On 05/12/2017 02:47 AM, David Raymond wrote:
> My brain might just not be working right today. Would you be so kind as to give an example for:
>
> "Transfer any terms of the HAVING clause that use only columns mentioned in the GROUP BY clause over to the WHERE clause for faster processing."

Say:

   CREATE TABLE t1(a,b);
   CREATE INDEX t1a ON t1(a);

then:

   SELECT * FROM t1 GROUP BY t1.a HAVING t1.a=?

may be rewritten as

   SELECT * FROM t1 WHERE t1.a=? GROUP BY t1.a

In which the filtering is done before the grouping instead of after it,
which will be faster. And because the filter uses the index, in this
case it loads less data from disk too.

Combined with SQLite's subquery flattening:

   https://www.sqlite.org/optoverview.html#flattening

it can effectively transform things like:

   SELECT x,y FROM (
     SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
   ) WHERE x BETWEEN 8888 AND 9999;

to

   SELECT x,y FROM (
     SELECT a AS x, sum(b) AS y FROM t1 WHERE a BETWEEN 8888 AND 9999 GROUP BY a
   );

as well.

Dan.






>
> ?
>
> Thanks
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
> Sent: Thursday, May 11, 2017 2:55 PM
> To: sqlite-dev; General Discussion of SQLite Database
> Subject: [sqlite] Version 3.19.0 about to enter testing
>
> SQLite version 3.19.0 will soon enter testing with a target release
> date of 2017-05-25.  If you have any issues or concerns, please bring
> them up now.
>
>     Change log:  https://sqlite.org/draft/releaselog/3_19_0.html
>     Snapshot: https://sqlite.org/download.html
>     Checklist: https://sqlite.org/checklists/3190000/index
>
> The release will occur when the checklist goes all-green.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Version 3.19.0 about to enter testing

David Raymond
Thank you,

I think my issue was that I didn't know HAVING could use a non-aggregate expression.

I'll do more reading tomorrow on that and bare columns in aggregate queries.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Dan Kennedy
Sent: Thursday, May 11, 2017 4:01 PM
To: [hidden email]
Subject: Re: [sqlite] Version 3.19.0 about to enter testing

On 05/12/2017 02:47 AM, David Raymond wrote:
> My brain might just not be working right today. Would you be so kind as to give an example for:
>
> "Transfer any terms of the HAVING clause that use only columns mentioned in the GROUP BY clause over to the WHERE clause for faster processing."

Say:

   CREATE TABLE t1(a,b);
   CREATE INDEX t1a ON t1(a);

then:

   SELECT * FROM t1 GROUP BY t1.a HAVING t1.a=?

may be rewritten as

   SELECT * FROM t1 WHERE t1.a=? GROUP BY t1.a

In which the filtering is done before the grouping instead of after it,
which will be faster. And because the filter uses the index, in this
case it loads less data from disk too.

Combined with SQLite's subquery flattening:

   https://www.sqlite.org/optoverview.html#flattening

it can effectively transform things like:

   SELECT x,y FROM (
     SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
   ) WHERE x BETWEEN 8888 AND 9999;

to

   SELECT x,y FROM (
     SELECT a AS x, sum(b) AS y FROM t1 WHERE a BETWEEN 8888 AND 9999 GROUP BY a
   );

as well.

Dan.






>
> ?
>
> Thanks
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
> Sent: Thursday, May 11, 2017 2:55 PM
> To: sqlite-dev; General Discussion of SQLite Database
> Subject: [sqlite] Version 3.19.0 about to enter testing
>
> SQLite version 3.19.0 will soon enter testing with a target release
> date of 2017-05-25.  If you have any issues or concerns, please bring
> them up now.
>
>     Change log:  https://sqlite.org/draft/releaselog/3_19_0.html
>     Snapshot: https://sqlite.org/download.html
>     Checklist: https://sqlite.org/checklists/3190000/index
>
> The release will occur when the checklist goes all-green.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...