MySQL Bugs: #28258: A version of GREATEST() / LEAST() what would ignore NULL values

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

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

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?

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

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.

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?

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

Let's hope developers will agree with your reasoning and implement something like that eventually.

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