CODEWITHSUNDEEP
oracle12c
Patrick C.
Patrick C.

Reputation: 2411

Oracle 12c: Bug with trunc(date) results in wrong data

I have a very strange behavior on my Oracle 12 c database. When i do something like:

select count(*) from fact_table, date_dim, dim_table where 
date_dim.date >= trunc(sysdate)-1
and fact_table.DATE_ID = date_dim.DATE_ID
and fact_table.DIM_ID = dim_table.DIM_ID;

...I get a certain number. The strange behavior starts when I change the '>=' to '='- only then do I get the double amount. I checked the join condition, but as far as I know the amount should be the greater or the same if I filter on date '>=' yesterday instead of date '=' yesterday.

Why does this happen? Is there a setting that could lead to duplicate data in the SQL?

edited the join conditions both join conditions have just 1 result.

Upvotes: 2

Views: 763

Answers (1)

Patrick C.
Patrick C.

Reputation: 2411

After some intense analytics i found out that i faced an oracle bug -> Bug 18461054 : TRUNC WITH DATE BIND ON PARTITIONED TABLE GIVE WRONG RESULTS

So this problem can occure with trunc or round date.

The workaround for current sessions is 

alter session set "_optimizer_generate_transitive_pred"=false;

There is also a patch Patch 18461054: TRUNC WITH DATE BIND ON PARTITIONED TABLE GIVE WRONG RESULTS

Hope that helps someone else than me :)

Best,

Patrick

Upvotes: 1

Related Questions