[MDEV-31848] Lateral derived optimization causes incorrect results with <=> Created: 2023-08-04  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.1, 11.1.1, 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Väinö Mäkelä Assignee: Igor Babaev
Resolution: Unresolved Votes: 1
Labels: split_materialized, wrong_result
Environment:

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              |
 
+------+-----------------+------------+------+---------------+------+---------+-------------------+------+--------------------------+



 Comments   
Comment by Alice Sherepa [ 2023-08-04 ]

Thank you for the report!
I repeated as described on 10.4-11.0 with InnoDB engine, Myisam and Aria return expected result.

--source include/have_innodb.inc 
 
 
CREATE TABLE t1 (num1 int, KEY (num1))engine=innodb;
 
INSERT INTO t1 VALUES (NULL), (1);
 
 
 
CREATE TABLE t2 (num2 int, KEY (num2))engine=innodb;
 
INSERT INTO t2 VALUES (NULL), (2);
 
 
SET optimizer_switch='split_materialized=on';
 
 
SELECT dt.num1, t2.num2
 
FROM t2 
JOIN (SELECT num1  FROM t1  GROUP BY num1)dt ON dt.num1 <=> t2.num2
 
WHERE t2.num2 IS NULL;
 
 
 
SET optimizer_switch='split_materialized=off';
 
 
 
SELECT dt.num1, t2.num2
 
FROM t2 
JOIN (SELECT num1  FROM t1  GROUP BY num1)dt ON dt.num1 <=> t2.num2
 
WHERE t2.num2 IS NULL;
 
 
 
DROP TABLE t1,t2;

Generated at Thu Feb 08 10:26:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.