MySQL Bugs: #24593: Problem Ordering on return value of Stored Function/UDF. (derived table wrkarnd)

Bug #24593	Problem Ordering on return value of Stored Function/UDF. (derived table wrkarnd)
Submitted:	26 Nov 2006 14:55	Modified:	7 Feb 2008 14:18
Reporter:	Vikash Bucha	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.23-log and 5.0.18	OS:	Linux (Linux FC4 & FC5)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	ordering, Stored Functions, udf

Description:
I have this problem using ordering on return value from Stored Function(SF) which takes into account the ROW POSITION.
but if SF takes some constant values such as values from any column, which fixed output is as expected.

How to repeat:
To repeat execute the following SQL statements, which is a very simplified version of my actual usage.
Step 1:

DROP TABLE IF EXISTS sfbug;
CREATE TABLE sfbug (x INT);
INSERT INTO sfbug VALUES(4),(3),(2),(1);

DELIMITER //
DROP FUNCTION IF EXISTS sfbug_order//
CREATE FUNCTION sfbug_order() RETURNS INT 
-- just returns a counter which increments on subsequent rows
-- remember to reset @counter to 0, before using in a select
BEGIN 
SET @counter=@counter+1; 
RETURN @counter; 
END//
DELIMITER ;

STEP 2.
Execute the following Queries. but SET @counter=0; before each query, this is actually done in an SP.

---------------------------------------------------------------------------
SET @counter = 0; 
SELECT sfbug_order() AS ordering,x FROM sfbug ORDER BY ordering ASC;
Output is (as expected ...)
+----------+------+
| ordering | x    |
+----------+------+
|        1 |    4 |
|        2 |    3 |
|        3 |    2 |
|        4 |    1 |
+----------+------+
---------------------------------------------------------------------------

SET @counter = 0; 
SELECT sfbug_order() AS ordering,x FROM sfbug ORDER BY ordering DESC;
(Output does not order as expected ... )
+----------+------+
| ordering | x    |
+----------+------+
|        1 |    4 |
|        2 |    3 |
|        3 |    2 |
|        4 |    1 |
+----------+------+

---------------------------------------------------------------------------
Workaround is to use a derived sub select, which is un-optimized.

SET @counter=0;
SELECT * FROM (SELECT sfbug_order() AS ordering,x FROM sfbug) AS x ORDER BY ordering DESC;
+----------+------+
| ordering | x    |
+----------+------+
|        4 |    1 |
|        3 |    2 |
|        2 |    3 |
|        1 |    4 |
+----------+------+

Looks as if ordering by stored function results is not
possible at all, to prove this i replaced the function
by one simply returning RAND()*100:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES(4),(3),(2),(1);

DELIMITER //
DROP FUNCTION IF EXISTS f1//

CREATE FUNCTION f1() RETURNS INT 
BEGIN 
  RETURN RAND()*100;
END//

DELIMITER ;

SELECT f1() AS ordering,x FROM t1 ORDER BY ordering ASC;
EXPLAIN SELECT f1() AS ordering,x FROM t1 ORDER BY ordering ASC;

SELECT f1() AS ordering,x FROM t1 ORDER BY ordering DESC;
EXPLAIN SELECT f1() AS ordering,x FROM t1 ORDER BY ordering DESC;

SELECT * FROM (SELECT f1() AS ordering,x FROM t1) AS x ORDER BY ordering DESC;
EXPLAIN SELECT * FROM (SELECT f1() AS ordering,x FROM t1) AS x ORDER BY ordering DESC;

With the first two queries the results are always returned in 
insert order and the EXPLAIN output doesn't show any sorting
related information at all.

Only the query using a subselect returns ordered results and
shows a "using filesort" in its EXPLAIN output.

It is possible to sort on values returned from Stored Functions.
Try This.

CREATE FUNCTION sfbug_order(x INT) RETURNS INT BEGIN SET @counter=x+100; RETURN @counter; END//

Executing :
SELECT sfbug_order(x) AS ordering,x FROM sfbug ORDER BY ordering DESC;
we get
+----------+------+
| ordering | x    |
+----------+------+
|      104 |    4 |
|      103 |    3 |
|      102 |    2 |
|      101 |    1 |
+----------+------+

There's something more to the problem, I guess.

Dmitri, looks similar to the problem with the trigger field fixed recently by Igor.

Actually, it seems the root cause is that Item_func_sp does not report any tables used.
Perhaps it should return RAND_TABLE_BIT.
I'm not sure if this is a bug at all, need to check the standard - maybe we're allowed to evaluate such the function only once in this case.

Reassigned as it is similar to #29338 and #27354, as discussed on IRC with Igor and Gleb.

SP and UDF has similar ORDER-ing problem, but solutions are different.

UDF-related part of this bug is moved to bug #30355.

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/32358

ChangeSet@1.2501, 2007-08-10 19:14:26+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #24593.
  The "SELECT ... ORDER BY stored function" statement evaluated
  SFs twice, and an order of resulting data was incorrect.
  
  This bug affected non-deterministic SFs without arguments or
  with constant arguments, because the const_item flag of such
  SF items was always set to true.

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/32616

ChangeSet@1.2501, 2007-08-16 16:10:57+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #24593.
  When there is no index defined filesort is used to sort the result of a
  query. If there is a non-deterministic SF in the select list, and this
  function argument list is empty (or consists of constants), and the result
  set should be ordered by this SF 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.
  If the returning value of this SF changes from call to call, the server sends
  wrong results in the incorrect order to a client. Also double invocation
  of SFs applies side effects like incrementing of variables twice.
  
  This happens because filesort when sorts a table remembers only values of its
  fields and values of non-constant functions.
  
  All calls to non-deterministic functions without arguments (or with constant
  arguments) are affected.
  
  A temporary table should be used to store results of non-deterministic SFs
  and to sort them to avoid double SP evaluation and to get a correct result.