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))
...
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)) ...