FreeTDS Development Group

["Charles Bearden" <Charles.F.Bearden AT uth.tmc.edu>]

Wow!  I haven't read much of it yet, but it looks very helpful.  I
appreciate the time you and Joe have given to responding to me (off-list
as well as on), and Freddy77's patch that worked on the 50000 rows I
used to test.  I'll try to stay on the topic of FreeTDS development in
the future :)

As I said to Joe & Jim off-list, the folks on the FreeTDS project are
exemplary among open-source developers in their helpfulness.

Thanks,
Chuck

> > Thanks for your response.
> >
> > I'm not sure what to think.  I had been pretty well persuaded that
> NULLs
> >
> > and the three-value logic they entail were a bad thing, and frankly
> that
> > was after I had come to see them as a big annoyance since they don't
> work
> > with parameter placeholders in WHERE clauses.
> 
> The knock on NULL has a good theoretical foundation, and I'm not going
> to challenge it.  Every relational guru will tell you they don't
belong
> in the model or in your design, except when they do.  The trouble with
> an absolutist never-say-null policy is that no existing RDBMS will
> tolerate a "correctly" designed database i.e., one sufficiently
> normalized to eliminate NULLs.
> 
> A nullable column is an attribute that has an optional relationship to
> its key.  That is, its relationship to the key is different from those
> of the non-nullable columns.  According to normalization rules, such
> attributes constitute a separate entity, and thus  need their own
table.
> 
> 
> Suppose you have a table with key K and attributes A and B, of which B
> is nullable:
> 
>       K-A-B
> 
> The fact is that there will be more A's than B's.  What you really
have,
> normalizationwise, is two tables:
> 
>       K-A -+--o+- K-B
> 
> so the K-A entity has an optional relationship to K-B.
> 
> The problem is that no extant technology holds up very well if you
> dutifully break out all your optional relations into their own tables.
> Especially if K is very big, the redundant storage and joining effort
--
> combined with the fact that most nullable columns have their own
> distinct cardinality and thus each needs its own table -- quickly
> explode "simple" 4-table joins into 12-table or more, and overwhelm
the
> server.  In short: it's right, but not efficient.  So we compromise.
> 
> Having compromised, some folks are tempted to adhere to a no-null
policy
> by subverting the data.  (You can tell from that characterization what
I
> think about that.)  The introduce special data to represent what's
> really NULL: -1 for the unknown foreign ID, say, or 'June 6, 2079' for
> an unknown date.  Or 'N/A' or '' for an unknown string.  IMO, you're
> better off with NULL because:
> 
> 1.  With coalesce() and isnull(), it's easier to convert an unknown to
> something useful.
> 2.  NULL is "standard" whereas every database designer can come up
with
> his own pseudo-data.  Over time, the number and kind of
representations
> for (what would be) NULL tends to grow, and grow incompatibly.
> 3.  It doesn't help.
> 
> Why doesn't it help?  In isolated, special-use cases, it might, but in
> general any query that has to cope with NULL would have to cope with
the
> special case, too.  Consider:
> 
>       WHERE A = B or B is NULL
> and
>       WHERE A = B or B = ''
> 
> Don't think so?  How about:
> 
>       WHERE B between 'Apple' and 'Camel'
> 
> If B is anything besides real, honest-to-god data, you're going to
need
> an OR clause.
> 
> Illustrating argument #1, we could express
> 
>       WHERE A = B or B is NULL
> as
>       WHERE A = coalesce(B,A)
> 
> On SQL Server, I've found that sort of thing to be much faster than OR
> clauses.  YMMV.
> 
> > Both you and Joe Geiser refer to problems the use of empty strings
> instead
> > of NULLs causes.  It may be my lack of experience in RDBMs, but it's
> not yet
> > clear to me exactly what the nature of those problems is.  In fact,
> others
> > have told me the same thing about NULLs, namely that they break
> relational
> > models and will reach back and bite you at unexpected times.  I do
> know that
> > I find NULLs inconvenient because I can't test for them with the
same
> > operators as I use to test for non-NULL values.  Are there some
good,
> simple
> > examples of the problems caused by the use of empty strings instead
of
> 
> > NULLs?
> 
> They mostly involve gotchas.
> 
> One is "bcp -c".  There's no way for a delimited ASCII file to
> distinguish between a zero-length string and a NULL.  So you basically
> can't bulk-load such a table from external sources.  If you dump the
> table to a flat file with bcp, you won't be able to reload it without
> using a second table and some SQL.
> 
> Another is that ad hoc queries can be very confusing.  For example:
> 
>       SELECT B FROM T2 WHERE A NOT IN
>               (SELECT A FROM T1)
> 
>       B
>       ----------------------
> 
> 
> 
> 
> ISQL would show "NULL" for nulls, but mere blank space for zero-length
> strings.
> 
> If it's  a CHAR field, you can't tell how many spaces are in the
> non-data:
> 
> 1> create table #t(t char(1) not null)
> 2> insert #t values ('')
> 3> go
> (1 row affected)
> 1> select * from #t
> 2> go
>  t
>  -
> 
> 
> (1 row affected)
> 1> select * from #t where t = '   '
> 2> go
>  t
>  -
> 
> 
> (1 row affected)
> 1>
> 
> Consider also:
> 
>       WHERE B < 'Camel'
> 
> picks up all the zero-length strings (and none of the NULLs), which
> probably isn't what you want, so you have to write special case ugly
> code:
> 
>       WHERE B < 'Camel' and B <> ''
> 
> Oy.  The wages of using data to represent nondata.
> 
> That's my case, FWIW.  I normally don't burden the list with SQL
> disquistions, but you did ask, and it wouldn't be nice to leave you
> wondering about vague dark allusions to the evils of zero-length
> strings.  Hope this helps.
> 
> Regards,
> 
> --jkl
> 
> -----------------------------------------
> The information contained in this transmission may contain privileged
and
> confidential information and is intended only for the use of the
person(s)
> named above. If you are not the intended recipient, or an employee or
> agent responsible for delivering this message to the intended
recipient,
> any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended
> recipient, please contact the sender immediately by reply e-mail and
> destroy all copies of the original message. Please note that we do not
> accept account orders and/or instructions by e-mail, and therefore
will
> not be responsible for carrying out such orders and/or instructions.
> If you, as the intended recipient of this message, the purpose of
which is
> to inform and update our clients, prospects and consultants of
> developments relating to our services and products, would not like to
> receive further e-mail correspondence from the sender, please "reply"
to
> the sender indicating your wishes.  In the U.S.: 1345 Avenue of the
> Americas, New York, NY 10105.
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds