Description:
Documentation says:
````
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).
````
However, it doesn't describe the result when N1...Nx are nulls:
-- -1 < 1 is correct, however `null < -1 < 1` is incorrect
mysql> select interval( -1, null, 1);
+------------------------+ | interval( -1, null, 1) | +------------------------+ | 1 | +------------------------+
1 row in set (0.00 sec)
mysql> select interval( -1, null, null); +---------------------------+
| interval( -1, null, null) | +---------------------------+
| 2 | +---------------------------+
1 row in set (0.00 sec)
mysql> select interval( -1, null, null, null); +---------------------------------+
| interval( -1, null, null, null) |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set (0.00 sec)
I would expect the second and third query to return NULL as the result.
How to repeat:
select interval( -1, null, 1);
select interval( -1, null, null);
select interval( -1, null, null, null);
Suggested fix:
I think either of the following would be acceptable:
1. update the documentation for INTERVAL
2. let the function return NULL when the args are all nulls
I have a quick check on the function source code, it seems it uses literal `<=` to do the comparison.
Description: Documentation says: ```` Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast). ```` However, it doesn't describe the result when N1...Nx are nulls: -- -1 < 1 is correct, however `null < -1 < 1` is incorrect mysql> select interval( -1, null, 1); +------------------------+ | interval( -1, null, 1) | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec) mysql> select interval( -1, null, null); +---------------------------+ | interval( -1, null, null) | +---------------------------+ | 2 | +---------------------------+ 1 row in set (0.00 sec) mysql> select interval( -1, null, null, null); +---------------------------------+ | interval( -1, null, null, null) | +---------------------------------+ | 3 | +---------------------------------+ 1 row in set (0.00 sec) I would expect the second and third query to return NULL as the result. How to repeat: select interval( -1, null, 1); select interval( -1, null, null); select interval( -1, null, null, null); Suggested fix: I think either of the following would be acceptable: 1. update the documentation for INTERVAL 2. let the function return NULL when the args are all nulls I have a quick check on the function source code, it seems it uses literal `<=` to do the comparison.