Beta-testing success story! Was: The upcoming 3.23.0 release

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

Beta-testing success story! Was: The upcoming 3.23.0 release

Richard Hipp-3
Thanks to E.Pasma for finding a logic error in the new LEFT JOIN
strength reduction optimization!

A new pre-release snapshot with this bug fixed has now been uploaded
to the https://sqlite.org/download.html page.  Please, everybody,
continue testing!

The LEFT JOIN strength reduction optimization changes a LEFT JOIN into
an ordinary JOIN if any column of the right table of the LEFT JOIN is
used in a way that requires the value to be non-NULL.  The prover that
checks this condition was mistakenly assuming that a CASE expression
that contained a NULL value would always have a NULL answer.  This is
obviously wrong when you think about it for half a second, but I
missed this case when coding up the prover.  E.Pasma found a query
that exercises that case, however.

A simplified example:

CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,2),(3,4);
CREATE TABLE t2(x);
SELECT *
  FROM t1 LEFT JOIN t2
 WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;

Needless to say, new test cases have been added to SQLite's test
suites to make sure this particular mistake never happens again.  But
there are plenty of other potential mistakes out there, so do not
slack up on your testing, please!

On 3/24/18, E.Pasma <[hidden email]> wrote:

> 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
>


--
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: Beta-testing success story! Was: The upcoming 3.23.0 release

Olivier Mascia
> Le 24 mars 2018 à 14:35, Richard Hipp <[hidden email]> a écrit :
>
> A new pre-release snapshot with this bug fixed has now been uploaded
> to the https://sqlite.org/download.html page.  Please, everybody,
> continue testing!

If I'm not mistaken, a benign warning just appeared on line 185432 of sqlite3.c (amalgamation from sqlite-snapshot-201803241324.tar.gz, using Visual Studio 2017 compiler: "warning C4267: 'function': conversion from 'size_t' to 'int', possible loss of data". This is the usual one related to strlen returning size_t not int.

Both previous warnings about chmod/_chmod and unlink/_unlink have been taken care of: thanks!

--
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