Crazy Bug in CostBasedOptimizer in 23.26.0.0
So, color me surprised when I got a question from one of our developers why a pretty simple query didn't return the expected results.
Basically, the query was:
>SELECT * FROM table WHERE table.field IS NULL or table.field <= 'someValue';
And it returned only rows with values, ignoring the rows with NULL.
After testing, I found another weird behaviour: The table had a couple of NUMERIC columns. When I included one of those, the bug appeared. When I only selected other columns, it worked, and returned all expected rows.
After much googling, I found another way to make it work:
>SELECT /*+ RULE */ * FROM table WHERE table.field IS NULL or table.field <= 'someValue';
Forcing the Rule Based Optimizer makes it work as well.
So, we now have a ticket with Oracle, let's see what happens. But a bug of this severity is pretty crazy to me.
Version is 23.26.0.0, btw. And yes, it's only on our staging systems, but still. Not even a 0.0 version should have these kind of bugs.
So, if you're running (or testing) that version, and you get unexpected results, maybe it's that.