The upcoming 3.23.0 release

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

The upcoming 3.23.0 release

Richard Hipp-3
SQLite version 3.23.0 will probably be released soon, in early April.
For a summary of changes see
https://sqlite.org/draft/releaselog/3_23_0.html

Please download the latest Pre-release Snapshot
(https://sqlite.org/download.html) and test out the latest SQLite in
your applications.  Report any issues, either to this mailing list, or
directly to me at [hidden email].

All of our regression tests are passing with 100% branch coverage.
However, passing over 100 million test cases do not guarantee that all
is well, since developers who use SQLite tend to be very creative and
wind up using SQLite in ways that we never thought to test.  Hence,
your independent verification is helpful to us and much appreciated.

The release checklist for 3.23.0 is found at

    https://www.sqlite.org/checklists/3230000/index

We have not yet frozen the code nor started the release checklist.
But we will do that soon. You can follow our progress on the
checklist.  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
|

Re: The upcoming 3.23.0 release

Simon Slavin-3
On 22 Mar 2018, at 7:09pm, Richard Hipp <[hidden email]> wrote:

> For a summary of changes see
> https://sqlite.org/draft/releaselog/3_23_0.html

<https://sqlite.org/draft/c3ref/deserialize.html>

"causes the database connection D to disconnection from database S"

<https://sqlite.org/draft/optoverview.html#omitnoopjoin>

Wording changes case from "is" in item 1 to "must" in items 2 and 3.

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

Re: The upcoming 3.23.0 release

Olivier Mascia
In reply to this post by Richard Hipp-3
> Le 22 mars 2018 à 20:09, Richard Hipp <[hidden email]> a écrit :
>
> ...
> Please download the latest Pre-release Snapshot
> (https://sqlite.org/download.html) and test out the latest SQLite in
> your applications.  Report any issues, either to this mailing list, or
> directly to me at [hidden email].

I am of course not really much concerned by this detail — yet I'm writing an email about it :).  Since 3.22 and now with 3.23 snapshot as of today, latest updates of Visual Studio 2017 report this warning:

"shell.c(2377): warning C4996: 'chmod': The POSIX name for this item is deprecated. Instead, use the ISO C and C++ conformant name: _chmod."

It occurs at two places in shell.c, and the same thing occurs once for unlink (_unlink).

In their latest versions of the documentation about their UCRT they say:

> The C++ standard reserves names that begin with an underscore in the global namespace to the implementation. Because the POSIX functions are in the global namespace, but are not part of the standard C runtime library, the Microsoft-specific implementations of these functions have a leading underscore. For portability, the UCRT also supports the default names, but the Visual C++ compiler issues a deprecation warning when code that uses them is compiled. Only the default POSIX names are deprecated, not the functions. To suppress the warning, define _CRT_NONSTDC_NO_WARNINGS before including any headers in code that uses the original POSIX names.

See: https://docs.microsoft.com/en-us/cpp/c-runtime-library/compatibility

Adding a #define _CRT_NONSTDC_NO_WARNINGS to shell.c might indeed be a nice way to go.

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: The upcoming 3.23.0 release

Eric Grange
> Add the LEFT JOIN strength reduction optimization that converts a LEFT
JOIN into an ordinary JOIN

A question on this one: I have been using LEFT JOIN for many years (and not
juste in SQLIte) in cases where a JOIN could have been used as a way to
"hint" query optimizers which tables to scan first, typically in cases
where the WHERE is too complex or misleading for the optimizer (ie.
whenever index statistics paint a wrong picture, for whatever specific
reasons).
I can see that change "breaking" optimized queries back to their original
performance.

Is there a planned "QUERY PLAN" or lightweight hinting is in the works?
The current suggestions in the doc are to use cross joins or stat3/stat4
tables, but both are quite more complex to maintain, while just introducing
a "LEFT" before a "JOIN" in a particular query used to be enough :)

On Fri, Mar 23, 2018 at 1:06 AM, Olivier Mascia <[hidden email]> wrote:

> > Le 22 mars 2018 à 20:09, Richard Hipp <[hidden email]> a écrit :
> >
> > ...
> > Please download the latest Pre-release Snapshot
> > (https://sqlite.org/download.html) and test out the latest SQLite in
> > your applications.  Report any issues, either to this mailing list, or
> > directly to me at [hidden email].
>
> I am of course not really much concerned by this detail — yet I'm writing
> an email about it :).  Since 3.22 and now with 3.23 snapshot as of today,
> latest updates of Visual Studio 2017 report this warning:
>
> "shell.c(2377): warning C4996: 'chmod': The POSIX name for this item is
> deprecated. Instead, use the ISO C and C++ conformant name: _chmod."
>
> It occurs at two places in shell.c, and the same thing occurs once for
> unlink (_unlink).
>
> In their latest versions of the documentation about their UCRT they say:
>
> > The C++ standard reserves names that begin with an underscore in the
> global namespace to the implementation. Because the POSIX functions are in
> the global namespace, but are not part of the standard C runtime library,
> the Microsoft-specific implementations of these functions have a leading
> underscore. For portability, the UCRT also supports the default names, but
> the Visual C++ compiler issues a deprecation warning when code that uses
> them is compiled. Only the default POSIX names are deprecated, not the
> functions. To suppress the warning, define _CRT_NONSTDC_NO_WARNINGS before
> including any headers in code that uses the original POSIX names.
>
> See: https://docs.microsoft.com/en-us/cpp/c-runtime-library/compatibility
>
> Adding a #define _CRT_NONSTDC_NO_WARNINGS to shell.c might indeed be a
> nice way to go.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> _______________________________________________
> 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
|

Re: The upcoming 3.23.0 release

Clemens Ladisch
Eric Grange wrote:
>> Add the LEFT JOIN strength reduction optimization that converts a LEFT
>> JOIN into an ordinary JOIN
>
> A question on this one: I have been using LEFT JOIN for many years (and not
> juste in SQLIte) in cases where a JOIN could have been used as a way to
> "hint" query optimizers which tables to scan first.
>
> Is there a planned "QUERY PLAN" or lightweight hinting is in the works?

No, because it's already there:
http://www.sqlite.org/lang_select.html#crossjoin


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

Re: [EXTERNAL] Re: The upcoming 3.23.0 release

Hick Gunter
In reply to this post by Eric Grange
Replace LEFT with CROSS in all cases that you used LEFT as a hint. Not at all complex, apart from you having to discern between "hint" and "necessities".

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Eric Grange
Gesendet: Freitag, 23. März 2018 09:07
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] The upcoming 3.23.0 release

