Skip to Content.
Sympa Menu

freetds - RE: [freetds] OT: NULL Considered Helpful

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Charles Bearden" <Charles.F.Bearden AT uth.tmc.edu>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] OT: NULL Considered Helpful
  • Date: Tue, 10 Aug 2004 15:25:45 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page