Bug #19862 SELECT from view with ORDER BY evaluates select expression too many times
Submitted: 16 May 2006 20:57 Modified: 18 Sep 2006 9:54
Reporter: Mats Kindahl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1 OS:Any (ALL)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[16 May 2006 20:57] Mats Kindahl 
Description:
When executing a SELECT with ORDER BY on a view that is constructed from a select statement containing a stored function, the stored function is evaluated too many times.

How to repeat:
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
CREATE FUNCTION f1(x INT) RETURNS INT
  BEGIN
    INSERT INTO t1 VALUES (x);
    RETURN x+1;
  END//
INSERT INTO t2 VALUES (1), (2);
CREATE VIEW v1 AS SELECT f1(a) AS f FROM t2;
SELECT * FROM v1 ORDER BY f;
SELECT * FROM t1;
SELECT * FROM t2;

Observe the contents of t1
[17 May 2006 13:01] Hartmut Holzgraefe 
mysqltest test case

Attachment: bug19862.tar.gz (application/x-gzip, text), 975 bytes.
[20 Jul 2006 19:43] Evgeny Potemkin 
The bug can be checked a bit easier:

select f1(a) from t2 order by 1;
[24 Jul 2006 13:00] 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/9494
[25 Jul 2006 20:16] 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/9551
[25 Jul 2006 20:31] 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/9553
[2 Aug 2006 18:57] Evgeny Potemkin 
When there is no index defined filesort is used to sort the result of a
  query. If there is a function in the select list and the result set should be
  ordered by it's value then this function will be evaluated twice. First time to
  get the value of the sort key and second time to send its value to a user.
  This happens because filesort when sorts a table remembers only values of its
  fields but not values of functions.

Fixed in 5.0.25, 5.1.12
[2 Aug 2006 19:05] Paul DuBois 
Noted in 5.0.25, 5.1.12 changelogs.
[14 Aug 2006 20:43] Konstantin Osipov 
Merged into 5.1.12
[15 Sep 2006 16:01] Lars Thalmann 
test case rpl_sp_effects still disabled by this bug
[18 Sep 2006 9:54] Evgeny Potemkin 
Test case enabled in 5.1-opt