Description:
Problem Description:
When the session uses NO_BACKSLASH_ESCAPES SQL mode and derived_condition_pushdown is enabled in optimizer_switch, the REGEXP filter from the outer query produces incorrect matching results after being pushed down into a CTE/derived table that contains JSON_TABLE.
Strings that should match the regex pattern return no matches when condition pushdown is active. The query returns correct results when derived_condition_pushdown is disabled.
Root cause hypothesis:
During the condition pushdown process, the escape handling logic for the string pattern does not properly inherit the session-level NO_BACKSLASH_ESCAPES rule. This causes the regex pattern to be parsed incorrectly, resulting in wrong matching behavior.
How to repeat:
Steps to reproduce:
1. Set session parameters:
SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES';
SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
SET SESSION optimizer_switch = 'derived_condition_pushdown=on';
2. Run the test query:
WITH u AS (
SELECT s
FROM JSON_TABLE('["a"]', '$[*]'
COLUMNS (s VARCHAR(9) PATH '$')) AS j
UNION ALL
SELECT 'x' WHERE 0
)
SELECT s
FROM u
WHERE s REGEXP '\\w';
Observed result:
Query returns 0 rows.
Expected result:
Query returns 1 row with value 'a'.
The character 'a' is a word character and should match the \w regex pattern.
Verification:
Run SET SESSION optimizer_switch = 'derived_condition_pushdown=off'; and re-execute the query. It correctly returns 1 row 'a', confirming that the issue is caused by the condition pushdown logic.
Suggested fix:
Fix the string semantics propagation logic in derived condition pushdown.
Ensure that when pushing down string conditions such as REGEXP, the session-level NO_BACKSLASH_ESCAPES escape rules are correctly inherited, so that the regex parsing semantics remain identical before and after condition pushdown.
Description: Problem Description: When the session uses NO_BACKSLASH_ESCAPES SQL mode and derived_condition_pushdown is enabled in optimizer_switch, the REGEXP filter from the outer query produces incorrect matching results after being pushed down into a CTE/derived table that contains JSON_TABLE. Strings that should match the regex pattern return no matches when condition pushdown is active. The query returns correct results when derived_condition_pushdown is disabled. Root cause hypothesis: During the condition pushdown process, the escape handling logic for the string pattern does not properly inherit the session-level NO_BACKSLASH_ESCAPES rule. This causes the regex pattern to be parsed incorrectly, resulting in wrong matching behavior. How to repeat: Steps to reproduce: 1. Set session parameters: SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES'; SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin; SET SESSION optimizer_switch = 'derived_condition_pushdown=on'; 2. Run the test query: WITH u AS ( SELECT s FROM JSON_TABLE('["a"]', '$[*]' COLUMNS (s VARCHAR(9) PATH '$')) AS j UNION ALL SELECT 'x' WHERE 0 ) SELECT s FROM u WHERE s REGEXP '\\w'; Observed result: Query returns 0 rows. Expected result: Query returns 1 row with value 'a'. The character 'a' is a word character and should match the \w regex pattern. Verification: Run SET SESSION optimizer_switch = 'derived_condition_pushdown=off'; and re-execute the query. It correctly returns 1 row 'a', confirming that the issue is caused by the condition pushdown logic. Suggested fix: Fix the string semantics propagation logic in derived condition pushdown. Ensure that when pushing down string conditions such as REGEXP, the session-level NO_BACKSLASH_ESCAPES escape rules are correctly inherited, so that the regex parsing semantics remain identical before and after condition pushdown.