3 value logic. Why is SQL so special?

"Karen Hill" wrote in message

> X-No-Archive:yes
>
> I know that visual basic, lisp, python, c , c++ , perl, all have 2
> value logic. What makes SQL so special that it needs 3vl when all
> these langauges make do with 2vl?
>
> I read a book on SQL which was authored by Date before I knew of the
> whole Date controversy and when I was learning SQL. I was thoroughly
> confused when he went on a spiel about nulls. I echoed this sentiment
> at work and was chastized and told that Date is a kook. Yet I have
> some time later accepted nulls and work with them just fine. In the
> back of my mind though, coming from a C++ background I still feel that
> if c++ can make it on 2vl why not SQL?
>
> Why do nulls make us feel so strange?
>

As you know, 3 valued logic is simple: true, false, don't know. The problem
with null is that it is often used as a default value which is almost always
surely wrong. Null does not mean N/A, or missing. That's why you can't do
arithmetic with it. Null is a semantic construct that started out correctly
and ended up in most RDMS implementations. FWIW, I try to create subtypes
where there are no nulls.

Evan
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Evan Keel wrote:

> "Karen Hill" wrote in message
>
>
>>X-No-Archive:yes
>>
>>I know that visual basic, lisp, python, c , c++ , perl, all have 2
>>value logic. What makes SQL so special that it needs 3vl when all
>>these langauges make do with 2vl?
>>
>>I read a book on SQL which was authored by Date before I knew of the
>>whole Date controversy and when I was learning SQL. I was thoroughly
>>confused when he went on a spiel about nulls. I echoed this sentiment
>>at work and was chastized and told that Date is a kook. Yet I have
>>some time later accepted nulls and work with them just fine. In the
>>back of my mind though, coming from a C++ background I still feel that
>>if c++ can make it on 2vl why not SQL?
>>
>>Why do nulls make us feel so strange?
>
> As you know, 3 valued logic is simple: true, false, don't know.

Actually, I don't know that and neither does anyone else really. The
3-vl semantics in SQL are inconsistent. Sometimes null behaves like
unknown and other times it behaves like inapplicable.


The problem
> with null is that it is often used as a default value which is almost always
> surely wrong.

That's hardly the only or even the primary problem. Null--even when
handled with the utmost care--breaks fundamental identities.


Null does not mean N/A, or missing.

Except that sometimes it does.


That's why you can't do
> arithmetic with it.

What do you mean? x + null = null <-- that's arithmetic.

Except that sometimes x + null = x due to the inconsistencies in SQL.


Null is a semantic construct that started out correctly
> and ended up in most RDMS implementations.

Some of the best minds in database management would disagree that it
ever started out correctly.


FWIW, I try to create subtypes
> where there are no nulls.

That's nice. I simply don't allow null in any database I design.
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Karen Hill schrieb:
> X-No-Archive:yes
>
> I know that visual basic, lisp, python, c , c++ , perl, all have 2
> value logic. What makes SQL so special that it needs 3vl when all
> these langauges make do with 2vl?
>
> I read a book on SQL which was authored by Date before I knew of the
> whole Date controversy and when I was learning SQL. I was thoroughly
> confused when he went on a spiel about nulls. I echoed this sentiment
> at work and was chastized and told that Date is a kook. Yet I have
> some time later accepted nulls and work with them just fine. In the
> back of my mind though, coming from a C++ background I still feel that
> if c++ can make it on 2vl why not SQL?
>
> Why do nulls make us feel so strange?

Because they have different formal and informal interpretations.

As a matter of fact, in most cases you use 2-valued logic:

either an element exists in a database or it does not.

So the two values can be called "existence" and "non-existence". Indeed,
if your take any database then you either will find some concrete
element in it or you will not.

