MySQL Bugs: #30355: Incorrect ordering of UDF results

Bug #30355	Incorrect ordering of UDF results
Submitted:	10 Aug 2007 10:19	Modified:	24 Jan 2008 19:53
Reporter:	Gleb Shchepa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0-bk/5.1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
This problem was reported as a part of the bug #24593 (SPs have similar problem, but solution is different).

How to repeat:
DROP FUNCTION IF EXISTS sequence;
DROP TABLE IF EXISTS t1;
CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (4),(3),(2),(1);
SELECT sequence() AS seq, x FROM t1 ORDER BY seq ASC;
seq     x
1       4
2       3
3       2
4       1
SELECT sequence() AS seq, x FROM t1 ORDER BY seq DESC;
seq     x
1       4
2       3
3       2
4       1
DROP FUNCTION sequence;
DROP TABLE t1;

test case

Attachment: 30355.test (application/octet-stream, text), 467 bytes.

Thank you for the bug report.

[miguel@skybr 5.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.21-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP FUNCTION IF EXISTS sequence;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (x INT);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t1 VALUES (4),(3),(2),(1);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT sequence() AS seq, x FROM t1 ORDER BY seq ASC;
+-----+------+
| seq | x    |
+-----+------+
|   1 |    4 | 
|   2 |    3 | 
|   3 |    2 | 
|   4 |    1 | 
+-----+------+
4 rows in set (0.04 sec)

mysql> SELECT sequence() AS seq, x FROM t1 ORDER BY seq DESC;
+-----+------+
| seq | x    |
+-----+------+
|   1 |    4 | 
|   2 |    3 | 
|   3 |    2 | 
|   4 |    1 | 
+-----+------+
4 rows in set (0.01 sec)

mysql>

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

ChangeSet@1.2580, 2007-11-26 19:16:51+02:00, gkodinov@magare.gmz +5 -0
  Bug #30355: Incorrect ordering of UDF results
  
  Currently the initialization of an UDF is plumbed 
  together with determining if it returns a const result.
  This doesn't take into account the optimizer's pass to
  re-evaluate if a function is a const when marking tables
  as const or doing source transformations.
  This caused the sequence() UDF to be considered 
  deterministic and returning constant by the optimizer.
  Fixed by:
  1. Marking sequence() as returning a non-const.
  2. When an UDF returns a non-const flag and 
  has no tables it depends on consider this function 
  non-deterministic and set RAND_TABLE_BIT
  3. Implemented a shim update_used_tables() to 
  prevent calling update_used_tables() for 
  non-deterministic UDF.
  Note that update_used_tables() will still be called
  for deterministic UDFs and will allow for a good 
  optimization of such calls.

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

ChangeSet@1.2580, 2007-11-27 16:52:28+02:00, gkodinov@magare.gmz +6 -0
  Bug #30355: Incorrect ordering of UDF results
  
  There's currently no way of knowing the determinicity of an UDF.
  And the optimizer and the sequence() UDFs were making wrong
  assumptions about what the is_const member means.
  Plus there was no implementation of update_system_tables()
  causing the optimizer to overwrite the information returned by
  the <udf>_init function.
  
  Fixed by equating the assumptions about the semantics of 
  is_const and providing a implementation of update_used_tables().
  Added a TODO item for the UDF API change needed to make a better 
  implementation.

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

ChangeSet@1.2580, 2007-11-27 17:16:52+02:00, gkodinov@magare.gmz +6 -0
  Bug #30355: Incorrect ordering of UDF results
  
  There's currently no way of knowing the determinicity of an UDF.
  And the optimizer and the sequence() UDFs were making wrong
  assumptions about what the is_const member means.
  Plus there was no implementation of update_system_tables()
  causing the optimizer to overwrite the information returned by
  the <udf>_init function.
  
  Fixed by equating the assumptions about the semantics of 
  is_const and providing a implementation of update_used_tables().
  Added a TODO item for the UDF API change needed to make a better 
  implementation.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

The optimizer made incorrect assumptions about the value of the
is_member value for user-defined functions, sometimes resulting in
incorrect ordering of UDF results.