Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8, 12.2.2
-
None
-
Docker: mariadb:12.2.2
Server version: 12.2.2-MariaDB-ubu2404
Source revision: d26a6f44c1f2119377e79a9540886c6d8c01472f
Description
Hi, I found a logic bug in MariaDB 12.2.2.
Two equivalent queries return different results.
We have two queries, namely query_A and query_B:
– query_A
SELECT u.c0, u.c0
FROM ((SELECT c0 FROM t1 WHERE c0 IS NULL) UNION (SELECT c0 FROM t0)) AS u;
– query_B
SELECT u.c0, u.c0 FROM (...) u WHERE ('test' <> u.c0)
UNION ALL
SELECT u.c0, u.c0 FROM (...) u WHERE NOT ('test' <> u.c0)
UNION ALL
SELECT u.c0, u.c0 FROM (...) u WHERE (('test' <> u.c0) IS NULL);
Expected results of query_B:
+----------+----------+
|
| c0 | c0 |
|
+----------+----------+
|
| NULL | NULL |
|
| 0 | 0 |
|
| 0.330266 | 0.330266 |
|
| 0.764851 | 0.764851 |
|
+----------+----------+
|
Actual Results of query_B:
+----------+----------+
|
| c0 | c0 |
|
+----------+----------+
|
| 0.330266 | 0.330266 |
|
| 0.764851 | 0.764851 |
|
| NULL | NULL |
|
+----------+----------+
|
How to repeat:
-- create table
|
DROP TABLE IF EXISTS `t0`; |
CREATE TABLE `t0` ( `c0` float DEFAULT NULL, UNIQUE KEY `c0` (`c0`), KEY `i0` (`c0`) ) CHARSET=utf8mb4; |
INSERT INTO `t0` VALUES (NULL),(NULL),(0),(0.330266),(0.764851); |
DROP TABLE IF EXISTS `t1`; |
CREATE TABLE `t1` ( `c0` text ) CHARSET=utf8mb4; |
INSERT INTO `t1` VALUES ('0.9394696888792586'),('B>'),('-697283540'),('-2100146179'); |
|
|
-- query A
|
SELECT u.c0, u.c0 |
FROM ((SELECT c0 FROM t1 WHERE c0 IS NULL) UNION (SELECT c0 FROM t0)) AS u; |
|
|
-- query B
|
SELECT u.c0, u.c0 FROM ((SELECT c0 FROM t1 WHERE c0 IS NULL) UNION (SELECT c0 FROM t0)) AS u WHERE ('test' <> u.c0) |
UNION ALL |
SELECT u.c0, u.c0 FROM ((SELECT c0 FROM t1 WHERE c0 IS NULL) UNION (SELECT c0 FROM t0)) AS u WHERE NOT ('test' <> u.c0) |
UNION ALL |
SELECT u.c0, u.c0 FROM ((SELECT c0 FROM t1 WHERE c0 IS NULL) UNION (SELECT c0 FROM t0)) AS u WHERE (('test' <> u.c0) IS NULL); |
|