Bug #147 Bug with group by + having involving Date calculations
Submitted: 12 Mar 2003 10:55 Modified: 12 Mar 2003 10:55
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:all OS:Any (any)
Assigned to: Sergei Golubchik CPU Architecture:Any

[12 Mar 2003 10:55] Alexander Keremidarski
Description:
Wrong result with HAVING clause

How to repeat:
drop table if exists t1;
create table t1 (a date default null, b date default null);
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');

select * from t1;
+------------+------------+
| a          | b          |
+------------+------------+
| 1999-10-01 | 2000-01-10 |
| 1997-01-01 | 1998-10-01 |
+------------+------------+

select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;

+------------+------------+------------------------+
| a          | c          | count(distinct rand()) |
+------------+------------+------------------------+
| 1997-01-01 | 1998-10-01 |                      1 |
+------------+------------+------------------------+
1 row in set (0.01 sec)

Should return zero rows.

drop table t1;

Suggested fix:
===== sql/item.h 1.15 vs 1.16 =====
--- 1.15/sql/item.h     Wed Oct 16 10:32:43 2002
+++ 1.16/sql/item.h     Tue Mar 11 22:20:51 2003
@@ -82,6 +82,8 @@
   virtual void split_sum_func(List<Item> &fields) {}
   virtual bool get_date(TIME *ltime,bool fuzzydate);
   virtual bool get_time(TIME *ltime);
+  virtual bool get_date_result(TIME *ltime,bool fuzzydate)
+  { return get_date(ltime,fuzzydate); }
 };

@@ -129,8 +131,9 @@
     return field->result_type();
   }
   Field *tmp_table_field() { return result_field; }
-  bool get_date(TIME *ltime,bool fuzzydate);
-  bool get_time(TIME *ltime);
+  bool get_date(TIME *ltime,bool fuzzydate);
+  bool get_date_result(TIME *ltime,bool fuzzydate);
+  bool get_time(TIME *ltime);
 };

@@ -332,8 +335,8 @@
     return tmp;
   }
   bool get_date(TIME *ltime,bool fuzzydate)
-  {
-    return (null_value=(*ref)->get_date(ltime,fuzzydate));
+  {
+    return (null_value=(*ref)->get_date_result(ltime,fuzzydate));
   }
   bool send(String *tmp)               { return (*ref)->send(tmp); }
   void make_field(Send_field *field)   { (*ref)->make_field(field); }
===== sql/item.cc 1.14 vs 1.15 =====
--- 1.14/sql/item.cc    Wed Oct 16 10:32:42 2002
+++ 1.15/sql/item.cc    Tue Mar 11 22:20:51 2003
@@ -194,6 +194,17 @@
   return 0;
 }

+bool Item_field::get_date_result(TIME *ltime,bool fuzzydate)
+{
+  if ((null_value=result_field->is_null()) ||
+      result_field->get_date(ltime,fuzzydate))
+  {
+    bzero((char*) ltime,sizeof(*ltime));
+    return 1;
+  }
+  return 0;
+}
+
 bool Item_field::get_time(TIME *ltime)
 {
   if ((null_value=field->is_null()) || field->get_time(ltime))
[12 Mar 2003 10:55] Alexander Keremidarski
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html