Invitation to a quarrel

#31

On 19/08/2011 14:01, Paul White wrote:

Quote:

On Net writes
Nullable columns take me back to the dawn of my computing career, where
the use of rogue/special values took the place of nulls and that is a
very bad thing. How many ingres developers have text columns populated
with spaces that logically should be nulls?

Yes, it reminds me of a sales application which stores detail, subtotals and
grand totals for region, state, month and year all in the same table using
marker values for the totals. The unique primary keys are like this.
2011 01 VIC V1
2011 02 VIC V1
2011 01 VIC V2
2011 02 VIC V2
2011 01 NSW N1
.
2011 0 VIC V1
2011 0 VIC V2
.
2011 0 NSW **
.
2011 0 *** **

The design is intended to provide fast response to common selection criteria
in a sales enquiry screen. Eg Total sales for VIC by month, or Sales by
state for 2011. Overnight batch processing recalculates the summary entries
as part of sales update. I found the design appealing because of the
simplicity of the schema and the ease of construction of the SQL statements
with optional grouping/totalling. In hindsight I see NULL could be used
instead of marker values for the subtotals, so it would seem legitimate
that the primary key would be unique with nullable key parts.

Paul White

I have done similar things, though really it was more about forming
reports and using rows and columns as a kind of stand-in spreadsheet -
certainly not what would be regarded as "proper" usage.

#32

Standard SQL has two equivalence relationships. There is the usual =
(equal) in which (NULL <theta op> [<expr>| NULL]) is UNKNOWN. Then the
is grouping in which NULLs form their own equivalence class. This is
why GROUP BY puts the NULLs in one group, why the new IS [NOT]
DISTINCT FROM works, etc. And it is why UNIQUE allows one NULL in
each of the columns in it. Keys are based on grouping, not equality.

CREATE TABLE UniqueTest
(a INTEGER,
b INTEGER,
UNIQUE(a, b));

INSERT INTO UniqueTest VALUES (1, 2); -- good
INSERT INTO UniqueTest VALUES (1, 2); error !! dup row
INSERT INTO UniqueTest VALUES (NULL, NULL); -- good
INSERT INTO UniqueTest VALUES (1, NULL); -- still good
INSERT INTO UniqueTest VALUES (1, NULL); -- error !! dup row
INSERT INTO UniqueTest VALUES (NULL, NULL); -- good
INSERT INTO UniqueTest VALUES (NULL, NULL); -- error! dup row
INSERT INTO UniqueTest VALUES (NULL, 3); -- good
INSERT INTO UniqueTest VALUES (NULL, 3); --error! dup row

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Invitation to a quarrel

comp.databases.ingres comp.databases.ingres

Re: [Info-Ingres] Invitation to a quarrel - 08-19-2011 , 08:38 AM

Re: Invitation to a quarrel - 08-20-2011 , 08:58 AM