maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Sergei,

Please review a patch for MDEV-9521.

Thanks.

diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 4e299ec..31add0f 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -440,3 +440,38 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 1
 drop table t1;
+#
+# MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
+#
+CREATE TABLE t1 (
+id BIGINT NOT NULL,
+date_debut DATE NOT NULL,
+date_fin DATE DEFAULT NULL);
+CREATE TABLE t2(
+id BIGINT NOT NULL,
+date_debut DATE NOT NULL,
+date_fin DATE DEFAULT NULL);
+INSERT INTO t1 VALUES (1,'2016-01-01','2016-01-31');
+INSERT INTO t1 VALUES (2,'2016-02-01',null);
+INSERT INTO t1 VALUES (3,'2016-03-01','2016-03-31');
+INSERT INTO t1 VALUES (4,'2016-04-01',null);
+INSERT INTO t2 VALUES (1,'2016-01-01','2016-01-31');
+INSERT INTO t2 VALUES (2,'2016-02-01','2016-01-28');
+INSERT INTO t2 VALUES (3,'2016-03-01',null);
+INSERT INTO t2 VALUES (4,'2016-04-01',null);
+SELECT t1.id, 
+GREATEST(t2.date_debut, t1.date_debut) AS date_debut,
+LEAST(IFNULL(t2.date_fin, IFNULL(t1.date_fin, NULL)),
+IFNULL(t1.date_fin, IFNULL(t2.date_fin, NULL))) AS date_fin
+FROM t1 LEFT JOIN t2 ON (t1.id=t2.id);
+id	date_debut	date_fin
+1	2016-01-01	2016-01-31 00:00:00
+2	2016-02-01	2016-01-28 00:00:00
+3	2016-03-01	2016-03-31 00:00:00
+4	2016-04-01	NULL
+DROP TABLE t1,t2;
+SELECT
+LEAST(COALESCE(DATE(NULL), DATE(NULL)), COALESCE(DATE(NULL), DATE(NULL))) AS d0,
+LEAST(IFNULL(DATE(NULL), DATE(NULL)), IFNULL(DATE(NULL), DATE(NULL))) AS d1;
+d0	d1
+NULL	NULL
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 0b2ce16..f291a64 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -386,3 +386,32 @@ select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 drop table t1;
 
+--echo #
+--echo # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
+--echo #
+CREATE TABLE t1 (
+  id BIGINT NOT NULL,
+  date_debut DATE NOT NULL,
+  date_fin DATE DEFAULT NULL);
+CREATE TABLE t2(
+  id BIGINT NOT NULL,
+  date_debut DATE NOT NULL,
+  date_fin DATE DEFAULT NULL);
+INSERT INTO t1 VALUES (1,'2016-01-01','2016-01-31');
+INSERT INTO t1 VALUES (2,'2016-02-01',null);
+INSERT INTO t1 VALUES (3,'2016-03-01','2016-03-31');
+INSERT INTO t1 VALUES (4,'2016-04-01',null);
+
+INSERT INTO t2 VALUES (1,'2016-01-01','2016-01-31');
+INSERT INTO t2 VALUES (2,'2016-02-01','2016-01-28');
+INSERT INTO t2 VALUES (3,'2016-03-01',null);
+INSERT INTO t2 VALUES (4,'2016-04-01',null);
+SELECT t1.id, 
+ GREATEST(t2.date_debut, t1.date_debut) AS date_debut,
+ LEAST(IFNULL(t2.date_fin, IFNULL(t1.date_fin, NULL)),
+       IFNULL(t1.date_fin, IFNULL(t2.date_fin, NULL))) AS date_fin
+FROM t1 LEFT JOIN t2 ON (t1.id=t2.id);
+DROP TABLE t1,t2;
+SELECT
+  LEAST(COALESCE(DATE(NULL), DATE(NULL)), COALESCE(DATE(NULL), DATE(NULL))) AS d0,
+  LEAST(IFNULL(DATE(NULL), DATE(NULL)), IFNULL(DATE(NULL), DATE(NULL))) AS d1;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index d5f5087..4670bea 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2558,10 +2558,7 @@ bool Item_func_ifnull::date_op(MYSQL_TIME *ltime, uint fuzzydate)
   DBUG_ASSERT(fixed == 1);
   if (!args[0]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
     return (null_value= false);
-  if (!args[1]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
-    return (null_value= false);
-  bzero((char*) ltime,sizeof(*ltime));
-  return null_value= !(fuzzydate & TIME_FUZZY_DATES);
+  return (null_value= args[1]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES));
 }
 
 
@@ -3311,15 +3308,12 @@ double Item_func_coalesce::real_op()
 bool Item_func_coalesce::date_op(MYSQL_TIME *ltime,uint fuzzydate)
 {
   DBUG_ASSERT(fixed == 1);
-  null_value= 0;
   for (uint i= 0; i < arg_count; i++)
   {
-    bool res= args[i]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES);
-    if (!args[i]->null_value)
-      return res;
+    if (!args[i]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
+      return (null_value= false);
   }
-  bzero((char*) ltime,sizeof(*ltime));
-  return null_value|= !(fuzzydate & TIME_FUZZY_DATES);
+  return (null_value= true);
 }