Why is NULL divided by 1, 0 (and not: NULL)

[Q&A] Developer Tools
1

I found out that in Novulo, when you divide NULL by a number, the result is 0.

I would have expected it to be NULL, as any operation that contains a NULL should result in NULL.

E.g. float:getnull().div(1)
Also for money. And also for .add and then I expect it also for div and sub. And in C# and SQL.

I see the expression optimizer in SQL explicitly does this.

Is there a specific reason for it and can it be avoided?

It now makes that we are creating ugly expressions like

money:if(
field.isnotnull(),
field.div(quantity),
money:getnull()
)

Is there any better way to handle it?

2

I’m not sure about the history, but the column totals and averages would be NULL a lot. Especially with a debit and a credit column.

Also changing this would create a lot of fallout I think. I hope Erik-Jan or Wim can comment on the history of this.

3

From what I can see, this has been the behavior for a looooong time; in the logs I see it has been like this in C# from the very beginning (2008), when we changed from PHP to C#.

Interestingly it seems that in PHP the behavior was what you propose; I can’t remember what the reason was to change it (and can’t find anything about it in the logs), but it seems we have done so consistently (for all arithmetic operations). Maybe Wim remembers?

I agree that I would expect null/1 to be null as well, but for other operators (null+1, null-1, null*1) I am less sure. And what about things like sum? And avg?

I agree with Helena that changing this could have unwanted consequences in many places, so it feels like something that we cannot change overnight even if we wanted to. Maybe for “div” alone it wouldn’t be so bad, but for the others it probably would.

4

I came to the same conclusion as Erik-Jan. It’s been that way forever, and consistently implemented that way for all/most the arithmetic operators.

I’m not sure that it ‘should’ be one way or the other. Either choice has its pros and cons. It seems like that in Javascript and in PHP null/1 = 0. In C# (sort of - when you use Nullable for example) and in SQL null/1 = null.

It comes down to the definition for null that you choose. Is it ‘the absence of a value’, is it ‘an invalid value’, is it ‘stating that there is a value but you don’t know which one (yet)’? Javascript distincts 0, null, undefined and NaN. In the C programming language, if I remember correctly, NULL is actually defined as a constant with the value 0.

I don’t remember why exactly we chose the JS/PHP way over the C#/SQL way in 2008 and couldn’t find any notes on this topic with a quick search. Changing it could break a lot. It seems quite doable however to create a NullableDiv function in a plug-in.

5

Hi Wim, thanks for the explanation and context. A nullable Div (and then I guess also a nullable Mult, Add and Sub) sounds very good. I’ll create an SR for it. I keep the post open, so as soon as we have a new plug-in (revision) to post it here.