MDEV-6657: Poor plan choice for ORDER BY key DESC optimization...bb-10.1-mdev6657

The problem was caused by the following scenario:
- range optimizer picks an index IDX1 which doesn't match the ORDER BY ...
  LIMIT clause.
- test_if_skip_sort_order() decides to switch to index IDX2 which matches
  the ORDER BY ... LIMIT.
- it runs SQL_SELECT::test_quick_select() for the second time to produce
  an quick select for IDX2.
- However, test_quick_select() would figure that full index scan on IDX1
  is still cheaper (its calculations ignore the LIMIT n).

Fixed this by
- passing force_quick_range=true to test_quick_select()
- in test_quick_select, don't consider full index scans if the mentioned
  parameter is true.

Numerous changes in .result files are caused by test_quick_select() being
run after "early/late NULLs filtering" feature has injected NOT NULL
condition.

1 files changed, 3 insertions, 3 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 294142737d9..aba8d6c201c 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -297,7 +297,7 @@ create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
 insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
 explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	22	NULL	11	Using where; Using index
+1	SIMPLE	t1	range	a	a	22	NULL	2	Using where; Using index
 select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
 a	b	c
 1	NULL	b
@@ -2569,7 +2569,7 @@ SELECT * FROM t1 r JOIN t1 s ON r.a = s.a
 WHERE s.a IN (2,9) OR s.a < 100  AND s.a != 0
 ORDER BY 1 LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	r	index	PRIMARY	PRIMARY	4	NULL	10	100.00	Using where; Using index
+1	SIMPLE	r	range	PRIMARY	PRIMARY	4	NULL	12	100.00	Using where; Using index
 1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	4	test.r.a	1	100.00	Using index
 Warnings:
 Note	1003	select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where ((`test`.`s`.`a` = `test`.`r`.`a`) and ((`test`.`r`.`a` in (2,9)) or ((`test`.`r`.`a` < 100) and (`test`.`r`.`a` <> 0)))) order by 1 limit 10
@@ -2600,7 +2600,7 @@ CREATE TABLE t1 (a INT,KEY (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	5	NULL	10	Using where; Using index; Using filesort
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
 SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
 a	1
 10	1