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.


include/mysql_com.h:
  Bug #30355: comment added
mysql-test/r/udf.result:
  Bug #30355: test case
mysql-test/t/udf.test:
  Bug #30355: test case
sql/item_func.cc:
  Bug #30355: keep const_item_cache and used_tables_cache in sync
sql/item_func.h:
  Bug #30355: 
   - a better implementation of update_used_tables()
   - keep const_item_cache and used_tables_cache in sync
sql/udf_example.c:
  Bug #30355: Wrong value for const_item fixed.

1 files changed, 27 insertions, 0 deletions

diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index e6797796ea0..a79be1c3189 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -327,4 +327,31 @@ DROP FUNCTION check_const_len;
 DROP PROCEDURE check_const_len_sp;
 DROP TRIGGER check_const_len_trigger;
 DROP TABLE const_len_bug;
+CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (4),(3),(2),(1);
+INSERT INTO t2 SELECT * FROM t1;
+SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC;
+seq	a
+1	4
+2	3
+3	2
+4	1
+SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC;
+seq	a
+4	1
+3	2
+2	3
+1	4
+SELECT * FROM t1 WHERE a = sequence();
+a
+SELECT * FROM t2 WHERE a = sequence();
+a
+1
+2
+3
+4
+DROP FUNCTION sequence;
+DROP TABLE t1,t2;
 End of 5.0 tests.