After that the question is how to denote these two cases and how to
interpret them formally from the point of view of numerous existing
logics. All these issues produce infinite and frequently fruitless
debates mostly of religious character. But the fact is that in most
practical cases given a database you either find a data item in it or
you cannot find it there. 3rd value, 4th value or an infinite number of
values (say, fuzzy logic) can be introduced but these approaches are not
widely used.

As to nulls, then the simplest and the most natural interpretation of it
is as "non-existence". More precisely, null is a marker for a thing that
is known to be non-existing. If you ask why to mark things as
non-existing, then the answer is that in many cases it is more efficient
to use this method instead of physical deletion.

--
http://conceptoriented.com
 >> Stay informed about: 3 value logic. Why is SQL so special? 

"Bob Badour" wrote in message

> Evan Keel wrote:
>
> > "Karen Hill" wrote in message
> >
> >
> >>X-No-Archive:yes
> >>
> >>I know that visual basic, lisp, python, c , c++ , perl, all have 2
> >>value logic. What makes SQL so special that it needs 3vl when all
> >>these langauges make do with 2vl?
> >>
> >>I read a book on SQL which was authored by Date before I knew of the
> >>whole Date controversy and when I was learning SQL. I was thoroughly
> >>confused when he went on a spiel about nulls. I echoed this sentiment
> >>at work and was chastized and told that Date is a kook. Yet I have
> >>some time later accepted nulls and work with them just fine. In the
> >>back of my mind though, coming from a C++ background I still feel that
> >>if c++ can make it on 2vl why not SQL?
> >>
> >>Why do nulls make us feel so strange?
> >
> > As you know, 3 valued logic is simple: true, false, don't know.
>
> Actually, I don't know that and neither does anyone else really. The
> 3-vl semantics in SQL are inconsistent. Sometimes null behaves like
> unknown and other times it behaves like inapplicable.
>
>
> The problem
> > with null is that it is often used as a default value which is almost
always
> > surely wrong.
>
> That's hardly the only or even the primary problem. Null--even when
> handled with the utmost care--breaks fundamental identities.
>
>
> Null does not mean N/A, or missing.
>
> Except that sometimes it does.
>
>
> That's why you can't do
> > arithmetic with it.
>
> What do you mean? x + null = null <-- that's arithmetic.
>
> Except that sometimes x + null = x due to the inconsistencies in SQL.
>
>
> Null is a semantic construct that started out correctly
> > and ended up in most RDMS implementations.
>
> Some of the best minds in database management would disagree that it
> ever started out correctly.
>
>
> FWIW, I try to create subtypes
> > where there are no nulls.
>
> That's nice. I simply don't allow null in any database I design.

I'm with you on this one.

Evan
 >> Stay informed about: 3 value logic. Why is SQL so special? 

> X-No-Archive:yes
>
> I know that visual basic, lisp, python, c , c++ , perl, all have 2
> value logic. What makes SQL so special that it needs 3vl when all
> these langauges make do with 2vl?
>
> I read a book on SQL which was authored by Date before I knew of the
> whole Date controversy and when I was learning SQL. I was thoroughly
> confused when he went on a spiel about nulls. I echoed this sentiment
> at work and was chastized and told that Date is a kook. Yet I have
> some time later accepted nulls and work with them just fine. In the
> back of my mind though, coming from a C++ background I still feel that
> if c++ can make it on 2vl why not SQL?

Well, clearly it could, and indeed should. It is extremely simple to
recognise Nulls as gibberish if one simply rewinds back to the
proposition being recorded. Consider that

"The employee with empid 101 has a firstname John, lastname Doe and
middlename NULL."

makes absolutely no sense in native english. Either one is trying to
say:

"The employee with empid 101 has a firstname John and lastname Doe"
AND
"The employee with empid 101 and has no middlename"

or trying to state that:

"The employee with empid 101 has a firstname John and lastname Doe"
AND
"The employee with empid 101 does have a middlename, but we don't know
what it is"

