[Commits] Rev 3021: LP BUG#823169 fix. in file:///home/bell/maria/bzr/work-maria-5.2-lpb823169/

sanja at askmonty.org sanja at askmonty.org
Wed Aug 31 15:32:08 EEST 2011


At file:///home/bell/maria/bzr/work-maria-5.2-lpb823169/

------------------------------------------------------------
revno: 3021
revision-id: sanja at askmonty.org-20110831123206-cxywpz9vqvwy99he
parent: monty at askmonty.org-20110829173821-7otwpzhurkzovjc3
committer: sanja at askmonty.org
branch nick: work-maria-5.2-lpb823169
timestamp: Wed 2011-08-31 15:32:06 +0300
message:
  LP BUG#823169 fix.
  
  When max/min subquery looking for max/min NULLs should be ignored for transformed ANY subqueries and
  always be result (if found) for transformed ALL subqueries.
  
  Transformation with aggregate function for ALL and and possible NULLs should be prohibited because it
  could not be calculated correctly.
-------------- next part --------------
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result	2011-07-21 09:29:00 +0000
+++ b/mysql-test/r/explain.result	2011-08-31 12:32:06 +0000
@@ -171,7 +171,7 @@ DROP TABLE t1;
 # Bug#48295:
 # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode
 #
-CREATE TABLE t1 (f1 INT);
+CREATE TABLE t1 (f1 INT not null);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
 EXPLAIN EXTENDED SELECT 1 FROM t1

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-05-02 17:58:45 +0000
+++ b/mysql-test/r/subselect.result	2011-08-31 12:32:06 +0000
@@ -1483,7 +1483,7 @@ id	select_type	table	type	possible_keys
 Warnings:
 Note	1003	select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
 drop table t1,t2;
-create table t2 (a int, b int);
+create table t2 (a int, b int not null);
 create table t3 (a int);
 insert into t3 values (6),(7),(3);
 select * from t3 where a >= all (select b from t2);
@@ -4779,3 +4779,145 @@ SELECT 1 as foo FROM t1 WHERE a < SOME
 );
 foo
 DROP TABLE t1;
+CREATE TABLE t1 (a int(11), b varchar(1));
+INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );
+a
+5
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b );
+a
+7
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 );
+a
+7
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b );
+a
+delete from t1;
+INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );
+a
+5
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b );
+a
+7
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 );
+a
+7
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 );
+a
+5
+7
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b );
+a
+5
+7
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b );
+a
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 );
+a
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b );
+a
+drop table t1;
+End of 5.2 tests

=== modified file 'mysql-test/t/explain.test'
--- a/mysql-test/t/explain.test	2010-10-18 12:12:27 +0000
+++ b/mysql-test/t/explain.test	2011-08-31 12:32:06 +0000
@@ -152,7 +152,7 @@ DROP TABLE t1;
 --echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode
 --echo #
 
-CREATE TABLE t1 (f1 INT);
+CREATE TABLE t1 (f1 INT not null);
 
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-05-02 17:58:45 +0000
+++ b/mysql-test/t/subselect.test	2011-08-31 12:32:06 +0000
@@ -924,7 +924,7 @@ drop table t1,t2;
 #
 # correct ALL optimisation
 #
-create table t2 (a int, b int);
+create table t2 (a int, b int not null);
 create table t3 (a int);
 insert into t3 values (6),(7),(3);
 select * from t3 where a >= all (select b from t2);
@@ -3797,3 +3797,68 @@ SELECT 1 as foo FROM t1 WHERE a < SOME
   ); 
 
 DROP TABLE t1;
