Description:
When the join order of SQL statements differs, the execution results also differ.The expected result is the same.
The following SQL returns 8 rows.
SELECT /*+ join_order(table_5_utf8_undef,table_30_utf8mb4_undef) */ col_bigint_key_unsigned as f1 FROM table_30_utf8mb4_undef where BINARY col_bigint_key_unsigned IN (SELECT col_datetime_key_signed FROM table_5_utf8_undef);
The following SQL returns 30 rows.
SELECT /*+ join_order(table_30_utf8mb4_undef,table_5_utf8_undef) */ col_bigint_key_unsigned as f1 FROM table_30_utf8mb4_undef where BINARY col_bigint_key_unsigned IN (SELECT col_datetime_key_signed FROM table_5_utf8_undef);
How to repeat:
set sql_mode='';
create table table_5_utf8_undef (
`pk` int primary key,
`col_bigint_undef_signed` bigint ,
`col_bigint_undef_unsigned` bigint unsigned ,
`col_bigint_key_signed` bigint ,
`col_bigint_key_unsigned` bigint unsigned ,
`col_float_undef_signed` float ,
`col_float_undef_unsigned` float unsigned ,
`col_float_key_signed` float ,
`col_float_key_unsigned` float unsigned ,
`col_double_undef_signed` double ,
`col_double_undef_unsigned` double unsigned ,
`col_double_key_signed` double ,
`col_double_key_unsigned` double unsigned ,
`col_decimal(40, 20)_undef_signed` decimal(40, 20) ,
`col_decimal(40, 20)_undef_unsigned` decimal(40, 20) unsigned ,
`col_decimal(40, 20)_key_signed` decimal(40, 20) ,
`col_decimal(40, 20)_key_unsigned` decimal(40, 20) unsigned ,
`col_char(20)_undef_signed` char(20) ,
`col_char(20)_key_signed` char(20) ,
`col_varchar(20)_undef_signed` varchar(20) ,
`col_varchar(20)_key_signed` varchar(20) ,
`col_date_undef_signed` date ,
`col_date_key_signed` date ,
`col_time_undef_signed` time ,
`col_time_key_signed` time ,
`col_datetime_undef_signed` datetime ,
`col_datetime_key_signed` datetime ,
`col_timestamp_undef_signed` timestamp ,
`col_timestamp_key_signed` timestamp ,
key (`col_bigint_key_signed`),
key (`col_bigint_key_unsigned`),
key (`col_float_key_signed`),
key (`col_float_key_unsigned`),
key (`col_double_key_signed`),
key (`col_double_key_unsigned`),
key (`col_decimal(40, 20)_key_signed`),
key (`col_decimal(40, 20)_key_unsigned`),
key (`col_char(20)_key_signed`),
key (`col_varchar(20)_key_signed`),
key (`col_date_key_signed`),
key (`col_time_key_signed`),
key (`col_datetime_key_signed`),
key (`col_timestamp_key_signed`)
) character set utf8 ;
insert into table_5_utf8_undef values (0,75,0,-0.0001,0,2,105,1.009,30433,-28680,1,-2,13157,1.009,1,-1,12.991,'-0',' 3','-0','3
','15:53:30','2006-04-01 05:44:40',null,20190827073915,'
3','x','-1','1'),(1,-12,2,0.0001,19,-0.0001,0,27606,1,12.991,32,1,15,0,0.0001,0.0001,12.991,'3
','-0',' 3','y',20170413090737,'2006',20010205080236,'2019-09-22 16:29:17','3
','0','z','1'),(2,1.009,30761,1.009,1,88.1356,2,0.0001,1.009,-9.183,8.0433,12.991,19683,0,2,-0.0001,12.991,'-1','-0','1','
3',20030705114254,20050424052517,null,'2014-08-04 09:22:52','-1',"now",'
3','0'),(3,-3888,12.991,0.0001,0.0001,-0.0001,5.1626,-24492,85.0859,0,0,1.009,0.0001,12.991,12.991,-108,0.0001,'0',' 3',' 3',' 3','2009-10-23 03:27:46',20090611082704,null,'06:54:15','0','x','
3','0'),(4,-10947,1,0,12869,1,79,-0,2,-9.183,1,-2,1.009,1,1,-2,0,' 3','-0','3
','-1','2009-09-26',20040328205328,'02:49:13','2018','-1','3 ','-0','v');
create table table_30_utf8mb4_undef (
`pk` int primary key,
`col_bigint_undef_signed` bigint ,
`col_bigint_undef_unsigned` bigint unsigned ,
`col_bigint_key_signed` bigint ,
`col_bigint_key_unsigned` bigint unsigned ,
`col_float_undef_signed` float ,
`col_float_undef_unsigned` float unsigned ,
`col_float_key_signed` float ,
`col_float_key_unsigned` float unsigned ,
`col_double_undef_signed` double ,
`col_double_undef_unsigned` double unsigned ,
`col_double_key_signed` double ,
`col_double_key_unsigned` double unsigned ,
`col_decimal(40, 20)_undef_signed` decimal(40, 20) ,
`col_decimal(40, 20)_undef_unsigned` decimal(40, 20) unsigned ,
`col_decimal(40, 20)_key_signed` decimal(40, 20) ,
`col_decimal(40, 20)_key_unsigned` decimal(40, 20) unsigned ,
`col_char(20)_undef_signed` char(20) ,
`col_char(20)_key_signed` char(20) ,
`col_varchar(20)_undef_signed` varchar(20) ,
`col_varchar(20)_key_signed` varchar(20) ,
`col_date_undef_signed` date ,
`col_date_key_signed` date ,
`col_time_undef_signed` time ,
`col_time_key_signed` time ,
`col_datetime_undef_signed` datetime ,
`col_datetime_key_signed` datetime ,
`col_timestamp_undef_signed` timestamp ,
`col_timestamp_key_signed` timestamp ,
key (`col_bigint_key_signed`),
key (`col_bigint_key_unsigned`),
key (`col_float_key_signed`),
key (`col_float_key_unsigned`),
key (`col_double_key_signed`),
key (`col_double_key_unsigned`),
key (`col_decimal(40, 20)_key_signed`),
key (`col_decimal(40, 20)_key_unsigned`),
key (`col_char(20)_key_signed`),
key (`col_varchar(20)_key_signed`),
key (`col_date_key_signed`),
key (`col_time_key_signed`),
key (`col_datetime_key_signed`),
key (`col_timestamp_key_signed`)
) character set utf8mb4 ;
insert into table_30_utf8mb4_undef values (0,0,1.009,-0,2,112,18,-1,1,-8541,24.1505,1,2,-1,0.0001,18383,29.1672,'s','3 ','
3','3 ',null,'22:57:30','2002','2000-08-09 11:16:00','-0','3
','-1','l'),(1,-0,33.0991,15299,101,-0.0001,7505,-25363,6.0904,12.991,1.009,0.0001,0.0001,-0,61,2,965,'
3','q','0',' 3',20120328233554,'2006',null,'2017-05-05',"about",' 3','
3',"a"),(2,-0,12.991,13293,74.0809,1.009,12.991,2,1,-0,2,-95,2,43.1635,12.991,71.0883,28.1345,'c','0','3
','3 ','03:34:57','2019-02-08 19:05:28','2015-05-14','2006-01-23','1','-0',"now",'3
'),(3,1.009,2,1,24876,0.0001,2,89,1.009,-2,1,67.0562,1.009,0,0.0001,-1,1,'3 ','1','-1','-0',20110518203535,'10:39:01','2013','2017',"see",' 3',' 3','-1'),(4,1,117,25.0310,0.0001,12.991,1.009,12066,12.991,-15352,0,1.009,1,2,2,1.009,1.009,'-0','1','3 ','0','2013-07-22','2002-06-27','2007-09-02','2004','-1','3
','-1','3 '),(5,-0.0001,98.0448,1,1.009,4,1.009,73.1053,12.991,2,12.991,88.1086,1.009,0,1.009,-1,2,'3 ','b',' 3','3 ','2008-10-01 06:38:18','2001-12-08 13:44:41',null,null,'
3','3
',' 3','1'),(6,2,2,1.009,0,-0,0,-0,2,1.009,120,-2,0,-2,2,12.991,12.991,'0','-0','
3',' 3','2003','13:08:38',20020414020443,null,"that's",'0','-0','0'),(7,68.0979,25360,0.0001,2,-2934,52.0753,1.009,2,-0.0001,4006,-7317,27885,-0.0001,15.1668,30929,2,"the",'c','3
','-1','2012-07-27',20050413032453,'2001-07-22','2013-09-25','-1','0','1',"we"),(8,116,1.009,0.0001,2,-9.183,1.009,1.009,2,1.009,0.0001,-1,12.991,-0,0,2,1.009,' 3','-0','
3','z',null,null,20050716191033,'2006-08-03 09:39:29','0',"want",'
3',"if"),(9,12.991,2,-0,1,12.991,0.0001,-0.0001,0,-2,0,2,1,1,45,7261,53,'3 ','
3','1',"why",'2012-01-14 20:04:33','2004-06-22 06:50:17',null,'2018-09-13 19:52:36',"your",'3 ','0','
3'),(10,-2,37.0666,38.1109,0,77.0557,1.009,-15639,12.991,58.0408,0.0001,1.009,12.991,1,116,12.991,2,'-0','b','3
',"see",null,20140126093830,'2015-05-12 17:50:28',null,' 3','-0','3 ','3 '),(11,1,12.991,0.0001,1.009,12.991,704,-1,0.0001,46.1745,12.991,1.009,0.0001,2,2,1,0.0001,'i','3 ',' 3','3
',null,'2003-03-11','21:27:42',20170227144146,'-0','1','-0',"out"),(12,2,1.009,-0,2,-1,48,-0,0,1.009,12.991,25360,39.0650,1,1,1.009,1,'3
','-0','-0','k','2019-10-19','2008','2007',null,'-1','
3',' 3','1'),(13,0.0001,24194,2,12.991,2,1,-9.183,86.0166,96.1724,98,-9.183,21404,0,1,-1,70.0229,'
3','3 ','-1','
3','2002-10-24','2019-03-25',20120407072204,'15:01:42','1','e','1','0'),(14,12.991,125,12.991,0,-1,66.0765,-9.183,0.0001,24.0274,0,12.991,1.009,-9.183,0.0001,12.991,28.0497,' 3','3
','m','x','2000-02-25 11:18:28',null,20050625075455,'2000-02-04',"really",'
3',' 3','1'),(15,-0,1.009,-37,0,12.991,0,70.1352,0,0.0001,35.1941,-9.183,4.1520,0.0001,2,2,0,'3
','d','3 ','3
','2017-12-15','2003-03-18 10:36:19','2007','2001-01-16','i','3
',' 3',"her"),(16,2,0,0.0001,12.991,1,0.0001,1,12.991,-2,1.009,12.991,2,1.009,0,0.0001,2,'
3','-1',' 3','1','11:24:05',null,'2016-08-22 10:39:10','2017-05-13','3 ','d',"this",' 3'),(17,-1,1,-2,0,21736,1,1.009,52,-2,52.0480,97.1402,1.009,23.1439,165,-9.183,2,"then",'1','1','
3','21:08:58','01:42:20','2010-02-08',null,"what",'-1','0','-1'),(18,-0.0001,1.009,-0,2,60.1346,1,13.1246,0.0001,-9.183,1.009,-1,12.991,0,1.009,-0.0001,32161,'1',"now",'1','-0','2011-06-20 17:14:47',20091216100059,20110615194526,'2000-11-11 20:41:01','-1','3
','1','-0'),(19,1.009,48.1000,12.991,1.009,2,96.0251,12.991,1.009,50,7.0227,-2,12.991,-87,0.0001,-0,20128,'o','
3','-0','0',20061005135052,'06:53:48','2004-05-13','2013-07-07 11:30:29',"to",'-0',"like",'3
'),(20,1.009,2,12.991,1.009,0,0,0,1.009,1,76.1247,1,2,-0,2,12.991,12.991,'-1',"that's",'3
',"your",'2014-04-08','05:27:46',20030702043010,20041127002658,'-1','p','
3','-0'),(21,-0,1.009,12.991,12.991,17113,47,-2,99,0.0001,12.991,2,119,0.0001,1.009,4856,1,'0','-0','-0','-1','2012-05-09 12:26:18','03:42:31','17:01:19','2004-10-03','3
',"didn't",'1','3
'),(22,-0,1,1,1.009,-0.0001,0,2,1,0.0001,0,-1,32.1479,12.991,54.0706,82.1650,0.0001,'3 ','-0','-1',' 3',20041112223019,'2014',20070610104408,'16:33:11','3 ','-0','-0',' 3'),(23,95.1840,0,60,0.0001,2,12.991,-0.0001,0.0001,-1,12.991,-9.183,0.0001,0.0001,2,-1,26022,' 3','
3','3
','1','2003','2000-10-23 01:56:02',20070226043837,'2007-03-28','-0','
3','k','0'),(24,5.1324,42.1764,-0,2,1,2,1.009,0.0001,91,1,1.009,1,1,12.991,-0,0.0001,'-1','3
','0','3
',null,'2017','2012','2017-12-19 02:56:03','-1',"so",'-1','1'),(25,1.009,56.0367,10,96.0694,5.1045,12.991,-2,0,27654,17637,-0.0001,0.0001,2,0.0001,1,1,'-1',' 3','0','-1','2001','2002','2019-09-17',20000124044213,'3
','-0','3 ','-1'),(26,-13781,0.0001,1,12.991,1.009,1,-34,26.0628,1,1,2,24021,2,0.0001,1,0,'-0',' 3','-0','-0',null,'2001-03-19',20120628185659,'2004-03-09 22:11:39','0',"or",'l',' 3'),(27,15277,0,20,0,-2,0,0,2,2,2,12.991,12.991,-2,2,1.009,0.0001,'0','
3','3 ','
3','2016-08-08 07:08:33',20101127113358,'2003-12-24 20:41:21','11:24:01','0','1','3 ','y'),(28,-2,0,-2305,21807,0,71,-2,0.0001,-0.0001,1,1,1.009,-77,0.0001,-1,94.0593,'3 ','0',"yeah",'-1','2007-02-16',20071108113709,'12:49:40','04:20:21','z','0',' 3','-0'),(29,55.0832,13.1376,2,81.1045,1,23.0864,1.009,1.009,57,2,-2,18.0545,0,21906,8.1302,1.009,'-0',"you",'m','3 ','2008','08:54:28','2008-01-09 21:07:42',null,"don't","how",'3
','
3');
mysql> SELECT /*+ join_order(table_5_utf8_undef,table_30_utf8mb4_undef) */ col_bigint_key_unsigned as f1 FROM table_30_utf8mb4_undef where BINARY col_bigint_key_unsigned IN (SELECT col_datetime_key_signed FROM table_5_utf8_undef);
+-------+
| f1 |
+-------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 13 |
| 13 |
| 13 |
| 13 |
| 74 |
| 81 |
| 96 |
| 101 |
| 21807 |
| 24876 |
+-------+
30 rows in set, 31 warnings (0.00 sec)
mysql> SELECT /*+ join_order(table_30_utf8mb4_undef,table_5_utf8_undef) */ col_bigint_key_unsigned as f1 FROM table_30_utf8mb4_undef where BINARY col_bigint_key_unsigned IN (SELECT col_datetime_key_signed FROM table_5_utf8_undef);
+------+
| f1 |
+------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------+
8 rows in set, 39 warnings (0.00 sec)
