Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.1, 11.1.1, 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
-
Docker Hub's official MariaDB image
Description
When lateral derived optimization is applied to a join using <=>, NULL values are not joined as expected. This issue seems to have been present since MariaDB 10.3.1 when the lateral derived optimization was introduced.
How to reproduce:
CREATE DATABASE testdb;
|
USE testdb;
|
|
|
CREATE TABLE test1 (
|
num1 INT NULL,
|
KEY (num1)
|
);
|
INSERT INTO test1 VALUES (NULL), (1);
|
|
|
CREATE TABLE test2 (
|
num2 INT NULL,
|
KEY (num2)
|
);
|
INSERT INTO test2 VALUES (NULL), (2);
|
|
|
-- Set split_materialized to off to receive correct results
|
SET optimizer_switch='split_materialized=on';
|
|
|
WITH test AS (
|
SELECT num1
|
FROM test1
|
GROUP BY num1
|
)
|
SELECT test.num1, test2.num2
|
FROM test2
|
-- Both test and test2 have a NULL value, so this join should return one row.
|
INNER JOIN test ON test.num1 <=> test2.num2
|
-- test2.num2 is NULL for the only expected row, but this condition is here to
|
-- make the optimizer use lateral derived optimization. Removing this condition
|
-- causes the query to return correct results.
|
WHERE test2.num2 IS NULL;
|
Expected results (returned when running with split_materialized=off or after removing the WHERE):
+------+------+
|
| num1 | num2 |
|
+------+------+
|
| NULL | NULL |
|
+------+------+
|
1 row in set
|
Actual results:
Empty set
EXPLAIN output for the query with split_materialized enabled:
+------+-----------------+------------+------+---------------+------+---------+-------------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+-------------------+------+--------------------------+
|
| 1 | PRIMARY | test2 | ref | num2 | num2 | 5 | const | 1 | Using where; Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | testdb.test2.num2 | 1 | Using where |
|
| 2 | LATERAL DERIVED | test1 | ref | num1 | num1 | 5 | testdb.test2.num2 | 1 | Using index |
|
+------+-----------------+------------+------+---------------+------+---------+-------------------+------+--------------------------+
|