|
There is a difference in how COALESCE and GREATEST
passed to a function requiring a valid date process their
arguments.
1. COALESCE recursively requires valid dates from its arguments:
mysql> select convert_tz(coalesce('2010-00-01','2010-02-02'),'+00:00','+01:00'); show warnings;
|
+-------------------------------------------------------------------+
|
| convert_tz(coalesce('2010-00-01','2010-02-02'),'+00:00','+01:00') |
|
+-------------------------------------------------------------------+
|
| 2010-02-02 01:00:00 |
|
+-------------------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
+---------+------+----------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------+
|
| Warning | 1292 | Incorrect datetime value: '2010-00-01' |
|
+---------+------+----------------------------------------+
|
1 row in set (0.00 sec)
|
Notice, it ignored the first argument and reported a warning for it,
then chose the second argument.
2. GREATEST does not require validity from its arguments:
mysql> select convert_tz(greatest('2010-00-01','2010-02-02'),'+00:00','+01:00'); show warnings;
|
+-------------------------------------------------------------------+
|
| convert_tz(greatest('2010-00-01','2010-02-02'),'+00:00','+01:00') |
|
+-------------------------------------------------------------------+
|
| 2010-02-02 01:00:00 |
|
+-------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
Empty set (0.00 sec)
|
Notice, it did not require validity from the first argument and did warn
not warn about it, and returned the second argument.
If GREATEST worked in the similar way with COALESCE,
the result for the entire query would be NULL, because
get_date('2010-00-01', NO_ZERO_IN_DATE) would return NULL.
This difference should be discussed,
and the other functions should be checked
Another example when COALESCE and GREATEST/LEAST return different results:
mysql-tmp --column-type-info test
|
Server version: 10.0.3-MariaDB-debug Source distribution
|
MariaDB [test]> select coalesce(1.0,'10'), least(1.0,'10');
|
Field 1: `coalesce(1.0,'10')`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: VAR_STRING
|
Collation: utf8_general_ci (33)
|
Length: 12
|
Max_length: 3
|
Decimals: 31
|
Flags: NOT_NULL
|
|
Field 2: `least(1.0,'10')`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: VAR_STRING
|
Collation: binary (63)
|
Length: 23
|
Max_length: 1
|
Decimals: 31
|
Flags: NOT_NULL BINARY
|
|
+--------------------+-----------------+
|
| coalesce(1.0,'10') | least(1.0,'10') |
|
+--------------------+-----------------+
|
| 1.0 | 1 |
|
+--------------------+-----------------+
|
1 row in set (0.01 sec)
|
Notice difference in:
- Length: 12 vs 23
- Collation: utf8_general_ci vs binary
- Flags: BINARY in least only
- Returned result: '1.0' vs '1'
|