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

            Ascending order - Click to sort in descending order
            alvinr Alvin Richards (Inactive) created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Assignee Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            Component/s Syntax [ 13909 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Component/s Parser [ 10201 ]
            Fix Version/s 10.3.0 [ 22127 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Labels datatype
            bar Alexander Barkov made changes -
            Labels datatype Compatibility datatype
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79666 ] MariaDB v4 [ 151718 ]

            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.