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

Concatenation operator || returns wrong results in sql_mode=ORACLE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.0
    • Fix Version/s: 10.3.7
    • Component/s: OTHER
    • Labels:
      None

      Description

      In Oracle, concatenation has the same priority with + (and lower than *).

      SELECT -1+1||1 FROM DUAL /* (-1+1)||'1' */;
      

      01
      

      SELECT -1||0+1 FROM DUAL /* (-1||0)+1 */;
      

      -9
      

      SELECT 1*1||-1 FROM DUAL /* (1*1)||-1 */;
      

      1-1
      

      SELECT 1||1*-1 FROM DUAL /* 1||(1*-1) */;
      

      1-1
      

      In MariaDB, the concatenation operator || (with PIPES_AS_CONCAT enables) has higher priority over + and * and bit shift. This gives difference results in Oracle compatibility mode:

      SET sql_mode=ORACLE;
      SELECT -1+1||1 FROM DUAL /* (-1+1)||'1' */;
      

      10
      

      SET sql_mode=ORACLE;
      SELECT -1||0+1 FROM DUAL /* (-1||0)+1 */;
      

      -9
      

      SET sql_mode=ORACLE;
      SELECT 1*1||-1 FROM DUAL /* (1*1)||-1 */;
      

      1
      

      SET sql_mode=ORACLE;
      SELECT 1||1*-1 FROM DUAL /* 1||(1*-1) */;
      

      -11
      

      It should be fixed to produce Oracle compatible results when sql_mode=ORACLE is set.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: