← Back to team overview

maria-developers team mailing list archive

MDEV-9181 (NULLIF(table.col)0) gives wrong result on 10.1.x

 

 Hi Sergei,


Please find a prototype patch for MDEV-9181 attached.

It works, but I'm not fully sure.

Any better ideas?

Thanks.
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 5617431..928b197 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -5649,5 +5649,29 @@ CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED)
 Warnings:
 Warning	1292	Truncated incorrect INTEGER value: '1IJ3'
 #
+# MDEV-9181 (NULLIF(table.col)0) gives wrong result on 10.1.x
+#
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
+INSERT INTO t1 VALUES ('a'),('b');
+SELECT
+HEX(GROUP_CONCAT(a)),
+HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0062)),
+HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0062 COLLATE ucs2_general_ci)),
+HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0061002C0062)),
+HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0061002C0062 COLLATE ucs2_general_ci))
+FROM t1;
+HEX(GROUP_CONCAT(a))	HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0062))	HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0062 COLLATE ucs2_general_ci))	HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0061002C0062))	HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0061002C0062 COLLATE ucs2_general_ci))
+612C62	612C62	612C62	NULL	NULL
+SELECT
+HEX(GROUP_CONCAT(DISTINCT a)),
+HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0062)),
+HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0062 COLLATE ucs2_general_ci)),
+HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0061002C0062)),
+HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0061002C0062 COLLATE ucs2_general_ci))
+FROM t1;
+HEX(GROUP_CONCAT(DISTINCT a))	HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0062))	HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0062 COLLATE ucs2_general_ci))	HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0061002C0062))	HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0061002C0062 COLLATE ucs2_general_ci))
+612C62	612C62	612C62	NULL	NULL
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index b4cebac..3a51a0f 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1465,5 +1465,147 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2020) and ((case when 2020 = 2010 then NULL else `test`.`t1`.`a` end) = concat('2020',rand())))
 DROP TABLE t1;
 #
