Forum Stats

  • 3,874,659 Users
  • 2,266,761 Discussions
  • 7,911,932 Comments

Discussions

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

2

Comments

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

    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?

    Lukas Eder wrote: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?

    zero is a known value

    an empty string is... well.... it has no value.

    Can't compare Oranges with Apples.

    Thorsten Kettner
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Nov 6, 2017 11:02AM

    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?

    Lukas Eder wrote:...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?

    To be fair, this is a different behaviour for different operators. I assume, this is also documented somewhere as ANSI behaviour.

    But it is not about the value itself.

    However we see that in this specific case, the NULL values for strings already behaves like sarod wants it to be.

    How I understand the "idea" is that

    a) the IS NULL comparison would return FALSE it the value is an empty string and TRUE if it is null.

    b) the equality comparison A=B returns TRUE if both values are empty strings, instead of NULL (=FALSE in the where clause) as it is currently.

    I think having a new type of equality comparison operator. would solve most problems that might be the reason for the suggestion.

    As a side note: Standard compliance usually is good.

    However to assume that if two databases are standard compliant, that you can copy code from one to another, is a fallacy.

    BluShadowLukas Eder
  • Sebastien A.
    Sebastien A. Member Posts: 7
    edited Nov 6, 2017 11:25AM
    Lukas Eder wrote:...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?

    To be fair, this is a different behaviour for different operators. I assume, this is also documented somewhere as ANSI behaviour.

    But it is not about the value itself.

    However we see that in this specific case, the NULL values for strings already behaves like sarod wants it to be.

    How I understand the "idea" is that

    a) the IS NULL comparison would return FALSE it the value is an empty string and TRUE if it is null.

    b) the equality comparison A=B returns TRUE if both values are empty strings, instead of NULL (=FALSE in the where clause) as it is currently.

    I think having a new type of equality comparison operator. would solve most problems that might be the reason for the suggestion.

    As a side note: Standard compliance usually is good.

    However to assume that if two databases are standard compliant, that you can copy code from one to another, is a fallacy.

    Sven W. wrote:Lukas Eder wrote:...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?To be fair, this is a different behaviour for different operators. I assume, this is also documented somewhere as ANSI behaviour.But it is not about the value itself.However we see that in this specific case, the NULL values for strings already behaves like sarod wants it to be.

    I think there is a misunderstanding here. What I would expect is to have the string concatenation behave as specified by the standard. That is  'a' || NULL should yield NULL and 'a' || '' shoudl yield ''

    As a side note: Standard compliance usually is good.However to assume that if two databases are standard compliant, that you can copy code from one to another, is a fallacy.

    I agree that a standard will never be able to standardize 100% of behaviour of something as complex as SQL and there will always be corner cases where RDBMS behaves differently.

    However string concatenation is clearly specified in AINSI SQL and current Oracle behaviour when concatenating string with null is not compliant with the spec.

    Additionally the spec basically says:

    *   'a' || NULL should yield NULL

    *   'a' || '' should yiled 'a'
    And I don't see how oracle could comply to the spec without differentiating empty string from null string.

  • GregV
    GregV Member Posts: 3,106 Gold Crown

    I agree with the fact that it would probably break many things in place. Though I don't see the need for the distinction, I do think there are inconsistencies in some built-in functions' results.

    'a' || NULL should return NULL, as should Length('a' || NULL)

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    Personally I think having two kinds of 'empty' is madness, the ANSI committee were crazy to come up with it, and Oracle has done well to stand up against it all these years. However, the standard is out there and there is a portability argument for at least providing tools to support it.

    Perhaps a new 'a' prefix could be added for text literals so that a'' would give an ANSI-style empty string.

    Sven W.BluShadowThorsten Kettner
  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge
    Sven W. wrote:Lukas Eder wrote:...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?

    To be fair, this is a different behaviour for different operators. I assume, this is also documented somewhere as ANSI behaviour.

    But it is not about the value itself.

    However we see that in this specific case, the NULL values for strings already behaves like sarod wants it to be.

    I think there is a misunderstanding here. What I would expect is to have the string concatenation behave as specified by the standard. That is  'a' || NULL should yield NULL and 'a' || '' shoudl yield ''

    As a side note: Standard compliance usually is good.However to assume that if two databases are standard compliant, that you can copy code from one to another, is a fallacy.

    I agree that a standard will never be able to standardize 100% of behaviour of something as complex as SQL and there will always be corner cases where RDBMS behaves differently.

    However string concatenation is clearly specified in AINSI SQL and current Oracle behaviour when concatenating string with null is not compliant with the spec.

    Additionally the spec basically says:

    *   'a' || NULL should yield NULL

    *   'a' || '' should yiled 'a'
    And I don't see how oracle could comply to the spec without differentiating empty string from null string.

    'a' || '' shoudl yield ''

    That's a typo, I'm assuming? It should yield 'a'

  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge
    Lukas Eder wrote: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?

    zero is a known value

    an empty string is... well.... it has no value.

    Can't compare Oranges with Apples.

    It is a well defined value and concept: https://en.wikipedia.org/wiki/Empty_string. It is the identity element of the concatenation operation, just like zero is the identity element of the addition operation. So, really. This isn't apples and oranges. This is just Oracle being super quirky.

    I understand that you've never seen the point of it from your experience, but that doesn't mean that the concept of an empty string is unnecessary / foreign / surprising / etc. Again, it's a well-defined and useful concept in pretty much every other language / platform.

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

    It is a well defined value and concept: https://en.wikipedia.org/wiki/Empty_string. It is the identity element of the concatenation operation, just like zero is the identity element of the addition operation. So, really. This isn't apples and oranges. This is just Oracle being super quirky.

    I understand that you've never seen the point of it from your experience, but that doesn't mean that the concept of an empty string is unnecessary / foreign / surprising / etc. Again, it's a well-defined and useful concept in pretty much every other language / platform.

    You don't have to tell me.  I've worked on more than just Oracle databases in my time.  Ingres is a good example as it has always treated empty strings as being different from null.  I have good experience of the numerous bugs and issues that occurred because people hadn't accounted for both empty strings and nulls in their queries, and also the developers in my team who had to enlist my help to find the issues because there's no visual difference when they are looking at the data as both things look blank on the screen.

    It was a blessing to work with Oracle where that annoyance (and after years of it, it really had become an annoyance) was removed and then we only had to worry about the difference between nulls and strings with only spaces in them.

    Thorsten Kettner
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Nov 10, 2017 3:36AM

    It is a well defined value and concept: https://en.wikipedia.org/wiki/Empty_string. It is the identity element of the concatenation operation, just like zero is the identity element of the addition operation. So, really. This isn't apples and oranges. This is just Oracle being super quirky.

    I understand that you've never seen the point of it from your experience, but that doesn't mean that the concept of an empty string is unnecessary / foreign / surprising / etc. Again, it's a well-defined and useful concept in pretty much every other language / platform.

    I am sure it is useful in a 3GL to be able to distinguish between initialised and uninitialised variables or have the flexibility to set strings to some non-empty dummy value, especially when a string is implemented as an array (and of course we have "is empty" for arrays), but really in SQL I think it is a huge mistake. We had a situation on my project just recently where a "rating not in ('C','D')" condition against a complex view was excluding unrated data, and my manager, from a SQL Server background, thought we needed to go and check whether the values were being stored as null or as empty strings. I was able to tell him there is no such distinction in Oracle and we all saved some time we might have wasted in comparing different kinds of emptiness. I expect there is a third or fourth kind of nothingness we could come up with if we put our minds to it, but those would be mistakes too.

    Thorsten Kettner
  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond

    It is a well defined value and concept: https://en.wikipedia.org/wiki/Empty_string. It is the identity element of the concatenation operation, just like zero is the identity element of the addition operation. So, really. This isn't apples and oranges. This is just Oracle being super quirky.

    I understand that you've never seen the point of it from your experience, but that doesn't mean that the concept of an empty string is unnecessary / foreign / surprising / etc. Again, it's a well-defined and useful concept in pretty much every other language / platform.

    Lukas Eder wrote: Again, it's a well-defined and useful concept in pretty much every other language / platform.

    Are you sure?

    Looking at MS ODBC documentation:

    https://docs.microsoft.com/en-us/sql/odbc/microsoft/data-type-limitations

    quote:

    Zero-Length Strings When a dBASE, Microsoft Excel, Paradox, or Textdriver is used, inserting a zero-length string into a column actually inserts a NULL instead.

    Interestingly, when googling a little, it's surprising to see that people are still raising issues about their queries not doing what they expect and finding it's because they are not catering for both null and empty string in those other database.

    As someone who's spent a long time on the Oracle community, especially in the area of SQL and PL/SQL, I have to say it's rare to see questions that relate to people being confused over empty strings being null.  When we do get such questions, it's clearly people who've come from other databases and are extremely new to Oracle, but that is not very often.