Re: Proposal: 6NF

From: Cimode <cimode_at_hotmail.com>
Date: 8 Oct 2006 02:45:04 -0700
Message-ID: <1160300704.857861.228120_at_i3g2000cwc.googlegroups.com>


Frank Hamersley wrote:
> Cimode wrote:
> [..]
> > My advice is to dump *politically correct* approaches. SQL NULL is not
> > a better treatment of missing data. It is poor (in a word you don't
> > have to choose between pest and cholera).
>
> This phrasing is so unbecoming...the sky is definitely not falling!
>
> > Not convinced? Tons of arguments and proofs were presented to make a
> > case against them..Another proof, run this in SQL ORACLE, SQL Server or
> > DB2
>
> Should I try it on Sybase ASE 12.5?
I personally do not work on toys to draw conclusions...Using only main products...

ORACLE + SQL Server + DB2 = 3 out of 4 db's right?

> > create table table1(field1 int, field2 varchar(1))
> > go
> > insert table1
> > select 1, 'A'
> > insert table1
> > select 2, 'B'
> > insert table1
> > select 3, 'C'
> > insert table1
> > select NULL, 'B'

> Hmmm - the last statement fails...as it should! Maybe that is why you
> only got 3 rows back :-).
No problem inserting...Seems Sybase works differently...(are constraint applied automatically)

A I specified it works on ORACLE, SQL Server, DB2 (try all variations of NULL) .. Just make sure there is at least 1 row with field2 = NULL ex:

insert table1
select NULL, 'D'

> > --Query1/ The following should return ALL records whether NULL or not
> > select * from table1 where field2 = field2
>
> Hmmm - ASE returns all rows.
SQL Server, ORACLE and DB2 do not...meaning that 3 out 4 dbms consider that NULL <> NULL

> > --it turns out it return 3 out of 4 records --therefore we could
> > conclude that the system considers that NULL <> NULL Right? (opposite
> > of = should be <> right?)
>
> This is wacky - trying to rationalise 3VL by clutching at 2VL thinking
> is very trippy.
Trippy? I say Bullshit...Facts are NOT 3 VL. Logically anything that is not = is necessarily different

> > Next run...
> > select * from table1 where field2 <> field2 to get the NULL value and
>
> Hmmm - ASE returns no rows as I expect.
Of course it does not if it has not inserted any 4 th line...

> > the system returns no records which is madness because the system
> > NEITHER considers NULLS as being = NOR <> from one another...As a
> > result, all counts on tables including NULLS can only be WRONG...I do
> > not care whether other technologies do worse....This is bad enough...
>
> Even allowing for your likely intent to store the NULL in field2 as ...
> create table table1(field1 int, field2 varchar(1) null)
> and
> insert table1 select 4,NULL
> the first query returns 3 rows as expected and the second returns no
> rows...as expected!

> QED the "Proof" crashes and burns!
I indicated it works on SQL Server/ORACLE/DB2... If you want to verify an experiment result at least do it right.... Received on Sun Oct 08 2006 - 11:45:04 CEST

Original text of this message