First, a request that you avoid cross-posting to multiple lists. And, when
you think you must do so, delete the [list tag] from the Subject before you
do so. A lot of us are monitoring multiple lists involving scores of
threads daily. It's not acceptable list etiquette for people to screw up
our list filters. - ^hb


At 08:54 PM 17/12/2004 -0500, you wrote:

>:: This was it:
>:: CREATE UNIQUE INDEX "IDX_Cart_1" ON "Cart"("InvoiceNo");
>::
>:: When trying to insert new rows and InvoiceNo was null, it
>:: did this. Null is not a value - so why would this be a problem?
>
>More info. This index was applied with many rows already having null. Ok -
>but as soon as I applied this index, I could not insert rows any more with
>null in that field. As soon as I deactivated or deleted the index - I can
>insert just fine again.
>
>Happens on both production and test DBs.

Take Alan McDonald's advice on board....it's always the right thing to do
to inform yourself in advance of the collateral effects of changing
metadata and to make existing data consistent with the change you intend to
make before you make the change.

However, I've reproduced the behaviour you describe and I think it is
sufficiently inconsistent to be regarded as a bug. We "old hands" are not
used to being able to put unique indexes or constraints on nullable columns
and would not have found this in the normal run of things.

I have a testcase which I'll give to Claudio, as I think it was he who was
responsible for the change in DSQL that causes an explicit or implicit null
to be presented to the parser as a literal blank under some conditions. If
it 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.

However, I'll need to download v.1.5.2 RC 5 first and just do a reality
check that the inconsistency is still there...so be patient...I'm on a slow
dialup link here in the Wopwops, where "broadband" is still the red satiny
thing that male Flamenco dancers wear around their waists.

./heLen