Re: Examples of SQL anomalies?

Marshall wrote:

> On Jul 2, 6:26 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>

>>If one expects a bill and the total is 0, that's just as useful to alert
>>one that the bill has not yet arrived as a NULL would be.

Yes, that's a separate question--probably the first question one might ask when one sees the total of $0 for the invoices this month. Perhaps I was not sufficiently clear. When I wrote "alert", I meant it in the sense of something that tells one one has to investigate.

> On the other hand, we have the question of How much do we owe?
> These are two different questions, even if there is some
> relationship between the two.
>
> If we try to put multiple things into a single value, it's generally
> a bad idea without a discipline of some kind. The best approach is
> either 1) don't do that or 2) use a union type. There is also
> 3) partition the range of values.
>
> Splitting the question up into different question (1) is the best
> approach here.

Absolutely, I agree. That's the basis for my observation that some folks expect computers to magically evaluate external predicates. I didn't mean to suggest one should overload the meaning of a zero total.

When one asks for a total sum of an empty set of numbers, the computer has no business trying to second guess the semantics of the question. The sum is 0. If one means to ask: "Are there any numbers to sum?", one can ask that instead.

One can even ask: "How many invoices are there and what is their sum?" if that is what one wants to know.

  If SQL had union types, we could do that.
> NULL is a poor-man's union type, with difficult-to-predict
> limited semantics.
>
> An example of 3) would be a function that returns a natural
> number on success or a negative code on failure.
>
>
> Marshall
Received on Wed Jul 02 2008 - 20:14:37 CEST