MySQL Bugs: #82565: Conditions are only constant propagated within an AND condition

Bug #82565	Conditions are only constant propagated within an AND condition
Submitted:	12 Aug 2016 12:31	Modified:	13 Aug 2016 5:07
Reporter:	Steinar Gunderson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.0-dmr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It seems that we don't constant-fold runtime-constant equality expressions unless they are part of an AND condition, e.g., “WHERE col=some_func()” is not constant-folded but “WHERE col=some_func() AND TRUE” is.

change_cond_ref_to_const() is supposed to do this, but it is only ever called by propagate_cond_constants(), which explicitly checks that the condition either is an AND, or is part of one.

How to repeat:
The following MTR test demonstrates the issue:

DELIMITER |;

CREATE FUNCTION some_func()
    RETURNS INT
    DETERMINISTIC
    NO SQL
BEGIN
    RETURN 123;
END|

DELIMITER ;|

CREATE TABLE t1 (
  id int NOT NULL
);

EXPLAIN SELECT * FROM t1 WHERE id = some_func();
EXPLAIN SELECT * FROM t1 WHERE id = some_func() AND TRUE;

DROP FUNCTION some_func;
DROP TABLE t1;

The two EXPLAINs are shown as:

Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`id` = <cache>(`some_func`()))
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`id` = 123)

Both should have been id = 123, of course.

From my limited understanding of the EXPLAIN output, it appears 5.5 does not have this problem.

5.7 has the problem.

Hello Steinar,

Thank you for the report and test case.

Thanks,
Umesh