diff options
| -rw-r--r-- | mysql-test/r/bdb_notembedded.result | 35 | ||||
| -rw-r--r-- | mysql-test/r/subselect3.result | 19 | ||||
| -rw-r--r-- | mysql-test/t/bdb_notembedded.test | 38 | ||||
| -rw-r--r-- | mysql-test/t/subselect3.test | 18 | ||||
| -rw-r--r-- | sql/item_subselect.h | 6 |
5 files changed, 115 insertions, 1 deletions
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(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`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 <join attr> 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 |