Unexpected change of query behaviour due to optimization in 2.23.0

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

Unexpected change of query behaviour due to optimization in 2.23.0

Raphael Michel
Hello,

the recently-introduced change "The LEFT JOIN Strength Reduction
Optimization"[1] lead to a critical change in the behaviour of a very
simple query in our application.

The query involves two tables, "pretixbase_cartposition" and
"pretixbase_voucher". Both tables have a primary key called "id" and
the "pretixbase_cartposition" has a field "voucher_id" with a foreign
key to the "id" column of "pretixbase_voucher". This foreign key is
NULLable.

Our query looked like this:

    SELECT "pretixbase_cartposition"."id"
    FROM "pretixbase_cartposition"
    LEFT OUTER JOIN "pretixbase_voucher"
    ON (
        "pretixbase_cartposition"."voucher_id" =
        "pretixbase_voucher"."id"
    )
    WHERE NOT (
        "pretixbase_cartposition"."voucher_id" IS NOT NULL AND
        "pretixbase_voucher"."block_quota" = 1
    )

Looking at the changelog,

> In particular, if any column in the right-hand
> table of the LEFT JOIN must be non-NULL in order for the WHERE clause
> to be true, then the LEFT JOIN is demoted to an ordinary JOIN

we expected that the query would not be affected since the WHERE clause
can in fact be true when the right-hand table is NULL and the query
returned correct results before upgrading to 2.23.0.

In our test data set, the foreign key is NULL on all existing rows.
Before 2.23.0, all rows have been returned. With 2.23.0, no rows are
returned.

If we restructure the query to the logically equivalent

    SELECT "pretixbase_cartposition"."id"
    FROM "pretixbase_cartposition"
    LEFT OUTER JOIN "pretixbase_voucher"
    ON (
        "pretixbase_cartposition"."voucher_id" =
        "pretixbase_voucher"."id"
    )
    WHERE
        "pretixbase_cartposition"."voucher_id" IS NULL
        OR NOT("pretixbase_voucher"."block_quota" = 1)

it returns all rows again. However, if we hadn't noticed this through
our test suite, this change would silently have had critical impact on
our business operations which is not at all expected from the changelog.
The changelog explicitly states:

> The prover that determines whether any column of the right-hand table
> of a LEFT JOIN must be non-NULL in the WHERE clause is imperfect. It
> sometimes returns a false negative. In other words, it sometimes
> fails to reduce the strength of a LEFT JOIN when doing so was in fact
> possible.

However, if I'm not mistaking, what I'm experiencing here is not a
false negative (optimizer not optimizing although possible) but a false
negative (optimizer optimizing although not possible).

I'd be happy for your feedback on if this is intended or indeed a bug.

Best
Raphael

[1] https://sqlite.org/optoverview.html#leftjoinreduction

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

attachment0 (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected change of query behaviour due to optimization in 2.23.0

Raphael Michel
Am Tue, 10 Apr 2018 12:55:13 +0200
schrieb Raphael Michel <[hidden email]>:
> However, if I'm not mistaking, what I'm experiencing here is not a
> false negative (optimizer not optimizing although possible) but a
> false negative (optimizer optimizing although not possible).

Errata: Of course, I meant false positive in the second part of the
sentence.

Sorry for the noise.

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

attachment0 (849 bytes) Download Attachment