MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:January 19 2010 12:27pm
Subject:bzr commit into mysql-6.0-codebase-bugfixing branch (olav:3832)
View as plain text  
#At file:///export/home/tmp/olav/opt-icpbug-tests/ based on revid:tor.didriksen@stripped

 3832 Olav Sandstaa	2010-01-19
      Test cases for the following "ICP for InnoDB" bugs:
      
      Bug#40992 InnoDB: Crash when engine_condition_pushdown is on
      Bug#35080 Innodb crash at mem_block_get_len line 72
      Bug#41996 multi-table delete crashes server (InnoDB table)
      Bug#43448 Server crashes on multi table delete with Innodb
      
      All these bugs are duplicates of either one or both of Bug 43360 or 36981.
      Note that in order to run these tests successfully the patches for bug 43360 
      and 36981 must be applied first.
     @ mysql-test/include/icp_tests.inc
        Test cases for the following "ICP for InnoDB" bugs:
        
        Bug#43360 Server crash with a simple multi-table update (reviewed earlier)
        Bug#40992 InnoDB: Crash when engine_condition_pushdown is on
        Bug#35080 Innodb crash at mem_block_get_len line 72
        Bug#41996 multi-table delete crashes server (InnoDB table)
        Bug#43448 Server crashes on multi table delete with Innodb
     @ mysql-test/r/innodb_icp.result
        Result file
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug40992.result
        Deleted test result file
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug41996.result
        Deleted test result file
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug43448.result
        Deleted test result file
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug40992.test
        Moved to the new include/icp_tests.inc file
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug41996.test
        Moved to the new include/icp_tests.inc file
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test
        Moved to the new include/icp_tests.inc file
     @ mysql-test/t/innodb_icp.test
        New ICP test for InnoDB. This is taken from the patch for Bug#43360 
        (already reviewed but not pushed)

    removed:
      mysql-test/suite/optimizer_unfixed_bugs/r/bug40992.result
      mysql-test/suite/optimizer_unfixed_bugs/r/bug41996.result
      mysql-test/suite/optimizer_unfixed_bugs/r/bug43448.result
      mysql-test/suite/optimizer_unfixed_bugs/t/bug40992.test
      mysql-test/suite/optimizer_unfixed_bugs/t/bug41996.test
      mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test
    added:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/t/innodb_icp.test