To this day, I don't see any relevance whatsoever for 3VL in recording
these two distinct propositions. (There is of course a third option,
where one doesn't know anything /at all/ concerning the person's
middlename, but then why /on earth/ would you be trying to record
something about it anyway?)

As ever its just bad design, generated by those who have no idea what
the tool they are using actually does.

Thank god these people aren't building bridges.
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Paul wrote:
> Bob Badour wrote:
>
>
> > > FWIW, I try to create subtypes
> > > where there are no nulls.
>
> > That's nice. I simply don't allow null in any database I design.
>
>
> Why not?

you're kidding right?

>
> (Personal work experience). Aircraft. Flight Schedules.
>
> Dep time,
> Arr time,
>
> &c.
>
>
> Scheduled_Departure_Time = 'DD:MM:YYYY 10:15'
> Actual_Departure_Time = <null> untill we know different, then we put
> in a value.
>
>
> Otherwise you have to take account of the fact that these aircraft
> took off in 9999 or else in 0001 or whatever.
>
>
> Null is easier. The aircraft hasn't taken off yet, we don't know when
> it will take off, or even if it will take off.

So an aircraft might not take off and yet it has a departure time
attribute? That makes absolutely no sense at all.

>
>
> Paul...
>
>
>
> --
>
> plinehan __at__ yahoo __dot__ __com__
>
> XP Pro, SP 2,
>
> Oracle, 9.2.0.1.0 (Enterprise Ed.)
> Interbase 6.0.1.0;
>
> When asking database related questions, please give other posters
> some clues, like operating system, version of db being used and DDL.
> The exact text and/or number of error messages is useful (!= "it didn't work!").
> Thanks.
>
> Furthermore, as a courtesy to those who spend
> time analysing and attempting to help, please
> do not top post.
 >> Stay informed about: 3 value logic. Why is SQL so special? 

"JOG" writes:
> So an aircraft might not take off and yet it has a departure time
> attribute? That makes absolutely no sense at all.

i think the characteristic was scheduled departure time. scheduled
departure time can be useful for a number of things.

commercial aircraft tend to have scheduled departure times (helps give
you an approx idea when to show up at the airport) ... as well as
scheduled arrival times.

at one time the FAA didn't interfer with departure times ... and let
aircraft circle if weather cut down on capacity of airfield landing
rates. at some point the FAA started holding plane departures if there
was predictions about reduced capacity to land the plane.

you frequently hear pilots making announcements about particular
arrival being ahead of time or late ... which is the difference
between the scheduled arrival time and the actual arrival time. both
the actual arrival time and the difference (early/late) between the
actual arrival time and the scheduled arrival time ... aren't actually
known until near to the time that the plane actually arrives.

in the routes database used for scheduling reservations involving
connecting flights ... there typically is an attempt to match
scheduled arrival time to connecting scheduled departure time (within
some fudge factor).

however as anybody who has had a late arrival and missed their
connecting flight ... the scheduled arrival time and the actual
arrival time may be different. also some carriers when they see that
their are a significant connecting passengers ... on a flight there is
starting to show a predicted late arrival time ... may attempt to
delay the departure of some number of flights as convinience to
connecting passengers. however, this can have cascading effects if the
connecting departing flight then impacts other passengers that have
connecting flights at the next arrival.

lots of flights that have significant, long term differences between
scheduled arrival and actual arrival may indicate various loading and
capacity issues and reason to update projected schedules. changes in
projected specific schedules then can have cascading effects with
regard to scheduling departure/arrivals for purposes of route and load
planning involving connecting flights.