+# MDEV-9181 (NULLIF(table.col)0) gives wrong result on 10.1.x
+#
+CREATE TABLE IF NOT EXISTS t1 (c1 VARCHAR(50) DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES ('hello'),('hello\r\n'),('hello'),('hello');
+SELECT NULLIF(COUNT(c1),0) FROM t1;
+NULLIF(COUNT(c1),0)
+4
+SELECT IF(COUNT(c1)=0,NULL,COUNT(c1)) FROM t1;
+IF(COUNT(c1)=0,NULL,COUNT(c1))
+4
+SELECT CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END FROM t1;
+CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END
+4
+SELECT NULLIF(COUNT(DISTINCT c1),0) FROM t1;
+NULLIF(COUNT(DISTINCT c1),0)
+2
+SELECT IF(COUNT(DISTINCT c1)=0,NULL,COUNT(DISTINCT c1)) FROM t1;
+IF(COUNT(DISTINCT c1)=0,NULL,COUNT(DISTINCT c1))
+2
+SELECT CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END FROM t1;
+CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END
+2
+SELECT
+NULLIF(COUNT(c1)+0,0) AS c1,
+NULLIF(CAST(COUNT(c1) AS SIGNED),0) AS c2,
+NULLIF(CONCAT(COUNT(c1)),0) AS c3
+FROM t1;
+c1	c2	c3
+4	4	4
+DROP TABLE t1;
+CREATE TABLE t1 (c1 INT DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+NULLIF(AVG(c1),0) AS c1,
+IF(AVG(c1)=0,NULL,AVG(c1)) AS c2,
+CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END AS c3
+FROM t1;
+c1	c2	c3
+2.5000	2.5000	2.5000
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DECIMAL(10,2) DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+NULLIF(AVG(c1),0) AS c1,
+IF(AVG(c1)=0,NULL,AVG(c1)) AS c2,
+CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END AS c3
+FROM t1;
+c1	c2	c3
+2.500000	2.500000	2.500000
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DOUBLE DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+NULLIF(AVG(c1),0) AS c1,
+IF(AVG(c1)=0,NULL,AVG(c1)) AS c2,
+CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END AS c3
+FROM t1;
+c1	c2	c3
+2.5	2.5	2.5
+DROP TABLE t1;
+CREATE TABLE t1 (c1 INT DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+NULLIF(MAX(c1),0) AS c1,
+IF(MAX(c1)=0,NULL,MAX(c1)) AS c2,
+CASE WHEN MAX(c1)=0 THEN NULL ELSE MAX(c1) END AS c3
+FROM t1;
+c1	c2	c3
+4	4	4
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DECIMAL(10,2) DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+NULLIF(MAX(c1),0) AS c1,
+IF(MAX(c1)=0,NULL,MAX(c1)) AS c2,
+CASE WHEN MAX(c1)=0 THEN NULL ELSE MAX(c1) END AS c3
+FROM t1;
+c1	c2	c3
+4.00	4.00	4.00
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DOUBLE DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+NULLIF(MAX(c1),0) AS c1,
+IF(MAX(c1)=0,NULL,MAX(c1)) AS c2,
+CASE WHEN MAX(c1)=0 THEN NULL ELSE MAX(c1) END AS c3
+FROM t1;
+c1	c2	c3
+4	4	4
+DROP TABLE t1;
+CREATE TABLE t1 (
+id INT NOT NULL,
+c1 INT DEFAULT NULL
+);
+INSERT INTO t1 VALUES (1,1),(1,1),(2,3),(2,4),(2,5),(2,5);
+SELECT
+NULLIF(COUNT(c1),0) AS c1,
+NULLIF(COUNT(c1)+0,0) AS c1_plus,
+IF(COUNT(c1)=0,NULL,COUNT(c1)) AS c1_if,
+CASE WHEN COUNT(c1) IS NULL THEN 0 ELSE COUNT(c1) END AS c1_case
+FROM t1 GROUP BY id;
+c1	c1_plus	c1_if	c1_case
+2	2	2	2
+4	4	4	4
+SELECT
+NULLIF(AVG(c1),0) AS c1,
+NULLIF(AVG(c1)+0,0) AS c1_plus,
+IF(AVG(c1)=0,NULL,AVG(c1)) AS c1_if,
+CASE WHEN AVG(c1) IS NULL THEN 0 ELSE AVG(c1) END AS c1_case
+FROM t1 GROUP BY id;
+c1	c1_plus	c1_if	c1_case
+1.0000	1.0000	1.0000	1.0000
+4.2500	4.2500	4.2500	4.2500
+SELECT
+NULLIF(MAX(c1),0) AS c2,
+NULLIF(MAX(c1)+0,0) AS c2_plus,
+IF(MAX(c1)=0,NULL,MAX(c1)) AS c2_if,
+CASE WHEN MAX(c1) IS NULL THEN 0 ELSE MAX(c1) END AS c2_case
+FROM t1 GROUP BY id;
+c2	c2_plus	c2_if	c2_case
+1	1	1	1
+5	5	5	5
+SELECT
+NULLIF(COUNT(DISTINCT c1),0) AS c1,
+NULLIF(COUNT(DISTINCT c1)+0,0) AS c1_plus,
+IF(COUNT(DISTINCT c1)=0,NULL,COUNT(DISTINCT c1)) AS c1_if,
+CASE WHEN COUNT(DISTINCT c1) IS NULL THEN 0 ELSE COUNT(DISTINCT c1) END AS c1_case
+FROM t1 GROUP BY id;
+c1	c1_plus	c1_if	c1_case
+1	1	1	1
+3	3	3	3
+SELECT
+NULLIF(AVG(DISTINCT c1),0) AS c1,
+NULLIF(AVG(DISTINCT c1)+0,0) AS c1_plus,
+IF(AVG(DISTINCT c1)=0,NULL,AVG(DISTINCT c1)) AS c1_if,
+CASE WHEN AVG(DISTINCT c1) IS NULL THEN 0 ELSE AVG(DISTINCT c1) END AS c1_case
+FROM t1 GROUP BY id;
+c1	c1_plus	c1_if	c1_case
+1.0000	1.0000	1.0000	1.0000
+4.0000	4.0000	4.0000	4.0000
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 2f48062..b35d570 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -955,5 +955,30 @@ SET NAMES utf8;
 SELECT CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED);
 
 --echo #
+--echo # MDEV-9181 (NULLIF(table.col)0) gives wrong result on 10.1.x
+--echo #
+# This test checks that the special code to handler aggregate functions
+# in Item_func_nullif::split_sum_func() and Item_func_nullif::result_arg()
+# does not affect the returned value. NULLIF() is expected to return
+# a latin1 result from args[2] rather than an ucs2 result from args[0].
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
+INSERT INTO t1 VALUES ('a'),('b');
+SELECT
+  HEX(GROUP_CONCAT(a)),
+  HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0062)),
+  HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0062 COLLATE ucs2_general_ci)),
+  HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0061002C0062)),
+  HEX(NULLIF(GROUP_CONCAT(a),_ucs2 0x0061002C0062 COLLATE ucs2_general_ci))
+FROM t1;
+SELECT
+  HEX(GROUP_CONCAT(DISTINCT a)),
+  HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0062)),
+  HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0062 COLLATE ucs2_general_ci)),
+  HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0061002C0062)),
+  HEX(NULLIF(GROUP_CONCAT(DISTINCT a),_ucs2 0x0061002C0062 COLLATE ucs2_general_ci))
+FROM t1;
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 5347a96..c978b58 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -909,6 +909,112 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)=CONCAT('2020',RAND());
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-9181 (NULLIF(table.col)0) gives wrong result on 10.1.x
+--echo #
+CREATE TABLE IF NOT EXISTS t1 (c1 VARCHAR(50) DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES ('hello'),('hello\r\n'),('hello'),('hello');
+SELECT NULLIF(COUNT(c1),0) FROM t1;
+SELECT IF(COUNT(c1)=0,NULL,COUNT(c1)) FROM t1;
+SELECT CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END FROM t1;
+SELECT NULLIF(COUNT(DISTINCT c1),0) FROM t1;
+SELECT IF(COUNT(DISTINCT c1)=0,NULL,COUNT(DISTINCT c1)) FROM t1;
+SELECT CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END FROM t1;
+SELECT
+  NULLIF(COUNT(c1)+0,0) AS c1,
+  NULLIF(CAST(COUNT(c1) AS SIGNED),0) AS c2,
+  NULLIF(CONCAT(COUNT(c1)),0) AS c3
+FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 INT DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+  NULLIF(AVG(c1),0) AS c1,
+  IF(AVG(c1)=0,NULL,AVG(c1)) AS c2,
+  CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END AS c3
+FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DECIMAL(10,2) DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+  NULLIF(AVG(c1),0) AS c1,
+  IF(AVG(c1)=0,NULL,AVG(c1)) AS c2,
+  CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END AS c3
+FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DOUBLE DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+  NULLIF(AVG(c1),0) AS c1,
+  IF(AVG(c1)=0,NULL,AVG(c1)) AS c2,
+  CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END AS c3
+FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 INT DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+  NULLIF(MAX(c1),0) AS c1,
+  IF(MAX(c1)=0,NULL,MAX(c1)) AS c2,
+  CASE WHEN MAX(c1)=0 THEN NULL ELSE MAX(c1) END AS c3
+FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DECIMAL(10,2) DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+  NULLIF(MAX(c1),0) AS c1,
+  IF(MAX(c1)=0,NULL,MAX(c1)) AS c2,
+  CASE WHEN MAX(c1)=0 THEN NULL ELSE MAX(c1) END AS c3
+FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DOUBLE DEFAULT NULL);
+INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
+SELECT
+  NULLIF(MAX(c1),0) AS c1,
+  IF(MAX(c1)=0,NULL,MAX(c1)) AS c2,
+  CASE WHEN MAX(c1)=0 THEN NULL ELSE MAX(c1) END AS c3
+FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  id INT NOT NULL,
+  c1 INT DEFAULT NULL
+);
+INSERT INTO t1 VALUES (1,1),(1,1),(2,3),(2,4),(2,5),(2,5);
+SELECT
+  NULLIF(COUNT(c1),0) AS c1,
+  NULLIF(COUNT(c1)+0,0) AS c1_plus,
+  IF(COUNT(c1)=0,NULL,COUNT(c1)) AS c1_if,
+  CASE WHEN COUNT(c1) IS NULL THEN 0 ELSE COUNT(c1) END AS c1_case
+FROM t1 GROUP BY id;
+SELECT
+  NULLIF(AVG(c1),0) AS c1,
+  NULLIF(AVG(c1)+0,0) AS c1_plus,
+  IF(AVG(c1)=0,NULL,AVG(c1)) AS c1_if,
+  CASE WHEN AVG(c1) IS NULL THEN 0 ELSE AVG(c1) END AS c1_case
+FROM t1 GROUP BY id;
+SELECT
+  NULLIF(MAX(c1),0) AS c2,
+  NULLIF(MAX(c1)+0,0) AS c2_plus,
+  IF(MAX(c1)=0,NULL,MAX(c1)) AS c2_if,
+  CASE WHEN MAX(c1) IS NULL THEN 0 ELSE MAX(c1) END AS c2_case
+FROM t1 GROUP BY id;
+
+SELECT
+  NULLIF(COUNT(DISTINCT c1),0) AS c1,
+  NULLIF(COUNT(DISTINCT c1)+0,0) AS c1_plus,
+  IF(COUNT(DISTINCT c1)=0,NULL,COUNT(DISTINCT c1)) AS c1_if,
+  CASE WHEN COUNT(DISTINCT c1) IS NULL THEN 0 ELSE COUNT(DISTINCT c1) END AS c1_case
+FROM t1 GROUP BY id;
+SELECT
+  NULLIF(AVG(DISTINCT c1),0) AS c1,
+  NULLIF(AVG(DISTINCT c1)+0,0) AS c1_plus,
+  IF(AVG(DISTINCT c1)=0,NULL,AVG(DISTINCT c1)) AS c1_if,
+  CASE WHEN AVG(DISTINCT c1) IS NULL THEN 0 ELSE AVG(DISTINCT c1) END AS c1_case
+FROM t1 GROUP BY id;
+
+DROP TABLE t1;
 
 --echo #
 --echo # End of 10.1 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index c968ff6..a521d63 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2531,6 +2531,37 @@ bool Item_func_if::date_op(MYSQL_TIME *ltime, uint fuzzydate)
 }
 
 
