PL/SQL parser (MDEV-10142)

[MDEV-14013] sql_mode=EMPTY_STRING_IS_NULL Created: 2017-10-05  Updated: 2022-03-30  Resolved: 2017-10-19

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3
Fix Version/s: 10.3.3

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16471 mysqldump throws "Variable 'sql_mode'... Closed
relates to MDEV-23253 EMPTY_STRING_IS_NULL: error setting a... Open
relates to MDEV-27744 LPAD in vcol created in ORACLE mode m... Closed
Sprint: 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';



 Comments   
Comment by Alexander Barkov [ 2017-10-19 ]

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

Comment by Anil [ 2017-12-18 ]

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'

Comment by Alexander Barkov [ 2017-12-18 ]

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

Comment by Anil [ 2017-12-18 ]

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

Comment by Anil [ 2017-12-18 ]

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

Comment by Mike Hawksfraert [ 2018-08-24 ]

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.

Generated at Thu Feb 08 08:10:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.