Bug #110550 ordering VALUES statement seems not to work as expected

• Submitted: 29 Mar 2023 12:42
• Modified: 30 Mar 2023 7:20
• Reporter: Simon Mudd (OCA)
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any
• Tags: 8.0.32, regression, values

[29 Mar 2023 12:42] Simon Mudd

Description:
Noticed by a colleague and seeing the description

The new VALUES statement syntax described at https://dev.mysql.com/doc/refman/8.0/en/values.html  seems to not always work as expected when using ORDER BY.

I guess this syntax is little used so this hasn't been noticed.

If confirmed it would be good to resolve this.

How to repeat:
Case 1. Broken, as the ordering is not correct.

root@host [(none)]> VALUES ROW(1,1,1), ROW(5,5,5), ROW(4,4,4) ORDER BY column_1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        1 |        1 |
|        5 |        5 |        5 |
|        4 |        4 |        4 |
+----------+----------+----------+
3 rows in set (0.00 sec)

Case 2. Correct where the ordering is as expected

root@host [(none)]> VALUES ROW(1,1,1), ROW(5,5,5), ROW(4,4,4) UNION VALUES ROW(0,0,0) ORDER BY column_1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        0 |        0 |        0 |
|        1 |        1 |        1 |
|        4 |        4 |        4 |
|        5 |        5 |        5 |
+----------+----------+----------+
4 rows in set (0.00 sec)

Version tested against.

root@host [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

root@host [(none)]>

Suggested fix:
Ensure the ORDER BY request is actually respected and the rows are in the correct order.

[29 Mar 2023 13:00] MySQL Verification Team

Hello Simon,

Thank you for the report and feedback.

regards,
Umesh

[30 Mar 2023 7:20] MySQL Verification Team

Hello Simon,

Confirmed internally that this is duplicate of Roy's "Bug #100996 Table value constructor followed by ORDER BY is not sorted correctly". This is currently marked as private hence not visible. I'll check internally and request concern to publish if possible.

regards,
Umesh

[30 Mar 2023 7:43] MySQL Verification Team

Related - Bug #105287 	Table value constructor fails resolution of ORDER BY synthetic name