MySQL Bugs: #118252: When the same table is imported to different databases, the results of the same selection are different.

Bug #118252	When the same table is imported to different databases, the results of the same selection are different.
Submitted:	22 May 12:28	Modified:	22 Jun 13:15
Reporter:	linyan kong	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The same SQL file is imported to different databases for multiple times. If the same select statement is executed for multiple times, the results may be inconsistent occasionally.

How to repeat:
1、Import data to different databases multiple times.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk1 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk2 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk3 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk4 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk5 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk6 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk7 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk8 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk9 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk10 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk11 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk12 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk13 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk14 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk15 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk16 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk17 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk18 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk19 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk20 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk21 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk22 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk23 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk24 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk25 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk26 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p*** -h10.83.35.197 -P3307 -Dtest_kk27 < tbl_mrr_test11.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

2、Run the following command:
SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;

mysql> use test_kk22;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;
+--------------+------------+---------+--------------------------------+------------+---------------+
| longtext_col | date_col   | int_col | mediumblob_col                 | date_col   | mediumint_col |
+--------------+------------+---------+--------------------------------+------------+---------------+
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
+--------------+------------+---------+--------------------------------+------------+---------------+
5 rows in set (0.00 sec)

mysql> use test_kk23;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;
+--------------+------------+---------+--------------------------------+------------+---------------+
| longtext_col | date_col   | int_col | mediumblob_col                 | date_col   | mediumint_col |
+--------------+------------+---------+--------------------------------+------------+---------------+
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
+--------------+------------+---------+--------------------------------+------------+---------------+
5 rows in set (0.01 sec)

mysql> use test_kk24;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;
+--------------+------------+---------+--------------------------------+------------+---------------+
| longtext_col | date_col   | int_col | mediumblob_col                 | date_col   | mediumint_col |
+--------------+------------+---------+--------------------------------+------------+---------------+
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
+--------------+------------+---------+--------------------------------+------------+---------------+
5 rows in set (0.01 sec)

mysql> use test_kk25;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;
+--------------+------------+---------+--------------------------------+------------+---------------+
| longtext_col | date_col   | int_col | mediumblob_col                 | date_col   | mediumint_col |
+--------------+------------+---------+--------------------------------+------------+---------------+
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
+--------------+------------+---------+--------------------------------+------------+---------------+
5 rows in set (0.01 sec)

mysql> use test_kk26;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;
+--------------+------------+---------+--------------------------------+------------+---------------+
| longtext_col | date_col   | int_col | mediumblob_col                 | date_col   | mediumint_col |
+--------------+------------+---------+--------------------------------+------------+---------------+
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |      84 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
| a            | 2020-01-01 |     100 | 0x61                           | 2020-01-01 |             1 |
+--------------+------------+---------+--------------------------------+------------+---------------+
5 rows in set (0.01 sec)

mysql> use test_kk27;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT longtext_col, date_col, int_col, mediumblob_col, date_col, mediumint_col FROM tbl_mrr_test1 WHERE generated_time_col NOT BETWEEN '01:59:59.000000' AND NULL OR tinyint_col > 98 ORDER BY 1,2,3,4,5,6 limit 5;
+--------------+------------+---------+--------------------------------+------------+---------------+
| longtext_col | date_col   | int_col | mediumblob_col                 | date_col   | mediumint_col |
+--------------+------------+---------+--------------------------------+------------+---------------+
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
| NULL         | 1992-03-04 |      67 | NULL                           | 1992-03-04 |             1 |
+--------------+------------+---------+--------------------------------+------------+---------------+
5 rows in set (0.01 sec)

3、When the same command is executed for tables in different databases, the query results are inconsistent.

Hello linyan kong,

Thank you for the bug report.
Could you please provide sample sql file to reproduce this issue at our end?

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".