Bug #83895 LEAST and GREATEST makes inconsistent data types, compared to UNION and COALESCE

• Submitted: 20 Nov 2016 11:57
• Modified: 31 Oct 2017 20:43
• Reporter: Roy Lyseng
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[20 Nov 2016 11:57] Roy Lyseng

Description:
LEAST and GREATEST does not use the same logic as UNION and COALESCE, hence derived data types become inconsistent.

Notice also that result type and field type for LEAST/GREATEST nodes differ, meaning that comparison may be performed in a different type than the derived field type, which is both confusing and differing from how UNION performs comparisons.

How to repeat:
Note: due to bug#25123820, run this test case with sql_mode='';

CREATE TABLE u AS
SELECT 5 as c1, 5 as c2, 5 as c3, 5 as c4, 5 as c5, 5 as c6,
       5.5 as c12, 5.5 as c13, 5.5 as c14, 5.5 as c15, 5.5 as c16,
       5.5e5 as c23, 5.5e5 as c24, 5.5e5 as c25, 5.5e5 as c26,
       '5' as c34, '5' as c35, '5' as c36,
       date '2005-05-05' as c45, date '2005-05-05' as c46,
       time '05:05:05.555555' as c56
UNION
SELECT 5.5, 5.5e5, '5', date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       5.5e5, '5', date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       '5', date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       date '2005-05-05', time '05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555',
       time '05:05:05.555555', timestamp '2005-05-05 05:05:05.555555',
       timestamp '2005-05-05 05:05:05.555555';

CREATE TABLE c AS
SELECT COALESCE(5, 5.5) AS c1,
       COALESCE(5, 5.5e5) AS c2,
       COALESCE(5, '5') AS c3,
       COALESCE(5, date '2005-05-05') AS c4,
       COALESCE(5, time '05:05:05.555555') AS c5,
       COALESCE(5, timestamp '2005-05-05 05:05:05.555555') AS c6,
       COALESCE(5.5, 5.5e5) AS c12,
       COALESCE(5.5, '5') AS c13,
       COALESCE(5.5, date '2005-05-05') AS c14,
       COALESCE(5.5, time '05:05:05.555555') AS c15,
       COALESCE(5.5, timestamp '2005-05-05 05:05:05.555555') AS c16,
       COALESCE(5.5e5, '5') AS c23,
       COALESCE(5.5e5, date '2005-05-05') AS c24,
       COALESCE(5.5e5, time '05:05:05.555555') AS c25,
       COALESCE(5.5e5, timestamp '2005-05-05 05:05:05.555555') AS c26,
       COALESCE('5', date '2005-05-05') AS c34,
       COALESCE('5', time '05:05:05.555555') AS c35,
       COALESCE('5', timestamp '2005-05-05 05:05:05.555555') AS c36,
       COALESCE(date '2005-05-05', time '05:05:05.555555') AS c45,
       COALESCE(date '2005-05-05', timestamp '2005-05-05 05:05:05.555555') AS c46,
       COALESCE(time '05:05:05.555555', timestamp '2005-05-05 05:05:05.555555') AS c56;

CREATE TABLE g AS
SELECT GREATEST(5, 5.5) AS c1,
       GREATEST(5, 5.5e5) AS c2,
       GREATEST(5, '5') AS c3,
       GREATEST(5, date '2005-05-05') AS c4,
       GREATEST(5, time '05:05:05.555555') AS c5,
       GREATEST(5, timestamp '2005-05-05 05:05:05.555555') AS c6,
       GREATEST(5.5, 5.5e5) AS c12,
       GREATEST(5.5, '5') AS c13,
       GREATEST(5.5, date '2005-05-05') AS c14,
       GREATEST(5.5, time '05:05:05.555555') AS c15,
       GREATEST(5.5, timestamp '2005-05-05 05:05:05.555555') AS c16,
       GREATEST(5.5e5, '5') AS c23,
       GREATEST(5.5e5, date '2005-05-05') AS c24,
       GREATEST(5.5e5, time '05:05:05.555555') AS c25,
       GREATEST(5.5e5, timestamp '2005-05-05 05:05:05.555555') AS c26,
       GREATEST('5', date '2005-05-05') AS c34,
       GREATEST('5', time '05:05:05.555555') AS c35,
       GREATEST('5', timestamp '2005-05-05 05:05:05.555555') AS c36,
       GREATEST(date '2005-05-05', time '05:05:05.555555') AS c45,
       GREATEST(date '2005-05-05', timestamp '2005-05-05 05:05:05.555555') AS c46,
       GREATEST(time '05:05:05.555555', timestamp '2005-05-05 05:05:05.555555') AS c56;

SHOW CREATE TABLE u;
SHOW CREATE TABLE c;
SHOW CREATE TABLE g;

DROP TABLE u, c, g;

Suggested fix:
Make LEAST and GREATEST use same data type derivation and comparison as UNION and COALESCE.

[31 Oct 2017 20:43] Jon Stephens

Documented fix in the MySQL 8.0.4 changelog, as follows:

    *Important Change* The LEAST() and GREATEST() functions no longer 
    attempt to infer a context for their arguments. For example, 
    LEAST("11", "2") returned "11", but LEAST("11", "2") + 0 evaluated 
    the arguments as integers rather than as strings, and returned 2. 
    Now these functions always evaluate their arguments strictly according 
    to type, and any data type coercion is performed only on the result
    returned by the function. This means that the expression
    LEAST("11", "2") + 0 now evaluates to "11" + 0 = 11.

    This change has been made due to the following considerations:

        1. Rules for deriving context within these two functions 
        were not always clear or consistent.

        2. The results of these functions were not consistent with 
        the results of COALESCE() or of a UNION query.

Closed.

[31 Oct 2017 23:52] Jon Stephens

Also added a note pointing out this change in the Functions chapter of the MySQL 8.0 Manual.