+/**
+  Note:
+    NULLIF(args[0], args[1])
+  emulates behaviour of:
+    CASE WHEN args[0]=args[1] THEN NULL ELSE args[2] END
+  where args[2] originally points to the same Item with args[0],
+  but can point to a different Item after fix_fields(), which can
+  replace arguments, e.g. in:
+  - equal field propagation
+  - collation aggregation
+  - YEAR/LONGLONG, in Item_func::convert_const_compared_to_int_field().
+  See the comment in item_cmpfunc.h
+
+  In case args[0]/args[2] after fix_fields() are still aggregate functions,
+  we don't split args[2] because that would make it call Item_sum_count::add()
+  or Item_sum_avg::add() two times for the same value.
+  Instead, we get result from args[0] at val_xxx() time.
+  Another option would be to do real translation from NULLIF to its
+  equivalent CASE and calculate args[0] and args[2] separately.
+*/
+void Item_func_nullif::split_sum_func(THD *thd, Item **ref_pointer_array,
+                               List<Item> &fields, uint flags)
+{
+  Item **arg, **arg_end;
+  // Note, we split only args[0] and args[1], and do not split args[2].
+  for (arg= args, arg_end= args + 2; arg != arg_end ; arg++)
+    (*arg)->split_sum_func2(thd, ref_pointer_array, fields, arg,
+                            flags | SPLIT_SUM_SKIP_REGISTERED);
+}
+
+
 void
 Item_func_nullif::fix_length_and_dec()
 {
@@ -2631,8 +2662,9 @@ Item_func_nullif::real_op()
     null_value=1;
     return 0.0;
   }
