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

[12 Aug 2016 12:31] Steinar Gunderson
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.
[12 Aug 2016 12:47] Steinar Gunderson
From my limited understanding of the EXPLAIN output, it appears 5.5 does not have this problem.
[12 Aug 2016 12:50] Steinar Gunderson
5.7 has the problem.
[13 Aug 2016 5:07] MySQL Verification Team
Hello Steinar,

Thank you for the report and test case.

Thanks,
Umesh