Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.5
-
None
-
None
-
OS: Ubuntu 24.04
DBMS: MariaDB
Docker image: mariadb:11.8.5
Image ID: bfe9184ea9e5
Description
Summary
When running two queries with the same logic, one using an explicit WHERE clause and the other using a subquery with a conditional expression (IS NULL and ^ operator), the results are inconsistent. The first query, which directly uses the condition in the WHERE clause, returns no rows, while the second query, which uses the same condition in a subquery, returns one row. This issue appears to stem from how MariaDB handles the condition when used in a subquery.
Minimal Reproduction Steps
CREATE OR REPLACE TABLE t0(c0 REAL UNIQUE NOT NULL) engine=Aria; |
CREATE OR REPLACE TABLE t1 LIKE t0; |
|
|
INSERT INTO t0 VALUES (-447161518); |
INSERT INTO t1 VALUES (-1913732449); |
INSERT INTO t1 VALUES (-863037638); |
|
|
SELECT CONCAT(t0.c0, t1.c0) FROM t0, t1 WHERE ((+ t1.c0) >= (-1306572359 ^ (t1.c0 IS NULL)));-- cardinality: 0 |
SELECT ref0 FROM (SELECT CONCAT(t0.c0, t1.c0) AS ref0, ((+ t1.c0) >= (-1306572359 ^ (t1.c0 IS NULL))) AS ref1 FROM t0, t1) AS s WHERE ref1;-- cardinality: 1 |
Expected Behavior
mysql> SELECT CONCAT(t0.c0, t1.c0) FROM t0, t1 WHERE ((+ t1.c0) >= (-1306572359 ^ (t1.c0 IS NULL)));-- cardinality: 0 |
Empty set (0.00 sec) |
|
|
mysql> SELECT ref0 FROM (SELECT CONCAT(t0.c0, t1.c0) AS ref0, ((+ t1.c0) >= (-1306572359 ^ (t1.c0 IS NULL))) AS ref1 FROM t0, t1) AS s WHERE ref1;-- cardinality: 1 |
Empty set (0.00 sec) |
Actual Behavior
mysql> SELECT CONCAT(t0.c0, t1.c0) FROM t0, t1 WHERE ((+ t1.c0) >= (-1306572359 ^ (t1.c0 IS NULL)));-- cardinality: 0 |
Empty set (0.00 sec) |
|
|
mysql> SELECT ref0 FROM (SELECT CONCAT(t0.c0, t1.c0) AS ref0, ((+ t1.c0) >= (-1306572359 ^ (t1.c0 IS NULL))) AS ref1 FROM t0, t1) AS s WHERE ref1;-- cardinality: 1 |
+----------------------+ |
| ref0 |
|
+----------------------+ |
| -447161518-863037638 |
|
+----------------------+ |
1 row in set (0.00 sec) |
|
However, when I try "SET SESSION optimizer_switch = 'derived_merge=off';",both queries return empty set.