Description:
See bug #29338 ([26 Nov 21:40] Kjetil Torgrim Homme) for the original description of the problem.
Patch for bug #29338 fixes choosing of ineffective plan where column in comparison expression "column <op> func()" is indexed:
mysql> DELIMITER |
mysql> CREATE FUNCTION f1() RETURNS INT DETERMINISTIC BEGIN SET @cnt := @cnt + 1; END;|
mysql> DELIMITER ;
mysql> CREATE TABLE t1 (a INT, INDEX(a)); # column a is indexed
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SET @cnt := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE a = f1();
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql> SELECT @cnt;
+------+
| @cnt |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM t1 WHERE a = f1();
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | a | a | 5 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
However, if column is not indexed, optimize chooses correct plan (there is no way to choose anything more effective than full table scan), but the server evaluates DETERMINISTIC function on every row:
mysql> ALTER TABLE t1 DROP INDEX a;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SET @cnt := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE a = f1();
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql> SELECT @cnt;
+------+
| @cnt |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
Multiple calls to DETERMINISTIC function with constant (or empty) arguments should be optimized out.
How to repeat:
DELIMITER |
CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
BEGIN
SET @cnt := @cnt + 1;
RETURN 10;
END;|
DELIMITER ;
CREATE TABLE t1 (a INT);
INSERT INTO t2 (i) VALUES (1), (2), (3), (4), (5), (6), (7);
SET @cnt := 0;
SELECT * FROM t1 WHERE a = f1();
# this should return something significantly less than 10:
SET @cnt := 0;
