MDEV-29129: Performance regression starting in 10.6: select order by limit ...

The cause of regression was handling for ROWNUM() function.
For queries like

  SELECT ROWNUM() FROM ... ORDER BY ...

ROWNUM() should be computed before the ORDER BY.
The computation was moved to be before the ORDER BY for any entries in
the select list that had RAND_TABLE_BIT set.

This had a negative impact on queries in form:

  SELECT sp_func() FROM t1 ORDER BY ... LIMIT n

where sp_func() is NOT declared as DETERMINISTIC (and so has
RAND_TABLE_BIT set).

The fix is to require evaluation for sorting only for the ROWNUM()
function. Functions that just have RAND_TABLE_BIT() can be computed
after ORDER BY ... LIMIT is applied.

(think about a possible index that satisfies the ORDER BY clause. In
that case, the the rows would be read in the needed order and we would
stop after reading LIMIT rows, achieving the same effect).

6 files changed, 86 insertions, 9 deletions

diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index 50f6d381dd6..15a7784c890 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -2696,7 +2696,7 @@ ON p.id = g.p_random
 ORDER BY gallery_name ASC 
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	Using filesort
 1	PRIMARY	p	eq_ref	PRIMARY	PRIMARY	4	g.p_random	1	Using where
 2	DERIVED	gal	ALL	NULL	NULL	NULL	NULL	10	
 3	DEPENDENT SUBQUERY	pi	ref	gallery_id	gallery_id	4	test.gal.id	4	Using temporary; Using filesort
diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result
index ac26d69543d..d7cbadd5e75 100644
--- a/mysql-test/main/having.result
+++ b/mysql-test/main/having.result
@@ -834,13 +834,13 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 explain SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t) GROUP BY t HAVING r = 1  ORDER BY t1.u;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	t	5	NULL	8	Using temporary; Using filesort
 SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t) GROUP BY t HAVING r = 1  ORDER BY t1.u;
 t	r
 10	1
 12	1
-14	1
-16	1
+15	1
+17	1
 DROP TABLE t1;
 DROP FUNCTION next_seq_value;
 DROP TABLE series;
diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result
index 317a12ef311..6ad93930448 100644
--- a/mysql-test/main/show_explain.result
+++ b/mysql-test/main/show_explain.result
@@ -1007,7 +1007,7 @@ SELECT a+SLEEP(0.01) FROM t1
 WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
 ORDER BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using filesort
 set @show_explain_probe_select_id=1;
 SET debug_dbug='+d,show_explain_probe_join_exec_start';
 SELECT a+SLEEP(0.01) FROM t1
@@ -1016,7 +1016,7 @@ ORDER BY b;
 connection default;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using filesort
 Warnings:
 Note	1003	SELECT a+SLEEP(0.01) FROM t1
 WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
@@ -1039,7 +1039,7 @@ ORDER BY b;
 connection default;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using filesort
 Warnings:
 Note	1003	SELECT a+SLEEP(0.01) FROM t1
 WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index b14551d35c7..88df800b0e0 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -8932,3 +8932,43 @@ ERROR 42000: PROCEDURE does not support subqueries or stored functions
 DROP TABLE t1;
 # End of 10.4 tests
 #
+#
+# MDEV-29129: Performance regression starting in 10.6: unlimited "select order by limit"
+#
+CREATE TABLE t1 (
+lookupId int primary key,
+value varchar(255)
+);
+insert into t1 select seq, seq from seq_1_to_100;
+# Note: the function is intentionally NOT declared as DETERMINISTIC
+CREATE FUNCTION f1(LOOKUPID_IN INT) RETURNS varchar(255) CHARSET utf8
+BEGIN
+DECLARE LOOKUP_VALUE VARCHAR(255);
+SET LOOKUP_VALUE = (SELECT value FROM t1 WHERE lookupId = LOOKUPID_IN);
+set @counter=@counter+1;
+RETURN LOOKUP_VALUE;
+END; 
+//
+create table t2 (
+col1 int,
+col2 int
+);
+insert into t2 select mod(seq,100), seq from seq_1_to_1000;
+explain 
+select f1(col1) from t2 order by col2 desc limit 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000	Using filesort
+set @counter=0;
+select f1(col1) from t2 order by col2 desc limit 5;
+f1(col1)
+NULL
+99
+98
+97
+96
+# Must show 5, not 1000:
+select @counter;
+@counter
+5
+drop function f1;
+drop table t1,t2;
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
index b71cfe11d58..baf89eeaba5 100644
--- a/mysql-test/main/sp.test
+++ b/mysql-test/main/sp.test
@@ -10519,3 +10519,40 @@ DROP TABLE t1;
 
 --echo # End of 10.4 tests
 --echo #
+
+--echo #
+--echo # MDEV-29129: Performance regression starting in 10.6: unlimited "select order by limit"
+--echo #
+
+CREATE TABLE t1 (
+  lookupId int primary key,
+  value varchar(255)
+);
+insert into t1 select seq, seq from seq_1_to_100;
+
+-- echo # Note: the function is intentionally NOT declared as DETERMINISTIC
+delimiter //;
+CREATE FUNCTION f1(LOOKUPID_IN INT) RETURNS varchar(255) CHARSET utf8
+BEGIN
+  DECLARE LOOKUP_VALUE VARCHAR(255);
+  SET LOOKUP_VALUE = (SELECT value FROM t1 WHERE lookupId = LOOKUPID_IN);
+  set @counter=@counter+1;
+  RETURN LOOKUP_VALUE;
+END; 
+//
+delimiter ;//
+
+create table t2 (
+  col1 int,
+  col2 int
+);
+insert into t2 select mod(seq,100), seq from seq_1_to_1000;
+
+explain 
+select f1(col1) from t2 order by col2 desc limit 5;
+set @counter=0;
+select f1(col1) from t2 order by col2 desc limit 5;
+--echo # Must show 5, not 1000:
+select @counter;
+drop function f1;
+drop table t1,t2;
diff --git a/mysql-test/main/user_var.result b/mysql-test/main/user_var.result
index 924c252b951..a5837996c19 100644
--- a/mysql-test/main/user_var.result
+++ b/mysql-test/main/user_var.result
@@ -372,14 +372,14 @@ insert into t1 (b) values (10), (30), (10), (10);
 set @var := 0;
 explain select if(b=@var, 999, b) , @var := b from t1  order by b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
 set @var := 0;
 select if(b=@var, 999, b) , @var := b from t1  order by b;
 if(b=@var, 999, b)	@var := b
 10	10
-10	10
 30	30
 999	10
+999	10
 drop table t1;
 create temporary table t1 (id int);
 insert into t1 values (2), (3), (3), (4);