bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3395 to 3396)

[Ole John Aske]

 3396 Ole John Aske	2010-12-09
      PJS-scan-scan : Updated resultfile as explain output changed slightly after fix for #58818 
      may add an extra 'IS TRUE' to the having predicate.

    modified:
      mysql-test/r/subselect3.result
 3395 Ole John Aske	2010-12-08
      SPJ-sca-scan: Cherry picked fix for bug#58818: Incorrect result for IN/ANY subquery with HAVING condition
      
      The fix is to ensure that the HAVING condition 'IS TRUE' when doing a
      Item_in_subselect::single_value_transformer
      
      If the ::single_value_transformer() find an existing HAVING condition it used
      to do the transformation:
      
         1) HAVING cond -> (HAVING Cond) AND (cond_guard (Item_ref_null_helper(...))
      
      As the AND condition above is Mc'Carty evaluated, the idea was that the
      right side of the AND cond would be executed only if the HAVING evaluated to true.
      
      This logic failed to considder the 3-value logic required by NULL/UNKNOWN evaluation.
      Evaluating 'UNKNOWN AND <right cond>' requires the right AND-argument to be evaluated and
      'false' returned if <right cond> evaluated to false, else unknown.
      
      To get the required behaviour this fix change the transformation in 1) to be:
      
         2) HAVING cond -> ((HAVING Cond) IS TRUE) AND (cond_guard (Item_ref_null_helper(...))

    modified:
      mysql-test/r/subselect.result
      mysql-test/t/subselect.test
      sql/item_subselect.cc
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2010-03-09 10:36:26 +0000
+++ b/mysql-test/r/subselect3.result	2010-12-09 08:04:55 +0000
@@ -209,7 +209,7 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
 Warnings:
 Note	1276	Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having (((`test`.`t1`.`grp` = `test`.`t2`.`oref`) is true) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
 drop table t1, t2;
 create table t1 (a int, b int, primary key (a));
 insert into t1 values (1,1), (3,1),(100,1);

No bundle (reason: useless for push emails).