Zero-length binary data is NULL?

I ran into something that I didn't expect.

When running a test in DUnit, when inserting zero-length data into a not
null BLOB field, I consistently get:

Caught exception during WriteBinary([0 bytes]): NexusDB: <unnamed TnxQuery
instance>: Field value required. [$2604/9732]


Am I correctly interpreting this to mean that the SQL engine is interpreting
zero-length BLOB data as a NULL? This seems very odd to me.

For a discrete example:

create table foo ( bar text not null );

Then try:

insert into foo(bar) values ('xyzzy'); // This is OK

insert into foo(bar) values (''); // this raises exception

> Am I correctly interpreting this to mean that the SQL engine is interpreting
> zero-length BLOB data as a NULL? This seems very odd to me.

hm zero length means nothing = nil = null right? imho that's correct and
expectable behaviour. i've no idea about other engines though...


--

Hannes Danzl [NexusDB Developer]
Newsgroup archive at http://www.tamaracka.com/search.htm

> hm zero length means nothing = nil = null right? imho that's correct and
> expectable behaviour. i've no idea about other engines though...

The idea that "zero length means nothing" is a fundamental misunderstanding.

In other engines --and the SQL-92 standard -- this is different, which is
why I found it very surprising. (Some of the documented evidence is at the end.)

--- Theory ---

The reason for this is that zero length *does* have meaning. It means that
there is valid data, but the length of the valid data is zero. Null can be
interpreted that there are no valid data whatsoever, or indicate an
undefined state.

This is a subtle difference, but very imporant. In digital circuits, most
operate on a binary state: 0, 1 (off/on). However, in many circumstances one
must have a tri-state: 0, 1, undefined (off/on/floating).

This binary vs. tri-state idea, or the concept of "undefined," is what the
SQL-92 standard embodies, in other words, empty strings are not nulls, but
valid strings with zero length. In other words ''<>null just as for numbers
0<>null and for booleans false<>null.

--- Internal Evidence ---

Your reasoning that "zero length means nothing = nil = null" also appears
inconsistent with the NexusDB varchar field. In a varchar field I can store
a zero-length string in a not null field, but by your reasoning this should
not be allowed because '' = null.

In other words the following will not raise an exception:

create table foo (bar varchar(10) not null);
insert into foo(bar) values ('');

but the following will:

create table foo (bar text not null);
insert into foo(bar) values ('');

I do not see the logic that varchar will behave in one way (''<>null) but
text another (''=null). If zero length = null, then the two must behave the
same.

Similarly, the SQL-92 standard states that any comparison against a null
value must return null because it is neither true nor false, but unknown.

By extension this is why

select count(*) from #table where afield='';

will return a different result from

select count(*) from #table where afield is null;

--- SQL-92 Documentary Evidence ---

[This is incomplete because of the numerous of examples that validate the
theory previously presented.]

3.1.3(r) defines null as "A special value, or mark, that is used to indicate
the absence of any data value."

Section 4.1 (Conceptsata Types) states that each data type has a defined
litteral representation, and that "There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a null
value is desired." Note that zero length has a litteral representation,
specifically ''. Because zero length has a litteral representation, zero
length <> null.

See SQL-92 definition for a string litteral (5.3), for example. A litteral
is "a non-null value" and '' is a valid string litteral according to the
supplied BNF. Therefore ''<>null, or in other words zero length <> null.

Also see section 6.1 under the syntax rules where (9)b states that a
variable-length string has "a minimum length of 0", whereby ''<>null.

Similarly, one can do repeated searches through the raw SQL-92 texts to find
other examples of zero length <> null.

6.6 [General Rules] 4(a) is explicit that zero length is not null, even for
non-text data:

"If the data type of S is a character data type, then the result is the
number of characters in the value of S. Otherwise, the result is
OCTET_LENGTH(S)."

4) If a <char length expression> is specified, then

Case:

a) Let S be the <string value expression>. If the value of S is
not the null value, then

Case:

i) If the data type of S is a character data type, then the
result is the number of characters in the value of S.

ii) Otherwise, the result is OCTET_LENGTH(S).

b) Otherwise, the result is the null value.

Also interesting to see is the huge amount of complaining on the 'Net about
ORACLE's VARCHAR2 fields, which are in blatant violation of SQL-92. In
ORACLE, a zero-length LOB <> null (correct), but a zero-length string in
VARCHAR2 = null (incorrect). Very bad!!!!

This blatent SQL-92 violation sometimes makes porting data to/from ORACLE a
huge pain in the butt.

i stand corrected. I knew that in normal fields zero length <> null but
considered a blob with contents as null. good to learn from sql pros.

--

Hannes Danzl [NexusDB Developer]
Newsgroup archive at http://www.tamaracka.com/search.htm

> For a discrete example:
>
> create table foo ( bar text not null );
>
> Then try:
>
> insert into foo(bar) values ('xyzzy'); // This is OK
>
> insert into foo(bar) values (''); // this raises exception

How does the BDE treat the example above?

--


Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com

> How does the BDE treat the example above?

I've not used the BDE for years. I have no idea.

Eivind,

<< How does the BDE treat the example above? >>

Look to SQL standards (SQL-92 and SQL:1999) rather than the BDE <g>.

AFAIK, null represents a state and '' an empty value, hence the following
SQL has a different meaning:

a) insert into foo(bar) values (null); // No value assigned

b) insert into foo(bar) values (''); // A value of zero length assigned

Ole Willy Tuv

I guess it boils down to how closely to adhere to SQL-92. The web page says
"The main effort is to support full ANSI SQL compatibility first and then
extend it in a way our users want."

I'm guessing that it's not a huge programmatic change (yes, I said I'm
guesing that somewhere there's a test "if Length(Data)=0 then {insert null}
else {insert value}"), and therefore worth fixing to be able to claim SQL-92
compliance.

Otherwise, it's an ongoing "oh... in the documentation on page 123 it states
that the SQL engine is not fully SQL-92 compliant in cases where...."