Bug #120419 rematerialized temptable cause wrong order

Submitted: 8 May 7:50 Modified: 8 May 19:25
Reporter: zkong kong Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.46 OS:Linux
Assigned to: CPU Architecture:Any
Tags: temptable order

[8 May 7:50] zkong kong
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))
...
[8 May 19:25] Roy Lyseng
Thank you for the bug report.
Verified as described.

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.