MariaDB Development
  1. MariaDB Development
  2. MDEV-4655

Difference in how GREATEST and COALESCE process arguments

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.3, 5.5.31, 5.3.12
    • Fix Version/s: 10.0.11, 5.5.38, 5.3.13
    • Labels:
      None
    • Global Rank:
      3172

      Description

      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'

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Alexander Barkov
            Reporter:
            Alexander Barkov
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: