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#13655397 | Olav Sandstaa | 15 Feb |
