Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
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
|
|
Attachments
Issue Links
- relates to
-
MDEV-11880
sql_mode=ORACLE: Make the concatenation operator ignore NULL arguments
-
- Closed
-
-
MDEV-12964 sql_mode=ORACLE: multi-columns Unique index behavior to expect with NULL value
-
- Open
-
-
MDEV-14013
sql_mode=EMPTY_STRING_IS_NULL
-
- Closed
-
- split from
-
-
- Open
-
Activity
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)
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.
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"