Bug #67578 in ELT of LEAST of FLOOR of NULL NULL is not passed on but ignored, as if MIN
Submitted: 13 Nov 2012 17:19 Modified: 26 Mar 2013 5:58
Reporter: halászsándor halászsándor Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.8, 5.0.96, 5.1.67, 5.5.29, 5.7.0 OS:Any (MS Windows Vista, Linux)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[13 Nov 2012 17:19] halászsándor halászsándor
Description:
mysql> select ELT(LEAST(6, FLOOR((NULL + 4) / 7)), '$6.00 a month for 12 months', '$12.00 every other month for 12 months', '$18.00 every 3 months for 12 months', '$24.00 every 4 months for 12 months', '$36.00 twice a year for 1 year', '$72.00 paid in full') AS Plan;
+---------------------+
| Plan                |
+---------------------+
| $72.00 paid in full |
+---------------------+
1 row in set (0.00 sec)

mysql> select LEAST(6, FLOOR((NULL + 4) / 7));
+---------------------------------+
| LEAST(6, FLOOR((NULL + 4) / 7)) |
+---------------------------------+
|                            NULL |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select ELT((NULL + 4) / 7, '$6.00 a month for 12 months', '$12.00 every other month for 12 months', '$18.00 every 3 months for 12 months', '$24.00 every 4 months for 12 months', '$36.00 twice a year for 1 year', '$72.00 paid in full') AS Plan;
+------+
| Plan |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.8-log |
+-----------+
1 row in set (0.00 sec)

mysql> select ELT(FLOOR((NULL + 4) / 7), '$6.00 a month for 12 months', '$12.00 every other month for 12 months', '$18.00 every 3 months for 12 months', '$24.00 every 4 months for 12 months', '$36.00 twice a year for 1 year', '$72.00 paid in full') AS Plan;
+------+
| Plan |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

How to repeat:
as aforesaid

Suggested fix:
Make LEAST in all surroundings behave as documented
[13 Nov 2012 17:34] Sveta Smirnova
Thank you for the report.

Verified as described.
[25 Mar 2013 18:12] Paul Dubois
Noted in 5.7.2 changelog.

ELT(LEAST(...),..) could return a non-NULL value even if LEAST()
returned NULL.