Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-14013

sql_mode=EMPTY_STRING_IS_NULL

Details

    • Technical task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3(EOL)
    • 10.3.3
    • Parser
    • None
    • 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

    Description

      In order to emulate Oracle's behavior in handling empty strings as NULL, we'll add a new sql_mode flag EMPTY_STRING_IS_NULL.
      When this flag is set, we will:

      • translate Item_string created in the parser to Item_null
      • translate binding an empty string as prepared statement parameters to binding NULL

      Note, more NULL handling flags will be added later under terms of separate patch:

      • translating empty strings in function return values to NULL
      • handling empty strings as equal to NULL in comparison operators

      This task is only about literals and PS parameters.

      The new flag will be disabled by default.
      The new flag will NOT be a part of sql_mode=ORACLE.

      In order to activate this behavior, one will have to do:

      SET sql_mode='ORACLE,EMPTY_STRING_IS_NULL';

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16471 mysqldump throws "Variable 'sql_mode' can't be set to the value of 'NULL' (1231)"

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23253 EMPTY_STRING_IS_NULL: error setting a not-NULL system variable to empty string

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27744 LPAD in vcol created in ORACLE mode makes table corrupted in non-ORACLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment -

            Reviewed a contributed patch from Jerome B.
            Proposes a few small cleanups.
            Pushed.

            bar Alexander Barkov added a comment - Reviewed a contributed patch from Jerome B. Proposes a few small cleanups. Pushed.
            anilvidhani Anil added a comment -

            Hi team,
            I am getting the same issue when trying to set SQL_MODE as EMPTY_STRING_IS_NULL in Server version: 10.3.2-MariaDB MariaDB Server to compatible with ORACLE.

            getting below error for your reference :

            ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'EMPTY_STRING_IS_NULL'

            anilvidhani Anil added a comment - Hi team, I am getting the same issue when trying to set SQL_MODE as EMPTY_STRING_IS_NULL in Server version: 10.3.2-MariaDB MariaDB Server to compatible with ORACLE. getting below error for your reference : ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'EMPTY_STRING_IS_NULL'
            bar Alexander Barkov added a comment -

            Anil, this feature will be available in MariaDB-10.3.3.

            bar Alexander Barkov added a comment - Anil, this feature will be available in MariaDB-10.3.3.
            anilvidhani Anil added a comment -

            Please suggest how to fix if I want both the SQL_MODE active on MariaDB 10.3.2 server.

            SQL_MODE =STRICT_TRANS_TABLES, EMPTY_STRING_IS_NULL

            Thanks in advance
            Anil

            anilvidhani Anil added a comment - Please suggest how to fix if I want both the SQL_MODE active on MariaDB 10.3.2 server. SQL_MODE =STRICT_TRANS_TABLES, EMPTY_STRING_IS_NULL Thanks in advance Anil
            anilvidhani Anil added a comment -

            Thanks a lot Alexander Barkov for quick update .... you have save my day .... Thanks again.

            anilvidhani Anil added a comment - Thanks a lot Alexander Barkov for quick update .... you have save my day .... Thanks again.
            Zilldawg Mike Hawksfraert added a comment - - edited

            I am confused about this feature - is this supposed to work for LOAD DATA INFILE and mysqlimport loads as well?

            My version is 10.3.9-MariaDB-1:10.3.9+maria~xenial-log

            I have a MyISAM table with an int(11) column user_id which has DEFAULT NULL defined.

            When I insert records into this table using the INSERT INTO... command, using empty strings for user_id, I get the expected NULL for user_id.

            But when I try to load CSV data which is empty on the user_id field (as in "nothing between the commas"), the user_id for those rows is set to 0 (zero).

            This happens for both LOAD DATA INFILE and mysqlimport.

            Please advise.

            Zilldawg Mike Hawksfraert added a comment - - edited I am confused about this feature - is this supposed to work for LOAD DATA INFILE and mysqlimport loads as well? My version is 10.3.9-MariaDB-1:10.3.9+maria~xenial-log I have a MyISAM table with an int(11) column user_id which has DEFAULT NULL defined. When I insert records into this table using the INSERT INTO... command, using empty strings for user_id, I get the expected NULL for user_id. But when I try to load CSV data which is empty on the user_id field (as in "nothing between the commas"), the user_id for those rows is set to 0 (zero). This happens for both LOAD DATA INFILE and mysqlimport. Please advise.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.