=== added file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/icp_tests.inc	2010-01-19 12:27:43 +0000
@@ -0,0 +1,177 @@
+--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)
+);
+
+CREATE TABLE t2 (
+  a CHAR(2) NOT NULL PRIMARY KEY,
+  b VARCHAR(30) NOT NULL,
+  KEY (b)
+);
+
+INSERT INTO t1 VALUES 
+('AB','MySQL AB'),
+('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 ORDER BY a;
+
+SELECT * FROM t2 ORDER BY a;
+
+DROP TABLE t1,t2;   
+
+--echo #
+--echo # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
+--echo #
+
+CREATE TABLE t (
+  dummy INT PRIMARY KEY, 
+  a INT UNIQUE, 
+  b int
+);
+
+INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
+
+SELECT * FROM t WHERE a > 2 FOR UPDATE;
+
+DROP TABLE t;
+
+--echo #
+--echo # Bug#35080 - Innodb crash at mem_block_get_len line 72
+--echo #
+
+CREATE TABLE t1 (
+  t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
+  t1_uuid VARCHAR(36) DEFAULT NULL,
+  t2_uuid CHAR(36) DEFAULT NULL,
+  PRIMARY KEY (t1_autoinc),
+  KEY t2_uuid (t2_uuid),
+  KEY k1 (t1_uuid)
+);
+
+CREATE TABLE t2 (
+  t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
+  t1_uuid VARCHAR(36) DEFAULT NULL,
+  t2_uuid CHAR(36) DEFAULT NULL,
+  t2_date DATETIME DEFAULT NULL,
+  PRIMARY KEY (t2_autoinc),
+  KEY t1_uuid (t1_uuid),
+  KEY k1 (t2_uuid)
+);
+
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW v1 AS SELECT t1_autoinc, t1_uuid, t2_uuid FROM t1
+         WHERE (ISNULL(t1_uuid) OR (t1_uuid like '%-%')) */;
+
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW v2 AS SELECT t2_autoinc, t1_uuid, t2_uuid, t2_date FROM t2
+         WHERE (ISNULL(t1_uuid) OR (LENGTH(t1_uuid) = 36)) */;
+
+delimiter |;
+
+CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_multi`(IN uuid CHAR(36))
+BEGIN
+DELETE v1, v2 FROM v1 INNER JOIN v2
+ON v1.t1_uuid = v2.t1_uuid
+WHERE v1.t1_uuid = @uuid;
+END |
+
+delimiter ;|
+
+SET @uuid = UUID();
+
+INSERT INTO v1 (t1_uuid) VALUES (@uuid);
+INSERT INTO v2 (t1_uuid, t2_date) VALUES (@uuid, '2009-09-09');
+
+CALL delete_multi(@uuid);
+
+DROP procedure delete_multi;
+DROP table t1,t2;
+DROP view v1,v2;
+
+--echo #
+--echo # Bug#41996 - multi-table delete crashes server (InnoDB table)
+--echo #
+
+CREATE TABLE t1 (
+  b BIGINT,
+  i INT, 
+  KEY (b)
+);
+
+INSERT INTO t1 VALUES (2, 2);
+
+DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#43448 - Server crashes on multi table delete with Innodb
+--echo #
+
+CREATE TABLE t1 (
+  id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
+  t CHAR(12)
+);
+
+CREATE TABLE t2 (
+  id2 INT NOT NULL, 
+  t CHAR(12)
+);
+
+CREATE TABLE t3(
+  id3 INT NOT NULL, 
+  t CHAR(12), 
+  INDEX(id3)
+);
+
+disable_query_log;
+
+let $1 = 20;
+while ($1)
+{
+  let $2 = 2;
+  eval INSERT INTO t1(t) VALUES ('$1');
+  while ($2)
+  {
+    eval INSERT INTO t2(id2,t) VALUES ($1,'$2');
+    let $3 = 2;
+    while ($3)
+    {
+      eval INSERT INTO t3(id3,t) VALUES ($1,'$2');
+      dec $3;
+    }
+    dec $2;
+  }
+  dec $1;
+}
+
+enable_query_log;
+
+SELECT COUNT(*) FROM t1 WHERE id1 > 10;
+SELECT COUNT(*) FROM t2 WHERE id2 > 10;
+SELECT COUNT(*) FROM t3 WHERE id3 > 10;
+
+DELETE t1, t2, t3 
+FROM t1, t2, t3 
+WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
+
+DROP TABLE t1, t2, t3;

=== added file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/innodb_icp.result	2010-01-19 12:27:43 +0000
@@ -0,0 +1,140 @@
+set session debug="+d,optimizer_innodb_icp";
+set @save_storage_engine= @@storage_engine;
+set storage_engine=InnoDB;
+#
+# 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)
+);
+CREATE TABLE t2 (
+a CHAR(2) NOT NULL PRIMARY KEY,
+b VARCHAR(30) NOT NULL,
+KEY (b)
+);
+INSERT INTO t1 VALUES 
+('AB','MySQL AB'),
+('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 ORDER BY a;
+a	b
+AB	MySQL AB
+GO	Google Inc.
+IB	IBM- Inc.
+JA	Sun Microsystems
+MS	Microsoft
+SELECT * FROM t2 ORDER BY a;
+a	b
+AB	Sweden
+GO	South America
+IB	North America
+JA	USA
+MS	United States of America
+DROP TABLE t1,t2;
+#
+# Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
+#
+CREATE TABLE t (
+dummy INT PRIMARY KEY, 
+a INT UNIQUE, 
+b int
+);
+INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
+SELECT * FROM t WHERE a > 2 FOR UPDATE;
+dummy	a	b
+3	3	3
+5	5	5
+DROP TABLE t;
+#
+# Bug#35080 - Innodb crash at mem_block_get_len line 72
+#
+CREATE TABLE t1 (
+t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
+t1_uuid VARCHAR(36) DEFAULT NULL,
+t2_uuid CHAR(36) DEFAULT NULL,
+PRIMARY KEY (t1_autoinc),
+KEY t2_uuid (t2_uuid),
+KEY k1 (t1_uuid)
+);
+CREATE TABLE t2 (
+t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
+t1_uuid VARCHAR(36) DEFAULT NULL,
+t2_uuid CHAR(36) DEFAULT NULL,
+t2_date DATETIME DEFAULT NULL,
+PRIMARY KEY (t2_autoinc),
+KEY t1_uuid (t1_uuid),
+KEY k1 (t2_uuid)
+);
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW v1 AS SELECT t1_autoinc, t1_uuid, t2_uuid FROM t1
+WHERE (ISNULL(t1_uuid) OR (t1_uuid like '%-%')) */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW v2 AS SELECT t2_autoinc, t1_uuid, t2_uuid, t2_date FROM t2
+WHERE (ISNULL(t1_uuid) OR (LENGTH(t1_uuid) = 36)) */;
+CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_multi`(IN uuid CHAR(36))
+BEGIN
+DELETE v1, v2 FROM v1 INNER JOIN v2
+ON v1.t1_uuid = v2.t1_uuid
+WHERE v1.t1_uuid = @uuid;
+END |
+SET @uuid = UUID();
+INSERT INTO v1 (t1_uuid) VALUES (@uuid);
+INSERT INTO v2 (t1_uuid, t2_date) VALUES (@uuid, '2009-09-09');
+CALL delete_multi(@uuid);
+DROP procedure delete_multi;
+DROP table t1,t2;
+DROP view v1,v2;
+#
+# Bug#41996 - multi-table delete crashes server (InnoDB table)
+#
+CREATE TABLE t1 (
+b BIGINT,
+i INT, 
+KEY (b)
+);
+INSERT INTO t1 VALUES (2, 2);
+DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
+DROP TABLE t1;
+#
+# Bug#43448 - Server crashes on multi table delete with Innodb
+#
+CREATE TABLE t1 (
+id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
+t CHAR(12)
+);
+CREATE TABLE t2 (
+id2 INT NOT NULL, 
+t CHAR(12)
+);
+CREATE TABLE t3(
+id3 INT NOT NULL, 
+t CHAR(12), 
+INDEX(id3)
+);
+SELECT COUNT(*) FROM t1 WHERE id1 > 10;
+COUNT(*)
+10
+SELECT COUNT(*) FROM t2 WHERE id2 > 10;
+COUNT(*)
+20
+SELECT COUNT(*) FROM t3 WHERE id3 > 10;
+COUNT(*)
+40
+DELETE t1, t2, t3 
+FROM t1, t2, t3 
+WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
+DROP TABLE t1, t2, t3;
+set storage_engine= @save_storage_engine;

=== removed file 'mysql-test/suite/optimizer_unfixed_bugs/r/bug40992.result'
--- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug40992.result	2009-10-29 13:02:56 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug40992.result	1970-01-01 00:00:00 +0000
@@ -1,14 +0,0 @@
-#
-# Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
-#
-CREATE TABLE t (
-dummy INT PRIMARY KEY, 
-a INT UNIQUE, 
-b int
-) ENGINE=InnoDB;
-INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
-SELECT * FROM t WHERE a > 2 FOR UPDATE;
-dummy	a	b
-3	3	3
-5	5	5
-DROP TABLE t;

=== removed file 'mysql-test/suite/optimizer_unfixed_bugs/r/bug41996.result'
--- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug41996.result	2009-10-09 19:45:32 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug41996.result	1970-01-01 00:00:00 +0000
@@ -1,7 +0,0 @@
-drop table if exists `t1`;
-Warnings:
-Note	1051	Unknown table 't1'
-create table `t1` (`c` bigint, key(`c`),`a` int)engine=innodb;
-insert into `t1` values(2,2);
-delete `t1` from `t1` `a`, `t1` where `a`.`a`=`t1`.`c` ;
-drop table `t1`;

=== removed file 'mysql-test/suite/optimizer_unfixed_bugs/r/bug43448.result'
--- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug43448.result	2009-11-13 14:41:07 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug43448.result	1970-01-01 00:00:00 +0000
@@ -1,29 +0,0 @@
-#
-# Bug#43448 - Server crashes on multi table delete with Innodb
-#
-CREATE TABLE t1 (
-id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
-t CHAR(12)
-) ENGINE=InnoDB;
-CREATE TABLE t2 (
-id2 INT NOT NULL, 
-t CHAR(12)
-) ENGINE=InnoDB;
-CREATE TABLE t3(
-id3 INT NOT NULL, 
-t CHAR(12), 
-INDEX(id3)
-) ENGINE=InnoDB;
-SELECT COUNT(*) FROM t1 WHERE id1 > 90;
-COUNT(*)
-10
-SELECT COUNT(*) FROM t2 WHERE id2 > 90;
-COUNT(*)
-50
-SELECT COUNT(*) FROM t3 WHERE id3 > 90;
-COUNT(*)
-500
-DELETE t1, t2, t3 
-FROM t1, t2, t3 
-WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 5;
-DROP TABLE t1, t2, t3;

=== removed file 'mysql-test/suite/optimizer_unfixed_bugs/t/bug40992.test'
--- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug40992.test	2009-10-29 13:02:56 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug40992.test	1970-01-01 00:00:00 +0000
@@ -1,21 +0,0 @@
---echo #
---echo # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
---echo #
-
---source include/have_debug.inc
---source include/have_innodb.inc
-
-# Crash requires that we enable Index Condition Pushdown in InnoDB
-set session debug="+d,optimizer_innodb_icp";
-
-CREATE TABLE t (
-  dummy INT PRIMARY KEY, 
-  a INT UNIQUE, 
-  b int
-) ENGINE=InnoDB;
-
-INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
-
-SELECT * FROM t WHERE a > 2 FOR UPDATE;
-
-DROP TABLE t;

=== removed file 'mysql-test/suite/optimizer_unfixed_bugs/t/bug41996.test'
--- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug41996.test	2009-10-09 19:45:32 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug41996.test	1970-01-01 00:00:00 +0000
@@ -1,14 +0,0 @@
-# Test for BUG#41996 "multi-table delete crashes server (InnoDB
-# table)"
-
---source include/have_debug.inc
---source include/have_innodb.inc
-
-# crash requires this
-set session debug="+d,optimizer_innodb_icp";
-
-drop table if exists `t1`;
-create table `t1` (`c` bigint, key(`c`),`a` int)engine=innodb;
-insert into `t1` values(2,2);
-delete `t1` from `t1` `a`, `t1` where `a`.`a`=`t1`.`c` ;
-drop table `t1`;

=== removed file 'mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test'
--- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test	2009-11-13 14:41:07 +0000
+++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test	1970-01-01 00:00:00 +0000
@@ -1,58 +0,0 @@
---echo #
---echo # Bug#43448 - Server crashes on multi table delete with Innodb
---echo #
-
---source include/have_debug.inc
---source include/have_innodb.inc
-
-# crash requires ICP support in InnoDB
-set session debug="+d,optimizer_innodb_icp";
-
-CREATE TABLE t1 (
-  id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
-  t CHAR(12)
-) ENGINE=InnoDB;
-
-CREATE TABLE t2 (
-  id2 INT NOT NULL, 
-  t CHAR(12)
-) ENGINE=InnoDB;
-
-CREATE TABLE t3(
-  id3 INT NOT NULL, 
-  t CHAR(12), 
-  INDEX(id3)
-) ENGINE=InnoDB;
-
-disable_query_log;
-
-let $1 = 100;
-while ($1)
-{
-  let $2 = 5;
-  eval INSERT INTO t1(t) VALUES ('$1');
-  while ($2)
-  {
-    eval INSERT INTO t2(id2,t) VALUES ($1,'$2');
-    let $3 = 10;
-    while ($3)
-    {
-      eval INSERT INTO t3(id3,t) VALUES ($1,'$2');
-      dec $3;
-    }
-    dec $2;
-  }
-  dec $1;
-}
-
-enable_query_log;
-
-SELECT COUNT(*) FROM t1 WHERE id1 > 90;
-SELECT COUNT(*) FROM t2 WHERE id2 > 90;
-SELECT COUNT(*) FROM t3 WHERE id3 > 90;
-
-DELETE t1, t2, t3 
-FROM t1, t2, t3 
-WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 5;
-
-DROP TABLE t1, t2, t3;

=== added file 'mysql-test/t/innodb_icp.test'
--- a/mysql-test/t/innodb_icp.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/innodb_icp.test	2010-01-19 12:27:43 +0000
@@ -0,0 +1,22 @@
+#
+# ICP/InnoDB tests (Index Condition Pushdown)
+#
+
+--source include/have_innodb.inc
+
+# Index condition pushdown (ICP) is by default disabled in InnoDB. Support
+# for ICP can be enabled in InnoDB by using the optimizer_innodb_icp
+# debug flag. This will only be available in debug builds. 
+# When/if ICP get enabled by default the following two lines should
+# be removed.
+
+--source include/have_debug.inc
+set session debug="+d,optimizer_innodb_icp";
+
+
+set @save_storage_engine= @@storage_engine;
+set storage_engine=InnoDB;
+
+--source include/icp_tests.inc
+
+set storage_engine= @save_storage_engine;


Attachment: [text/bzr-bundle] bzr/olav@sun.com-20100119122743-ihd0owcg5e65c87x.bundle
Thread
bzr commit into mysql-6.0-codebase-bugfixing branch (olav:3832) Olav Sandstaa19 Jan
  • Re: bzr commit into mysql-6.0-codebase-bugfixing branch (olav:3832)Olav Sandstaa21 Jan
    • Re: bzr commit into mysql-6.0-codebase-bugfixing branch (olav:3832)Jørgen Løland26 Jan
      • Re: bzr commit into mysql-6.0-codebase-bugfixing branch (olav:3832)Olav Sandstaa3 Feb