-  value= args[2]->val_real();
-  null_value= args[2]->null_value;
+  Item *result_item= result_arg();
+  value= result_item->val_real();
+  null_value= result_item->null_value;
   return value;
 }
 
@@ -2646,8 +2678,9 @@ Item_func_nullif::int_op()
     null_value=1;
     return 0;
   }
-  value= args[2]->val_int();
-  null_value= args[2]->null_value;
+  Item *result_item= result_arg();
+  value= result_item->val_int();
+  null_value= result_item->null_value;
   return value;
 }
 
@@ -2661,8 +2694,9 @@ Item_func_nullif::str_op(String *str)
     null_value=1;
     return 0;
   }
-  res= args[2]->val_str(str);
-  null_value= args[2]->null_value;
+  Item *result_item= result_arg();
+  res= result_item->val_str(str);
+  null_value= result_item->null_value;
   return res;
 }
 
@@ -2677,8 +2711,9 @@ Item_func_nullif::decimal_op(my_decimal * decimal_value)
     null_value=1;
     return 0;
   }
-  res= args[2]->val_decimal(decimal_value);
-  null_value= args[2]->null_value;
+  Item *result_item= result_arg();
+  res= result_item->val_decimal(decimal_value);
+  null_value= result_item->null_value;
   return res;
 }
 
