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