> Add the LEFT JOIN strength reduction optimization that converts a LEFT
JOIN into an ordinary JOIN

A question on this one: I have been using LEFT JOIN for many years (and not juste in SQLIte) in cases where a JOIN could have been used as a way to "hint" query optimizers which tables to scan first, typically in cases where the WHERE is too complex or misleading for the optimizer (ie.
whenever index statistics paint a wrong picture, for whatever specific reasons).
I can see that change "breaking" optimized queries back to their original performance.

Is there a planned "QUERY PLAN" or lightweight hinting is in the works?
The current suggestions in the doc are to use cross joins or stat3/stat4 tables, but both are quite more complex to maintain, while just introducing a "LEFT" before a "JOIN" in a particular query used to be enough :)

On Fri, Mar 23, 2018 at 1:06 AM, Olivier Mascia <[hidden email]> wrote:



___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The upcoming 3.23.0 release

David Raymond
In reply to this post by Richard Hipp-3
2. Recognize TRUE and FALSE as constants. (For compatibility, if there exist columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.)
3. Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE.

On the documentation side of things I think
https://sqlite.org/draft/lang_expr.html
is gonna need a number of updates or additions to go over those a bit more. Including a reminder that [is true] is a new operator of two words which is a unary postfix operator, and that it isn't [is](the existing operator) [true](the new alias for 1)

