|
Wrong query results when optimizer_switch="split_materialized=on" optimization is enabled.
Tested with MariaDB 10.3.22, 10.4.10, 10.4.11, 10.4.12.
Works correctly with 10.2.31 (no optimization)
Example
MySQL:
CREATE DATABASE test;
|
CREATE TABLE test.a (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL);
|
CREATE TABLE test.b (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, related_object_id int(11) NOT NULL, is_default int(1) NOT NULL DEFAULT 0, KEY related_object_id (related_object_id) );
|
Shell:
for i in {1..1000}; do echo "INSERT INTO test.a(name) VALUES('name$i');"|mysql;done
|
for i in {1..1000}; do echo "INSERT INTO test.b(related_object_id, is_default) VALUES($i,0);"|mysql;done
|
for i in {1..1000}; do echo "INSERT INTO test.b(related_object_id, is_default) VALUES($i,1);"|mysql;done
|
Query:
SELECT
|
a.id AS id,
|
a.name AS name
|
FROM
|
a AS a
|
WHERE
|
a.id IN
|
(
|
SELECT
|
subp.id
|
FROM
|
(
|
SELECT
|
a.*
|
FROM
|
a a
|
LEFT JOIN
|
b AS b
|
ON (a.id = b.related_object_id AND b.is_default = 1)
|
GROUP BY
|
a.id
|
)
|
AS subp
|
)
|
|
Result:
+----+-------+
|
| id | name |
|
+----+-------+
|
| 1 | name1 |
|
+----+-------+
|
1 row in set (0.00 sec)
|
Query plan:
MariaDB [test]> explain extended SELECT
|
-> a.id AS id,
|
-> a.name AS name
|
-> FROM
|
-> a AS a
|
-> WHERE
|
-> a.id IN
|
-> (
|
-> SELECT
|
-> subp.id
|
-> FROM
|
-> (
|
-> SELECT
|
-> a.*
|
-> FROM
|
-> a a
|
-> LEFT JOIN
|
-> b AS b
|
-> ON (a.id = b.related_object_id AND b.is_default = 1)
|
-> GROUP BY
|
-> a.id
|
-> )
|
-> AS subp
|
-> );
|
+------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
|
| 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 100.00 | |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | |
|
| 3 | LATERAL DERIVED | a | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | 100.00 | |
|
| 3 | LATERAL DERIVED | b | ref | related_object_id | related_object_id | 4 | test.a.id | 1 | 100.00 | Using where |
|
+------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
|
5 rows in set, 1 warning (0.00 sec)
|
|
Turn split_materialized optimization
SET optimizer_switch="split_materialized=off";
|
Result:
| 991 | name991 |
|
| 992 | name992 |
|
| 993 | name993 |
|
| 994 | name994 |
|
| 995 | name995 |
|
| 996 | name996 |
|
| 997 | name997 |
|
| 998 | name998 |
|
| 999 | name999 |
|
| 1000 | name1000 |
|
+------+----------+
|
1000 rows in set (0.00 sec)
|
Query plan:
MariaDB [test]> explain extended SELECT
|
-> a.id AS id,
|
-> a.name AS name
|
-> FROM
|
-> a AS a
|
-> WHERE
|
-> a.id IN
|
-> (
|
-> SELECT
|
-> subp.id
|
-> FROM
|
-> (
|
-> SELECT
|
-> a.*
|
-> FROM
|
-> a a
|
-> LEFT JOIN
|
-> b AS b
|
-> ON (a.id = b.related_object_id AND b.is_default = 1)
|
-> GROUP BY
|
-> a.id
|
-> )
|
-> AS subp
|
-> );
|
+------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
|
| 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 100.00 | |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | |
|
| 3 | DERIVED | a | index | NULL | PRIMARY | 4 | NULL | 1000 | 100.00 | |
|
| 3 | DERIVED | b | ref | related_object_id | related_object_id | 4 | test.a.id | 1 | 100.00 | Using where |
|
+------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
|
5 rows in set, 1 warning (0.00 sec)
|
|