| Bug #114684 | Inconsistent data representation | ||
|---|---|---|---|
| Submitted: | 18 Apr 2024 13:54 | Modified: | 18 Apr 2024 15:30 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.36 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Apr 2024 14:10]
MySQL Verification Team
Hi Mr. Jove, Thank you for your bug report. We ran your test case 5 (five) times in each of our releases, 8.0.35, 8.0.36 and 8.0.37. Each time we get the same result: ------------------------------------- FIRST SELECT ca5 1 0 SECOND SELECT ca5 \0 THIRD SELECT ca5 \0 FOURTH SELECT ca5 \0 ------------------------------------- Can't repeat.
[18 Apr 2024 14:40]
John Jove
I run the above statement through MySQL client, and got the following results.
mysql> CREATE TABLE t1 (c1 bit,c2 tinyint, KEY i1 (c1), UNIQUE KEY i2 (c2,c1));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t1 VALUES (true,0), (false,1);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT ca5 FROM (SELECT c1 AS ca5 FROM t1 USE INDEX (i2)) AS ta1 GROUP BY ca5; -- {1,0}
+------+
| ca5 |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.01 sec)
mysql> SELECT c1 AS ca5 FROM t1 USE INDEX (i2) GROUP BY ca5; -- {0x01,0x00}
+------------+
| ca5 |
+------------+
| 0x01 |
| 0x00 |
+------------+
2 rows in set (0.00 sec)
mysql> SELECT ca5 FROM (SELECT c1 AS ca5 FROM t1 USE INDEX (i1)) AS ta1 GROUP BY ca5; -- {0x00,0x01}
+------------+
| ca5 |
+------------+
| 0x00 |
| 0x01 |
+------------+
2 rows in set (0.00 sec)
mysql> SELECT c1 AS ca5 FROM t1 USE INDEX (i1) GROUP BY ca5; -- {0x00,0x01}
+------------+
| ca5 |
+------------+
| 0x00 |
| 0x01 |
+------------+
2 rows in set (0.00 sec)
mysql>
[18 Apr 2024 14:54]
John Jove
By the way, through the JDBC client with driver version 8.2.0, I can still get the following inconsistent results.
CREATE TABLE t1 (c1 bit,c2 tinyint, KEY i1 (c1), UNIQUE KEY i2 (c2,c1));
INSERT INTO t1 VALUES (true,0), (false,1);
SELECT ca5 FROM (SELECT c1 AS ca5 FROM t1 USE INDEX (i2)) AS ta1 GROUP BY ca5; -- {1,0}
SELECT c1 AS ca5 FROM t1 USE INDEX (i2) GROUP BY ca5; -- {true,false}
SELECT ca5 FROM (SELECT c1 AS ca5 FROM t1 USE INDEX (i1)) AS ta1 GROUP BY ca5; -- {false,true}
SELECT c1 AS ca5 FROM t1 USE INDEX (i1) GROUP BY ca5; -- {false,true}
[18 Apr 2024 15:07]
MySQL Verification Team
Hi Mr. Jove, We tried 8.0.35, 8.0.36, 8.0.37 and 8.3.0, ran the query 5 (five) times in each and got the same results as those that we published. We can not verify a report without repeating it. We recommend that you check out your configuration file.
[18 Apr 2024 15:13]
John Jove
From the above submitted result pasted as follows, there exactly are some difference. Otherwise, my webpage does not show results properly. Moreover, I run the result through the MySQL client installed through apt command in Ubuntu, there may be different from your reproducing tool, may be mtr. ------------------------------------- FIRST SELECT ca5 1 0 SECOND SELECT -- not the same as the FIRST SELECT ca5 \0 THIRD SELECT -- seems a different result ca5 \0 FOURTH SELECT ca5 \0 -------------------------------------
[18 Apr 2024 15:14]
John Jove
I deploy MySQL through the official docker image with default configurations.
[18 Apr 2024 15:23]
MySQL Verification Team
Hi, We also used default configuration, with no entries except for the datadir. We have still got the same result. We used only MySQL CLI and we did not use any API or Connector for the testing. That is because the category is "MySQL Server" and not "C API" nor some Connector. We still get the same results.
[18 Apr 2024 15:28]
MySQL Verification Team
Hi, We also used Docker and got the same results. We must point out that we use binaries built by our company, that are available on https://dev/mysql.com. Can't repeat.
[18 Apr 2024 15:30]
John Jove
Thanks for your patience. I got it. There must be some difference in my system environment. Thanks again.
[18 Apr 2024 16:46]
MySQL Verification Team
You are truly welcome !!!
Description: I run the following statements, in which these queries are expected to return consistent data with respect to their literal representation. How to repeat: CREATE TABLE t1 (c1 bit,c2 tinyint, KEY i1 (c1), UNIQUE KEY i2 (c2,c1)); INSERT INTO t1 VALUES (true,0), (false,1); SELECT ca5 FROM (SELECT c1 AS ca5 FROM t1 USE INDEX (i2)) AS ta1 GROUP BY ca5; -- {1,0} SELECT c1 AS ca5 FROM t1 USE INDEX (i2) GROUP BY ca5; -- {0x01,0x00} SELECT ca5 FROM (SELECT c1 AS ca5 FROM t1 USE INDEX (i1)) AS ta1 GROUP BY ca5; -- {0x00,0x01} SELECT c1 AS ca5 FROM t1 USE INDEX (i1) GROUP BY ca5; -- {0x00,0x01} Suggested fix: The first query may also return {0x01,0x00}, instead of {1,0}.