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
begin
set @func_was_called=1;
return 0;
end;
go
delimiter ;

set @func_was_called=0;
select 1 from t1 where t1.id = '1234' and 1=returnzero(t1.id);
select version(), @func_was_called;
set @func_was_called=0;
select 1 from t1 where t1.id = 1234 and 1=returnzero(t1.id);
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


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


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.