[Commits] Rev 3395: Fixed LP bug #921167. in file:///home/igor/maria/maria-5.3-mwl247-merge-bug921167/

Igor Babaev igor at askmonty.org
Wed Jan 25 09:34:03 EET 2012


At file:///home/igor/maria/maria-5.3-mwl247-merge-bug921167/

------------------------------------------------------------
revno: 3395
revision-id: igor at askmonty.org-20120125073402-aan4r8l22ulhidig
parent: igor at askmonty.org-20120125051202-yip1anngo14dj87y
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl247-merge-bug921167
timestamp: Tue 2012-01-24 23:34:02 -0800
message:
  Fixed LP bug #921167.
  The fields ext_key_flags and ext_key_part_map must be initialized for any
  key, even for a MyISAM key that never is considered by the optimizer as one
  extended by hidden components. 
-------------- next part --------------
=== modified file 'mysql-test/r/innodb_ext_key.result'
--- a/mysql-test/r/innodb_ext_key.result	2012-01-25 05:12:02 +0000
+++ b/mysql-test/r/innodb_ext_key.result	2012-01-25 07:34:02 +0000
@@ -65,7 +65,7 @@
 select count(*) from lineitem
 where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	const	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	12	const,const,const	1	Using index
+1	SIMPLE	lineitem	const	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	8	const,const	1	
 flush status;
 select count(*) from lineitem 
 where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
@@ -632,5 +632,50 @@
 DROP VIEW v;
 DROP TABLE t1,t2,t3;
 set optimizer_switch=@save_optimizer_switch;
+#
+# LP Bug #921167: query containing IN subquery
+#                 + extended_keys = on 
+#
+CREATE TABLE t1 (
+a int NOT NULL, b varchar(1) NOT NULL, KEY(a), KEY(b,a)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(0,'j'), (8,'v'), (1,'c'), (8,'m'), (9,'d'), 
+(24,'d'), (6,'y'), (1,'t'), (6,'d'), (2,'s');
+CREATE TABLE t2 (
+c int NOT NULL PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10), (11), (12), (13), (14), 
+(15), (16), (17), (18), (19), (24);
+set @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'extended_keys=off';
+EXPLAIN
+SELECT a FROM t1 AS t, t2 
+WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
+1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; FirstMatch(t)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t2)
+SELECT a FROM t1 AS t, t2 
+WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+a
+24
+SET optimizer_switch = 'extended_keys=on';
+EXPLAIN
+SELECT a FROM t1 AS t, t2 
+WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
+1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; FirstMatch(t)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t2)
+SELECT a FROM t1 AS t, t2 
+WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+a
+24
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
 set optimizer_switch=@save_ext_key_optimizer_switch;
 SET SESSION STORAGE_ENGINE=DEFAULT;

=== modified file 'mysql-test/t/innodb_ext_key.test'
--- a/mysql-test/t/innodb_ext_key.test	2012-01-19 03:38:03 +0000
+++ b/mysql-test/t/innodb_ext_key.test	2012-01-25 07:34:02 +0000
@@ -342,5 +342,44 @@
 
 set optimizer_switch=@save_optimizer_switch;
 
+--echo #
+--echo # LP Bug #921167: query containing IN subquery
+--echo #                 + extended_keys = on 
+--echo #
+
+CREATE TABLE t1 (
+  a int NOT NULL, b varchar(1) NOT NULL, KEY(a), KEY(b,a)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+  (0,'j'), (8,'v'), (1,'c'), (8,'m'), (9,'d'), 
+  (24,'d'), (6,'y'), (1,'t'), (6,'d'), (2,'s');
+
+CREATE TABLE t2 (
+  c int NOT NULL PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+  (10), (11), (12), (13), (14), 
+  (15), (16), (17), (18), (19), (24);
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+SET optimizer_switch = 'extended_keys=off';
+EXPLAIN
+SELECT a FROM t1 AS t, t2 
+  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+SELECT a FROM t1 AS t, t2 
+  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+
+SET optimizer_switch = 'extended_keys=on';
+EXPLAIN
+SELECT a FROM t1 AS t, t2 
+  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+SELECT a FROM t1 AS t, t2 
+  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
 set optimizer_switch=@save_ext_key_optimizer_switch;
 SET SESSION STORAGE_ENGINE=DEFAULT;

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2012-01-17 11:26:49 +0000
+++ b/sql/table.cc	2012-01-25 07:34:02 +0000
@@ -907,6 +907,8 @@
       key_part->store_length=key_part->length;
     }
     keyinfo->ext_key_parts= keyinfo->key_parts;
+    keyinfo->ext_key_flags= keyinfo->flags;
+    keyinfo->ext_key_part_map= 0;
     if (share->use_ext_keys && i)
     {
       keyinfo->ext_key_flags= keyinfo->flags | HA_NOSAME;



More information about the commits mailing list