MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements

Analysis
The reason for the less efficient plan was result of a prior design decision -
to limit the eveluation of constant expressions during optimization to only
non-expensive ones. With this approach all stored procedures were considered
expensive, and were not evaluated during optimization. As a result, SPs didn't
participate in range optimization, which resulted in a plan with table scan
rather than index range scan.

Solution
Instead of considering all SPs expensive, consider expensive only those SPs
that are non-deterministic. If an SP is deterministic, the optimizer will
checj if it is constant, and may eventually evaluate it during optimization.

1 files changed, 19 insertions, 0 deletions

diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index dcfbe127f8a..0fce174ecb7 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -8478,3 +8478,22 @@ CALL p1(1);
 
 DROP PROCEDURE p1;
 
+--echo
+--echo MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements
+--echo
+
+CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now());
+
+CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8);
+INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+
+FLUSH TABLES;
+FLUSH STATUS;
+
+SHOW STATUS LIKE '%Handler_read%';
+UPDATE t1 SET a = '+' WHERE daynum=tdn();
+SHOW STATUS LIKE '%Handler_read%';
+
+drop function tdn;
+drop table t1;