Closed dynamobi-build closed 12 years ago
[author="elin", created="Fri, 22 Sep 2006 13:49:13 -0500 (GMT-05:00)"]
within the same test (convnumeric.createsource), the values +/-2147483647 inserted into the decimal and numeric columns, show up as +/-1.0000
to repro:
----------------
create table tt (
coldec decimal(14,4),
colnum numeric(14,4)
);
insert into tt values (-2147483647,-2147483647);
insert into tt values (2147483647,2147483647);
0: jdbc:luciddb:> select * from tt;
+----------+----------+
| COLDEC | COLNUM |
+----------+----------+
| 1.0000 | 1.0000 |
| -1.0000 | -1.0000 |
+----------+----------+
plan:
0: jdbc:luciddb:> explain plan for insert into tt values (-2147483647,-2147483647);
'column0'
'FennelToIteratorConverter'
' LcsTableAppendRel(table=[[LOCALDB, BOGUS, TT]])'
' IteratorToFennelConverter'
' IterCalcRel(expr#0..1=[{inputs}], expr#2=[10000], expr#3=[($t0, $t2)], expr#4=[Reinterpret($t3)], expr#5=[_($t1, $t2)], expr#6=[Reinterpret($t5)], COLDEC=[$t4], COLNUM=[$t6])'
'FennelToIteratorConverter'
' FennelRenameRel(fieldNames=[[COLDEC, COLNUM]])'
' IteratorToFennelConverter'
' IterCalcRel(expr#0=[{inputs}], expr#1=[-2147483647], EXPR$0=[$t1], EXPR$1=[$t1])'
'IterOneRowRel'
9 rows selected (0.024 seconds)
0: jdbc:luciddb:>
-----------------
other values:
value inserted / value shown
-2147483648 / 0.0000
2147483648 / 2147483648
2147483646 / -2.0000
-2147483646 / 2.0000
-2147483645 / 3.0000
2147483645 / -3.0000
[author="elin", created="Fri, 22 Sep 2006 13:51:03 -0500 (GMT-05:00)"] one more thing, inserting 2147483648-1 will get 2147483647.0000. So for now, I'm changing cr_source.sql to do that as a workaround.
[author="elin", created="Fri, 22 Sep 2006 14:13:21 -0500 (GMT-05:00)"] okay, workaround works for sqlline, doesn't work for test. I'm going to just flag this whole test suite as knownfailure for now. (conv_types/conv_numerics)
[author="jpham", created="Sun, 24 Sep 2006 15:45:23 -0500 (GMT-05:00)"]
As of 7760, the out of range error seems to come back for most inserts. The other problem, with the decimals is a bit trickier. It is tied into how we (don't) handle overflow for any arithmetic (which decimals happen to rely on.) This will also show an odd result with either Fennel or Java calc:
values 10000*(-2147483647);
[author="jpham", created="Mon, 25 Sep 2006 08:18:12 -0500 (GMT-05:00)"] It turned out to be bugs in the way Java calc did numeric literals (it would generate "1000" instead of "1000L" for a bigint value) and the way floats were converted into bigint (java.lang.Math.round(float) instead of round(double)). The checkin lu 7769 addresses these issues. We can hold off on general arithmetic overflow for this bug.
[reporter="elin", created="Thu, 21 Sep 2006 19:25:43 -0500 (GMT-05:00)"] -- examples:
create schema t;
set schema 't';
create table tt(
coltiny tinyint,
colsmall smallint,
colint integer,
colbig bigint,
coldec decimal(14,4),
colnum numeric(14,4),
coldouble double,
colfloat float,
colreal real);
-- these insertions should all cause out of range errors, but do not:
insert into tt(coltiny, colsmall, colint) values (128, 32768,2147483648), (-129, -32769,-2147483649);
-- these all seem to work fine, causing errors as expected
insert into tt(colbig) values (9223372036854775808), (-9223372036854775809);
insert into tt(coldec) values(100000000000);
insert into tt(coldec) values(10000000000.0);
insert into tt(colnum) values(100000000000);
insert into tt(colnum) values(10000000000.0);
insert into tt(coldouble) values(1.7977E+308);
insert into tt(coldouble) values(-1.7977E+308);
...
etc.
--------
related tests: calc.funcMathCover,conv_numeric.createsource
datatype max/min range, inclusive for fennel calc:
tinyint 127/-128
smallint 32767/-32768
integer 2147483647/-2147483648
bigint 9223372036854775807/-9223372036854775808
double 1.79769E+308/-1.79769E+308
float 1.79769E+308/-1.79769E+308
real 3.402E+38/-3.402E+38
numeric/decmials bounded by precision