Bug #111739 The query results are inconsistent.
Description:
After mysqld is restarted, the first query result is different from the subsequent query result.
How to repeat:
1.Preparing Data
Set the following parameters in the my.cnf configuration file:
sql_mode=''
Start mysqld and import data(The data files can be obtained by decompressing the attachment.):
bin/mysql --defaults-file=./my.cnf -uroot -e "drop database if exists test;"
bin/mysql --defaults-file=./my.cnf -uroot -e "create database test;"
bin/mysql --defaults-file=./my.cnf -uroot -Dtest --force < tbl_1_all_rangehashpart_type3index_desc_simplified.sql
bin/mysql --defaults-file=./my.cnf -uroot -Dtest --force < tbl_1_all_typeindex.sql
bin/mysql --defaults-file=./my.cnf -uroot -Dtest -e "create view vil_1_all_typeindex as select * from tbl_1_all_typeindex;"
2.Bug Verification.
2.1 Restart mysqld!
2.2 Login and execute:
bin/mysql --defaults-file=./my.cnf -uroot -Dtest
mysql> SELECT /*+ JOIN_FIXED_ORDER() */ A.set_col, A.datetime_col FROM (VALUES ROW(1,3,5), ROW(2,4,6) limit 1 offset 1) AS vtb(id_col,int_col,big_int_col) LEFT JOIN tbl_1_all_rangehashpart_type3index_desc A ON (true) JOIN vil_1_all_typeindex B ON (A.set_col = B.set_col) XOR (A.datetime_col = B.datetime_col) WHERE B.datetime_col = '2001-04-06 00:00:00.000000' GROUP BY 1 ORDER BY 1;
+---------+----------------------------+
| set_col | datetime_col |
+---------+----------------------------+
| 111 | 2012-11-09 00:00:00.000000 |
+---------+----------------------------+
1 row in set (0.25 sec)
mysql> SELECT /*+ JOIN_FIXED_ORDER() */ A.set_col, A.datetime_col FROM (VALUES ROW(1,3,5), ROW(2,4,6) limit 1 offset 1) AS vtb(id_col,int_col,big_int_col) LEFT JOIN tbl_1_all_rangehashpart_type3index_desc A ON (true) JOIN vil_1_all_typeindex B ON (A.set_col = B.set_col) XOR (A.datetime_col = B.datetime_col) WHERE B.datetime_col = '2001-04-06 00:00:00.000000' GROUP BY 1 ORDER BY 1;
+---------+----------------------------+
| set_col | datetime_col |
+---------+----------------------------+
| 111 | 1000-01-01 00:00:00.000000 |
+---------+----------------------------+
1 row in set (0.04 sec)
The execution results are inconsistent.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.