DIV0 and DIV0NULL: Change to results exceeding the output scale¶

Attention

This behavior change is in the 2023_08 bundle.

For the current status of the bundle, refer to Bundle History.

For the DIV0 and DIV0NULL functions, when the result of the division operation exceeds the output scale:

Before the change:

DIV0 and DIV0NULL truncate the output.

This is inconsistent with the results from using the division operator (/).

After the change:

DIV0 and DIV0NULL round the output half away from zero.

This is consistent with the results from using the division operator (/).

For example, suppose that you are dividing 5 by 9:

SELECT DIV0(5, 9), DIV0NULL(5, 9), 5/9;

Before the change:

DIV0 and DIV0NULL truncate the result to 0.555555, while the division operator rounds up the result to 0.555556.

+------------+----------------+----------+
| DIV0(5, 9) | DIV0NULL(5, 9) |      5/9 |
|------------+----------------+----------|
|   0.555555 |       0.555555 | 0.555556 |
+------------+----------------+----------+

After the change:

DIV0, DIV0NULL, and the division operator round up the result to 0.555556.

+------------+----------------+----------+
| DIV0(5, 9) | DIV0NULL(5, 9) |      5/9 |
|------------+----------------+----------|
|   0.555556 |       0.555556 | 0.555556 |
+------------+----------------+----------+

In addition, passing in more than two arguments to the DIV0 and DIV0NULL functions results in an exception.

Before the change:

Although the DIV0 and DIV0NULL functions only support two arguments, you can pass in additional arguments.

The functions ignore the additional arguments.

After the change:

Passing more than two arguments to the DIV0 and DIV0NULL functions results in the following exception:

000939 (22023): SQL compilation error: ...
  too many arguments for function [DIV0] expected 2, got 3

Ref: 1400