MySQL Bugs: #29338: View don't support DETERMINISTIC function

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	CPU Architecture:	Any
Tags:	backport_050044SP1, bfsm_2007_06_28, regression

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 | 
+----+-------------+-------+------+---------------------+------------+---------+-------------+------+--------------------------+

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.

Bug also occurs in 5.0.42, but doesn't exists in 5.0.41

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.

the correct fix is to amend the patch for Bug#27354, only functions declared as NON DETERMINISTIC should have RAND_BIT set.

> 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.

Yes, this is correct.
(one should not use DETERMINISTIC clause for routines with side effects)

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).

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).

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).

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.

Pushed into 5.1.21-beta

Pushed into 5.0.48

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.

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)

> [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.