+
+#
+# LP BUG#823169 NULLs with ALL/ANY and maxmin optimization
+#
+CREATE TABLE t1 (a int(11), b varchar(1));
+INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');
+
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b );
+
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b );
+
+delete from t1;
+INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');
+
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b );
+
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b );
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 );
+SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b );
+
+drop table t1;
+
+--echo End of 5.2 tests

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-05-03 16:10:10 +0000
+++ b/sql/item_subselect.cc	2011-08-31 12:32:06 +0000
@@ -390,7 +390,10 @@ Item_maxmin_subselect::Item_maxmin_subse
 {
   DBUG_ENTER("Item_maxmin_subselect::Item_maxmin_subselect");
   max= max_arg;
-  init(select_lex, new select_max_min_finder_subselect(this, max_arg));
+  init(select_lex,
+       new select_max_min_finder_subselect(this, max_arg,
+                                           parent->substype() ==
+                                           Item_subselect::ALL_SUBS));
   max_columns= 1;
   maybe_null= 1;
   max_columns= 1;
@@ -1012,7 +1015,9 @@ Item_in_subselect::single_value_transfor
         !select_lex->having &&
 	!select_lex->with_sum_func &&
 	!(select_lex->next_select()) &&
-        select_lex->table_list.elements)
+        select_lex->table_list.elements &&
+        (!select_lex->ref_pointer_array[0]->maybe_null ||
+         substype() != Item_subselect::ALL_SUBS))
     {
       Item_sum_hybrid *item;
       nesting_map save_allow_sum_func;

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2011-08-29 17:38:21 +0000
+++ b/sql/sql_class.cc	2011-08-31 12:32:06 +0000
@@ -2580,26 +2580,28 @@ bool select_max_min_finder_subselect::cm
 {
   Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0);
   double val1= cache->val_real(), val2= maxmin->val_real();
+
+  if (cache->null_value)
+    return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value);
+  if (maxmin->null_value)
+    return !is_all;
   if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       val1 > val2);
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     val1 < val2);
+    return(val1 > val2);
+  return (val1 < val2);
 }
 
 bool select_max_min_finder_subselect::cmp_int()
 {
   Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0);
   longlong val1= cache->val_int(), val2= maxmin->val_int();
+
+  if (cache->null_value)
+    return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value);
+  if (maxmin->null_value)
+    return !is_all;
   if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       val1 > val2);
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     val1 < val2);
+    return(val1 > val2);
+  return (val1 < val2);
 }
 
 bool select_max_min_finder_subselect::cmp_decimal()
@@ -2607,13 +2609,14 @@ bool select_max_min_finder_subselect::cm
   Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0);
   my_decimal cval, *cvalue= cache->val_decimal(&cval);
   my_decimal mval, *mvalue= maxmin->val_decimal(&mval);
+
+  if (cache->null_value)
+    return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value);
+  if (maxmin->null_value)
+    return !is_all;
   if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       my_decimal_cmp(cvalue, mvalue) > 0) ;
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     my_decimal_cmp(cvalue,mvalue) < 0);
+    return (my_decimal_cmp(cvalue, mvalue) > 0) ;
+  return (my_decimal_cmp(cvalue,mvalue) < 0);
 }
 
 bool select_max_min_finder_subselect::cmp_str()
@@ -2626,13 +2629,14 @@ bool select_max_min_finder_subselect::cm
   */
   val1= cache->val_str(&buf1);
   val2= maxmin->val_str(&buf1);
+
+  if (cache->null_value)
+    return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value);
+  if (maxmin->null_value)
+    return !is_all;
   if (fmax)
-    return (cache->null_value && !maxmin->null_value) ||
-      (!cache->null_value && !maxmin->null_value &&
-       sortcmp(val1, val2, cache->collation.collation) > 0) ;
-  return (maxmin->null_value && !cache->null_value) ||
-    (!cache->null_value && !maxmin->null_value &&
-     sortcmp(val1, val2, cache->collation.collation) < 0);
+    return (sortcmp(val1, val2, cache->collation.collation) > 0) ;
+  return (sortcmp(val1, val2, cache->collation.collation) < 0);
 }
 
 int select_exists_subselect::send_data(List<Item> &items)

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2011-08-29 17:38:21 +0000
+++ b/sql/sql_class.h	2011-08-31 12:32:06 +0000
@@ -2931,9 +2931,11 @@ class select_max_min_finder_subselect :p
   Item_cache *cache;
   bool (select_max_min_finder_subselect::*op)();
   bool fmax;
+  bool is_all;
 public:
-  select_max_min_finder_subselect(Item_subselect *item_arg, bool mx)
-    :select_subselect(item_arg), cache(0), fmax(mx)
+  select_max_min_finder_subselect(Item_subselect *item_arg, bool mx,
+                                  bool all)
+    :select_subselect(item_arg), cache(0), fmax(mx), is_all(all)
   {}
   void cleanup();
   int send_data(List<Item> &items);



More information about the commits mailing list