Bug #28258 A version of GREATEST() / LEAST() what would ignore NULL values
Submitted: 5 May 2007 6:18 Modified: 8 May 2007 23:06
Reporter: Ondra Zizka Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.37 OS:Any
Assigned to: CPU Architecture:Any
Tags: GREATEST, LEAST

[5 May 2007 6:18] Ondra Zizka
Description:
The old GREATEST() and LEAST() behavior was exactly what I am looking for:

Before MySQL 5.0.13, LEAST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL. 

I wonder why this was changed - for compatibility?

The main reason why I need it is that such functionality is quite hard to simulate other way.

How to repeat:
Example:
Say we have a 3 tables (with statistics, e.g.) with DATE column. Now we want to get GREATEST and LEAST date of all that three tables.

  SELECT MAX(date) INTO @minA FROM table_a;
  SELECT MAX(date) INTO @minB FROM table_b;
  SELECT MAX(date) INTO @minC FROM table_c;

  SELECT LEAST(@minA, @minB, @minC) INTO @minAll;

But some can have zero rows, so any of @min* can be NULL.
And how to deal with it? A buch of comparisons? ...

Suggested fix:
To introduce functions like these:

  LEAST_NULL(321,546,NULL,15) > 15
  GREATEST_NULL(321,546,NULL,15) > 546
[5 May 2007 6:46] Valeriy Kravchuk
Thank you for a feature request. If you expect NULL values, you may use IFNULL(@a, 0), for example, instead of @a. Read http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. 

Do you still need there new functions?
[6 May 2007 9:07] Ondra Zizka
Hi, 

I am aware of all functions that MySQL offers.
IFNULL() can't solve my problem, because I need the LEAST non-NULL value even if there are some NULL values.

Let's say you have a company offering 4 different services, thus storing records about executed sales in 4 tables with a id_customer reference to table of customers. Now you need each customer's first business contact with the company. So you use
SELECT MIN(date) INTO @dateTravel FROM records_traveling GROUP BY id_cust;
SELECT MIN(date) INTO @dateCarRent FROM records_car_rent GROUP BY id_cust;
...
SELECT LEAST( @dateTravel, @dateCarRent, ... ) INTO @dateFirstContact;

Now when some of the tables has zero records for a particular user, MIN() returns NULL. Still I want the first concact with the user, from other tables. So LEAST returning some special constant 'zero value' in case of use of IFNULL() would destroy the information I need.

The only way I thought out is to use as many IF's as needed to
 a) call the LEAST() only with non-NULL variables, or
 b) set all NULL variables to next non-NULL variable's value,
both methods being complicated overhead.

Thanks for considering,
Ondra
[6 May 2007 9:12] Ondra Zizka
I forgot, I though out another solution:
To write the function myself as a STORED FUNCTION.
But unfortunately, DATETIME comparison seems to be quite slow in stored procedures, thus native function, would help much.
[6 May 2007 17:22] Valeriy Kravchuk
You may use IFNULL() to substitute maximum possible value for a date to NULL for LEAST(), if you want. Why do you think it will NOT work in your case?
[7 May 2007 19:58] Ondra Zizka
Well yes, the final result can be achieved using current LEAST()'s behavior.
In my real-life application, I have this issue with about 10 - 15 values; So, the expression could look like this:

SELECT NULLIF( LEAST( IFNULL( @val1, '2222:22:22' ), IFNULL( @val2, '2222:22:22' ), IFNULL( @val3, '2222:22:22' ), IFNULL( @val4, '2222:22:22' ), IFNULL( @val5, '2222:22:22' ), IFNULL( @val6, '2222:22:22' ), IFNULL( @val7, '2222:22:22' ), IFNULL( @val8, '2222:22:22' ), IFNULL( @val9, '2222:22:22' ), IFNULL( @val10, '2222:22:22' ), IFNULL( @val11, '2222:22:22' ), IFNULL( @val12, '2222:22:22' ), IFNULL( @val13, '2222:22:22' ), '2222:22:22' );

But this approach is non-systemic and the code is complex, obscure and indecipherable. This looks definitely better:

SELECT LEAST_NON_NULL(@val1,@val2,@val3,@val4,@val5,@val6,@val7,
    @val8,@val9,@val10,@val11,@val12,@val13);

I don't say it's unsolvable in any other way; But I'd rather like the latter more convenient way and that's, imho, what feature request is for...

Thanks, Ondra
[8 May 2007 4:11] Valeriy Kravchuk
Let's hope developers will agree with your reasoning and implement something like that eventually.
[8 May 2007 23:06] Ondra Zizka
Sure, the hope dies last. The more hope I have since there is an archive of pre-5.0.13 versions with original GREATEST/LEAST just waiting for renaming...

double Item_func_min_max_non_null::val()
{
  double value=0.0;
  null_value=1;
  for (uint i=0; i < arg_count ; i++){
    if (null_value){
      value = args[i]->val();
      null_value = args[i]->null_value;
    }
    else{
      double tmp = args[i]->val();
      if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) > 0)
	      value=tmp;
    }
  }
  return value;
}

That should be it. Thanks and bye