Re: The Null Problem is a Non-issue

Norbert

> > On Tuesday, 9 September 2014 04:38:13 UTC+10, Norbert_Paul wrote:

> > I'd appreciate to read your definition of the "Null Problem".

> > Since I possess the miracle of sight, I try not to argue with the blind. I don't suffer from The Null Problem, I have the Resolution.

> So just say a word and make me see, too. > What ist the public definition of "The Null Problem" for the non-blind?

I just read your paper on Complex-Based BIM. Based on last para, p6, and the notes on "helper class" ...

It appears that you understand the issues (some, if not all) involved with "The Null Problem", you already see. You do not need a private definition from the likes of me.

I haven't seen your data model, therefore I am not certain if your understanding is complete.

<<< Recap Previous Summary >>>
1 inconsistency in treatment of Nulls that may occur in the result set (relations, projections, as opposed to Nulls in physical rows, which are a separate issue)
-- which means aggregates may or may not include relations with NULL in the relevant column

2 the "finiteness" is absent:
-- NULL does not equal NULL

• any value (based on the domain of the column) sometimes equals NULL, other times not
• testing for NULL or the empty set is inconsistent

The commercial SQLs have cleaned that up, and supplied a predictable language, that does not require the programmer to sit and ponder The Null Problem, or 3VL, at every SELECT.
-- aggregates exclude relations with NULL in the relevant column

• NULL equals NULL
• any value (based on the domain of the column), other than NULL, does not equal NULL
• testing for NULL or the empty set is simple and consistent

3 Storing NULL in the data store (which is a stupid thing to do), is a different problem. I did not intend it to be part of the problem I was attacking in this thread, but I will if I have to. Storing NULLS is in fact *implementing* the Null Problem, where there was not a problem. Point being, there is no value is proving that stored NULLs have the cancer that was injected into the patient, who did not have the cancer before the injection of stored NULLs.

No Commercial SQL, or Act of God, can help you if you inject cancer into patients who do not have it. It is beyond the scope of a platform. <<< End Recap >>>

a.1. Evidently, you have experienced [1] and [2] and are well aware of the issues. Your "helper class" solves [2]. Depending on your SQL platform, it will or will not handle [1] Nulls consistently.

a.2. You have closed the finiteness issue, and made Null finite.

a.3. Sybase & DB2 have a session-level configuration setting for ANSI_NULL, meaning, treat Nulls the way ANSI SQL demands it (legalised insanity, inconsistent treatment of Nulls). The default setting is OFF, meaning, treat Nulls using human logic, sanely, consistently. They do what your "helper class" does, for *all* operators. (Teradata has a similar mechanism, but I have forgotten the details.)

a.4. Therefore the commercial RDBMS treat Nulls consistently. Null is finite.

a.5. Therefore the commercial RDBMS do not need functions ala your "helper class", they are all built-in, and they work for all operations.

b. It appears that you [3] store Nulls in the database. That will cause problems. To that extent, you *do* suffer The Null Problem. To that extent, you have /believed/ the definition of The Null Problem as /marketed/ by the pseudo-academics, which exists only only the mental plane, and you have implemented it, where it did not exist, in the physical plane, due to their fraudulent representations The software for your SQL platform does not have Nulls or deadlocks in it: if you want them, you have to write it yourself.

If you avoid Null from being stored, it will eliminate that entire set of problems.

The method for eliminating Nulls from being stored in the db is:

i. Normalise each optional column (only optional columns contain Null, if your Normalisation thus far is correct), so that it is located in a separate table (to the mandatory columns). They can be grouped, it doesn't have to be one table per optional column.

(
Null is a non-fact, or an absence of a fact. I for one, cannot understand why people store non-facts in a database *of facts*. In 35 years of database implementations, I have never stored a Null, and I have never infected my customers with one. )

If you need more detail, although it is a horrible article, badly presented, chock-full of misrepresentations, since it is already referenced and linked, use the TweedleDumb article.

(
As always, the great harlot of Babylon takes every opportunity to push his schizophrenic views re "the Relational Model is incomplete" and "SQL is broken", which (the specific points made) are *all* patently false: you will have to read past them. The implementation in SQL, once you have Normalised the data (which evidently is impossible for harlots to do), is dead simple.

It is especially poor because it is supposed to be an "university lecture" in a "technical subject", but it is in fact full of confusion for the students. The University of Warwick CS253 "course" is of course totally fraudulent, it should be named "How to Produce an Un-useable Record Filing System Instead of a Relational Database, and Provide Really, Really Good Excuses for Doing So." )

Now the danger is, especially for you, since you believed The Null Problem to be real (ie. material, physical), due to articles like that one, you might well believe the false statements made about (a) The Null Problem, (b) SQL, and (c) the RM. Therefore, if you want a more direct, less problematic description, please ask a specific question, and I will answer it. Please ping me via email if I do not answer within one day.

ii. When you join the mandatory + optional columns (obviously, outer join), the projection will give you NULL in the optional columns for which there is no value. Use ISNULL() or COALESCE() or NULLIF(), and substitute the NULL with a value that meaningful, useable. "More zero than zero" is just fine with me, although for me it is a bit simpler: Null means nothing, nothing means zero.

(
I never allow the user to /see/ "NULL" in a column, even if it is a report (which is always a single SELECT command, and usually a View). They never have to figure out "Ooooh, what does 'NULL' mean ?", they never get the opportunity to interpret a Null.

For users who write their own reports, and for developers, the db documentation covers issues concerning a possible Null (eg. empty sets), which again, only happens in projections, never due to a stored Null, without ever mentioning The Null Problem. The database is physical, and my documentation contains only facts about physical reality, it does not contain any non-facts or any contemplations from the Dreamtime or glorifications of abstract universes. )

Cheers
Derek Received on Mon Jan 12 2015 - 08:36:26 CET