Missing can_eval_in_optimize() guard on SUBSTR pos-argument causes extra optimize-time evaluation of expensive/side-effecting expressions

--source include/have_innodb.inc

CREATE TABLE t1 (id INT PRIMARY KEY, cnt INT);

INSERT INTO t1 VALUES (1,0);

--DELIMITER //

CREATE FUNCTION bump_counter() RETURNS INT NOT DETERMINISTIC

BEGIN

  UPDATE t1 SET cnt = cnt + 1 WHERE id = 1;

  RETURN 3;

END//

--DELIMITER ;

CREATE TABLE t2 (id INT PRIMARY KEY, indexed_col VARCHAR(50), KEY k3 (indexed_col)) ENGINE=InnoDB;

INSERT INTO t2 VALUES (1,'xxabcxxx');

SELECT id FROM t2 WHERE SUBSTR(indexed_col, (SELECT bump_counter()), 3) = 'abc';

SELECT cnt AS c1_cnt FROM t1 WHERE id = 1;

UPDATE t1 SET cnt = 0 WHERE id = 1;

SELECT id FROM t2 WHERE SUBSTR(CONCAT(indexed_col,''), (SELECT bump_counter()), 3) = 'abc';

SELECT cnt AS c2_cnt FROM t1 WHERE id = 1;

DROP TABLE t2, t1;

DROP FUNCTION bump_counter;

args[val_idx]->can_eval_in_optimize() guards the string constant side ('abc'). Nothing guards func_args[1] (the pos arg). So if pos is itself a subquery or stored-function call — SUBSTR(col, (SELECT ...), N) = 'abc' — that subquery gets executed once, right now, at optimize time, before the real execution phase ever starts.

CREATE TABLE t1 (id INT PRIMARY KEY, cnt INT);

INSERT INTO t1 VALUES (1,0);

CREATE FUNCTION bump_counter() RETURNS INT NOT DETERMINISTIC

BEGIN

UPDATE t1 SET cnt = cnt + 1 WHERE id = 1;

RETURN 3;

END//

CREATE TABLE t2 (id INT PRIMARY KEY, indexed_col VARCHAR(50), KEY k3 (indexed_col)) ENGINE=InnoDB;

INSERT INTO t2 VALUES (1,'xxabcxxx');

SELECT id FROM t2 WHERE SUBSTR(indexed_col, (SELECT bump_counter()), 3) = 'abc';

id

1

SELECT cnt AS c1_cnt FROM t1 WHERE id = 1;

c1_cnt

3

UPDATE t1 SET cnt = 0 WHERE id = 1;

SELECT id FROM t2 WHERE SUBSTR(CONCAT(indexed_col,''), (SELECT bump_counter()), 3) = 'abc';

id

1

SELECT cnt AS c2_cnt FROM t1 WHERE id = 1;

c2_cnt

2

DROP TABLE t2, t1;

DROP FUNCTION bump_counter;