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.

1 Like
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.