sqlite> select 2 is true;
2 is true
1

sqlite> select 2 is 1;
2 is 1
0

sqlite> select true is 2;
true is 2
0

Hmm, my brain is sort of working, but not completely. Since [is true] is a unary postfix operator but we're respecting existing column names, how does "something is true" get parsed? Let's check... Looks like it prefers x [is] [true](column) over x [is true](postfix)

sqlite> create table foo (true boolean);

sqlite> insert into foo (true) values (false);

sqlite> select * from foo;
true
0

sqlite> select 1 is true;
1 is true
1

sqlite> select 1 is true from foo;
1 is true
0

sqlite> select 1, true, 1 is true from foo;
1|true|1 is true
1|0|0


Too much thinking, brain hurts now.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Thursday, March 22, 2018 3:09 PM
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite] The upcoming 3.23.0 release

SQLite version 3.23.0 will probably be released soon, in early April.
For a summary of changes see
https://sqlite.org/draft/releaselog/3_23_0.html

Please download the latest Pre-release Snapshot
(https://sqlite.org/download.html) and test out the latest SQLite in
your applications.  Report any issues, either to this mailing list, or
directly to me at [hidden email].

All of our regression tests are passing with 100% branch coverage.
However, passing over 100 million test cases do not guarantee that all
is well, since developers who use SQLite tend to be very creative and
wind up using SQLite in ways that we never thought to test.  Hence,
your independent verification is helpful to us and much appreciated.

The release checklist for 3.23.0 is found at

    https://www.sqlite.org/checklists/3230000/index

We have not yet frozen the code nor started the release checklist.
But we will do that soon. You can follow our progress on the
checklist.  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
|

Re: The upcoming 3.23.0 release

Richard Hipp-3
On 3/23/18, David Raymond <[hidden email]> wrote:
>
> sqlite> create table foo (true boolean);
>
> sqlite> insert into foo (true) values (false);
>

Then you get a non-empty set from:  SELECT * FROM foo WHERE true IS false;

Moral:  Don't use identifiers "true" or "false" as column names.
--
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
|

Re: The upcoming 3.23.0 release

David Raymond
"Moral:  Don't use identifiers "true" or "false" as column names."

Oh, I'm in full agreement there. But as you said: "developers who use SQLite tend to be very creative and wind up using SQLite in ways that we never thought to test." So I tried to think up some psychotic/pathological cases to see what it would do.

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

Re: The upcoming 3.23.0 release

R Smith-2
In reply to this post by David Raymond


On 2018/03/23 3:59 PM, David Raymond wrote:

> sqlite> create table foo (true boolean);
>
> sqlite> insert into foo (true) values (false);
>
> sqlite> select * from foo;
> true
> 0
>
> sqlite> select 1 is true;
> 1 is true
> 1
>
> sqlite> select 1 is true from foo;
> 1 is true
> 0
>
> sqlite> select 1, true, 1 is true from foo;
> 1|true|1 is true
> 1|0|0
>
>
> Too much thinking, brain hurts now.

Well brain-hurtz, yes, but it's just an exercise in linguistic semantics
juxtaposed with query semantics. Or perhaps it is "techno-poetry".  (If
the genre is new, I call naming-rights...)
You could as well have posed the query:

create table foo(blue, one);
insert into foo VALUES (1,true);
select blue, one from foo where one is blue or blue is true;
   --     blue     |      one
   -- ------------ | ------------
   --       1      |       1

Is the blue one true? or the true one blue? or is this the one true blue
in the foo?


We all remove unneeded complexity if it is better (and sometimes if it's
not better), but introducing willful obfuscation is not a thing I've
seen from too many programmers. I think we're safe from breaking
hysterical historical queries, but agree with needing it well-documented.

Cheers,
Ryan

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

Re: The upcoming 3.23.0 release

Simon Slavin-3
On 23 Mar 2018, at 3:09pm, R Smith <[hidden email]> wrote:

> select blue, one from foo where one is blue or blue is true;
>   --     blue     |      one
>   -- ------------ | ------------
>   --       1      |       1
>
> Is the blue one true? or the true one blue? or is this the one true blue in the foo?

You'll get mixed up, as you know
You're not sure what you know
So be sure when you test, test with care and great tact
And remember that coding's a balancing act
Work hard so your SQL is dextrous and deft
And make sure that your comments and docs are the best

All future SQLite documentation will be written by Dr Suess.

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

Re: The upcoming 3.23.0 release

Stephen Chrzanowski
If only all tech manuals were written like that, I'd be doing much MUCH
more entertaining things with my life.  Mind you, I love my job as is....

On Fri, Mar 23, 2018 at 1:14 PM, Simon Slavin <[hidden email]> wrote:

>
> You'll get mixed up, as you know
> You're not sure what you know
> So be sure when you test, test with care and great tact
> And remember that coding's a balancing act
> Work hard so your SQL is dextrous and deft
> And make sure that your comments and docs are the best
>
> All future SQLite documentation will be written by Dr Suess.
>
> Simon.
> _______________________________________________
> 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
|

Re: The upcoming 3.23.0 release

E.Pasma
In reply to this post by Richard Hipp-3
Dear Richard, the rather complicated script below used to print a tree:

node1|node2|node3|node4|node5
1||||
1|11|||
1|12|||
1|12|121||
1|13|||

and with yesterday's pre-release snapshot it does not yield any result.
A clue is this depends on the size of the query, not so much on the  
logic.
If you reduce the number of anticipated levels, by leaving out the  
last four lines, the output is alright.
Hope this causes no headache, E. Pasma

.version
SQLite 3.23.0 2018-03-22 12:00:43  
dd568c27b1d7656388ea5b4132cc0265aedd7348d265d8e8c7412b00b28a31aa
zlib version 1.2.3
gcc-4.0.1 (Apple Inc. build 5465)

create table node (node integer primary key, parent integer)
;
insert into node values (1,0),(11,1),(12,1),(13,1),(121,12)
;
create index node_parent on node (parent)
;
create table bit (bit integer primary key)
;
insert into bit values(0),(1)
;
SELECT  node1, node2, node3, node4, node5
/* level 1 is root */
FROM    (SELECT node AS node1 FROM node WHERE parent=0)
/* level 2 */
JOIN    (SELECT bit AS bit1 FROM bit)
LEFT JOIN (SELECT node AS node2, parent AS parent2 FROM node)
ON      bit1 AND parent2=node1
/* level 3 */
JOIN    (SELECT bit AS bit2 FROM bit)
ON      bit2<=CASE WHEN bit1 THEN CASE WHEN node2 THEN 1 ELSE -1 END  
ELSE 0 END
LEFT JOIN (SELECT node AS node3, parent AS parent3 FROM node)
ON      bit2 AND parent3=node2
/* level 4 */
JOIN    (SELECT bit AS bit3 FROM bit)
ON      bit3<=CASE WHEN bit2 THEN CASE WHEN node3 THEN 1 ELSE -1 END  
ELSE 0 END
LEFT JOIN (SELECT node AS node4, parent AS parent4 FROM node)
ON      bit3 AND parent4=node3
/* level 5 */
JOIN    (SELECT bit AS bit4 FROM bit)
ON      bit4<=CASE WHEN bit3 THEN CASE WHEN node4 THEN 1 ELSE -1 END  
ELSE 0 END
LEFT JOIN (SELECT node AS node5, parent AS parent5 FROM node)
ON      bit4 AND parent5=node4
;

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