PIPES_AS_CONCAT does not work well

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.