Bug #29338 View don't support DETERMINISTIC function
Submitted: 25 Jun 2007 14:06 Modified: 27 Jul 2007 16:27
Reporter: Mauro Braggio Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version: 5.1.19-beta-log MySQL, 5.0 OS:Linux
Assigned to: Gleb Shchepa
Tags: backport_050044SP1, bfsm_2007_06_28, regression

[25 Jun 2007 14:06] Mauro Braggio
Description:
Mag14 is a simple view on a table.

Mag_CM() and Mag_CODART() are DETERMINISTIC Functions, they returns only "A01" and "4X022"

Why if the where if static ( cm="A01" and codart="4X022" ) the rows are 37, and 
if the where is with deterministic function ( cm=Mag_CM() and codart=Mag_CODART() ) the rows are 39120 (all rows).

How to repeat:
mysql> explain select * from Mag14 where cm=Mag_CM() and codart=Mag_CODART();
+----+-------------+-------+-------+---------------+------------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | m     | index | data_mov      | iMagazzino | 152     | NULL | 39120 | Using where; Using index | 
+----+-------------+-------+-------+---------------+------------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from Mag14 where cm="A01" and codart="4X022";
+----+-------------+-------+------+---------------------+------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys       | key        | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------------+------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | m     | ref  | data_mov,iMagazzino | iMagazzino | 19      | const,const |   37 | Using where; Using index | 
+----+-------------+-------+------+---------------------+------------+---------+-------------+------+--------------------------+
[25 Jun 2007 21:01] Sveta Smirnova
Thank you for the report.

Verified as described using following test with exception selects from tables are affected as well:

create function f1() returns char(15) deterministic return 'foobarbaz';

create table t1 (f1 char(15), index(f1));

insert into t1 values('foo'), ('bar'), ('az'), ('foobarbaz');

create view v1 as select f1 from t1;

explain select * from t1 where f1=f1();

explain select * from v1 where f1=f1();

explain select * from t1 where f1='foobarbaz';

explain select * from v1 where f1='foobarbaz';

Bug has been introduced in ySQL 5.1.18 and current 5.0 BK sources.
[26 Jun 2007 8:35] Sveta Smirnova
Bug also occurs in 5.0.42, but doesn't exists in 5.0.41
[10 Jul 2007 0:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30580

ChangeSet@1.2525, 2007-07-10 05:26:54+05:00, gshchepa@gleb.loc +7 -0
  Fixed bug #29338.
  Trivial stored functions that consist of RETURN <constant expression>
  operator was evaluated ineffectively.
  This type of SF may be commonly used as named constants, so optimizations
  is desirable.
  
  Simple optimization has been added: now the optimizer treats such SF as
  constant expressions.
[16 Jul 2007 16:09] Sergei Golubchik
the correct fix is to amend the patch for Bug#27354, only functions declared as NON DETERMINISTIC should have RAND_BIT set.
[16 Jul 2007 16:40] Gleb Shchepa
> the correct fix is to amend the patch for Bug#27354, only functions
> declared as NON DETERMINISTIC should have RAND_BIT set.

In this case functions declared as DETERMINISTIC will be called only once per query. The DETERMINISTIC clause doesn't care about actual side effects of the function (changing tables and GLOBAL/SESSION variables), so query result will be changed too instead of query speed up.
[16 Jul 2007 18:33] Sergei Golubchik
Yes, this is correct.
(one should not use DETERMINISTIC clause for routines with side effects)
[17 Jul 2007 16:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31029

ChangeSet@1.2527, 2007-07-17 21:55:37+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #29338.
  Optimization of queries with DETERMINISTIC functions in the
  WHERE clause was not effective: sequential scan was always
  used.
  Now a SF with the DETERMINISTIC flags is treated as constant
  when it's arguments are constants (or a SF doesn't has arguments).
[19 Jul 2007 13:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31165

ChangeSet@1.2527, 2007-07-19 18:31:14+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #29338.
  Optimization of queries with DETERMINISTIC functions in the
  WHERE clause was not effective: sequential scan was always
  used.
  Now a SF with the DETERMINISTIC flags is treated as constant
  when it's arguments are constants (or a SF doesn't has arguments).
[19 Jul 2007 13:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31166

ChangeSet@1.2527, 2007-07-19 18:39:01+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #29338.
  Optimization of queries with DETERMINISTIC functions in the
  WHERE clause was not effective: sequential scan was always
  used.
  Now a SF with the DETERMINISTIC flags is treated as constant
  when it's arguments are constants (or a SF doesn't has arguments).
[24 Jul 2007 5:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31463

ChangeSet@1.2528, 2007-07-22 01:49:41+05:00, gshchepa@gleb.loc +2 -0
  sp.test, sp.result:
    Additional test case fix for bug #29338.
[26 Jul 2007 5:55] Bugs System
Pushed into 5.1.21-beta
[26 Jul 2007 5:57] Bugs System
Pushed into 5.0.48
[27 Jul 2007 16:27] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

Optimization of queries with DETERMINISTIC stored functions in the
WHERE clause was ineffective: A sequential scan was always used.
[26 Nov 2008 20:40] Kjetil Torgrim Homme
This bug seems still present in 5.0.51 (Fedora) and 5.0.67 (Ubuntu).  It still evaluates functions declared as DETERMINISTIC for every row.  Here's a simple test case:

mysql> CREATE TABLE test (a INTEGER);
mysql> INSERT INTO test VALUES (1);
mysql> INSERT INTO test VALUES (2);
mysql> INSERT INTO test VALUES (3);
mysql> INSERT INTO test VALUES (4);
mysql> delimiter |
mysql> CREATE FUNCTION slow (count INTEGER) RETURNS INTEGER DETERMINISTIC BEGIN 
WHILE count > 0 DO SET count = count - 1; END WHILE; RETURN count; END; |
mysql> delimiter ;
mysql> SELECT slow(1000000);
+---------------+
| slow(1000000) |
+---------------+
|             0 | 
+---------------+
1 row in set (1.88 sec)
mysql> SELECT * FROM test WHERE a = slow(1000000);
Empty set (7.16 sec)
mysql> INSERT INTO test VALUES (5);
mysql> INSERT INTO test VALUES (6);
mysql> SELECT * FROM test WHERE a = slow(1000000);
Empty set (10.46 sec)
[1 Dec 2008 15:56] Gleb Shchepa
> [26 Nov 21:40] Kjetil Torgrim Homme

> This bug seems still present in 5.0.51 (Fedora) and 5.0.67 (Ubuntu).
> It still evaluates functions declared as DETERMINISTIC for every row.

Thank you for your report.

The point of this bugfix is to choose effective execution plan (before this fix a full table scan was used in presence of indexed column in comparison with DETERMINISTIC SF).

Your example shows little bit different problem: a column is not indexed, so the only possible execution plan is a full table scan. OTOH an evaluation of DETERMINISTIC SF with constant arguments/empty argument list on every row is bad. To solve the problem a new bug #41153 has been reported.