Re: optimizer seems to skip stored function call in where clause?

[Peter Brawley]

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.