Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41911

GREATEST/LEAST compare mixed string/number arguments numerically, contrary to the documented string comparison

    XMLWordPrintable

Details

    Description

      The documentation of LEAST(), which GREATEST() refers to for its comparison rules, ends with:

      "In all other cases, the arguments are compared as case-insensitive strings."

      Mixed string/number argument lists fall into that case, but the comparison actually performed is numeric: the string arguments are coerced to numbers. With the default utf8mb4_general_ci collation, reproduced on 10.11.19 and on 13.1 (debug builds):

      SELECT GREATEST('B', 0);      -- 0     ('B' -> 0; as strings 'B' > '0', so the answer should be 'B')
      SELECT GREATEST('abc', 5);    -- 5     (as strings 'abc' > '5', so the answer should be 'abc')
      SELECT LEAST('abc', 5);       -- 0     (as strings '5' < 'abc', so the answer should be '5')
      SELECT GREATEST('5', 10);     -- 10    (as strings '5' > '10', so the answer should be '5')
      SELECT GREATEST('999', 1000); -- 1000  (as strings '999' > '1000', so the answer should be '999')

      In every case a different argument wins than the documented rule selects — not just a different value, a different operand. The first three examples cannot be explained by the first documented rule either ("all arguments are integer-valued ... compared as integers"), because 'B' and 'abc' are not integer-valued.

      MySQL follows the documented rule for the same statements: GREATEST('abc',5) returns 'abc', GREATEST('5',10) returns '5', GREATEST('999',1000) returns '999'.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            chunlingqin chunlingqin
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.