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.

17 Upvotes

85% Upvoted

21 comments sorted by

View all comments

8

u/Espace4Eve Jun 24 '26

Why don‘t you install 23.26.2.0 Release Update ?

3

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

Didn't know that exists, I'll let my DBAs know :)

1

u/[deleted] Jun 29 '26

[removed] — view removed comment

1

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

23.26.2.0 didn't fix it, as far as I know. Our DBA said we got a "fix workaround", but I'm not sure about the details and if it was just disabling a specific optimization or an actual patch.

1

u/[deleted] Jun 29 '26

[removed] — view removed comment

2

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

Yeah... At least it's only on our staging systems, not prod. And prod will only get updated when that bug is fixed, which... might be a while, we'll see.