Bug #93472 Inconsistent output of ORDER BY with MEMORY engine
Submitted: 4 Dec 2018 15:54 Modified: 5 Dec 2018 12:12
Reporter: Przemysław Skibiński (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[4 Dec 2018 15:54] Przemysław Skibiński
Description:
"select hex(a) from t1 order by a"
returns 3 different outputs in 10 subsequent runs:
61
6100
61

6100
61
61

61
61
6100

How to repeat:
CREATE table t1 (a char(5)) engine=MEMORY;
insert into t1 values ('a'),('a\0'),('a ');
select hex(a) from t1 order by a;
DROP table t1;

Suggested fix:
The issue is caused by the fact that Sort_param::make_sortkey() generates the same results for ('a'),('a\0'),('a '). It causes that the real order depends on lower bits of pointer (which is random).
[4 Dec 2018 17:52] Miguel Solorzano
Thank you for the bug report. I got same order disregarding # of queries:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE x;
Query OK, 1 row affected (0.14 sec)

mysql> USE X
Database changed
mysql> CREATE table t1 (a char(5)) engine=MEMORY;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values ('a'),('a\0'),('a ');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.01 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql>
[4 Dec 2018 19:45] Shane Bester
On mysql win64 8.0.13 release build I get:

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 61     |
| 61     |
| 6100   |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 61     |
| 61     |
| 6100   |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 61     |
| 61     |
| 6100   |
+--------+
3 rows in set (0.00 sec)
.

On debug trunk build I got:

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

mysql> select hex(a) from t1 order by a;
+--------+
| hex(a) |
+--------+
| 6100   |
| 61     |
| 61     |
+--------+
3 rows in set (0.00 sec)

Isn't that enough to prove the bug? (that we get different results)
[4 Dec 2018 20:37] Przemysław Skibiński
Please save my script as an MTR test and run it. Restarting of server should help with generating different/random memory addresses.
[5 Dec 2018 8:04] Przemysław Skibiński
The following test should allow to reproduce the issue at a single run:

CREATE table t1 (a char(5)) engine=MEMORY;
insert into t1 values ('a'),('a\0'),('a'),('a\0'),('a'),('a\0'),('a'),('a\0'),('a'),('a\0'),('a'),('a\0');
select hex(a) from t1 order by a;
DROP table t1;
[5 Dec 2018 12:12] Miguel Solorzano
Thank you for the feedback.
[21 Feb 11:04] Steinar Gunderson
This is not a bug. If two rows compare equal under ORDER BY, their order is indeterminate.