[MDEV-12076] CONCAT behavior with NULL is different to Oracle Created: 2017-02-16  Updated: 2017-04-09  Resolved: 2017-03-02

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.2.3
Fix Version/s: 10.3.0

Type: Bug Priority: Critical
Reporter: Alvin Richards (Inactive) Assignee: Alexander Barkov
Resolution: Duplicate Votes: 1
Labels: Compatibility, datatype

Issue Links:
Duplicate
duplicates MDEV-12143 sql_mode=ORACLE: Make the CONCAT func... Closed

 Description   

Problem

Given the following statement

SELECT CONCAT('TDSMGR',NULL)

In MariaDB this returns NULL, in Oracle this returns 'TDSMGR'.

In SQL_MODE='Oracle' this different behavior causes significant problems.

Background

CONCAT Function is different implemented.

https://www.techonthenet.com/mariadb/functions/concat.php
https://www.techonthenet.com/oracle/functions/concat.php

Reproduce

See above

Workaround

SELECT CONCAT_WS(', ',
 
concat_ws('',"TDSMGR",null)
 
);

or

SELECT CONCAT('TDSMGR',IFNULL(NULL,''));

Solution

For SQL_MODE = 'Oracle' ensure that CONCAT exhibits the Oracle behaviour.

 Comments   
Comment by Alexander Bienemann (Inactive) [ 2017-02-16 ]

A possible workaround or implementation might by e.g. a UDF or an „alias“ of the form

CONCAT_LAZY_EVAL(a,b,c) = CONCAT(UNNULL(a), UNNULL(b), UNNULL(c))

where UNNULL(a) = IF(a IS NULL, "", a) etc.

Generated at Thu Feb 08 07:54:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.