[MDEV-16186] Concatenation operator || returns wrong results in sql_mode=ORACLE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
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' */;

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' */;

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 */;

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

blocks

MDEV-16156 PIPES_AS_CONCAT does not work well

Open

relates to

MDEV-17359 || operator is not understand by "like" in Oracle

Closed

Activity

There are no comments yet on this issue.

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-05-16 04:18

Updated:: 2018-10-04 05:16

Resolved:: 2018-05-16 13:24

Git Integration

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

MariaDB Server