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

PIPES_AS_CONCAT does not work well

    XMLWordPrintable

    Details

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

      Description

      In MariaDB the pipes operator || stands for boolean OR by default. For compatibility purposes || can change its meaning to concatenation when PIPES_AS_CONCAT is enabled, either as a separate flag, or as a part of these sql_mode aliases:

      • ANSI
      • ORACLE
      • MSSQL
      • POSTGRESQL
      • DB2
      • MAXDB

      However, the precedence of the concatenation operator || is not compatible with the emulated databases.

      In MariaDB, concatenation has higher priority over + and * and bit shift

      SET sql_mode=PIPES_AS_CONCAT;
      SELECT -1+1||1;   -- -1+(1||1)
      

      10

      SET sql_mode=PIPES_AS_CONCAT;
      SELECT -1||0+1;   --  (-1||0)+1
      

      -9

      SET sql_mode=PIPES_AS_CONCAT;
      SELECT 1*1||-1;   --  1*(1||-1)
      

      1
      Truncated incorrect DOUBLE value: '1-1'

      SET sql_mode=PIPES_AS_CONCAT;
      SELECT 1||1*-1;   --  (1||1)*-1
      

      -11

      SELECT 1<<1||1;  --  1<<(1||1)
      

      2048

      SELECT 1||1<<-1;  -- (1||1)<<-1
      

      0

      In PostgreSQL concatenation has the same priority with bit shift (and lower priority than + and *)

      PostgreSQL does not seem to tell explicitly in the documentation what is the priority of the concatenation operator ||, but it seems to have a lower priority than + and *, and the same priority with bit shift <<.

      In PostgreSQL at least one of the concatenation operands must be of a string type, so these queries return an error:

      SELECT -1+1||1;
      

      ERROR: operator does not exist: integer || integer

      SELECT -1||0+1;
      

      ERROR: operator does not exist: integer || integer

      SELECT 1*1||-1;
      

      ERROR: operator does not exist: integer || integer

      SELECT 1||1*-1;
      

      ERROR: operator does not exist: integer || integer

      Adding quotes reveals that concatenation has a priority lower than + and lower than *

      SELECT -1+1||'1';  --  (-1+1)||'1'
      

      01

      SELECT '-1'||0+1;  -- -1||(0+1)
      

      -11

      SELECT 1*1||'-1';  -- (1*1)||'-1'
      

      1-1

      SELECT '1'||1*-1;  -- '1'||(1*-1)
      

      1-1

      Further studies tell that concatenation has the same priority with bit shift:

      SELECT 1<<1||'1';  --  (1<<1)||'1'
      

      21

      SELECT '1'||1<<1;  -- ('1'||1)<<1
      

      ERROR: operator does not exist: text << integer

      PostgreSQL: mixing concatenation and INTERVAL+TIMESTAMP

      SET sql_mode=POSTGRESQL;
      SELECT INTERVAL '10' YEAR + TIMESTAMP'2001-01-01 10:20:30' || 'x' AS c1;
      

      +---------------------+
      | c1                  |
      +---------------------+
      | 2011-01-01 10:20:30 |
      +---------------------+
      1 row in set, 1 warning (0.00 sec)
      

      Looks wrong. The expected result is 2011-01-01 10:20:30x, i.e. it should do the following:

      • add 10 years to the given timestamp first
      • convert the result to string and append 'x' to the end

      PostgreSQL returns a correct result for the same query:

                c1          
      ----------------------
       2011-01-01 10:20:30x
      

      This query erroneously returns a syntax error in MariaDB:

      SET sql_mode=POSTGRESQL;
      SELECT TIMESTAMP'2001-01-01 10:20:30' + INTERVAL '10' YEAR || 'x' AS c1;
      

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '|| 'x' AS c1' at line 1
      

      The expected result is to return string '2011-01-01 10:20:30x'.

      PostgreSQL returns a correct result for the same query:

                c1          
      ----------------------
       2011-01-01 10:20:30x
      

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

      This is reported as a separate issue MDEV-16186

      The SQL standard and DB2 require string arguments for concatenation

      For strict compatibility, MariaDB should return an error on attempt to use concatenation over non-string data types when sql_mode=ANSI or sql_mode=DB2 is enabled.

      MSSQL does not support pipes

      It's not clear why PIPES_AS_CONCAT is a part of MSSQL:

      SET sql_mode=MSSQL;
      SELECT @@sql_mode;
      

      +-------------------------------------------------------------------------------------------------+
      | @@sql_mode                                                                                      |
      +-------------------------------------------------------------------------------------------------+
      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS |
      +-------------------------------------------------------------------------------------------------+
      

      In SQL Server, concatenation is performed by the operator + when arguments are strings. So perhaps PIPES_AS_CONCAT should be removed from MSSQL.

        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: