[Commits] Rev 2877: Merge backported subquery bugfixes/testcases into MariaDB 5.3 in file:///home/psergey/dev2/5.4-fix-subq-r2/

Sergey Petrunya psergey at askmonty.org
Fri Jan 14 12:07:56 EET 2011


At file:///home/psergey/dev2/5.4-fix-subq-r2/

------------------------------------------------------------
revno: 2877 [merge]
revision-id: psergey at askmonty.org-20110114100750-lrg1cs0qyfp3ybyq
parent: psergey at askmonty.org-20110112120010-w4wygo4cz7bs73og
parent: psergey at askmonty.org-20110113214703-a2edjfk75wjq36hx
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.4-fix-subq-r2
timestamp: Fri 2011-01-14 13:07:50 +0300
message:
  Merge backported subquery bugfixes/testcases into MariaDB 5.3
modified:
  mysql-test/r/func_group.result sp1f-func_group.result-20001228015633-oe57bieiww3s6erojiyha7p26m5ul5ql
  mysql-test/r/index_merge_innodb.result sp1f-index_merge_innodb.r-20060816114352-umgqkfavfljswrg7qhdkcoptdwi5gipo
  mysql-test/r/innodb_mrr.result innodb_mrr.result-20091215071342-e3drfbiqqrmxrpri-1
  mysql-test/r/join_cache.result join_cache.result-20091221012827-jfu65h0x5bmixhh3-1
  mysql-test/r/subselect.result  sp1f-subselect.result-20020512204640-zgegcsgavnfd7t7eyrf7ibuqomsw7uzo
  mysql-test/r/subselect_no_mat.result subselect_no_mat.res-20100117143924-hut18sl9k2c7qdj8-1
  mysql-test/r/subselect_no_opts.result subselect_no_opts.re-20100117143925-pabg7o8iyokjlu93-1
  mysql-test/r/subselect_no_semijoin.result subselect_no_semijoi-20100117143925-9yfygtcm7fwsuq2p-1
  mysql-test/t/func_group.test   sp1f-func_group.test-20001228015635-wkz277djccbddkitm63hibutxp7o4rb7
  mysql-test/t/index_merge_innodb.test sp1f-index_merge_innodb.t-20060816114353-jlqkbce4q5pp3yqqhbhrox4ovfz3yce4
  mysql-test/t/innodb_mrr.test   innodb_mrr.test-20091215071348-pq1qyz18e54ao399-1
  mysql-test/t/join_cache.test   join_cache.test-20091221012705-n3szmbc9blgmmu84-1
  mysql-test/t/subselect.test    sp1f-subselect.test-20020512204640-lyqrayx6uwsn7zih6y7kerkenuitzbvr
  sql/sql_join_cache.cc          sql_join_cache.cc-20091221012625-ipp8zu28iijhjmq2-1
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2010-10-30 13:07:45 +0000
+++ b/mysql-test/r/func_group.result	2010-12-25 13:23:16 +0000
@@ -1725,3 +1725,154 @@
 DROP TABLE t1;
 #
 End of 5.1 tests
+#
+# BUG#46680 - Assertion failed in file item_subselect.cc, 
+#             line 305 crashing on HAVING subquery
+#
+# Create tables
+#
+CREATE TABLE t1 (
+pk INT,
+v VARCHAR(1) DEFAULT NULL,
+PRIMARY KEY(pk)
+);
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+CREATE TABLE empty1 (a int);
+INSERT INTO t1 VALUES (1,'c'),(2,NULL);
+INSERT INTO t2 VALUES (3,'m'),(4,NULL);
+INSERT INTO t3 VALUES (1,'n');
+
+#
+# 1) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "Impossible WHERE"
+#
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(t2.pk)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'j'
+
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'j'
+
+#
+# 2) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "No matching min/max row"
+#
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(t2.pk)
+NULL
+
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+
+#
+# 3) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "Select tables optimized away"
+#
+# NOTE: The result of this query is actually wrong; it should be NULL
+# See BUG#47762. Even so, the test case is still needed to test
+# that the HAVING subquery does not crash the server
+# 
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(pk)
+NULL
+
+EXPLAIN
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+
+#
+# 4) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "No matching row in const table"
+#
+
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(a)
+NULL
+
+EXPLAIN 
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+
+#
+# 5) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "Impossible WHERE noticed 
+#    after reading const tables"
+#
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+min(t1.pk)
+NULL
+
+EXPLAIN
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+#
+# Cleanup for BUG#46680
+#
+DROP TABLE IF EXISTS t1,t2,t3,empty1;
+End of 6.0 tests

=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result	2010-12-10 05:55:14 +0000
+++ b/mysql-test/r/index_merge_innodb.result	2011-01-14 10:07:50 +0000
@@ -711,4 +711,35 @@
 COUNT(*)
 6145
 DROP TABLE t1;
+#
+# Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly
+#            with Innodb tables
+#
+CREATE TABLE t1 (
+i int(11) DEFAULT NULL,
+v1 varchar(1) DEFAULT NULL,
+v2 varchar(20) DEFAULT NULL,
+KEY i (i),
+KEY v (v1,i)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (1,'f','no');
+INSERT INTO t1 VALUES (2,'u','yes-u');
+INSERT INTO t1 VALUES (2,'h','yes-h');
+INSERT INTO t1 VALUES (3,'d','no');
+
+SELECT v2
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+v2
+yes-u
+yes-h
+
+# Should not use index_merge
+EXPLAIN
+SELECT v2
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i,v	i	5	const	2	Using where
+DROP TABLE t1;
 set optimizer_switch= @optimizer_switch_save;

=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result	2011-01-12 12:00:10 +0000
+++ b/mysql-test/r/innodb_mrr.result	2011-01-14 10:07:50 +0000
@@ -681,3 +681,46 @@
 4	40	0
 drop table t1;
 set optimizer_use_mrr = @my_save_optimizer_use_mrr;
+#
+# Bug#43360 - Server crash with a simple multi-table update
+#
+CREATE TABLE t1 (
+a CHAR(2) NOT NULL PRIMARY KEY,
+b VARCHAR(20) NOT NULL,
+KEY (b)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+a CHAR(2) NOT NULL PRIMARY KEY,
+b VARCHAR(20) NOT NULL,
+KEY (b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('AB','MySQLAB'),
+('JA','Sun Microsystems'),
+('MS','Microsoft'),
+('IB','IBM- Inc.'),
+('GO','Google Inc.');
+INSERT INTO t2 VALUES
+('AB','Sweden'),
+('JA','USA'),
+('MS','United States of America'),
+('IB','North America'),
+('GO','South America');
+Warnings:
+Warning	1265	Data truncated for column 'b' at row 3
+UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'United%';
+SELECT * FROM t1;
+a	b
+GO	Google Inc.
+IB	IBM- Inc.
+MS	Microsoft
+AB	MySQLAB
+JA	Sun Microsystems
+SELECT * FROM t2;
+a	b
+IB	North America
+GO	South America
+AB	Sweden
+MS	United States of Ame
+JA	USA
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2011-01-05 23:03:30 +0000
+++ b/mysql-test/r/join_cache.result	2011-01-14 10:07:50 +0000
@@ -6220,4 +6220,22 @@
 f1	f2	f1	f2
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2;
+#
+# Backported testcase for: Bug #45092: join buffer contains two blob columns one of which is
+#   used in the key employed to access the joined table
+#
+CREATE TABLE t1 (c1 int, c2 int, key (c2));
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (2,2);
+CREATE TABLE t2 (c1 text, c2 text);
+INSERT INTO t2 VALUES('tt', 'uu');
+INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
+ANALYZE TABLE t1,t2;
+set join_cache_level=6;
+SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
+WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
+c1	c2	c1	c2	LENGTH(t2.c1)	LENGTH(t2.c2)
+2	2	tt	uu	2	2
+set join_cache_level=default;
+DROP TABLE t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2010-12-27 22:22:05 +0000
+++ b/mysql-test/r/subselect.result	2011-01-14 10:07:50 +0000
@@ -4971,3 +4971,39 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	24	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2010-12-27 22:22:05 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2011-01-14 10:07:50 +0000
@@ -4974,6 +4974,41 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2010-12-27 22:22:05 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2011-01-14 10:07:50 +0000
@@ -4971,4 +4971,39 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2010-12-27 22:22:05 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2011-01-14 10:07:50 +0000
@@ -4971,4 +4971,39 @@
 3
 drop table t1,t2,t3;
 drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 set optimizer_switch=default;

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2010-08-27 09:44:35 +0000
+++ b/mysql-test/t/func_group.test	2010-12-25 13:23:16 +0000
@@ -1099,3 +1099,140 @@
 --echo #
 --echo End of 5.1 tests
 
+--echo #
+--echo # BUG#46680 - Assertion failed in file item_subselect.cc, 
+--echo #             line 305 crashing on HAVING subquery
+--echo #
+
+--echo # Create tables
+--echo #
+
+CREATE TABLE t1 (
+  pk INT,
+  v VARCHAR(1) DEFAULT NULL,
+  PRIMARY KEY(pk)
+);
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+CREATE TABLE empty1 (a int);
+
+INSERT INTO t1 VALUES (1,'c'),(2,NULL);
+INSERT INTO t2 VALUES (3,'m'),(4,NULL);
+INSERT INTO t3 VALUES (1,'n');
+
+--echo
+--echo #
+--echo # 1) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "Impossible WHERE"
+--echo #
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo 
+--echo #
+--echo # 2) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "No matching min/max row"
+--echo #
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+--echo #
+--echo # 3) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "Select tables optimized away"
+--echo #
+--echo # NOTE: The result of this query is actually wrong; it should be NULL
+--echo # See BUG#47762. Even so, the test case is still needed to test
+--echo # that the HAVING subquery does not crash the server
+--echo # 
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo 
+--echo #
+--echo # 4) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "No matching row in const table"
+--echo #
+--echo
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN 
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo 
+--echo #
+--echo # 5) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "Impossible WHERE noticed 
+--echo #    after reading const tables"
+--echo #
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo #
+--echo # Cleanup for BUG#46680
+--echo #
+DROP TABLE IF EXISTS t1,t2,t3,empty1;
+
+###
+--echo End of 6.0 tests

=== modified file 'mysql-test/t/index_merge_innodb.test'
--- a/mysql-test/t/index_merge_innodb.test	2010-12-10 05:55:14 +0000
+++ b/mysql-test/t/index_merge_innodb.test	2011-01-14 10:07:50 +0000
@@ -90,4 +90,37 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly
+--echo #            with Innodb tables
+--echo #
+
+CREATE TABLE t1 (
+  i int(11) DEFAULT NULL,
+  v1 varchar(1) DEFAULT NULL,
+  v2 varchar(20) DEFAULT NULL,
+  KEY i (i),
+  KEY v (v1,i)
+) ENGINE=innodb;
+
+INSERT INTO t1 VALUES (1,'f','no');
+INSERT INTO t1 VALUES (2,'u','yes-u');
+INSERT INTO t1 VALUES (2,'h','yes-h');
+INSERT INTO t1 VALUES (3,'d','no');
+
+--echo
+SELECT v2
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+
+--echo
+--echo # Should not use index_merge
+EXPLAIN
+SELECT v2
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+
+DROP TABLE t1;
+
 set optimizer_switch= @optimizer_switch_save;
+

=== modified file 'mysql-test/t/innodb_mrr.test'
--- a/mysql-test/t/innodb_mrr.test	2011-01-12 12:00:10 +0000
+++ b/mysql-test/t/innodb_mrr.test	2011-01-14 10:07:50 +0000
@@ -378,4 +378,40 @@
 select * from t1 where pk < 2 or pk between 3 and 4;   
 drop table t1;
 set optimizer_use_mrr = @my_save_optimizer_use_mrr;
+--echo #
+--echo # Bug#43360 - Server crash with a simple multi-table update
+--echo #
+CREATE TABLE t1 (
+  a CHAR(2) NOT NULL PRIMARY KEY,
+  b VARCHAR(20) NOT NULL,
+  KEY (b)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+  a CHAR(2) NOT NULL PRIMARY KEY,
+  b VARCHAR(20) NOT NULL,
+  KEY (b)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+('AB','MySQLAB'),
+('JA','Sun Microsystems'),
+('MS','Microsoft'),
+('IB','IBM- Inc.'),
+('GO','Google Inc.');
+
+INSERT INTO t2 VALUES
+('AB','Sweden'),
+('JA','USA'),
+('MS','United States of America'),
+('IB','North America'),
+('GO','South America');
+
+UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'United%';
+
+SELECT * FROM t1;
+
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;
 

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2011-01-05 23:03:30 +0000
+++ b/mysql-test/t/join_cache.test	2011-01-14 10:07:50 +0000
@@ -2845,6 +2845,32 @@
 SET SESSION join_cache_level = DEFAULT;
 
 DROP TABLE t1,t2;
-  
+
+--echo #
+--echo # Backported testcase for: Bug #45092: join buffer contains two blob columns one of which is
+--echo #   used in the key employed to access the joined table
+--echo #
+
+CREATE TABLE t1 (c1 int, c2 int, key (c2));
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (2,2);
+
+CREATE TABLE t2 (c1 text, c2 text);
+INSERT INTO t2 VALUES('tt', 'uu');
+INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
+
+--disable_result_log
+ANALYZE TABLE t1,t2;
+--enable_result_log
+
+set join_cache_level=6;
+
+SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
+  WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
+
+set join_cache_level=default;
+
+DROP TABLE t1,t2;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2010-12-06 08:25:44 +0000
+++ b/mysql-test/t/subselect.test	2011-01-13 16:25:31 +0000
@@ -4251,3 +4251,30 @@
 
 drop table t1,t2,t3;
 drop view v2;
+
+--echo #
+--echo # Bug#52068: Optimizer generates invalid semijoin materialization plan
+--echo #
+--disable_warnings
+drop table if exists ot1, ot2, it1, it2;
+--enable_warnings
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+
+let $query=
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+                        FROM it2,it3);
+
+eval $query;
+eval explain $query;
+
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+
+

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-12-27 00:31:03 +0000
+++ b/sql/sql_join_cache.cc	2011-01-14 10:07:50 +0000
@@ -169,9 +169,17 @@
   if (join_tab->first_sjm_sibling)
     return tab;
   uint i= tab-join->join_tab;
+  /*
+  Temporary measure before MWL#90 refactorings are there: if 'tab' is at upper
+  level (i.e. it's not inside an SJM nest), still include into the join buffer
+  the tables from within SJM nest.  We might need the subquery's select list
+  columns, because SJ-Materialization-Scan upacks data to those. 
+
   while (sj_is_materialize_strategy(join->best_positions[i].sj_strategy) &&
          i < join->tables)
     i+= join->best_positions[i].n_sj_tables;
+
+  */
   return join->join_tab+i < join_tab ? join->join_tab+i : NULL; 
 }
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-27 22:22:05 +0000
+++ b/sql/sql_select.cc	2011-01-14 10:07:50 +0000
@@ -13171,6 +13171,15 @@
       last_tab->read_record.read_record= rr_sequential_and_unpack;
     }
   }
+  else
+  {
+    if (sjm->is_sj_scan)
+    {
+      /* Reset the cursor for a new scan over the table */
+      if (sjm->table->file->ha_rnd_init(TRUE))
+        DBUG_RETURN(NESTED_LOOP_ERROR);
+    }
+  }
 
   if (sjm->is_sj_scan)
   {



More information about the commits mailing list