Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
None
-
None
-
OS: Ubuntu 24.04
DBMS: MariaDB
Docker image: mariadb:11.8.5
Image ID: bfe9184ea9e5
Description
Summary:
When using the derived_merge optimizer option in MariaDB, the query results are consistent for both the original and transformed queries. However, when derived_merge is disabled, the results become inconsistent. Specifically, the transformed query with a CONCAT function combined with a NOT IN condition in a subquery returns no rows, with the error message "Impossible WHERE noticed after reading const tables".
Description:
Expected Behavior: The original and transformed queries should return consistent results, regardless of the derived_merge optimizer setting.
Actual Behavior:
(1) When derived_merge=on, both the original and transformed queries return the same result, as expected.
(2) When derived_merge=off, the results become inconsistent. The original query returns one row, but the transformed query returns zero rows, accompanied by the error message "Impossible WHERE noticed after reading const tables".
Minimal Reproduction Steps
CREATE OR REPLACE TABLE t0(c0 REAL UNSIGNED ) engine=Aria; |
SET SESSION optimizer_switch = 'derived_merge=off'; -- if u change 'on', the second query result is one row. |
INSERT INTO t0 VALUES (0); |
|
|
SELECT t0.c0 FROM t0 WHERE CONCAT((- ''), (401286554 NOT IN (t0.c0)));-- cardinality: 1 |
|
|
SELECT ref0 FROM (SELECT t0.c0 AS ref0, CONCAT((- ''), (401286554 NOT IN (t0.c0))) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 0 |
mysql> EXPLAIN FORMAT=JSON SELECT t0.c0 FROM t0 WHERE CONCAT((- ''), (401286554 NOT IN (t0.c0)));-- cardinality: 1 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| EXPLAIN |
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| {
|
"query_block": { |
"select_id": 1, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t0", |
"access_type": "system", |
"rows": 1, |
"filtered": 100 |
}
|
}
|
]
|
}
|
} |
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
mysql> EXPLAIN FORMAT=JSON SELECT ref0 FROM (SELECT t0.c0 AS ref0, CONCAT((- ''), (401286554 NOT IN (t0.c0))) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 0 |
+--------------------------------------------------------------------------------------------------------------------------------------------+ |
| EXPLAIN |
|
+--------------------------------------------------------------------------------------------------------------------------------------------+ |
| {
|
"query_block": { |
"select_id": 1, |
"table": { |
"message": "Impossible WHERE noticed after reading const tables" |
}
|
}
|
} |
|
+--------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set, 2 warnings (0.00 sec) |
|