Bug #120419 rematerialized temptable cause wrong order
Description:
mysql> set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)
mysql> WITH cte AS (SELECT val FROM demo) SELECT c1.val FROM cte c1 JOIN LATERAL ( SELECT 1 FROM cte c2 WHERE c2.val = c1.val LIMIT 1 ) sub ON TRUE ORDER BY c1.val ASC LIMIT 5;
+-----+
| val |
+-----+
| 100 |
| 90 |
| 80 |
| 70 |
| 60 |
+-----+
5 rows in set (0.01 sec)
The correct result:
mysql> set optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)
mysql> WITH cte AS (SELECT val FROM demo) SELECT c1.val FROM cte c1 JOIN LATERAL ( SELECT 1 FROM cte c2 WHERE c2.val = c1.val LIMIT 1 ) sub ON TRUE ORDER BY c1.val ASC LIMIT 5;
+-----+
| val |
+-----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+-----+
5 rows in set (0.01 sec)
How to repeat:
CREATE TABLE demo (id INT PRIMARY KEY, val INT NOT NULL);
INSERT INTO demo VALUES
(1,100),(2,90),(3,80),(4,70),(5,60),
(6,50), (7,40),(8,30),(9,20),(10,10);
set optimizer_switch='derived_merge=off';
WITH cte AS (SELECT val FROM demo) SELECT c1.val FROM cte c1 JOIN LATERAL ( SELECT 1 FROM cte c2 WHERE c2.val = c1.val LIMIT 1 ) sub ON TRUE ORDER BY c1.val ASC LIMIT 5;
Suggested fix:
After some investigation:
sql/sql_optimizer.cc
// Create an index scan if the table is not a temporary table that uses
// Temptable engine (Does not support index_first() and index_last()) and
// if there was no new range scan created.
if (!(is_temporary_table(tab->table_ref) &&
tab->table_ref->table->s->db_type() == temptable_hton) &&
((!tab->range_scan() || tab->range_scan() == save_range_scan))) {
temptable disable the index_scan but not reset the can_skip_sorting
fix it by:
if ((is_temporary_table(tab->table_ref) &&
tab->table_ref->table->s->db_type() == temptable_hton) &&
((!tab->range_scan() || tab->range_scan() == save_range_scan)))
can_skip_sorting = false;
else if ((!tab->range_scan() || tab->range_scan() == save_range_scan))
...
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: mysql> set optimizer_switch='derived_merge=off'; Query OK, 0 rows affected (0.00 sec) mysql> WITH cte AS (SELECT val FROM demo) SELECT c1.val FROM cte c1 JOIN LATERAL ( SELECT 1 FROM cte c2 WHERE c2.val = c1.val LIMIT 1 ) sub ON TRUE ORDER BY c1.val ASC LIMIT 5; +-----+ | val | +-----+ | 100 | | 90 | | 80 | | 70 | | 60 | +-----+ 5 rows in set (0.01 sec) The correct result: mysql> set optimizer_switch='derived_merge=on'; Query OK, 0 rows affected (0.00 sec) mysql> WITH cte AS (SELECT val FROM demo) SELECT c1.val FROM cte c1 JOIN LATERAL ( SELECT 1 FROM cte c2 WHERE c2.val = c1.val LIMIT 1 ) sub ON TRUE ORDER BY c1.val ASC LIMIT 5; +-----+ | val | +-----+ | 10 | | 20 | | 30 | | 40 | | 50 | +-----+ 5 rows in set (0.01 sec) How to repeat: CREATE TABLE demo (id INT PRIMARY KEY, val INT NOT NULL); INSERT INTO demo VALUES (1,100),(2,90),(3,80),(4,70),(5,60), (6,50), (7,40),(8,30),(9,20),(10,10); set optimizer_switch='derived_merge=off'; WITH cte AS (SELECT val FROM demo) SELECT c1.val FROM cte c1 JOIN LATERAL ( SELECT 1 FROM cte c2 WHERE c2.val = c1.val LIMIT 1 ) sub ON TRUE ORDER BY c1.val ASC LIMIT 5; Suggested fix: After some investigation: sql/sql_optimizer.cc // Create an index scan if the table is not a temporary table that uses // Temptable engine (Does not support index_first() and index_last()) and // if there was no new range scan created. if (!(is_temporary_table(tab->table_ref) && tab->table_ref->table->s->db_type() == temptable_hton) && ((!tab->range_scan() || tab->range_scan() == save_range_scan))) { temptable disable the index_scan but not reset the can_skip_sorting fix it by: if ((is_temporary_table(tab->table_ref) && tab->table_ref->table->s->db_type() == temptable_hton) && ((!tab->range_scan() || tab->range_scan() == save_range_scan))) can_skip_sorting = false; else if ((!tab->range_scan() || tab->range_scan() == save_range_scan)) ...