Bug #106328 Huge value in ORDER BY clause cause unexpected output

• Submitted: 29 Jan 2022 0:40
• Modified: 9 Feb 2022 1:33
• Reporter: Ali Salam
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.28, 5.7.37
• OS: Any
• Assigned to: Jon Stephens
• CPU Architecture: Any

[29 Jan 2022 0:40] Ali Salam

Description:
According to previous bug with group by clause https://bugs.mysql.com/bug.php?id=106312, same bug way work with order by clause.

MySQL [test]> CREATE TABLE t1 (id int, val varchar(10));
Query OK, 0 rows affected (0.026 sec)

MySQL [test]> INSERT INTO t1 VALUES (1, 'value1');
Query OK, 1 row affected (0.016 sec)

MySQL [test]> INSERT INTO t1 VALUES (2, 'value2');
Query OK, 1 row affected (0.004 sec)

MySQL [test]> SELECT * FROM t1;
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set (0.001 sec)

MySQL [test]> SELECT * FROM t1 order by 1 asc;
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set (0.001 sec)

MySQL [test]> SELECT * FROM t1 order by 1 desc;
+------+--------+
| id   | val    |
+------+--------+
|    2 | value2 |
|    1 | value1 |
+------+--------+
2 rows in set (0.001 sec)

-- Expected, using the first column in the table t1.

MySQL [test]> SELECT * FROM t1 order by 123;
ERROR 1054 (42S22): Unknown column '123' in 'order clause'

-- Expected

MySQL [test]> SELECT * FROM t1 order by 178234238746328742384353489759873459834759348573948679348634957034957349857934753947539483489579348753498; -- Random big number. 
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set, 1 warning (0.001 sec)

MySQL [test]> SELECT * FROM t1 order by 178234238746328742384353489759873459834759348573948679348634957034957349857934753947539483489579348753498 asc; -- Random big number.
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set, 1 warning (0.001 sec)

MySQL [test]> SELECT * FROM t1 order by 178234238746328742384353489759873459834759348573948679348634957034957349857934753947539483489579348753498 desc; -- Random big number. 
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set, 1 warning (0.001 sec)

-- Expects error, gets row output instead

How to repeat:
MySQL [test]> CREATE TABLE t1 (id int, val varchar(10));
Query OK, 0 rows affected (0.026 sec)

MySQL [test]> INSERT INTO t1 VALUES (1, 'value1');
Query OK, 1 row affected (0.016 sec)

MySQL [test]> INSERT INTO t1 VALUES (2, 'value2');
Query OK, 1 row affected (0.004 sec)

MySQL [test]> SELECT * FROM t1 order by 178234238746328742384353489759873459834759348573948679348634957034957349857934753947539483489579348753498; -- Random big number. 
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set, 1 warning (0.001 sec)

MySQL [test]> SELECT * FROM t1 order by 178234238746328742384353489759873459834759348573948679348634957034957349857934753947539483489579348753498 asc; -- Random big number.
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set, 1 warning (0.001 sec)

MySQL [test]> SELECT * FROM t1 order by 178234238746328742384353489759873459834759348573948679348634957034957349857934753947539483489579348753498 desc; -- Random big number. 
+------+--------+
| id   | val    |
+------+--------+
|    1 | value1 |
|    2 | value2 |
+------+--------+
2 rows in set, 1 warning (0.001 sec)

[29 Jan 2022 11:26] MySQL Verification Team

Hello  	Ali Salam,

Thank you for the report and test case.

regards,
Umesh

[9 Feb 2022 1:33] Jon Stephens

This is essentially the same issue as BUG#106312.

Closed as a duplicate of that bug.