Re: Examples of SQL anomalies?

From: -CELKO- <jcelko212_at_earthlink.net>
Date: Wed, 2 Jul 2008 13:39:13 -0700 (PDT)
Message-ID: <e71f7cec-b23d-4b32-85a6-d4f59a4fb505@56g2000hsm.googlegroups.com>


>> Splitting the question up into different question (1) is the best approach here. If SQL had union types, we could do that. <<

Okay,I have a union type column which alternately holds integer, float, strong and temporal data in each row. Please define the theta joins on such a column. Please define SUM() on such a column. Please define math, string and temporal operators on the column. If you think NULLs are bad, what does the DB do for all possible union types?

Old joke about weak data types:
Teacher: "Billy, what is 6 times 9?"
Billy: "ahhh, red?"
Teacher: "NO! , Sally, what is 6 times 9?" Sally: "Thursday?"
Teacher: "NO! Tommy, what is 6 times 9?" Tommy: "54."
Teacher: "Right! Now tell the class how you ngot the answer." Tommy: "I divided red by Thursday!"

RDBMS and SQL are *very* strongly typed as part of First Normal Form. A scalar value has to have one and only one data type. That is why Codd put NULLs into his type model. In SQL Server, this problem showed up when their proprietary BIT data type first do not allow NULLs, then suddenly did allow them when it was put into the exact numeric family instead of a more traditional hardware level implementation.

>> NULL is a poor-man's union type, with difficult-to-predict limited semantics. <<

No, it does not change types on the fly (NULL is part of each SQL data type), the semantics are limited, and the limitations make them very easy to predict. Received on Wed Jul 02 2008 - 15:39:13 CDT

Original text of this message