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: | |
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 |
[25 Jun 2007 14:06]
Mauro Braggio
[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.