Hi Team et al,
   With the release of v 2.30 b2 my issue with filtering boolen = False is resolved.  Thank you Tim.
   
   In the light of the apparent 'cause' behind this I would like to ask a question..
   
   I had left the Enable Standard NULL Bhaviour not set as released/issued, it didn't appear to do anythingdetrimental to
my programming/apps.
   
   Having now set the Bhaviour to True and still not seeing any impact, I am wondering what exactly is the benefit of
having it False?
   
   Yes I read Description however I'm not real sure why I would want the non-standard behaviour.
   
   What benefit do you see in the non-standard mode please?

Regards & TIA,
Ian
   

Ian Branch

I may not have this right, NULL behaviour is one of those SQL-grail things, if others think I am wrong they can chime in ...

3 records with values 1, Null, 5.

With Standard Null Behavour:

SUM(3 Records) = NULL.

With "Non Standard"

SUM(3 Records) = 6, ie the "NULL" is parlayed into a 0.

Adam

Adam


OK, you've tempted me. I was going to keep out of it (mainly cos I start ranting and foaming at the mouth when I start on about null)

From the SQL manual

<<This is also the case with aggregate functions like MIN, MAX, SUM, COUNT, etc. that operate on an
individual column. Any row values having a NULL on the column being operated on will be ignored for
the purposes of the operation.>>

From the ElevateDB manual

<<By default, Elevate exhibits strict ANSI-standard NULL behaviors, as documented in the NULLs topic in
the SQL manual. By setting the TEDBEngine StandardNullBehavior property to False you can enable a
relaxed NULL behavior for SQL comparisons and other binary operations. It is important to realize that
this property does not affect the storage of column values, and only affects how such comparisons and
binary operations are made with respect to NULL values.>>

Or to sum up - it only affects the bit of the WHERE clause where you test nulls.

Standard behaviour - fld = NULL - error - need to use fld IS NULL

Non-standard behaviour

fld = null accepted
fld = '' (ie empty string) also accepted and gives same result

The aggregate functions always ignored NULL values in rows - the beancounters would have done nasty things with blunt knives to the standard setters if that hadn't been the case!

However, consider the following

select
sum(_income),
avg(_income),
count(*),
avg(_income) * count(*),
(select count(*) from transactions where _income is not null),
avg(_income) * (select count(*) from transactions where _income is not null)
from transactions

IE it IGNORES the rows with a null value, it doesn't count them as zero.

Roy Lambert

Roy Lambert wrote:

> OK, you've tempted me. I was going to keep out of it (mainly cos I start ranting and foaming at the mouth when I start on about null)

Well, we all know that the grown-up database uses NULLs properly, so this option is only used by kids learning anyway yes? 8-)

I must say, NULL being different is certainly the way I like to manage my data, but I can see cases where it helps. I'd never start a new project with the non-standard behaviour though.

--

Matthew Jones

Matthew


>Well, we all know that the grown-up database uses NULLs properly, so this option is only used by kids learning anyway yes? 8-)

I do think you have that slightly wrong - they all assume that NULL <> '' which by any standard of sanity is just plain wrong.

Roy

On 1/16/2019 5:54 AM, Matthew Jones wrote:
> I must say, NULL being different is certainly the way I like to manage my data, but I can see cases where it helps. I'd never start a new project with the non-standard behaviour though.

Really depends on your design.

While there can be some (small i say) value in using NULLs we have very
rarely seen actual practical value and avoid using it as much as
possible - setting defaults to non-null at schema level etc to make this
as automatic etc.

Raul

Raul


I agree with you about the "usefulness" of NULL but I decided to go the other way and created by table component to replace '' with NULL. I went that way because it was then consistent with tablefield.Clear in Delphi.

Roy Lambert

Roy,

1. The logical value of (NULL <> '') is not TRUE , it is NULL.
That means that they do NOT assume that NULL <> '', they assume that it is UNKNOWN if NULL is or is not ''

2. The definition "standard of sanity" is also NULL


--
Fernando Dias
[Team Elevate]

Raul,

<<While there can be some (small i say) value in using NULLs we have very rarely seen actual practical value and avoid using it as much as possible>>

Without NULLs referential integrity is NULL I would say that the value of using NULLs is huge.
That is because, for example, not knowing my name is not the same as saying that I have no name.

--
Fernando Dias
[Team Elevate]

On 1/16/2019 10:10 AM, Fernando Dias wrote:
> That is because, for example, not knowing my name is not the same as
> saying that I have no name.

Sure but name='' is my world means same thing.

Raul