[FRG-106] expression with division and multiplication for Decimal(10,0) differs for java and fennel calc

[dynamobi-build]

[reporter="elin", created="Tue, 11 Apr 2006 14:35:04 -0500 (GMT-05:00)"] An expression with decimal(10,0) will result in truncation if the java calc is used and rounding if fennel calc is used.

Division with 2 integers will result in truncation by both calcs.

Straight division of a decimal(10,0) by an integer doesn't round or truncate.

To reproduce on luciddb:

create table tdec (col1 decimal(10,0), col2 decimal(10,0), col3 decimal(10,0));
insert into tdec values
(101, 2, 7), (127, 2, 7);

alter system set "calcVirtualMachine"='CALCVM_FENNEL';
select col1 * col2 / col3 from tdec;
values (101 * 2 / 7);
select col1 / col3 from tdec;

alter system set "calcVirtualMachine"='CALCVM_JAVA';
select col1 * col2 / col3 from tdec;
values (101 * 2 / 7);
select col1 / col3 from tdec;

[dynamobi-build]

[author="jvs", created="Tue, 11 Apr 2006 15:47:50 -0500 (GMT-05:00)"] In other cases, we settled on rounding, so we should make that consistent everywhere.

[dynamobi-build]

[author="jhyde", created="Tue, 11 Apr 2006 16:14:11 -0500 (GMT-05:00)"] JVS, did that decision get into any of the end-user doc?

By the way, a good place for such tests is in SqlOperatorTests.java. This testcase is automatically run against both VMs. To testDivideOperator(), add

        getTester().checkScalarExact(
                " cast(101 as decimal(10,0)) * cast(2 as decimal(10,0))/ cast(7 as decimal(10,0))",
                "DECIMAL(19, 0)",
                "29");

[dynamobi-build]

[author="jvs", created="Tue, 11 Apr 2006 16:33:16 -0500 (GMT-05:00)"] We've decided rounding should be the correct behavior in all cases. John P, I'm assigning to you since DECIMAL is involved; Xiaoyang took care of one of these previously (I think the CAST case) so send him mail if you have questions.

[dynamobi-build]

[author="jvs", created="Tue, 11 Apr 2006 16:35:48 -0500 (GMT-05:00)"] "End-user doc" added to http://wiki.eigenbase.org/PragmaticSql2003.

[dynamobi-build]

[author="jpham", created="Mon, 17 Apr 2006 17:34:05 -0500 (GMT-05:00)"] I think this is related to OJRex translation.
My guess is we're not getting good test coverage
of nullable column testing due to the primary key
requirement of Farrago.

I made a simpler Farrago test case:

create table tdouble (i int primary key, d double);
insert into tdouble values (1, 28.857);
0: jdbc:farrago:> select cast(d as bigint) from tdouble;
+---------+
| EXPR$0 |
+---------+
| 28 |
+---------+

[dynamobi-build]

[author="elin", created="Tue, 19 Sep 2006 12:20:36 -0500 (GMT-05:00)"] Duplicate of FRG-204