Details
Critical Description
When set optimizer_switch='materialization=on', the following query returns incorrect results. The problem starts with version 10.2.10 (results are correct when materialization=on on versions 10.2.9 and below) and continues through 10.3.6
set optimizer_switch='materialization=off';
|
|
|
SELECT touter.id
|
FROM touter
|
INNER JOIN tinner1
|
ON touter.id = tinner1.id
|
WHERE touter.type = 2
|
AND touter.id IN (
|
SELECT tref.ref_id
|
FROM tref
|
INNER JOIN tinner1
|
ON tref.id = tinner1.id
|
WHERE tref.type = 'incident'
|
);
|
|
|
+----------------------------------+
|
| id |
|
+----------------------------------+
|
| ffffffb61e68ffffff2302003d4f2b49 |
|
| cb9763cda2dd48bbc751130045aa3ea9 |
|
| cb97631ea2dd48bbc7511300d96afaa9 |
|
| ce61030eb96d45a3d71002008d0a4f77 |
|
+----------------------------------+
|
4 rows in set (0.00 sec)
|
|
|
set optimizer_switch='materialization=on';
|
|
|
SELECT touter.id
|
FROM touter
|
INNER JOIN tinner1
|
ON touter.id = tinner1.id
|
WHERE touter.type = 2
|
AND touter.id IN (
|
SELECT tref.ref_id
|
FROM tref
|
INNER JOIN tinner1
|
ON tref.id = tinner1.id
|
WHERE tref.type = 'incident'
|
);
|
|
|
+----------------------------------+
|
| id |
|
+----------------------------------+
|
| ce61030eb96d45a3d71002008d0a4f77 |
|
+----------------------------------+
|
1 row in set (0.00 sec)
|
The attached SQL file will create the database 'matbug', populate it, and run the test query with the materialization switch both on and off.
Attachments
Issue Links
- relates to
-
MDEV-15454 Nested SELECT IN returns wrong results
-
- Closed
-