[Commits] Rev 2844: Test case for LP BUG#641245 in file:///home/tsk/mprog/src/5.3-mwl89/

timour at askmonty.org timour at askmonty.org
Fri Nov 5 17:10:55 EET 2010


At file:///home/tsk/mprog/src/5.3-mwl89/

------------------------------------------------------------
revno: 2844
revision-id: timour at askmonty.org-20101105151048-owgvmj1z8o8g98md
parent: timour at askmonty.org-20101105124258-nakbav615mi9tv0y
committer: timour at askmonty.org
branch nick: 5.3-mwl89
timestamp: Fri 2010-11-05 17:10:48 +0200
message:
  Test case for LP BUG#641245
  
  The bug itself got fixed after merging with the main 5.3.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_mat_cost.result'
--- a/mysql-test/r/subselect_mat_cost.result	2010-11-02 13:27:01 +0000
+++ b/mysql-test/r/subselect_mat_cost.result	2010-11-05 15:10:48 +0000
@@ -3820,3 +3820,58 @@ WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FRO
 c2
 set session optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
+#
+# LP BUG#641245 Crash in Item_equal::contains
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+c2 int(11) DEFAULT NULL,
+c3 varchar(1) DEFAULT NULL,
+c4 varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY c2 (c2),
+KEY c3 (c3,c2));
+INSERT INTO t1 VALUES (10,7,8,'v','v');
+INSERT INTO t1 VALUES (11,1,9,'r','r');
+INSERT INTO t1 VALUES (12,5,9,'a','a');
+create table t1a like t1;
+insert into t1a select * from t1;
+create table t1b like t1;
+insert into t1b select * from t1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+c2 int(11) DEFAULT NULL,
+c3 varchar(1) DEFAULT NULL,
+c4 varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY c2 (c2),
+KEY c3 (c3,c2));
+INSERT INTO t2 VALUES (1,NULL,2,'w','w');
+INSERT INTO t2 VALUES (2,7,9,'m','m');
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
+EXPLAIN EXTENDED SELECT pk
+FROM t1
+WHERE c1 IN
+(SELECT t1a.c1
+FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN
+t1a ON (t1a.c2 = t1b.pk AND 2)
+WHERE t1.pk) ;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
+2       DEPENDENT SUBQUERY      t2      index   c3      c3      9       NULL    2       100.00  Using index
+2       DEPENDENT SUBQUERY      t1b     ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer
+2       DEPENDENT SUBQUERY      t1a     ref     c2      c2      5       test.t1b.pk     2       100.00  Using where
+Warnings:
+Note    1276    Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
+Note    1003    select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,(`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where (`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`)))))
+SELECT pk
+FROM t1
+WHERE c1 IN
+(SELECT t1a.c1
+FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN
+t1a ON (t1a.c2 = t1b.pk AND 2)
+WHERE t1.pk) ;
+pk
+DROP TABLE t1, t1a, t1b, t2;

=== modified file 'mysql-test/t/subselect_mat_cost.test'
--- a/mysql-test/t/subselect_mat_cost.test	2010-11-02 13:27:01 +0000
+++ b/mysql-test/t/subselect_mat_cost.test	2010-11-05 15:10:48 +0000
@@ -269,3 +269,56 @@ WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FRO
 
 set session optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
+
+
+--echo #
+--echo # LP BUG#641245 Crash in Item_equal::contains
+--echo #
+
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  c1 int(11) DEFAULT NULL,
+  c2 int(11) DEFAULT NULL,
+  c3 varchar(1) DEFAULT NULL,
+  c4 varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY c2 (c2),
+  KEY c3 (c3,c2));
+
+INSERT INTO t1 VALUES (10,7,8,'v','v');
+INSERT INTO t1 VALUES (11,1,9,'r','r');
+INSERT INTO t1 VALUES (12,5,9,'a','a');
+
+create table t1a like t1;
+insert into t1a select * from t1;
+
+create table t1b like t1;
+insert into t1b select * from t1;
+
+CREATE TABLE t2 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  c1 int(11) DEFAULT NULL,
+  c2 int(11) DEFAULT NULL,
+  c3 varchar(1) DEFAULT NULL,
+  c4 varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY c2 (c2),
+  KEY c3 (c3,c2));
+
+INSERT INTO t2 VALUES (1,NULL,2,'w','w');
+INSERT INTO t2 VALUES (2,7,9,'m','m');
+
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
+
+let $query=
+SELECT pk
+FROM t1
+WHERE c1 IN
+      (SELECT t1a.c1
+       FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN
+            t1a ON (t1a.c2 = t1b.pk AND 2)
+       WHERE t1.pk) ;
+eval EXPLAIN EXTENDED $query;
+eval $query;
+
+DROP TABLE t1, t1a, t1b, t2;



More information about the commits mailing list