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.
