[FRG-204] cast from nullable DOUBLE to INT truncates instead of rounding

[dynamobi-build]

[reporter="jvs", created="Tue, 12 Sep 2006 13:18:01 -0500 (GMT-05:00)"] 0: jdbc:farrago:> !run test.sql
1/13 create schema anil;
No rows affected (0.076 seconds)
2/13
3/13 create table anil.t3(
i int not null primary key,
d1 double,
d2 double not null,
n1 numeric(10,2),
n2 numeric(10,2) not null
);
No rows affected (0.556 seconds)
4/13
5/13 insert into anil.t3 values (1, 9.6, 9.6, 9.6, 9.6);
1 row affected (0.722 seconds)
6/13
7/13 alter system set "calcVirtualMachine"='CALCVM_JAVA';
No rows affected (0.018 seconds)
8/13
9/13 select cast(d1 as int), cast(d2 as int), cast(n1 as int), cast(n2 as int)
from anil.t3;
+---------+---------+---------+---------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 |
+---------+---------+---------+---------+
| 9 | 10 | 10 | 10 |
+---------+---------+---------+---------+
1 row selected (0.322 seconds)
10/13
11/13alter system set "calcVirtualMachine"='CALCVM_FENNEL';
No rows affected (0.036 seconds)
12/13
13/13select cast(d1 as int), cast(d2 as int), cast(n1 as int), cast(n2 as int)
from anil.t3;
+---------+---------+---------+---------+
| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 |
+---------+---------+---------+---------+
| 10 | 10 | 10 | 10 |
+---------+---------+---------+---------+
1 row selected (0.199 seconds)

[dynamobi-build]

[author="jvs", created="Tue, 12 Sep 2006 13:20:59 -0500 (GMT-05:00)"] Most likely REAL will have the same problem.

[dynamobi-build]

[author="elin", created="Tue, 19 Sep 2006 12:21:06 -0500 (GMT-05:00)"] FRG-106 closed as duplicate of this issue.

[dynamobi-build]

[author="elin", created="Thu, 21 Sep 2006 17:22:44 -0500 (GMT-05:00)"] Perhaps related to FRG-54, FRG-171?
tests: calc.explicitConversion, calc.bugs, numerics.decimal

[dynamobi-build]

[author="jpham", created="Sun, 24 Sep 2006 14:49:36 -0500 (GMT-05:00)"] should be fixed by 7760

[dynamobi-build]

[author="schoi", created="Sun, 24 Sep 2006 23:01:46 -0500 (GMT-05:00)"] from FRG-106 which was closed as a dup of this:

values (101 * 2 / 7)

still returns 28. Should round to 29.

[dynamobi-build]

[author="jpham", created="Mon, 25 Sep 2006 08:42:39 -0500 (GMT-05:00)"] Currently, Farrago's integer division returns an integer, with the result truncated. It is much like the basic division operator in C or Java. It would be an enhancement to change the current behavior.

The SQL 2003 Spec is pretty loose about the requirements for division. In Part 2 Chapter 6.26 Syntax Rule 1a, it allows the result to be an exact numeric with implementation defined precision and scale. In the same chapter, General Rule 5a makes the choice of whether to round or truncate implementation-defined.

To get "29" one could do something like:
values cast( cast(101*2 as numeric(3,0)) / 7 as int);

[dynamobi-build]

[author="jpham", created="Thu, 26 Oct 2006 23:58:43 -0500 (GMT-05:00)"] Resolving for now since the issue Sunny describes is expected behavior.
Ex: 9/10 = 0, for better or worse