3902 Roy Lyseng 2012-05-03
WL#5561 - Enable semi-join transformation with outer join queries
Fourth worklog commit, after Oystein's review.
mysql-test/include/subquery_sj.inc
Subquery-related test moved from type_time.test.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Updated test results.
mysql-test/r/type_time.result
Updated test results.
mysql-test/t/type_time.test
Subquery-related test moved to subquery_sj.inc.
sql/sql_optimizer.cc
Moved test for semi-join materialization applicability into
semijoin_types_allow_materialization().
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
mysql-test/r/type_time.result
mysql-test/t/type_time.test
sql/sql_optimizer.cc
3901 Roy Lyseng 2012-04-30
Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
This bug has its cause in two problems in the code base.
First, there is a bug in Optimize_table_order::fix_semijoin_strategies()
that does not properly clear semi-join plan data that has been rejected.
Second, there is a bug in Optimize_table_order::advance_sj_state() when
keeping track of potential LooseScan table orders.
For the first bug, when fix_semijoin_strategies() is invoked, there
are two potential semi-join strategies in join->best_positions:
Positions 0 1 2 3
<-- DupsWeedout -->
<-------- LooseScan ------>
fix_semijoin_strategies() scans best_positions in reverse order,
finding (correctly) the LooseScan strategy when looking at
best_positions[3]. (After greedy_search() procedure, semi-join
strategy choices are recorded in the last element.) The strategy
information is then re-arranged so that semi-join strategy selection
is recorded in the first element, and all other elements should be
cleared. However, on doing this, only one element is actually cleared,
because the number of elements to clear is taken from n_sj_tables.
Thus, on the next round, the DupsWeedout strategy is found in
best_positions[2], and then we have overlapping semi-join strategies,
which is not supported by the optimizer.
This problem is fixed by clearing semi-join information for all
elements of best_positions that are covered by the semi-join strategy.
For the second bug, look at the proposed table order:
subquery3_t1 - t3 - table1 - table2
subquery3_t1 is a semi-join inner table, for which a LooseScan strategy
is possible. When looking at the next table (t3), which is another
semi-join inner table from another semi-join nest, advance_sj_state()
adds this table to the LooseScan prefix, even though this table has
forward references (to table table1), and it belongs to another
semi-join nest. Other semi-join inner tables must be semi-joined to
the LooseScan driving table by application of FirstMatch strategy, and
they must be in the same semi-join nest for this strategy to be possible.
Thus, the criteria for setting up table order for LooseScan are wrong.
mysql-test/include/subquery_sj.inc
Added test case for bug#13971022.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#13971022.
sql/sql_planner.cc
In Optimize_table_order::fix_semijoin_strategies(), made sure that
semi-join strategy information is properly cleared after fixing
each strategy.
In Optimize_table_order::advance_sj_state(), changed the LooseScan
table order check to make sure that only tables from one semi-join
nest take part in LooseScan operation.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_planner.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-04-30 12:15:37 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-05-03 13:35:42 +0000
@@ -6062,4 +6062,69 @@ DROP TABLE t1, t2, t3;
--echo # End of test for bug#13971022.
+--echo #
+--echo # Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+--echo # TIME/DATETIME COMPARE" - Subquery part of test.
+--echo #
+
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+
+CREATE TABLE t1 (
+ pk INT NOT NULL,
+ col_int_nokey INT,
+ col_int_key INT NOT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+
+CREATE TABLE t2 (
+ col_int_nokey INT NOT NULL,
+ col_datetime_key DATETIME NOT NULL,
+ col_varchar_key VARCHAR(1) NOT NULL,
+ KEY col_datetime_key (col_datetime_key),
+ KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+
+CREATE TABLE t3 (
+ col_time_key TIME,
+ KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+
+let $query=
+SELECT outr.col_int_nokey
+FROM t2 as outr
+ STRAIGHT_JOIN t3 AS outr2
+ ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+ SELECT col_int_key
+ FROM t1 AS innr
+ WHERE innr.pk >= innr.col_int_nokey
+) AND (
+ outr.col_int_nokey <= 6
+ OR
+ outr.col_varchar_key IS NULL
+);
+eval EXPLAIN EXTENDED $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1,t2,t3;
+
+SET TIMESTAMP = DEFAULT;
+
+--echo # End of test for bug#13623473.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-05-03 13:35:42 +0000
@@ -9613,5 +9613,94 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-05-03 13:35:42 +0000
@@ -9618,6 +9618,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr); Using join buffer (Batched Key Access)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-05-03 13:35:42 +0000
@@ -9619,6 +9619,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr); Using join buffer (Batched Key Access)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-05-03 13:35:42 +0000
@@ -9619,6 +9619,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr); Using join buffer (Batched Key Access (unique))
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-05-03 13:35:42 +0000
@@ -9597,5 +9597,94 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; Start temporary; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-05-03 13:35:42 +0000
@@ -9598,6 +9598,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-05-03 13:35:42 +0000
@@ -9606,6 +9606,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-05-03 13:35:42 +0000
@@ -9596,6 +9596,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-05-03 13:35:42 +0000
@@ -9597,6 +9597,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-05-03 13:35:42 +0000
@@ -9605,6 +9605,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-05-03 13:35:42 +0000
@@ -9598,6 +9598,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; FirstMatch(outr)
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-05-03 13:35:42 +0000
@@ -9598,5 +9598,94 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; Start temporary; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-05-03 13:35:42 +0000
@@ -9607,6 +9607,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-05-03 13:35:42 +0000
@@ -9600,6 +9600,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-05-03 13:35:42 +0000
@@ -9611,5 +9611,94 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; Start temporary; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-05-03 13:35:42 +0000
@@ -9612,6 +9612,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-05-03 13:35:42 +0000
@@ -9613,6 +9613,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-05-03 13:35:42 +0000
@@ -9613,6 +9613,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where; Start temporary
+1 SIMPLE innr ref col_int_key col_int_key 4 test.outr.col_int_nokey 1 100.00 Using where; End temporary
+1 SIMPLE outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-05-03 13:35:42 +0000
@@ -9683,5 +9683,94 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 PRIMARY outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+2 SUBQUERY innr ALL NULL NULL NULL NULL 20 100.00 Using where
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,`test`.`outr`.`col_int_nokey` in ( <materialize> (/* select#2 */ select `test`.`innr`.`col_int_key` from `test`.`t1` `innr` where (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) ), <primary_index_lookup>(`test`.`outr`.`col_int_nokey` in <temporary table> on distinct_key where ((`test`.`outr`.`col_int_nokey` = `materialized subselect`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-05-03 13:35:42 +0000
@@ -9598,5 +9598,94 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 PRIMARY outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+2 DEPENDENT SUBQUERY innr index_subquery col_int_key col_int_key 4 func 1 100.00 Using where
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 PRIMARY outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+2 DEPENDENT SUBQUERY innr index_subquery col_int_key col_int_key 4 func 1 100.00 Using where
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 PRIMARY outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+2 DEPENDENT SUBQUERY innr index_subquery col_int_key col_int_key 4 func 1 100.00 Using where
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-05-03 13:35:42 +0000
@@ -9600,6 +9600,95 @@ FROM t1 AS subquery3_t1
field1
DROP TABLE t1, t2, t3;
# End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
+1 PRIMARY outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
+2 DEPENDENT SUBQUERY innr index_subquery col_int_key col_int_key 4 func 1 100.00 Using where
+Warnings:
+Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/type_time.result'
--- a/mysql-test/r/type_time.result 2012-04-11 12:12:00 +0000
+++ b/mysql-test/r/type_time.result 2012-05-03 13:35:42 +0000
@@ -2701,64 +2701,8 @@ col_int_nokey col_datetime_key col_varch
1 2001-11-04 19:07:55 k 19:22:21
1 2001-11-04 19:07:55 k 19:47:59
1 2001-11-04 19:07:55 k 21:22:34
-set @optimizer_switch_saved=@@session.optimizer_switch;
-set @@session.optimizer_switch='semijoin=off,materialization=off';
-EXPLAIN EXTENDED SELECT outr.col_int_nokey
-FROM t2 as outr
-STRAIGHT_JOIN t3 AS outr2
-ON outr2.col_time_key > outr.col_datetime_key
-WHERE outr.col_int_nokey IN (
-SELECT col_int_key
-FROM t1 AS innr
-WHERE innr.pk >= innr.col_int_nokey
-) AND (
-outr.col_int_nokey <= 6
-OR
-outr.col_varchar_key IS NULL
-);
-id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY outr ALL col_datetime_key NULL NULL NULL 1 100.00 Using where
-1 PRIMARY outr2 ALL col_time_key NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1)
-2 DEPENDENT SUBQUERY innr index_subquery col_int_key col_int_key 4 func 1 100.00 Using where
-Warnings:
-Note 1003 /* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
-SELECT outr.col_int_nokey
-FROM t2 as outr
-STRAIGHT_JOIN t3 AS outr2
-ON outr2.col_time_key > outr.col_datetime_key
-WHERE outr.col_int_nokey IN (
-SELECT col_int_key
-FROM t1 AS innr
-WHERE innr.pk >= innr.col_int_nokey
-) AND (
-outr.col_int_nokey <= 6
-OR
-outr.col_varchar_key IS NULL
-);
-col_int_nokey
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-set @@session.optimizer_switch=@optimizer_switch_saved;
DROP TABLE t1,t2,t3;
-SET TIMESTAMP=0;
+SET TIMESTAMP = DEFAULT;
#
# End of 5.6 tests
#
=== modified file 'mysql-test/t/type_time.test'
--- a/mysql-test/t/type_time.test 2012-02-13 11:57:09 +0000
+++ b/mysql-test/t/type_time.test 2012-05-03 13:35:42 +0000
@@ -420,30 +420,8 @@ eval EXPLAIN EXTENDED $query;
--sorted_result
eval $query;
-set @optimizer_switch_saved=@@session.optimizer_switch;
-set @@session.optimizer_switch='semijoin=off,materialization=off';
-
-let $query=SELECT outr.col_int_nokey
-FROM t2 as outr
- STRAIGHT_JOIN t3 AS outr2
- ON outr2.col_time_key > outr.col_datetime_key
-WHERE outr.col_int_nokey IN (
- SELECT col_int_key
- FROM t1 AS innr
- WHERE innr.pk >= innr.col_int_nokey
-) AND (
- outr.col_int_nokey <= 6
- OR
- outr.col_varchar_key IS NULL
-);
-eval EXPLAIN EXTENDED $query;
---sorted_result
-eval $query;
-
-set @@session.optimizer_switch=@optimizer_switch_saved;
-
DROP TABLE t1,t2,t3;
-SET TIMESTAMP=0; # back to current time
+SET TIMESTAMP = DEFAULT;
--echo #
--echo # End of 5.6 tests
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-04-30 06:56:15 +0000
+++ b/sql/sql_optimizer.cc 2012-05-03 13:35:42 +0000
@@ -3558,11 +3558,11 @@ void semijoin_types_allow_materializatio
List_iterator<Item> it1(sj_nest->nested_join->sj_outer_exprs);
List_iterator<Item> it2(sj_nest->nested_join->sj_inner_exprs);
- sj_nest->nested_join->sjm.scan_allowed= FALSE;
- sj_nest->nested_join->sjm.lookup_allowed= FALSE;
+ sj_nest->nested_join->sjm.scan_allowed= false;
+ sj_nest->nested_join->sjm.lookup_allowed= false;
- bool all_are_fields= TRUE;
- bool blobs_involved= FALSE;
+ bool all_are_fields= true;
+ bool blobs_involved= false;
Item *outer, *inner;
while (outer= it1++, inner= it2++)
{
@@ -3574,6 +3574,25 @@ void semijoin_types_allow_materializatio
}
sj_nest->nested_join->sjm.scan_allowed= all_are_fields;
sj_nest->nested_join->sjm.lookup_allowed= !blobs_involved;
+
+ if (sj_nest->embedding)
+ {
+ DBUG_ASSERT(sj_nest->embedding->join_cond());
+ /*
+ There are two issues that prevent materialization strategy from being
+ used when a semi-join nest is on the inner side of an outer join:
+ 1. If the semi-join contains dependencies to outer tables,
+ materialize-scan strategy cannot be used.
+ 2. sub_select_sjm() lacks support for setup of first_unmatched
+ and evaluation of triggered conditions, as in
+ evaluate_null_complemented_join_record().
+ Handle this by disabling materialization strategies:
+ */
+ sj_nest->nested_join->sjm.scan_allowed= false;
+ sj_nest->nested_join->sjm.lookup_allowed= false;
+ DBUG_VOID_RETURN;
+ }
+
DBUG_PRINT("info",("semijoin_types_allow_materialization: ok, allowed"));
DBUG_VOID_RETURN;
@@ -3996,24 +4015,6 @@ static bool optimize_semijoin_nests_for_
Check whether data types allow execution with materialization.
*/
semijoin_types_allow_materialization(sj_nest);
-
- if (sj_nest->embedding)
- {
- DBUG_ASSERT(sj_nest->embedding->join_cond());
- /*
- There are two issues that prevent materialization strategy
- from being used when a semi-join nest is on the inner side of
- an outer join:
- 1. If the semi-join contains dependencies to outer tables,
- materialize-scan strategy cannot be used.
- 2. sub_select_sjm() lacks support for setup of first_unmatched
- and evaluation of triggered conditions, as in
- evaluate_null_complemented_join_record().
- Handle this by disabling materialization strategies:
- */
- sj_nest->nested_join->sjm.scan_allowed= false;
- sj_nest->nested_join->sjm.lookup_allowed= false;
- }
if (sj_nest->nested_join->sjm.scan_allowed ||
sj_nest->nested_join->sjm.lookup_allowed)
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3901 to 3902) WL#5561 | Roy Lyseng | 8 May |
