Forum Stats

  • 3,853,188 Users
  • 2,264,189 Discussions
  • 7,905,282 Comments

Discussions

Support ANSI SQL behaviour for null/empty Character string data types

Sebastien A.
Sebastien A. Member Posts: 7
edited Nov 7, 2017 5:38AM in Database Ideas - Ideas

Oracle considers the empty string '' to be the same as null. This differs from what most SQL dialect do and it prevents compliance with ANSI SQL.

Proposed Idea

Have a database configuration that would make oracle compliant by:

  • Considering null/empty string as different
    • in comparison
    • while stored (inserting then selecting an empty string should return an empty string)
  • Modify concatenation operator behave as specified in the spec ('a' || null = null, and 'a' || '' = 'a')
  • Modify functions having a string as input to comply with the spec. For instance LENGTH(null) should yield null but LENGTH('') should yield 0.

This configuration would need to be opt-in to avoid breaking changes for existing oracle application.

Note:

It seems that VARCHAR2 was created to keep VARCHAR  datatype available to support the standard behaviour in the future.

However I a database level configuration would be needed to configure how empty string literals should be considered.

Motivation

The goal of such idea is to be able to make SQL vendor independant application easier to write.

Some application developped with standard SQL in mind and actually making a difference between '' and null  have a hard time being made compatible with Oracle.

ANSI SQL: empty character string vs null behaviour

Even though we can find lot of places on the web people saying that in standard SQL empty character string should not be considered null I couldn't find in the SQL specification a place that explicitely states that.

However what the spec says the following

Concatenating character string with null should yield null

From ISO/IEC 9075-2:2003 (E) 6.28 <string value expression>:

    <concatenation> ::= <character value expression> <concatenation operator> <character factor>    ...    b) Let S1 and S2 be the result of the <character value expression> and <character factor>, respectively. Case:        i) If either S1 or S2 is the null value, then the result of the <concatenation> is the null value.

So the spec says 'foo' || null = null but in oracle 'foo' || null = 'foo'.

Even if not explicited in the spec 'foo' || '' should yield 'foo'. (I can't think of an argument to have anything else).

Hence one cannot comply with the spec if considering '' and null because it requires need different results for input '' and null.

Length(null) should yield null value

From ISO/IEC 9075-2:2003 (E) 6.27 <numeric value function>

<length expression> ::=<char length expression>| <octet length expression><char length expression> ::={ CHAR_LENGTH | CHARACTER_LENGTH } <left paren> <string value expression>[ USING <char length units> ] <right paren>...General Rules1) If the value of one or more <string value expression>s, <datetime value expression>s, <interval valueexpression>s, and <collection value expression>s that are simply contained in a <numeric value function>is the null value, then the result of the <numeric value function> is the null value.

So LENGTH(null) should yield null but LENGTH('') should yield 0.

Hence one cannot comply with the spec if considering '' and null because it requires need different results for input '' and null.

<literal> are used to specify non-null values

From ISO/IEC 9075-2:2003 (E) 5.3 <literal>.

Function: Specify a non-null value.

This implies that '' should not be null or not be a valid string literal.

But in oracle '' is considered to be null.

Update:

Rephrasing to:

* clarify what the proposed idea.

* summarize evidences from SQL specification.

Sebastien A.Lukas Edercormaco3563003Piotr Dobrogost
9 votes

Active · Last Updated

«13

