MySQL Forums
Forum List  »  Optimizer & Parser

Re: optimizer seems to skip stored function call in where clause?
Posted by: Peter Brawley
Date: January 08, 2020 01:11PM

There's a problem with the script as posted---returnzero() declares a param that the script doesn't pass.

When that's corrected, the script incorrectly returns a row when the query's int constant value is single-quoted, not when not.

The anomaly doesn't occur either in 5.6 or 5.7.

A simpler way to track when the func is called ...

drop table if exists t1;
create table t1 (id int PRIMARY KEY);
insert into t1 VALUES(1234);

drop function if exists returnzero;
delimiter go
create function returnzero(pid int) returns int
set @func_was_called=1;
return 0;
delimiter ;

set @func_was_called=0;
select 1 from t1 where = '1234' and 1=returnzero(;
select version(), @func_was_called;
set @func_was_called=0;
select 1 from t1 where = 1234 and 1=returnzero(;
select version(), @func_was_called;

Versions 5.6 and 5.7 always call the func, correctly return an empty set. In 8.0.18, when the constant int is unquoted, behaviour is like 5.6 & 5.7.

Edited 2 time(s). Last edit at 01/08/2020 01:16PM by Peter Brawley.

Options: ReplyQuote

Written By
Re: optimizer seems to skip stored function call in where clause?
January 08, 2020 01:11PM

Sorry, you can't reply to this topic. It has been closed.

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.