MINUS query giving results on exactly same data in two tables

I am writing this post based on a very rare but simple data issue faced by our QA team. Our QA team was trying to compare data of two tables using the MINUS query for some regression testing. Both tables have the same exact structure, and columns in concern were of NUMBER type.

nimish@garg> select PIPELINED_AMT from SALES_FY2018
  2  minus
  3  select PIPELINED_AMT from REGRESSION_FY2018;
PIPELINED_AMT  ORG_TERR_ID
------------   -----------
  1408.92113        111587
  .000000408        118104
  6.5786E-07        118993
  7.2110E-07        176297
  7.5482E-07        145634
    .0000018        120008
  1.8962E-06        120536
   .00000228        169570

When QA team manually validated each PIPELINED_AMT value for every ORG_TERR_ID, the data same -

nimish@garg> select PIPELINED_AMT from SALES_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT
------------
  1408.92113

nimish@garg> select PIPELINED_AMT from REGRESSION_FY2018 where org_terr_id = 111587  ;
PIPELINED_AMT
------------
  1408.92113

The issue came to me, citing that both tables have exactly same data still MINUS query is giving the results. It was a quite strange issue. As a first debugging step I checked what actually was stored in the tables using DUMP function.

DUMP function shows the datatype code, length and the internal representation of the expression. Two columns having the same datatype and value always return the exact same dump.

nimish@garg> select PIPELINED_AMT, dump(PIPELINED_AMT) from SALES_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT    DUMP(PIPELINED_AMT)
------------     -----------------------------------------------------------------
  1408.92113     Typ=2 Len=21: 194,15,9,93,12,29,12,4,63,25,34,98,68,65,42,93,97,72,20,29,45

nimish@garg> select PIPELINED_AMT, dump(PIPELINED_AMT) from REGRESSION_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT    DUMP(PIPELINED_AMT)
------------     -----------------------------------------------------------------
  1408.92113     Typ=2 Len=21: 194,15,9,93,12,29,12,4,63,25,34,98,68,65,42,93,97,72,20,29,47

Ahaan, Dataype and Length are same for both columns but last value of DUMP is different. SALES_FY2018 has 45 and REGRESSION_FY2018 has 47.

I started doubting that client, which was SQLPLUS, was not displaying the complete value and was rounding/truncating the actual values for formatting. So I used TO_CHAR to show the actual complete value stored in PIPELINED_AMT as string.

nimish@garg> select PIPELINED_AMT, to_char(PIPELINED_AMT) from SALES_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT TO_CHAR(PIPELINED_AMT)
------------ ----------------------------------------
  1408.92113 1408.92112811036224339767644192967119284

nimish@garg> select PIPELINED_AMT, to_char(PIPELINED_AMT) from REGRESSION_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT TO_CHAR(PIPELINED_AMT)
------------ ----------------------------------------
  1408.92113 1408.92112811036224339767644192967119285

Now, we can see there is a difference at last digit of values after decimal point (highlighted). It was the 35th digit after decimal, which was causing the issue and it actually has no significance. So I suggested QA and Dev Team to round off the values upto 5 decimal points, and complete the regression.

As a final note, we need to understand that client tools, SQL Plus, SQL Developer or Toad formats the data for better display, and sometimes are not the true representation of values stored in the database.


Related Post:
- SQL Interview Question Answers
- ISNUMERIC Function Check Value is Number or Not
- ROUND, TRUNC, CEIL and FLOOR in Oracle Database
- Oracle: Some Important Date Queries
- Playing With Truncate and Date
- Append String to CLOB in Optimized way
- ORA-01722: invalid number