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

CONCAT behavior with NULL is different to Oracle

Details

    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.

      Attachments

        Issue Links

          duplicates

          Technical task - A technical task. MDEV-12143 sql_mode=ORACLE: Make the CONCAT function ignore NULL arguments

          • Major - Major loss of function.
          • Closed

          Activity

            abienemann Alexander Bienemann (Inactive) added a comment -

            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.

            abienemann Alexander Bienemann (Inactive) added a comment - 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.

            People

              bar Alexander Barkov
              alvinr Alvin Richards (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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