List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:February 14 2012 2:43pm
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3899 to 3900) Bug#13655397
View as plain text  
 3900 Olav Sandstaa	2012-02-14
      Fix for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
      
      This crash occured due to pushing an index condition down to InnoDB
      that contained a call to a stored function. When the index condition
      was executed from within InnoDB, the stored function caused a second
      call from the server into InnoDB. Since InnoDB does not handle that
      the same session executes more than one operation against InnoDB
      at a given time this triggered an assert in InnoDB.
      
      The fix is to prevent that we push down index conditions to the
      storage engine that contain calls to stored functions.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ mysql-test/r/innodb_icp.result
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ mysql-test/r/innodb_icp_all.result
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ mysql-test/r/innodb_icp_none.result
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ mysql-test/r/myisam_icp.result
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ mysql-test/r/myisam_icp_all.result
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ mysql-test/r/myisam_icp_none.result
        Test case for Bug#13655397 CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX
     @ sql/sql_optimizer.cc
        Bug#13655397: Avoid that pushed down index conditions (ICP) contain
        stored functions.

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_all.result
      mysql-test/r/myisam_icp_none.result
      sql/sql_optimizer.cc
 3899 Sunny Bains	2012-02-14
      Bug #13628249 - ASSERT MAX_UNDO_LOGS == ULINT_UNDEFINED WHEN INNODB-FORCE-RECOVERY >= 5
      
      Disable innodb_bug-13628249.test for embedded builds.

    modified:
      mysql-test/suite/innodb/t/innodb_bug-13628249.test
=== modified file 'mysql-test/include/icp_tests.inc'

=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2012-02-08 15:25:17 +0000
+++ b/mysql-test/include/icp_tests.inc	2012-02-14 14:42:12 +0000
@@ -1119,3 +1119,34 @@
 DROP PROCEDURE proc1;
 
 DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+--echo #
+
+CREATE TABLE t1 (
+  i1 INTEGER NOT NULL,
+  i2 INTEGER NOT NULL,
+  KEY (i1)
+);
+
+INSERT INTO t1 VALUES (4,4), (5,5);
+
+CREATE TABLE t2 (
+  pk INTEGER NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t2 VALUES (1);
+
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+
+let query=
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP FUNCTION f1;
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/innodb_icp.result	2012-02-14 14:42:12 +0000
@@ -1041,5 +1041,29 @@
 20
 DROP PROCEDURE proc1;
 DROP TABLE t1, t2;
+#
+# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+i2 INTEGER NOT NULL,
+KEY (i1)
+);
+INSERT INTO t1 VALUES (4,4), (5,5);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+i1	i2
+5	5
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2012-02-14 14:42:12 +0000
@@ -1041,5 +1041,29 @@
 20
 DROP PROCEDURE proc1;
 DROP TABLE t1, t2;
+#
+# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+i2 INTEGER NOT NULL,
+KEY (i1)
+);
+INSERT INTO t1 VALUES (4,4), (5,5);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+i1	i2
+5	5
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2012-02-14 14:42:12 +0000
@@ -1040,5 +1040,29 @@
 20
 DROP PROCEDURE proc1;
 DROP TABLE t1, t2;
+#
+# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+i2 INTEGER NOT NULL,
+KEY (i1)
+);
+INSERT INTO t1 VALUES (4,4), (5,5);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+i1	i2
+5	5
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/myisam_icp.result	2012-02-14 14:42:12 +0000
@@ -1036,4 +1036,28 @@
 20
 DROP PROCEDURE proc1;
 DROP TABLE t1, t2;
+#
+# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+i2 INTEGER NOT NULL,
+KEY (i1)
+);
+INSERT INTO t1 VALUES (4,4), (5,5);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+i1	i2
+5	5
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2012-02-14 14:42:12 +0000
@@ -1036,4 +1036,28 @@
 20
 DROP PROCEDURE proc1;
 DROP TABLE t1, t2;
+#
+# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+i2 INTEGER NOT NULL,
+KEY (i1)
+);
+INSERT INTO t1 VALUES (4,4), (5,5);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+i1	i2
+5	5
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2012-02-14 14:42:12 +0000
@@ -1035,4 +1035,28 @@
 20
 DROP PROCEDURE proc1;
 DROP TABLE t1, t2;
+#
+# Bug#13655397 "CRASH IN SYNC_THREAD_LEVELS_NONEMPTY_TRX"
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+i2 INTEGER NOT NULL,
+KEY (i1)
+);
+INSERT INTO t1 VALUES (4,4), (5,5);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE FUNCTION f1() RETURNS INTEGER
+RETURN (SELECT MOD(COUNT(DISTINCT pk), 10) FROM t2);
+EXPLAIN SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+SELECT i1, i2 FROM t1 WHERE f1() = 1 AND i1 = 5;
+i1	i2
+5	5
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-02-09 17:07:12 +0000
+++ b/sql/sql_optimizer.cc	2012-02-14 14:42:12 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -3739,24 +3739,31 @@
 
   const Item::Type item_type= item->type();
 
-  /* 
-    Don't push down the triggered conditions. Nested outer joins execution 
-    code may need to evaluate a condition several times (both triggered and
-    untriggered), and there is no way to put thi
-    TODO: Consider cloning the triggered condition and using the copies for:
-      1. push the first copy down, to have most restrictive index condition
-         possible
-      2. Put the second copy into tab->m_condition. 
-  */
-  if (item_type == Item::FUNC_ITEM && 
-      ((Item_func*)item)->functype() == Item_func::TRIG_COND_FUNC)
-    return FALSE;
-
   switch (item_type) {
   case Item::FUNC_ITEM:
     {
+      Item_func *item_func= (Item_func*)item;
+      const Item_func::Functype func_type= item_func->functype();
+
+      /*
+        Avoid some function types from being pushed down to storage engine:
+        - Don't push down the triggered conditions. Nested outer joins
+          execution code may need to evaluate a condition several times
+          (both triggered and untriggered).
+          TODO: Consider cloning the triggered condition and using the
+                copies for: 
+                 1. push the first copy down, to have most restrictive
+                    index condition possible.
+                 2. Put the second copy into tab->m_condition.
+        - Stored functions contain a statement that might start new operations
+          against the storage engine. This does not work against all storage
+          engines.
+      */
+      if (func_type == Item_func::TRIG_COND_FUNC ||
+          func_type == Item_func::FUNC_SP)
+        return false;
+
       /* This is a function, apply condition recursively to arguments */
-      Item_func *item_func= (Item_func*)item;
       if (item_func->argument_count() > 0)
       {        
         Item **item_end= (item_func->arguments()) + item_func->argument_count();

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3899 to 3900) Bug#13655397Olav Sandstaa15 Feb