I just thought I'd have a play with the above

With it set to false

select * from companies where _website = null
or
select * from companies where _website is null

return 6120 records

whilst

select * from companies where _website = ''

return 0 records.

This isn't how I interpret the manual. I'd expect the same number of records in both cases.

Am I misinterpreting the manual?

Roy Lambert

Roy,

<< This isn't how I interpret the manual. I'd expect the same number of records in both cases.

Am I misinterpreting the manual? >>

Yes, although the manual could be worded better.  The whole point of StandardNullBehavior is to avoid having to use constructs like IS/IS NOT NULL for comparisons so as to avoid an exception.  It only affects how NULLs are *produced*, not the nature of NULLs in general.  In other words, comparing a string with a NULL value results in a False result instead of a NULL result.

The general rule of thumb is this: if you think that setting this property to False is going to save you from having to worry about NULLs, then don't set it to False, because it won't.  If EDB worked like you want, then changing this property would corrupt existing indexes.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Yes, although the manual could be worded better. The whole point of StandardNullBehavior is to avoid having to use constructs like IS/IS NOT NULL for comparisons so as to avoid an exception. It only affects how NULLs are *produced*, not the nature of NULLs in general. In other words, comparing a string with a NULL value results in a False result instead of a NULL result.
>
>The general rule of thumb is this: if you think that setting this property to False is going to save you from having to worry about NULLs, then don't set it to False, because it won't. If EDB worked like you want, then changing this property would corrupt existing indexes.

That is what my memory was saying from ages ago but reading the manual made me wonder

Roy Lambert