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))