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
pgpcTIHrIjmUc.pgp
Description: Digitale Signatur von OpenPGP
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
