MySQL Forums
Forum List  »  Optimizer & Parser

optimizer seems to skip stored function call in where clause?
Posted by: John LeSueur
Date: January 08, 2020 11:07AM

We have found something we think is a bug in the optimizer, but before reporting wanted to see whether there was something we just don't know.

I'll follow up with a script to reproduce, but the simplest expression of the bug is this:
select 1 from t1 where t1.id = '1234' and 1=returnzero(t1.id)
returns a single row.
select 1 from t1 where t1.id = 1234 and 1=returnzero(t1.id)
returns no rows.

We've determined that in the first case, returnzero is never called. In the second, it is called.

In addition, we looked at the optimizer trace, and the biggest difference we can find is there is a "condition_on_constant_tables" step in the first variation that doesn't exist on the second.

We can work around this bug (there are various ways to fix it), but we want to make sure we know if there are other ways it might present, and also if it's truly a bug, it probably is going to bite someone else :)

Options: ReplyQuote


Subject
Views
Written By
Posted
optimizer seems to skip stored function call in where clause?
154
January 08, 2020 11:07AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.