@@ -2689,14 +2724,14 @@ Item_func_nullif::date_op(MYSQL_TIME *ltime, uint fuzzydate)
   DBUG_ASSERT(fixed == 1);
   if (!cmp.compare())
     return (null_value= true);
-  return (null_value= args[2]->get_date(ltime, fuzzydate));
+  return (null_value= result_arg()->get_date(ltime, fuzzydate));
 }
 
 
 bool
 Item_func_nullif::is_null()
 {
-  return (null_value= (!cmp.compare() ? 1 : args[2]->null_value));
+  return (null_value= (!cmp.compare() ? 1 : result_arg()->null_value));
 }
 
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index e06d93f..6253d3e 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -995,6 +995,32 @@ class Item_func_nullif :public Item_func_hybrid_field_type
     - Item_field::propagate_equal_fields(ANY_SUBST) for the left "a"
     - Item_field::propagate_equal_fields(IDENTITY_SUBST) for the right "a"
   */
+  /**
+    Returns which args[] should be used to return the result.
+    Normally its args[2], but in case of aggregate functions it's args[0].
+  */
+  Item *result_arg()
+  {
+    /**
+      In queries like:
+        SELECT NULLIF(AVG(field2),0) FROM t1 GROUP BY id;
+      args[0] is replaced to Item_aggregate_ref pointing to
+      Item_avg_field_decimal/Item_avg_field_double.
+
+      In queries like:
+        SELECT NULLIF(COUNT(field2),0) FROM t1 GROUP BY id;
+      args[0] is replaced to Item_aggregate_ref pointing to
+      Item_field with Field_longlong.
+
+      If this substitution happened, we should return the result
+      from args[0], as args[2] still points to the original leftmost argument
+      which has no valid aggregate value.
+    */
+    return
+      args[0]->type() == REF_ITEM &&
+      (static_cast<Item_ref*>(args[0])->ref_type() == Item_ref::AGGREGATE_REF) ?
+      args[0] : args[2];
+  }
 public:
   // Put "a" to args[0] for comparison and to args[2] for the returned value.
   Item_func_nullif(THD *thd, Item *a, Item *b):
@@ -1023,6 +1049,8 @@ class Item_func_nullif :public Item_func_hybrid_field_type
                                                          cond, &args[2]);
     return this;
   }
+  void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields,
+                      uint flags);
 };