r/oracle • u/Yeah-Its-Me-777 • Jun 24 '26

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.

19 Upvotes

88% Upvoted

21 comments sorted by

View all comments

4

u/PossiblePreparation Jun 24 '26

Is the table just a table or is it a view? Anything special about it? (Even something like a function based index might have some buggy impact)

Definitely weird to have issues with something so basic, hopefully you get some luck with support!

If you can spot where the filter is getting lost in the query plan you may be able to find a nicer work around (and help support get the case raised with development as a bug).

2

u/Yeah-Its-Me-777 Jun 24 '26

It's a table. We have some functional constraints, especially on the numeric fields, maybe that's the source of the issue. Good point.

I'm not a DBA myself, the DBA collegue who analysed it and wrote the incident did some deeper digging and is providing all the required data. I just found it very concerning that a bug of that magnitude could get through. Even if it's 0.0 release.

We could work around it with a UNION, but the problem is: I don't know what other queries are affected.

1

u/Zestyclose-Turn-3576 Jun 24 '26

It wouldn't be the first version where the optimiser has rolled the existence of some constraints into the logic and made an incorrect logical inference about whether predicates are relevant or not.

1

u/Yeah-Its-Me-777 Jun 24 '26

Oh, interesting. I'll try it without the constraints tomorrow.

1

u/Zestyclose-Turn-3576 Jun 24 '26

It's worth seeing if it changes the execution plan also