#At file:///home/rl136806/mysql/repo/mysql-work3/ based on revid:guilhem.bichot@stripped
3393 Roy Lyseng 2011-06-27
Bug#12603183: Segfault in hp_movelink
The bug manifests itself sometimes as a segmentation fault,
sometimes as a Valgrind warning. Consistent faulting has been
difficult to achieve.
The problem is with the Materialization scan semi-join strategy.
When materialization is done, the read_first_record function
pointer is replaced with a function to read from the materialized
table instead. This strategy works when the materialization is
performed once per query, even when the materialized table is
read multiple times. However, if the materialization is performed
multiple times, such as when called from within another subquery,
the original function pointer is never restored, and the wrong
function is used to read from the subquery tables when materializing.
The solution for the problem is to save the original function pointer
in the save_read_first_record field of the join_tab and restore
it for every new materialization.
save_read_first_record is now used both from materialize-scan and
from subqueries that toggle between ref access and full table scan.
We could imagine a query that used both strategies at the same time:
SELECT ... FROM ot...
WHERE ocol NOT IN (SELECT ... FROM mt...
WHERE mcol IN (SELECT ... FROM it ...))
Where the inner-most subquery is transformed into a semi-join and
materialize-scan strategy is selected, and the NOT IN alternates
betwen a ref access and a full table scan on its inner table.
However, the ref access would have to be against table "mt", but the
table being materialized in this case is "it".
Hence, I think this scenario is not possible.
Notice that there are still some result differences, which would
not be seen with the original "LIMIT 1" specification.
It seems that Materialization scan is still slightly broken when
used together with an outer join. This problem will be looked at
in the context of WL#5561. Notice also that when semi-join
transformation for outer joins is enabled, both subqueries of this
query will be converted, and the materialization will be performed
only once, avoiding the entire problem. Hence, to reproduce this
problem in context of WL#5561, make sure that the outer subquery
is not transformed.
mysql-test/include/subquery_sj.inc
Added test case for bug#12603183.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
Updated with test results for bug#12603183.
sql/sql_select.cc
sub_select_sjm():
Save read function pointer into save_read_first_record on first
materialization, and restore it on subsequent materializations.
Deleted a DBUG_ASSERT that seemed redundant, and moved setting
of sjm->materialized to a better place.
sql/sql_select.h
Updated comment for join_tab field save_read_first_record.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-06-21 09:29:01 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-06-27 06:45:51 +0000
@@ -3748,3 +3748,58 @@ eval $query;
DROP TABLE t1, t2;
--echo # End of bug#12603200
+
+--echo #
+--echo # Bug#12603183: Segfault in hp_movelink
+--echo #
+
+CREATE TABLE t1 (
+ col_varchar_key varchar(1) ,
+ col_varchar_nokey varchar(1) ,
+ KEY col_varchar_key(col_varchar_key)
+);
+
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+
+CREATE TABLE t2 (
+ col_varchar_nokey varchar(1)
+);
+
+INSERT INTO t2 VALUES
+('b');
+
+let $query=
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+ (SELECT parent1.col_varchar_nokey
+ FROM t1 AS parent1
+ WHERE parent1.col_varchar_key IN
+ (SELECT child1.col_varchar_nokey AS c1
+ FROM t1 AS child1 LEFT JOIN t2 AS child2
+ ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo -- Notice that Materialize-scan algorithm reports wrong result for this query.
+--echo -- This problem will be filed as a separate bug and dealt with in WL#5561.
+
+DROP TABLE t1, t2;
+
+--echo # End of the test for bug#12603183.
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-06-27 06:45:51 +0000
@@ -5644,4 +5644,76 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; Using join buffer (BKA, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-06-27 06:45:51 +0000
@@ -5643,4 +5643,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-06-27 06:45:51 +0000
@@ -5647,5 +5647,78 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-06-27 06:45:51 +0000
@@ -5647,5 +5647,78 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-06-27 06:45:51 +0000
@@ -5645,6 +5645,79 @@ c 13
DROP TABLE t1, t2;
# End of bug#12603200
#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 15 Using where
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(parent1)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-06-27 06:45:51 +0000
@@ -5649,6 +5649,79 @@ c 13
DROP TABLE t1, t2;
# End of bug#12603200
#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 15 Using where
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(parent1)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-06-27 06:45:51 +0000
@@ -5649,6 +5649,79 @@ c 13
DROP TABLE t1, t2;
# End of bug#12603200
#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 15 Using where
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(parent1)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-06-27 06:45:51 +0000
@@ -5644,4 +5644,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,78 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,78 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-06-27 06:45:51 +0000
@@ -5644,4 +5644,76 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-06-27 06:45:51 +0000
@@ -5866,4 +5866,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 SUBQUERY parent1 ALL NULL NULL NULL NULL 15 Using where
+3 SUBQUERY child1 ALL NULL NULL NULL NULL 15
+3 SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-06-27 06:45:51 +0000
@@ -5789,4 +5789,77 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 15 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-06-27 06:45:51 +0000
@@ -5793,5 +5793,78 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 15 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-06-27 06:45:51 +0000
@@ -5793,5 +5793,78 @@ c 3
c 13
DROP TABLE t1, t2;
# End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL NULL NULL NULL NULL 15 Using where
+1 PRIMARY grandparent2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 15 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 15 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-06-24 13:09:12 +0000
+++ b/sql/sql_select.cc 2011-06-27 06:45:51 +0000
@@ -17366,6 +17366,20 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
Next_select_func next_func= join_tab[sjm->table_count - 1].next_select;
join_tab[sjm->table_count - 1].next_select= end_sj_materialize;
+ if (sjm->is_scan)
+ {
+ JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
+ if (last_tab->save_read_first_record == NULL)
+ {
+ /* Save a copy of the read first function before substituting it */
+ last_tab->save_read_first_record= last_tab->read_first_record;
+ }
+ else
+ {
+ /* Restore read function saved in previous materialization round */
+ last_tab->read_first_record= last_tab->save_read_first_record;
+ }
+ }
/*
Now run the join for the inner tables. The first call is to run the
join, the second one is to signal EOF (this is essential for some
@@ -17382,7 +17396,6 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
/*
Ok, materialization finished. Initialize the access to the temptable
*/
- sjm->materialized= TRUE;
join_tab->read_record.read_record= join_no_more_records;
if (sjm->is_scan)
{
@@ -17391,7 +17404,6 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
init_read_record(&last_tab->read_record, join->thd,
sjm->table, NULL, TRUE, TRUE, FALSE);
- DBUG_ASSERT(last_tab->read_record.read_record == rr_sequential);
last_tab->read_first_record= join_read_record_no_init;
last_tab->read_record.copy_field= sjm->copy_field;
last_tab->read_record.copy_field_end= sjm->copy_field +
@@ -17402,6 +17414,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
last_tab->last_inner= NULL;
last_tab->first_unmatched= NULL;
}
+
+ sjm->materialized= true;
}
else
{
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-06-24 13:22:37 +0000
+++ b/sql/sql_select.h 2011-06-27 06:45:51 +0000
@@ -308,9 +308,10 @@ public:
Next_select_func next_select;
READ_RECORD read_record;
/*
- Currently the following two fields are used only for a [NOT] IN subquery
- if it is executed by an alternative full table scan when the left operand of
+ The following two fields are used for a [NOT] IN subquery if it is
+ executed by an alternative full table scan when the left operand of
the subquery predicate is evaluated to NULL.
+ save_read_first_record is also used by semi-join materialization strategy.
*/
READ_RECORD::Setup_func save_read_first_record;/* to save read_first_record */
READ_RECORD::Read_func save_read_record;/* to save read_record.read_record */
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110627064551-dhzsrv4q0tp1wfim.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (roy.lyseng:3393) Bug#12603183 | Roy Lyseng | 27 Jun |