various past posts mentioning 3-value logic
http://www.garlic.com/~lynn/2003g.html#40 How to cope with missing values - NULLS?
http://www.garlic.com/~lynn/2004f.html#2 Quote of the Week
http://www.garlic.com/~lynn/2004l.html#75 NULL
http://www.garlic.com/~lynn/2005.html#15 Amusing acronym
http://www.garlic.com/~lynn/2005b.html#17 [Lit.] Buffer overruns
http://www.garlic.com/~lynn/2005i.html#35 The Worth of Verisign's Brand
http://www.garlic.com/~lynn/2005m.html#19 Implementation of boolean types
http://www.garlic.com/~lynn/2005t.html#20 So what's null then if it's not nothing?
http://www.garlic.com/~lynn/2005t.html#23 So what's null then if it's not nothing?
http://www.garlic.com/~lynn/2005t.html#33 What ever happened to Tandem and NonStop OS ?
http://www.garlic.com/~lynn/2005u.html#12 3vl 2vl and NULL
http://www.garlic.com/~lynn/2006e.html#34 CJ Date on Missing Information

various past posts mentiong working with OAG database of all
(world-wide) commercial schedule flight segments as part of airline
res system (over 4k airports and over 400k commercial scheduled flight
segments)
http://www.garlic.com/~lynn/99.html#136a checks (was S/390 on PowerPC?)
http://www.garlic.com/~lynn/2000f.html#20 Competitors to SABRE?
http://www.garlic.com/~lynn/2001d.html#69 Block oriented I/O over IP
http://www.garlic.com/~lynn/2002g.html#2 Computers in Science Fiction
http://www.garlic.com/~lynn/2002j.html#83 Summary: Robots of Doom
http://www.garlic.com/~lynn/2003o.html#17 Rationale for Supercomputers
http://www.garlic.com/~lynn/2004b.html#6 Mainframe not a good architecture for interactive workloads
http://www.garlic.com/~lynn/2004o.html#23 Demo: Things in Hierarchies (w/o RM/SQL)
http://www.garlic.com/~lynn/2004q.html#85 The TransRelational Model: Performance Concerns
http://www.garlic.com/~lynn/2005o.html#24 is a computer like an airport?
http://www.garlic.com/~lynn/2005p.html#8 EBCDIC to 6-bit and back
http://www.garlic.com/~lynn/2006o.html#18 RAMAC 305(?)
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Bob Badour writes:
> How is null easier than not inserting anything into an actual
> departures relation?

re:
http://www.garlic.com/~lynn/2006q.html#22 3 value logic. Why is SQL so special?

you have a row entry for a specific flight that has fields for
scheduled and actual departures and arrival ... possibly with
provision for computed values (difference between scheduled and
actual).

or possibly you have two different tables ... one with scheduled
flights and another with actual flights. an entry isn't inserted in
the table of actual flights until the flight has actually happened.

the absence of a row in the actual flight table takes the place of
null value fields (for not yet known information) in the table of
specific flights. two tables then possibly involves referential
integrity if there is an attempt to match an actual flight against a
scheduled flight (in two different tables).

there is also, always the possibility that you might have an
unscheduled actual flight ... an unplanned flight for which there
hadn't been a (pre-)scheduled departure/arrival ... but for which
there is actual departure ... with initially a projected arrival (as
opposed to a scheduled arrival) ... and then eventually an actual
arrival.
 >> Stay informed about: 3 value logic. Why is SQL so special? 

"JOG" wrote in message

>
> Well, clearly it could, and indeed should. It is extremely simple to
> recognise Nulls as gibberish if one simply rewinds back to the
> proposition being recorded. Consider that

I think you have shown a few specific glitches without giving a proper
appreciation of the full horror of the situation. From my point of view, as
a practitioner slaving at the code face, THE overwhelming problem with being
allowed to fob the DBMS off with a null is that it allows the (so-called)
database designer to just sweep a whole lot of inconvenient details under
the carpet.

He "appears" to have produced a complete database design, and that
appearance is reinforced by the fact that he can write an SQL script that
will run without error to create a database. Unfortunately he's done next
to nothing about capturing any understanding of what data the application
will be expected to handle or what the application should (and should not)
do with it. Instead of being forced to discover that there are, for
example, six different types of customer, with different business rules, the
database designer declares a one-shape-fits-all table with a lot of nullable
attributes and leaves me to figure out what is really going on, and to write
the giant tangle of code to make it happen. Gee, thanks. What a hero.

