From 579c2bff63fb8833226d86afb6ac2eab0ead9737 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 17 Apr 2007 17:35:29 -0700 Subject: Fixed bug #27870. The bug that causes crashes manifests itself at some conditions when executing an equijoin query with WHERE condition containing a subquery predicate of the form join_attr NOT IN (SELECT ...). To resolve a problem of the correct evaluation of the expression attr NOT IN (SELECT ...) an array of guards is created to make it possible to filter out some predicates of the EXISTS subquery into which the original subquery predicate is transformed, in the cases when a takes the NULL value. If attr is defined as a field that cannot be NULL than such an array is not needed and is not created. However if the field a occurred also an an equijoin predicate t2.a=t1.b and table t1 is accessed before table t2 then it may happen that the the EXISTS subquery is pushed down to the condition evaluated just after table t1 has been accessed. In this case any occurrence of t2.a is substituted for t1.b. When t1.b takes the value of NULL an attempt is made to turn on the corresponding guard. This action caused a crash as no guard array had been created. Now the code of Item_in_subselect::set_cond_guard_var checks that the guard array has been created before setting a guard variable on. Otherwise the method does nothing. It cannot results in returning a row that could be rejected as the condition t2.a=t1.b will be checked later anyway. mysql-test/r/subselect3.result: Added a test case for bug #27870. mysql-test/t/subselect3.test: Added a test case for bug #27870. sql/item_subselect.h: Fixed bug #27870. The bug that causes crashes manifests itself at some conditions when executing an equijoin query with WHERE condition containing a subquery predicate of the form join_attr NOT IN (SELECT ...). Forced Item_in_subselect::set_cond_guard_var to check that the guard array has been created before setting a guard variable on. Otherwise the method does nothing. --- mysql-test/r/bdb_notembedded.result | 35 ++++++++++++++++++++++++++++++++++ mysql-test/r/subselect3.result | 19 +++++++++++++++++++ mysql-test/t/bdb_notembedded.test | 38 +++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect3.test | 18 ++++++++++++++++++ sql/item_subselect.h | 6 +++++- 5 files changed, 115 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/bdb_notembedded.result create mode 100644 mysql-test/t/bdb_notembedded.test diff --git a/mysql-test/r/bdb_notembedded.result b/mysql-test/r/bdb_notembedded.result new file mode 100644 index 00000000000..14cb5fad915 --- /dev/null +++ b/mysql-test/r/bdb_notembedded.result @@ -0,0 +1,35 @@ +set autocommit=1; +reset master; +create table bug16206 (a int); +insert into bug16206 values(1); +start transaction; +insert into bug16206 values(2); +commit; +show binlog events; +Log_name Pos Event_type Server_id End_log_pos Info +f n Format_desc 1 n Server ver: VERSION, Binlog ver: 4 +f n Query 1 n use `test`; create table bug16206 (a int) +f n Query 1 n use `test`; insert into bug16206 values(1) +f n Query 1 n use `test`; insert into bug16206 values(2) +drop table bug16206; +reset master; +create table bug16206 (a int) engine= bdb; +insert into bug16206 values(0); +insert into bug16206 values(1); +start transaction; +insert into bug16206 values(2); +commit; +insert into bug16206 values(3); +show binlog events; +Log_name Pos Event_type Server_id End_log_pos Info +f n Format_desc 1 n Server ver: VERSION, Binlog ver: 4 +f n Query 1 n use `test`; create table bug16206 (a int) engine= bdb +f n Query 1 n use `test`; insert into bug16206 values(0) +f n Query 1 n use `test`; insert into bug16206 values(1) +f n Query 1 n use `test`; BEGIN +f n Query 1 n use `test`; insert into bug16206 values(2) +f n Query 1 n use `test`; COMMIT +f n Query 1 n use `test`; insert into bug16206 values(3) +drop table bug16206; +set autocommit=0; +End of 5.0 tests diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 33e7fc54ed2..f52249db8a1 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -692,3 +692,22 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int, PRIMARY KEY(b)); +INSERT INTO t1 VALUES (1), (NULL), (4); +INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6); +EXPLAIN EXTENDED +SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not((`test`.`t1`.`a`,(select 1 AS `Not_used` from `test`.`t1` where (((`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having (`test`.`t1`.`a`)))))) +SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); +a +SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); +a +1 +4 +DROP TABLE t1,t2; diff --git a/mysql-test/t/bdb_notembedded.test b/mysql-test/t/bdb_notembedded.test new file mode 100644 index 00000000000..24e64ebbfb2 --- /dev/null +++ b/mysql-test/t/bdb_notembedded.test @@ -0,0 +1,38 @@ +-- source include/not_embedded.inc +-- source include/have_bdb.inc + +# +# Bug #16206: Superfluous COMMIT event in binlog when updating BDB in autocommit mode +# +set autocommit=1; + +let $VERSION=`select version()`; + +reset master; +create table bug16206 (a int); +insert into bug16206 values(1); +start transaction; +insert into bug16206 values(2); +commit; +--replace_result $VERSION VERSION +--replace_column 1 f 2 n 5 n +show binlog events; +drop table bug16206; + +reset master; +create table bug16206 (a int) engine= bdb; +insert into bug16206 values(0); +insert into bug16206 values(1); +start transaction; +insert into bug16206 values(2); +commit; +insert into bug16206 values(3); +--replace_result $VERSION VERSION +--replace_column 1 f 2 n 5 n +show binlog events; +drop table bug16206; + +set autocommit=0; + + +--echo End of 5.0 tests diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 11468cd6759..dfe09968fa2 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -528,3 +528,21 @@ SELECT a, MAX(b), DROP TABLE t1, t2; + + +# +# Bug #27870: crash of an equijoin query with WHERE condition containing +# a subquery predicate of the form NOT IN (SELECT ...) +# + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int, PRIMARY KEY(b)); +INSERT INTO t1 VALUES (1), (NULL), (4); +INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6); + +EXPLAIN EXTENDED +SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); +SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); +SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); + +DROP TABLE t1,t2; diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 6b605e96432..118609671b8 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -276,7 +276,11 @@ public: { return pushed_cond_guards ? pushed_cond_guards + i : NULL; } - void set_cond_guard_var(int i, bool v) { pushed_cond_guards[i]= v; } + void set_cond_guard_var(int i, bool v) + { + if ( pushed_cond_guards) + pushed_cond_guards[i]= v; + } bool have_guarded_conds() { return test(pushed_cond_guards); } Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery -- cgit v1.2.1