[MDEV-16186] Concatenation operator || returns wrong results in sql_mode=ORACLE Created: 2018-05-16  Updated: 2018-10-04  Resolved: 2018-05-16

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-16156 PIPES_AS_CONCAT does not work well Open
Relates
relates to MDEV-17359 || operator is not understand by "lik... Closed

 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.
Generated at Thu Feb 08 08:27:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.