bk commit into 5.0 tree (igor:1.2456) BUG#27870

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-04-17 17:35:29-07:00, igor@stripped +3 -0
  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@stripped, 2007-04-17 17:35:24-07:00, igor@stripped +19
-0
    Added a test case for bug #27870.

  mysql-test/t/subselect3.test@stripped, 2007-04-17 17:35:24-07:00, igor@stripped +18 -0
    Added a test case for bug #27870.

  sql/item_subselect.h@stripped, 2007-04-17 17:35:24-07:00, igor@stripped +5 -1
    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.        

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug27870

--- 1.88/sql/item_subselect.h	2007-04-17 17:35:34 -07:00
+++ 1.89/sql/item_subselect.h	2007-04-17 17:35:34 -07:00
@@ -276,7 +276,11 @@
   {
     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

--- 1.8/mysql-test/r/subselect3.result	2007-04-17 17:35:34 -07:00
+++ 1.9/mysql-test/r/subselect3.result	2007-04-17 17:35:34 -07:00
@@ -692,3 +692,22 @@
 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;

--- 1.8/mysql-test/t/subselect3.test	2007-04-17 17:35:34 -07:00
+++ 1.9/mysql-test/t/subselect3.test	2007-04-17 17:35:34 -07:00
@@ -528,3 +528,21 @@
 
 
 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;