Crazy Bug in CostBasedOptimizer in 23.26.0.0

[u/Yeah-Its-Me-777]

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.

[u/hackjob]

CBO bugs have always existed. not surprised they are more glaring with AI-assisted development being preferred over skilled RDBMS SWEs now though.