Re: Why are empty VARCHAR2S always NULL?

"Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns9271AB88C729Awrgrpde_at_62.153.159.134...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> news:3d639ab8$0$8507$ed9e5944_at_reading.news.pipex.net:
>
> > Maybe I'm just sheltered but I can't see any reason why anyone would
> > regard using the absence of any entry as signifying NULL a bug. What
> > representation would you use for unknown.
>
> NULL is unknown, but '' is a known empty string. At least that's
> what I'd expect it to be.

I see that the rest of your comments follow from this. I don't see you answering my second question though. If not an empty string what representation would you use for NULL in a character field.

>
> We have, for example, a table with parts we shipped. That table
> contains a "customer id" column. Some parts have not yet been
> shipped, so that column is empty. It's intentionally left empty;
> it's not intended to be NULL because its contents is not unknown.

presumably it should actually contain 'NOT SHIPPED' or 'ON HOLD' or similar.

> I can search for "customer = 'Oracle'" or "customer LIKE 'ABC%'"
> but I have to use "customer IS NULL" to search for parts that
> have not yet been shipped. Ok so far, but the problem came up
> when someone wanted to search for all parts not shipped to a
> particular group of customers. He tried "customer NOT LIKE 'Pattern'"
> and was very surprised that he didn't get any of the not yet
> shipped parts. Ternary logic is hard to explain to non-databasers.

Agreed.

>
> Another problem is that we get NULL without noticing. We let users
> enter data and store it in the database, something like this:
>
> update table set column='data';
>
> and expect to be able to retrieve it later with:
>
> select * from table where column='data';
>
> This works fine except when data happens to be empty, we have to
> make sure we create a proper IS NULL clause even though we didn't
> "set column=NULL". Quite confusing, tedious, and error-prone.

Again I'd be defining the column in your example table as NOT NULL (maybe with a default of 'NOT YET SHIPPED' or some such) and having a valid list of shipping statuses.

> > What you mean is how do I join two unknown values. This is a
> > meaningless join.
>
> Joining by NULL certainly is meaningless but, given the application
> above, joining by empty columns is not. Imagine a second table which
> also has (possibly empty) customer ids which is to be joined against
> the parts table for some reason.

In the end this is a philosophical difference I think and not a bug as such. The product is working as intended and documented but not as you expect.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************