1--source include/default_optimizer_switch.inc 2 3# 4# MDEV-6065 MySQL Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH TIME/DATETIME COMPARE" 5# 6 7# Systematic testing of ref access and range scan 8 9SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35'); 10 11CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)); 12INSERT INTO t1 VALUES ('00:00:00'),('-24:00:00'),('-48:00:00'),('24:00:00'),('48:00:00'); 13CREATE TABLE t2 (col_datetime_key DATETIME, KEY(col_datetime_key)); 14INSERT INTO t2 SELECT * FROM t1; 15 16let $cnt_0=5; 17let $operator= =; 18# For operator in =, >=, >, <=, < 19while ($cnt_0) 20{ 21 let $cnt_1=2; 22 let $first_table=t1; 23 # for table in t1,t2 24 while ($cnt_1) 25 { 26 if ($first_table==t1) 27 { 28 let $first_index=col_time_key; 29 let $second_table=t2; 30 let $second_index=col_datetime_key; 31 } 32 if ($first_table==t2) 33 { 34 let $first_index=col_datetime_key; 35 let $second_table=t1; 36 let $second_index=col_time_key; 37 } 38 let $cnt_2=2; 39 let $first_index_hint=ignore; 40 # for first_index_hint in ignore,force 41 while ($cnt_2) 42 { 43 let $cnt_3=2; 44 let $second_index_hint=ignore; 45 # for second_index_hint in ignore, force 46 while ($cnt_3) 47 { 48 let $cnt_4=2; 49 let $first_operand=col_time_key; 50 # for first_operand in col_time_key, col_datetime_key 51 while ($cnt_4) 52 { 53 if ($first_operand==col_time_key) 54 { 55 let $second_operand=col_datetime_key; 56 } 57 if ($first_operand==col_datetime_key) 58 { 59 let $second_operand=col_time_key; 60 } 61 62 eval EXPLAIN EXTENDED SELECT * FROM 63 $first_table $first_index_hint INDEX ($first_index) 64 STRAIGHT_JOIN 65 $second_table $second_index_hint INDEX ($second_index) 66 WHERE $first_operand $operator $second_operand; 67 --sorted_result 68 eval SELECT * FROM 69 $first_table $first_index_hint INDEX ($first_index) 70 STRAIGHT_JOIN 71 $second_table $second_index_hint INDEX ($second_index) 72 WHERE $first_operand $operator $second_operand; 73 74 let $first_operand=col_datetime_key; 75 dec $cnt_4; 76 } 77 let $second_index_hint=force; 78 dec $cnt_3; 79 } 80 let $first_index_hint=force; 81 dec $cnt_2; 82 } 83 let $first_table=t2; 84 dec $cnt_1; 85 } 86 if ($cnt_0==5) 87 { 88 let $operator= >=; 89 } 90 if ($cnt_0==4) 91 { 92 let $operator= >; 93 } 94 if ($cnt_0==3) 95 { 96 let $operator= <=; 97 } 98 if ($cnt_0==2) 99 { 100 let $operator= <; 101 } 102 dec $cnt_0; 103} 104 105DROP TABLE t1,t2; 106 107# 108# Original test of the bug report 109# 110 111CREATE TABLE t1 ( 112 pk INT NOT NULL AUTO_INCREMENT, 113 col_int_nokey INT, 114 col_int_key INT NOT NULL, 115 PRIMARY KEY (pk), 116 KEY col_int_key (col_int_key) 117); 118 119INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3), 120(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6), 121(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9), 122(27,3,5), (28,6,0), (29,6,3); 123 124CREATE TABLE t2 ( 125 col_int_nokey INT NOT NULL, 126 col_datetime_key DATETIME NOT NULL, 127 col_varchar_key VARCHAR(1) NOT NULL, 128 KEY col_datetime_key (col_datetime_key), 129 KEY col_varchar_key (col_varchar_key) 130); 131 132INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k'); 133 134CREATE TABLE t3 ( 135 col_time_key TIME, 136 KEY col_time_key (col_time_key) 137); 138 139INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'), 140('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'), 141('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'), 142('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'), 143('02:59:24'), ('00:01:58'); 144 145let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) 146 ON t3.col_time_key > t2.col_datetime_key; 147eval EXPLAIN EXTENDED $query; 148--sorted_result 149eval $query; 150 151let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) 152 ON t3.col_time_key > t2.col_datetime_key; 153eval EXPLAIN EXTENDED $query; 154--sorted_result 155eval $query; 156 157let $query=SELECT outr.col_int_nokey 158FROM t2 as outr 159 STRAIGHT_JOIN t3 AS outr2 160 ON outr2.col_time_key > outr.col_datetime_key 161WHERE outr.col_int_nokey IN ( 162 SELECT col_int_key 163 FROM t1 AS innr 164 WHERE innr.pk >= innr.col_int_nokey 165) AND ( 166 outr.col_int_nokey <= 6 167 OR 168 outr.col_varchar_key IS NULL 169); 170eval EXPLAIN EXTENDED $query; 171--sorted_result 172eval $query; 173 174DROP TABLE t1,t2,t3; 175SET TIMESTAMP=0; # back to current time 176 177 178--echo # 179--echo # MDEV-15262 Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column 180--echo # 181 182SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35'); 183CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)); 184CREATE TABLE t2 (col_datetime_key DATETIME); 185INSERT INTO t1 VALUES ('-760:00:00'),('760:00:00'); 186INSERT INTO t1 VALUES ('-770:00:00'),('770:00:00'); 187INSERT INTO t2 SELECT * FROM t1; 188SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key; 189SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key; 190INSERT INTO t1 VALUES ('-838:59:59'),('838:59:59'); 191INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '-838:59:59' HOUR_SECOND)); 192INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '838:59:59' HOUR_SECOND)); 193INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '-839:00:00' HOUR_SECOND)); 194INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '839:00:00' HOUR_SECOND)); 195SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key; 196SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key; 197DROP TABLE t1, t2; 198SET TIMESTAMP=DEFAULT; 199 200# 201# End of 10.0 tests 202# 203