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: | |
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
[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