MDEV-24089 support oracle syntax: rownum

The ROWNUM() function is for SELECT mapped to JOIN->accepted_rows, which is
incremented for each accepted rows.
For Filesort, update, insert, delete and load data, we map ROWNUM() to
internal variables incremented when the table is changed.
The connection between the row counter and Item_func_rownum is done
in sql_select.cc::fix_items_after_optimize() and
sql_insert.cc::fix_rownum_pointers()

When ROWNUM() is used anywhere in query, the optimization to ignore ORDER
BY in sub queries are disabled. This was done to get the following common
Oracle query to work:
select * from (select * from t1 order by a desc) as t where rownum() <= 2;
MDEV-3926 "Wrong result with GROUP BY ... WITH ROLLUP" contains a discussion
about this topic.

LIMIT optimization is enabled when in a top level WHERE clause comparing
ROWNUM() with a numerical constant using any of the following expressions:
- ROWNUM() < #
- ROWNUM() <= #
- ROWNUM() = 1
ROWNUM() can be also be the right argument to the comparison function.

LIMIT optimization is done in two cases:
- For the current sub query when the ROWNUM comparison is done on the top
  level:
  SELECT * from t1 WHERE rownum() <= 2 AND t1.a > 0
- For an inner sub query, when the upper level has only a ROWNUM comparison
  in the WHERE clause:
  SELECT * from (select * from t1) as t WHERE rownum() <= 2

In Oracle mode, one can also use ROWNUM without parentheses.

Other things:
- Fixed bug where the optimizer tries to optimize away sub queries
  with RAND_TABLE_BIT set (non-deterministic queries). Now these
  sub queries will not be converted to joins.  This bug fix was also
  needed to get rownum() working inside subqueries.
- In remove_const() remove setting simple_order to FALSE if ROLLUP is
  USED. This code was disable a long time ago because of wrong assignment
  in the following code.  Instead we set simple_order to false if
  RAND_TABLE_BIT was used in the SELECT list.  This ensures that
  we don't delete ORDER BY if the result set is not deterministic, like
  in 'SELECT RAND() AS 'r' FROM t1 ORDER BY r';
- Updated parameters for Sort_param::init_for_filesort() to be able
  to provide filesort with information where the number of accepted
  rows should be stored
- Reordered fields in class Filesort to optimize storage layout
- Added new error messsage to tell that a function can't be used in HAVING
- Added field 'with_rownum' to THD to mark that ROWNUM() is used in the
  query.

Co-author: Oleksandr Byelkin <sanja@mariadb.com>
           LIMIT optimization for sub query

1 files changed, 2 insertions, 2 deletions

diff --git a/mysql-test/main/having.test b/mysql-test/main/having.test
index 7e0a0439f8e..5e091afec1e 100644
--- a/mysql-test/main/having.test
+++ b/mysql-test/main/having.test
@@ -884,8 +884,8 @@ CREATE TABLE t1 (t INT, u INT, KEY(t));
 INSERT INTO t1 VALUES(10, 10), (11, 11), (12, 12), (12, 13),(14, 15), (15, 16),
                      (16, 17), (17, 17);
 ANALYZE TABLE t1;
-SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
-       GROUP BY t HAVING r = 1  ORDER BY t1.u;
+explain SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t) GROUP BY t HAVING r = 1  ORDER BY t1.u;
+SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t) GROUP BY t HAVING r = 1  ORDER BY t1.u;
 
 DROP TABLE t1;
 DROP FUNCTION next_seq_value;