2
SUM(DECODE(cod_drcr, 'D', -amt_txn, amt_txn)) OVER(ORDER BY dat_txn RANGE UNBOUNDED PRECEDING) "Balance"

Given this query the result gives 9999.99999999999 while the result should be 10000. The final result returns -1E-9.

Below is part of the DDL

  CREATE TABLE "SCHM"."TRANSACTION"    (   "DAT_TXN" DATE,
       "COD_DRCR" CHAR(1),
       "AMT_TXN" NUMBER DEFAULT 0,
        CHECK (cod_drcr     IN ('C', 'D')) ENABLE NOVALIDATE    ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 90 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 1048576 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "CASEDATA3"

What is the root cause?

Currently, I use the database 19c version, but the data inserted into this table in 2012.

Image of problem

Data dump on another table

And also, when I'm trying select amt_txn, asciistr(amt_txn) from name_table the result of the error number is 17910295.11, 17910295.110000001 .

Another question is, "Why does the number can be inserted like this into the database"?

May
  • 31
  • 6
  • 1
    Please edit your question to include the query, raw data and current and expected results as formatted text. Are you actually using PL/SQL, or plain SQL? Either way, please include all the relevant data types. – Alex Poole Oct 12 '21 at 10:59
  • 1
    Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – piet.t Oct 12 '21 at 10:59
  • 3
    @piet.t An Oracle `NUMBER` column is not stored as a floating point number. Oracle stores it internally with 2-digits of the number per byte so it can always be represented as an exact decimal. The OP has not said how they store their values so any answer is going to come down to clarification of the question. – MT0 Oct 12 '21 at 11:27
  • @MT0 From the given results there is no doubt that there's floating-point-arithmetic in there somewhere. As to why - yes, we need clarification from the OP. – piet.t Oct 12 '21 at 11:31
  • 2
    Cannot replicate your issue using either `NUMBER` or `FLOAT` data type [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=27abeb524bfacbfab2e8e0037458b576) – MT0 Oct 12 '21 at 11:48
  • Good Morning @MT10, you're correct. I try using my own table, for update, copy paste the data, the result shows as Number. But, for the existing table, I do right click the table--> View --> the data type shows as Number, but the result on query result is the floating one. Three ways I just did: Firstly, create my table --> for update --> paste the data --> the result gives 0. Secondly, create table as select * from table_name --> the results gives -1E-9. Thirdly, create table as select * from table name where 1=2 --> for update --> paste the data -->the results give 0. – May Oct 13 '21 at 05:31
  • @May - numbers have precision and scale; look at the column definition in `user_tab_columns` or `all_tab_columns` (if it isn't in your schema), or - if it's yours - run `select dbms_metadata.get_ddl('TABLE', '') from dual` and add the result to your question as formatted text. – Alex Poole Oct 13 '21 at 06:11
  • Hi! @AlexPoole The value on DATA_PRECISION and the DATA_SCALE is blank and also medata result is . Then? – May Oct 13 '21 at 06:38
  • Your client probably has an option to see the actual CLOB value, maybe just by clicking or double-clicking on it. Or you can use `substr` to get the first 4000 chars and hope the column is within that length. – Alex Poole Oct 13 '21 at 06:59
  • When you get the DDL statement, please don't post it as a comment; [edit] your question and include it there. – MT0 Oct 13 '21 at 07:26
  • 1
    @May - OK, then that is weird; might be interesting to compare the DDL of your real table with the new ones you created, and dumping the values in your original table - particularly the `amt_txn` value for the 8300000 debit, which is where it seems to start to go wrong. What database version and patch level are you on? It sounds a bit like bug 28755084 but it could be simpler than that. – Alex Poole Oct 13 '21 at 09:13
  • @AlexPoole Hi, I don't get the meaning of "patch level"? – May Oct 13 '21 at 09:26
  • @AlexPoole I backup the original table -- > delete without truncate the original table --> copas the value from the backup table to original table --> I get 0 result, not -1E-9. I think the number inserted long time back to the original table is wrong, but I don't know how to convince that the number is decimal and not number. – May Oct 13 '21 at 10:23
  • Dumping the original value might have shown something; now you've deleted it you'll probably never know quite what was wrong. – Alex Poole Oct 13 '21 at 10:58
  • Can you give the reference how to dump the data? Since I have many cases. – May Oct 13 '21 at 11:23
  • @May Can you edit the question with your Oracle version, and can you create a fully reproducible test case including sample data? Older versions of Oracle have some extremely rare wrong-results bugs where aggregate functions can be slightly off. See my answer [here](https://stackoverflow.com/a/27972097/409172) for an example. Unfortunately, wrong-results bugs are so rare that people won't believe you unless you can build a complete test case, which can be tough. – Jon Heller Oct 14 '21 at 04:36
  • @May - `select dump(amt_txn) from transactions where ...` with some clause identifies rows you think cause the problem, or at least a small set of rows that gets this kind of result. A bug is certainly possible but the different result from recreating the same (apparent) values makes me wonder if there could be some really subtle corruption of some of the number values instead. The more information you can give us the better; or raise an Oracle support issue perhaps. – Alex Poole Oct 14 '21 at 19:16
  • @AlexPoole datadump updated on my article – May Oct 25 '21 at 07:17

1 Answers1

0

Don't store numbers as BINARY_DOUBLE or BINARY_FLOAT; store them using NUMBER.

CREATE TABLE table_name (
  cod_drcr VARCHAR2(1),
  amt_txn  BINARY_DOUBLE,
  dat_txn  DATE
);

INSERT INTO table_name (cod_drcr, amt_txn, dat_txn)
SELECT 'C',   10000,    DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 'C', 1510000,    DATE '2021-01-02' FROM DUAL UNION ALL
SELECT 'D', 1509364.59, DATE '2021-01-03' FROM DUAL UNION ALL
SELECT 'D',     635.41, DATE '2021-01-04' FROM DUAL UNION ALL
SELECT 'C',  300000,    DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 'D',  300000,    DATE '2021-01-06' FROM DUAL UNION ALL
SELECT 'C',  300000,    DATE '2021-01-07' FROM DUAL UNION ALL
SELECT 'D',  300000,    DATE '2021-01-08' FROM DUAL UNION ALL
SELECT 'C',  800000,    DATE '2021-01-09' FROM DUAL UNION ALL
SELECT 'D',  800000,    DATE '2021-01-10' FROM DUAL UNION ALL
SELECT 'C', 1000000,    DATE '2021-01-11' FROM DUAL UNION ALL
SELECT 'D', 1000000,    DATE '2021-01-12' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-13' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-14' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-15' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-16' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-17' FROM DUAL;

Then:

SELECT DECODE(cod_drcr, 'D', NULL, amt_txn) AS credit,
       DECODE(cod_drcr, 'D', amt_txn, NULL) AS debit,
       SUM(DECODE(cod_drcr, 'D', -amt_txn, amt_txn))
         OVER(ORDER BY dat_txn RANGE UNBOUNDED PRECEDING) "Balance"
FROM   table_name;

Outputs:

CREDIT DEBIT Balance
1.0E+004 1.0E+004
1.51E+006 1.52E+006
1.5093645900000001E+006 1.0635409999999916E+004
6.3540999999999997E+002 9.9999999999999163E+003
3.0E+005 3.0999999999999994E+005
3.0E+005 9.9999999999999418E+003
3.0E+005 3.0999999999999994E+005
3.0E+005 9.9999999999999418E+003
8.0E+005 8.1E+005
8.0E+005 1.0E+004
1.0E+006 1.01E+006
1.0E+006 1.0E+004
2.0E+003 8.0E+003
2.0E+003 6.0E+003
2.0E+003 4.0E+003
2.0E+003 2.0E+003
2.0E+003 0

If you use NUMBER:

CREATE TABLE table_name (
  cod_drcr VARCHAR2(1),
  amt_txn  NUMBER(15,2),
  dat_txn  DATE
);

With the same INSERT then the same query outputs:

CREDIT DEBIT Balance
10000 10000
1510000 1520000
1509364.59 10635.41
635.41 10000
300000 310000
300000 10000
300000 310000
300000 10000
800000 810000
800000 10000
1000000 1010000
1000000 10000
2000 8000
2000 6000
2000 4000
2000 2000
2000 0

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Hi! The data type already Number. But the result like what I just posted on this website – May Oct 12 '21 at 13:50
  • @May A `NUMBER` column stores the exact value as it stores the individual digits; it will not introduce floating-point errors so what you are seeing should not be possible with a `NUMBER` data type. Please [edit](https://stackoverflow.com/posts/69539288/edit) your question to show a [MRE] with: the `CREATE TABLE` statement for your table; the `INSERT` statements for your sample data; and a complete `SELECT` query that reproduces the issue. – MT0 Oct 12 '21 at 18:20
  • Good Morning @MT10, you're correct. I try using my own table, for update, copy paste the data, the result shows as Number. But, for the existing table, I do right click the table--> View --> the data type shows as Number, but the result on query result is the floating one. Help! – May Oct 13 '21 at 04:00
  • Three way I just did: Firstly, create my table --> for update --> paste the data --> the result gives 0. Secondly, create table as select * from table_name --> the results gives -1E-9. Thirdly, create table as select * from table name where 1=2 --> for update --> paste the data -->the results give 0. – May Oct 13 '21 at 04:08