Bug #41153 DETERMINISTIC SF is evaluated too many times: comparison with not indexed column
Submitted: 1 Dec 2008 15:45 Modified: 13 Nov 2009 15:24
Reporter: Gleb Shchepa Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.72, 5.1.30, 6.0.7 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: DETERMINISTIC, performance
Triage: Needs Triage: D5 (Feature request) / R3 (Medium) / E2 (Low)

[1 Dec 2008 15:45] Gleb Shchepa
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;
[1 Dec 2008 19:49] Valeriy Kravchuk
Thank you for a bug report. Verified just as described, but the last 4 rows of the test case should be:

INSERT INTO t1 (a) VALUES (1), (2), (3), (4), (5), (6), (7);
SET @cnt := 0;
SELECT * FROM t1 WHERE a = f1();
SELECT @cnt;
[13 Nov 2009 15:24] Evgeny Potemkin
A duplicate of the bug#33546.