I have come to suspect that *at least* 75% of the many tens of thousand of
lines of code I see in a year are there only because some slack-ass DB
designer didn't want to spec out a few more tables.

Roy
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Bob Badour wrote:


> > FWIW, I try to create subtypes
> > where there are no nulls.

> That's nice. I simply don't allow null in any database I design.


Why not?

(Personal work experience). Aircraft. Flight Schedules.

Dep time,
Arr time,

&c.


Scheduled_Departure_Time = 'DD:MM:YYYY 10:15'
Actual_Departure_Time = <null> untill we know different, then we put
in a value.


Otherwise you have to take account of the fact that these aircraft
took off in 9999 or else in 0001 or whatever.


Null is easier. The aircraft hasn't taken off yet, we don't know when
it will take off, or even if it will take off.


Paul...



--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Paul wrote:

>
> Bob Badour wrote:
>
>
>
>>> FWIW, I try to create subtypes
>>>where there are no nulls.
>
>
>
>>That's nice. I simply don't allow null in any database I design.
>
>
>
> Why not?
>
> (Personal work experience). Aircraft. Flight Schedules.
>
> Dep time,
> Arr time,
>
> &c.
>
>
> Scheduled_Departure_Time = 'DD:MM:YYYY 10:15'
> Actual_Departure_Time = <null> untill we know different, then we put
> in a value.
>
>
> Otherwise you have to take account of the fact that these aircraft
> took off in 9999 or else in 0001 or whatever.

Or whatever. One describes the actual departure time of a flight in a
relation describing actual departures and not in a relation describing
aircraft.


> Null is easier. The aircraft hasn't taken off yet, we don't know when
> it will take off, or even if it will take off.

How is null easier than not inserting anything into an actual departures
relation?
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Anne & Lynn Wheeler wrote:
> Bob Badour writes:
>
>>How is null easier than not inserting anything into an actual
>>departures relation?
>
>
> re:
> http://www.garlic.com/~lynn/2006q.html#22 3 value logic. Why is SQL so special?
>
> you have a row entry for a specific flight that has fields for
> scheduled and actual departures and arrival ... possibly with
> provision for computed values (difference between scheduled and
> actual).
>
> or possibly you have two different tables ... one with scheduled
> flights and another with actual flights. an entry isn't inserted in
> the table of actual flights until the flight has actually happened.
>
> the absence of a row in the actual flight table takes the place of
> null value fields (for not yet known information) in the table of
> specific flights. two tables then possibly involves referential
> integrity if there is an attempt to match an actual flight against a
> scheduled flight (in two different tables).
>
> there is also, always the possibility that you might have an
> unscheduled actual flight ... an unplanned flight for which there
> hadn't been a (pre-)scheduled departure/arrival ... but for which
> there is actual departure ... with initially a projected arrival (as
> opposed to a scheduled arrival) ... and then eventually an actual
> arrival.

And your point would be? What is the theoretical foundation or even the
ad hoc design criterion to choose among the options you present?
 >> Stay informed about: 3 value logic. Why is SQL so special? 

Anne & Lynn Wheeler wrote:

> "JOG" writes:
>> Paul wrote:
>>
>>> Scheduled_Departure_Time = 'DD:MM:YYYY 10:15'
>>> Actual_Departure_Time = <null> untill we know different, then we put
>>> in a value.

>>So an aircraft might not take off and yet it has a departure time
>>attribute? That makes absolutely no sense at all.
>
> i think the characteristic was scheduled departure time. scheduled
> departure time can be useful for a number of things.

The characteristic was quite explicitly the actual departure time. If
you were less interested in self-promotion, you might actually pause
long enough to read with comprehension.
 >> Stay informed about: 3 value logic. Why is SQL so special?