| Bug #93472 | Inconsistent output of ORDER BY with MEMORY engine | ||
|---|---|---|---|
| Submitted: | 4 Dec 2018 15:54 | Modified: | 22 May 2019 17:18 |
| Reporter: | Przemysław Skibiński (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S3 (Non-critical) |
| Version: | 8.0.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Dec 2018 17:52]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
Thank you for the feedback.
[21 Feb 2019 11:04]
Steinar Gunderson
This is not a bug. If two rows compare equal under ORDER BY, their order is indeterminate.
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).