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/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
index eefd4f86708..ba5c0baf79b 100644
--- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
+++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
@@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1
 ####################################
 SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long;
 event_name	digest	digest_text	sql_text
-statement/sql/select	5169a25ba78dc5dc99487237f8459aa8	SELECT ? + ? + 	SELECT ...
-statement/sql/truncate	48bb01298bb30bda20be141838160182	TRUNCATE TABLE 	truncat...
+statement/sql/select	62764f94ca18cf720c44d84579e05e60	SELECT ? + ? + 	SELECT ...
+statement/sql/truncate	5947880b8ba439f0ed3ff0bfbb04eebf	TRUNCATE TABLE 	truncat...