InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

Bill

Thanks for your analysis of this. I _think_ I agree. I'm just not sure if
"DISTINCT" is considered a special case or not.

I've been wading through books on SQL over the last few days trying to find
something that specifically refers to equality testing of NULL in the
presence of a DISTINCT clause and haven't found anything so far, in which
case everything you say would appear to be correct.

I'd be very interested to hear everyone's opinion on this one, especially if
anyone has done anything similar with other databases. As you seem to have
hinted that it's OK to post this to the mailing list, I've taken the liberty
of doing just that (I hope you don't mind).

As an example of what's going on:

create table tmp (id int, colname int)
insert into tmp values (1, 1)
insert into tmp values (2, null)
insert into tmp values (2, null)
SELECT DISTINCT colname FROM tmp WHERE colname IS NULL

produces:

colname
NULL
NULL

The question is, should the above produce one or two rows of output?

Regards

Peter Hearty
Lutris Technologies UK Ltd.
-----Original Message-----
From: Bill Karwin <bill.karwin@lutris.com>
To: Peter Hearty <peter.hearty@lutris.com>
Date: 19 July 2000 20:02
Subject: Re: InstantDB: SELECT DISTINCT and NULL


>[I'm sending this directly to you to avoid questioning you in an open
>forum.  Let me know if you want me to post this to the InstantDB list.]
>
>Depends on your interpretation of row equality for purposes of DISTINCT.
>
>NULL does not equal NULL in SQL's tri-value boolean semantics.  NULL
>isn't a value, it's a state of having no value.  That is, NULL = NULL is
>false, and likewise NULL != NULL is false, and NOT (NULL = NULL) is
>false.  Or more strictly speaking, these expressions aren't false,
>they're NULL.
>
>So if the algorithm of DISTINCT is that two rows are equal (and subject
>to collapsing to one row in a DISTINCT output) if all fields in the
>select-list are equal, then two rows containing NULLs in a field will
>naturally not pass this test.
>
>In other words, I believe the behavior Lennart doesn't like is in fact
>correct, for strict adherence to the SQL standard.
>
>In my experience, developers hate the boolean semantics of NULLs because
>it results in complicated logic.  They want to treat NULL as a value.
>But that's the SQL standard, love it or hate it.  Joe Celko ("SQL For
>Smarties") recommends solving the issue by using NOT NULL everywhere in
>your column definitions.
>
>Regards,
>Bill Karwin
>
>Peter Hearty wrote:
>>
>> Lennart
>>
>> I'm afraid this looks like a bug. It'll probably be a couple of weeks
before
>> there's a fix available for this.
>>
>> Regards
>>
>> Peter Hearty
>> Lutris Technologies UK Ltd.
>>
>> > Hello,
>> >
>> > the SQL statement
>> >
>> > SELECT DISTINCT colname FROM table WHERE colname IS NULL
>> >
>> > gives a result set with one row for each row in table where colname IS
>> NULL.
>> >
>> > Is there any work-around? It is a show-stopper for using InstantDB in a
>> > product we are developing.
>> >
>> > Regards
>> >
>> > Lennart Esklund
>> > Zenon AB
>> > Sollentuna, Sweden
>>
>> -------------------------------------------------------------------------
----
>> To unsubscribe from this mailing list, send email to
majordomo@enhydra.org
>> with the text "unsubscribe instantdb" in the body of the email.
>> If you have other questions regarding this mailing list, send email to
>> the list admin at owner-instantdb@enhydra.org.
>

-----------------------------------------------------------------------------
To unsubscribe from this mailing list, send email to majordomo@enhydra.org
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at owner-instantdb@enhydra.org.