Bug #111917 derived condition pushdown query with wrong result

• Submitted: 30 Jul 2023 5:35
• Modified: 31 Jul 2023 6:30
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.1.0, 8.0.34
• OS: Ubuntu (22.04)
• CPU Architecture: x86 (x86_64)
• Tags: Derived condition push down

[30 Jul 2023 5:35] Pedro Ferreira

Description:
Run these queries:

SET SESSION optimizer_switch='derived_condition_pushdown=on';
SELECT 1 FROM (SELECT b'1') y(y) WHERE (NULL || y.y) NOT LIKE 'a';
 -- 1 row
SET SESSION optimizer_switch='derived_condition_pushdown=off';
SELECT 1 FROM (SELECT b'1') y(y) WHERE (NULL || y.y) NOT LIKE 'a';
 -- empty

Both queries must output the same rows. It seems the 'derived_condition_pushdown=on' query is wrong.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

[31 Jul 2023 6:30] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and test case.

regards,
Umesh