Comments

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond

    Unfortunately, making a change to the way the standard datatypes behave would typically break thousands of databases around the world.  I can't see that happening any time soon.

  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown

    Can you reference the ansi document where it shows how NULLs should be handled for varchar2 strings?

    William Robertson
  • Sebastien A.
    Sebastien A. Member Posts: 7
    edited Nov 6, 2017 5:41AM

    I know it would be a breaking change. It should be opt-in configuration like SQL server did it with ANSI_NULLS

    Lukas EderWilliam Robertson
  • Can you reference the ansi document where it shows how NULLs should be handled for varchar2 strings?

    I'm no SQL standard expert the only thing I could personnaly found in the spec is in ISO/IEC 9075-2:2003 (E) 5.3 <literal>.

    5.3 <literal>Function: Specify a non-null value.

    This implies that '' should not be null or not be a valid string literal.

    Additionally there are plenty of places on the web where people claim this behaviour is not ANSI like those

    * https://stackoverflow.com/a/1171200/1069454

    * https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372

  • I'm no SQL standard expert the only thing I could personnaly found in the spec is in ISO/IEC 9075-2:2003 (E) 5.3 <literal>.

    5.3 <literal>Function: Specify a non-null value.

    This implies that '' should not be null or not be a valid string literal.

    Additionally there are plenty of places on the web where people claim this behaviour is not ANSI like those

    * https://stackoverflow.com/a/1171200/1069454

    * https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372

    I found another incompatibility with the SQL Spec induced by the fact that oracle considers '' as null.

    From ISO/IEC 9075-2:2003 (E) 6.28 <string value expression>:

    <concatenation> ::= <character value expression> <concatenation operator> <character factor>
    ...
    b) Let S1 and S2 be the result of the <character value expression> and <character factor>, respectively. Case:
        i) If either S1 or S2 is the null value, then the result of the <concatenation> is the null value.

    But in oracle concatenating null behaves as if not concatenating 'foo' || null yields 'foo' instead of null.

    Lukas Eder
  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown

    I'm no SQL standard expert the only thing I could personnaly found in the spec is in ISO/IEC 9075-2:2003 (E) 5.3 <literal>.

    5.3 <literal>Function: Specify a non-null value.

    This implies that '' should not be null or not be a valid string literal.

    Additionally there are plenty of places on the web where people claim this behaviour is not ANSI like those

    * https://stackoverflow.com/a/1171200/1069454

    * https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372

    A literal is something completlly different. You can't use this part of the spec. Before this discussion escalates further, it would be good to really check the standard and see what is written there. First need to get the facts right.

    Also what would the consequence of your suggestion be?

    If you declare a column as NOT NULL, you would allow to insert an empty string '' into the column? This would break most applications.

    If the only issue is how to compare NULL values, then maybe a different kind of comparison operator, like === (compare value, type and NULL) would be better suited to solve or avoid the problems that you see with the empty=NULL logic.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond

    A literal is something completlly different. You can't use this part of the spec. Before this discussion escalates further, it would be good to really check the standard and see what is written there. First need to get the facts right.

    Also what would the consequence of your suggestion be?

    If you declare a column as NOT NULL, you would allow to insert an empty string '' into the column? This would break most applications.

    If the only issue is how to compare NULL values, then maybe a different kind of comparison operator, like === (compare value, type and NULL) would be better suited to solve or avoid the problems that you see with the empty=NULL logic.

    Having worked on Ingres databases, the number of problems caused by having empty strings not the same as null, was more than I can count.  When viewing the data, you cannot tell if a column is containing nulls, empty strings, or a mixture of both, so you always had to try and remember to check for both things in your queries.  Most people usually forgot, and most people ended up with queries producing the wrong results.  It's actually great to work on Oracle where an empty string is null... as it just means we have one check to make, rather than two.  I can hardly think of any reason for storing an empty string in any of the different applications I've needed to write.

    Thorsten Kettner
  • A literal is something completlly different. You can't use this part of the spec. Before this discussion escalates further, it would be good to really check the standard and see what is written there. First need to get the facts right.

    Also what would the consequence of your suggestion be?

    If you declare a column as NOT NULL, you would allow to insert an empty string '' into the column? This would break most applications.

    If the only issue is how to compare NULL values, then maybe a different kind of comparison operator, like === (compare value, type and NULL) would be better suited to solve or avoid the problems that you see with the empty=NULL logic.

    Sven W. wrote:A literal is something completlly different. You can't use this part of the spec. Before this discussion escalates further, it would be good to really check the standard and see what is written there. First need to get the facts right.

    I updated the idea title and description to be more specific on the problem and use the terminology used in the SQL standard spec.

    The fact is that it seems that oracle is non standard compliant when it comes to handling null/empty character strings. I found 2 evidences listed above after scanning through the SQL standard spec.

    Also what would the consequence of your suggestion be?If you declare a column as NOT NULL, you would allow to insert an empty string '' into the column? This would break most applications.

    The goal of the idea is to provide configuration to make oracle behave the standard way.

    This would  be opt-in to prevent breaking existing oracle applications.

    Having a configuration to make oracle behave the standard way would allow applications to be more RDBMS independent. That's why standard exist.

    If the only issue is how to compare NULL values, then maybe a different kind of comparison operator, like === (compare value, type and NULL) would be better suited to solve or avoid the problems that you see with the empty=NULL logic.

    I don't think the only issue is how to compare strings.

    For other RDBMS null and empty strings are 2 different things but on oracle there is no way to make such differences. People resort to all sort of hack when porting to oracle to deal with this lack of difference (e.g. Oracle RDBMS empty string and null ).

  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    Having worked on Ingres databases, the number of problems caused by having empty strings not the same as null, was more than I can count.  When viewing the data, you cannot tell if a column is containing nulls, empty strings, or a mixture of both, so you always had to try and remember to check for both things in your queries.  Most people usually forgot, and most people ended up with queries producing the wrong results.  It's actually great to work on Oracle where an empty string is null... as it just means we have one check to make, rather than two.  I can hardly think of any reason for storing an empty string in any of the different applications I've needed to write.

    I don't buy this argument. I cannot recall any system / language where empty strings and null are the same thing, including Java, JavaScript, Scala, Kotlin, and pretty much every other SQL dialect. I don't even see the advantage of this "equality". Consider the inconsistent behaviour between 1 + NULL IS NULL and 'a' || NULL = 'a'. Why isn't zero (the "empty integer") and NULL the same thing to at least be consistent?

    Sebastien A.
  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    I know it would be a breaking change. It should be opt-in configuration like SQL server did it with ANSI_NULLS

    I definitely think that this would need to be introduced as an opt-in change at first, given its tremendous potential impact on everything.

    Sebastien A.