bzr commit into mysql-6.0-opt branch (sergefp:2687) Bug#37891

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-6.0-bug37977/

 2687 Sergey Petrunia	2008-09-05
      BUG#37891: Column cannot be null error with aggregate in a subquery
      - Add Item_sum_distinct::fix_fields() which sets maybe_null to TRUE.
        This makes SUM(DISTINCT x) and AVG(DISTINCT x) be nullable, which
        is needed as they have NULL value when evaluated over empty resultset.
modified:
  mysql-test/r/sum_distinct.result
  mysql-test/t/sum_distinct.test
  sql/item_sum.cc
  sql/item_sum.h

per-file messages:
  mysql-test/r/sum_distinct.result
    BUG#37891: Column cannot be null error with aggregate in a subquery
    - Testcase
  mysql-test/t/sum_distinct.test
    BUG#37891: Column cannot be null error with aggregate in a subquery
    - Testcase
  sql/item_sum.cc
    BUG#37891: Column cannot be null error with aggregate in a subquery
    - Add Item_sum_distinct::fix_fields() which sets maybe_null to TRUE.
      This makes SUM(DISTINCT x) and AVG(DISTINCT x) be nullable, which
      is needed as they have NULL value when evaluated over empty resultset.
  sql/item_sum.h
    BUG#37891: Column cannot be null error with aggregate in a subquery
    - Add Item_sum_distinct::fix_fields() which sets maybe_null to TRUE.
      This makes SUM(DISTINCT x) and AVG(DISTINCT x) be nullable, which
      is needed as they have NULL value when evaluated over empty resultset.
=== modified file 'mysql-test/r/sum_distinct.result'
--- a/mysql-test/r/sum_distinct.result	2005-04-07 16:24:14 +0000
+++ b/mysql-test/r/sum_distinct.result	2008-09-05 19:01:46 +0000
@@ -95,3 +95,26 @@ SELECT SUM(DISTINCT id % 11) FROM t1;
 SUM(DISTINCT id % 11)
 55
 DROP TABLE t1;
+
+BUG#37891: Column cannot be null error with aggregate in a subquery
+
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_key int(11) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,10);
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+time_nokey time DEFAULT NULL,
+datetime_key time DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY datetime_key (datetime_key)
+);
+INSERT INTO t2 VALUES (1,'18:19:29',NOW());
+SELECT * FROM t1 WHERE int_key IN ( SELECT SUM(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );
+pk	int_key
+SELECT * FROM t1 WHERE int_key IN ( SELECT AVG(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );
+pk	int_key
+drop table t1,t2;

=== modified file 'mysql-test/t/sum_distinct.test'
--- a/mysql-test/t/sum_distinct.test	2005-04-07 16:24:14 +0000
+++ b/mysql-test/t/sum_distinct.test	2008-09-05 19:01:46 +0000
@@ -93,3 +93,28 @@ SELECT SUM(DISTINCT id) FROM t1;
 SELECT SUM(DISTINCT id % 11) FROM t1;
 
 DROP TABLE t1;
+
+--echo 
+--echo BUG#37891: Column cannot be null error with aggregate in a subquery
+--echo
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_key int(11) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,10);
+
+CREATE TABLE t2 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  time_nokey time DEFAULT NULL,
+  datetime_key time DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY datetime_key (datetime_key)
+);
+INSERT INTO t2 VALUES (1,'18:19:29',NOW());
+
+SELECT * FROM t1 WHERE int_key IN ( SELECT SUM(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );
+SELECT * FROM t1 WHERE int_key IN ( SELECT AVG(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );
+drop table t1,t2;
+

=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc	2008-06-26 16:17:05 +0000
+++ b/sql/item_sum.cc	2008-09-05 19:01:46 +0000
@@ -978,6 +978,17 @@ void Item_sum_distinct::fix_length_and_d
 }
 
 
+bool Item_sum_distinct::fix_fields(THD *thd, Item **ref)
+{
+  int res= Item_sum_num::fix_fields(thd, ref);
+  /*
+    SUM(DISTINCT x) and AVG(DISTINCT x) may have NULL value even when x is
+    not nullable:
+  */
+  maybe_null= TRUE;
+  return res;
+}
+
 /**
   @todo
   check that the case of CHAR(0) works OK

=== modified file 'sql/item_sum.h'
--- a/sql/item_sum.h	2008-06-12 19:04:52 +0000
+++ b/sql/item_sum.h	2008-09-05 19:01:46 +0000
@@ -488,6 +488,7 @@ public:
   void reset_field() {} // not used
   void update_field() {} // not used
   virtual void no_rows_in_result() {}
+  bool fix_fields(THD *thd, Item **ref);
   void fix_length_and_dec();
   enum Item_result result_type () const { return val.traits->type(); }
   virtual void calculate_val_and_count();