PL/SQL parser - Phase 2 (MDEV-10764)

[MDEV-10574] sql_mode=ORACLE: IS NULL and empty strings Created: 2016-08-17  Updated: 2021-05-11

Status: Open
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 2
Labels: Compatibility, Oracle

Issue Links:
Relates
relates to MDEV-11880 sql_mode=ORACLE: Make the concatenati... Closed
relates to MDEV-12964 sql_mode=ORACLE: multi-columns Unique... Open

 Description   

Oracle does not distinguish between empty strings and NULL.
MariadDB should do the same when running with sql_mode=ORACLE.

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (id INT NOT NULL, s VARCHAR(10) NOT NULL);
 
INSERT INTO t1 VALUES (1, '');


INSERT INTO t1 VALUES (1, '')
 
                          *
 
ERROR at line 1:
 
ORA-01400: cannot insert NULL into ("SYSTEM"."T1"."S")

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (id INT NOT NULL, s VARCHAR(10));
 
INSERT INTO t1 VALUES (0, NULL);
 
INSERT INTO t1 VALUES (1, '');
 
INSERT INTO t1 VALUES (2, 'test');
 
SELECT * FROM t1 WHERE s IS NULL;


	ID S
 
---------- ----------
 
	 0
 
	 1

Example:

SELECT '' AS c1, NULL AS c2 FROM DUAL;


 
 
C C
 
- -

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (v VARCHAR(10), c CHAR(10));
 
INSERT INTO t1 VALUES ('','');
 
SELECT v,c FROM t1;


V	   C
 
---------- ----------

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (v VARCHAR(10));
 
INSERT INTO t1 VALUES ('');
 
SELECT * FROM t1 WHERE v='';


no rows selected

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (v VARCHAR(10));
 
INSERT INTO t1 VALUES ('');
 
SELECT * FROM t1 WHERE v IS NULL;


V
 
----------

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (c CHAR(10));
 
INSERT INTO t1 VALUES ('');
 
SELECT * FROM t1 WHERE c='';


no rows selected

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (c CHAR(10));
 
INSERT INTO t1 VALUES ('');
 
SELECT * FROM t1 WHERE c IS NULL;


C
 
----------

Example:

DROP TABLE t1;
 
CREATE TABLE t1 (v VARCHAR(10));
 
INSERT INTO t1 VALUES ('');
 
SELECT v||'A' FROM t1;


V||'A'
 
-----------
 
A



 Comments   
Comment by Michael Widenius [ 2016-08-18 ]

Note that this is true for VARCHAR columns only. For CHAR columns "" is not same as NULL.

The main challenge to do this is that we can't change the storage format as the data must be accessible in both oracle mode and ansi sql mode.

This functionality is quite hard to do, especially for indexed columns, so I recommend that we don't implement this if not critically necessary.

This comes from that we would have to change all queries that compare varchar strings from
a is null -> a is null or a = ""
All comparison of strings has to also take care of handling comparison with empty varchar strings.

Copying code also gets complicated as it's not clear if should store "" or null in the receiving table
if the users does:
insert into t1 select * from t2;

We would also have to convert all varchar string results from "" to NULL when sending to client.
As we don't currently distinguish between CHAR and VARCHAR this isn't trivial.

From Oracle's documentation:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.ht

"Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls"

Comment by Alvin Richards (Inactive) [ 2016-10-17 ]

We are considering to implement both of the following options (with a switch)

Transform Insert
– insert values ("") -> insert values (null)

Transform Select

  • where v=x => (v <> "" and V=X)
  • where v is null => (v="" or v is null)
Comment by Jérôme Brauge [ 2016-12-07 ]

Hi,
Even if you don't implement the feature, i think that it's important to change behavior of all string scalars functions.
These functions always return NULL instead of an empty string on Oracle.
Example 1:
select coalesce(substr('test',1,0),' was null') from dual;
COALESCE(
---------
was null

Example 2:
select coalesce(rtrim(' '),'was null') from dual;

COALESCE
--------
was null

We are looking forward to some features being implemented (like MDEV-10697) to evaluate the feasibility of bringing our application on mariadb.

Best regards.
Jérôme.

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