At 11:13 AM 18/12/2004 -0500, you wrote:

>Im amazed that you are filtering on subject and not recipient though. :)

I'm filtering on LIST, as is everyone else I know who monitors many
lists. If people behave themselves with replies, the mail or news client
will take care of the threading within the lists - provided people don't
forward/cross-post with the filter-grabbers belonging to a different list.

>Actually Im a bit confused by this. Wasn't it Bill Karwin that was always
>saying "null is a state, not a value"?

Sure. Tattoo it on your forehead.

>In Oracle, SQL server etc it treats
>it this way too. DBISAM was the only DB that treated null as a value and
>caused significant problems regardig this (I think they've finally addressed
>this regarding unique indexes in the latest versions). So if null is not a
>value - why should FB be seeing it as a violation of a unique in any case?

Because, up until Firebird 1.5, you could not have nulls in any unique key
or index - exactly for the reason that a null is not a value, so there is
no way to know whether a null is unique of not. (Null means
"unknown"). It was relaxed in 1.5 in response to a demand to enable
multi-segment keys to contain null segments. It's also useful for your
usage, where you are using it as an existential validation mechanism - "I
don't care if the value is unknown, but if it's known, then it has to be
unique".

Pre-1.5 you would not have been able to create the index, because the
column is nullable. You would have had to drop the column and re-add it
with a NOT NULL constraint, having previously "parked" the data in a temp
col and having run an update to set all nulls to something unique in the
set. (Pretty hopeless, but it enforced a stricter range of existential
rules than you needed).

What you were bumping into in v.1.5.1 was some timing problem with null
resolution at validation time, that affected the column immediately after
the index was created and went away once the index was deactivated and then
rebuilt.

>:: happened under all conditions, it could be regarded as a
>:: rule, but it isn't consistent. At least it is reproducibly
>:: inconsistent :-) and you've found a workaround for it for now.
>
>That being don't use the index? :)

No; if you are going to keep using v.1.5.1, then you will solve the
problem as you (indirectly) did - by doing ALTER INDEX blah INACTIVE
followed by ALTER INDEX blah ACTIVE after creating and committing the
CREATE INDEX statement. (I know you "worked around it" by dropping the
index, but you didn't have to...)

./hb