Pushing LIMIT to temp aggregation table is possible, but not when WITH
TIES is used. In a degenerate case with constant ORDER BY, the constant
gets removed and the code assumed the limit is push-able.

Ensure that if WITH TIES is present, that this does not happen.

Implemented according to standard OFFSET start { ROW | ROWS},
ROW and ROWS is mandatory after OFFSET.

This commit implements the standard SQL extension
OFFSET start { ROW | ROWS }
[FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }]

To achieve this a reserved keyword OFFSET is introduced.

The general logic for WITH TIES implies:
1. The number of rows a query returns is no longer known during optimize
   phase. Adjust optimizations to no longer consider this.
2. During end_send make use of an "order Cached_item"to compare if the
   ORDER BY columns changed. Keep returning rows until there is a
   change. This happens only after we reached the row limit.
3. Within end_send_group, the order by clause was eliminated. It is
   still possible to keep the optimization of using end_send_group for
   producing the final result set.

The function was originally introduced by eb0804ef
MDEV-18553: MDEV-16327 pre-requisits part 1: isolation of LIMIT/OFFSET handling

set_unlimited had an overloaded notion of both clearing the offset value
and the limit value. The code is used for SQL_CALC_ROWS option to
disable the limit clause after the limit is reached, while at the same
time the calling code suppreses sending of rows.

Proposed solution:
Dedicated clear method for query initialization (to ensure no garbage
remains between executions).
Dedicated set_unlimited that only alters the limit value.

Local variables such as keep_row_order are semantically clearer on the
intent, rather than using an or condition twice.

Also document (through function parameters names) which ORDER must be passed
to the function: first GROUP BY, then ORDER BY.

Reorder end_send to exit via return instead of large if-else block.

The variable is never used and only takes up SELECT_LEX space.

Replace
  * select_lex::offset_limit
  * select_lex::select_limit
  * select_lex::explicit_limit
with select_lex::Lex_select_limit

The Lex_select_limit already existed with the same elements and was used in
by the yacc parser.

This commit is in preparation for FETCH FIRST implementation, as it
simplifies a lot of the code.

Additionally, the parser is simplified by making use of the stack to
return Lex_select_limit objects.

Cleanup of init_query() too. Removes explicit_limit= 0 as it's done a bit later
in init_select() with limit_params.empty()

Aftermerge fixes.

Only return the error if field->store produced errors, not warnings.

Followup part#2: allocate the List object on the right mem-root, too.

Followup to review input address commit: pass the new parameter type
to find_field_in_table_ref().

MDEV-25420 JSON_TABLE: ASAN heap-buffer-overflow in Protocol::net_store_data or consequent failur es.

fixed changed.

Address review input: switch Name_resolution_context::ignored_tables from
table_map to a list of TABLE_LIST objects. The rationale is that table
bits may be changed due to query rewrites, etc, which may potentially
require updating ignored_tables.

MDEV-25420 JSON_TABLE: ASAN heap-buffer-overflow in Protocol::net_store_data or consequent failures.

error in the patch fixed.

Do not adapt to the argument charset anymore. Just use the utf8mb4 as
the default cherset.

MDEV-25420 JSON_TABLE: ASAN heap-buffer-overflow in Protocol::net_store_data or consequent failures.

Create_tmp_table::add_field didn't consider BIT type field for
null_counter.

We need to update default values if we change the column's charsets.

If a field is not in the read set - read it in the local buffer anyway to check for errors.

When doing name resolution, do the same what WHERE/ON clauses do:
they don't count in select_lex->non_agg_field_used().

atch to get rid of duplicating code of the Create_tmp_table.

MDEV-25259 JSON_TABLE: Illegal mix of collations upon executing query with combination of charsets via view.

now the ::print printed too much. Limit it for fields with no CHARSET
possible.

MDEV-25259 JSON_TABLE: Illegal mix of collations upon executing query with combination of charsets via view.

The ::print method didn't print the explicitly specified charset.

test crashing after any_db assinged nonzero lenght fixed.

test crashing after any_db assinged nonzero lenght fixed.

Table_function_json_table::m_dep_tables attempts to cache the value of
m_json->used_tables(), poorly. Remove the cache and use the value
directly.

The query used a subquery of this form:

SELECT ...
WHERE
   EXISTS( SELECT ...
           FROM JSON_TABLE(outer_ref, ..) as JT
           WHERE trivial_correlation_cond)

EXISTS-to-IN conversion code was unable to see that the subquery will
still be correlated after the trivial_correlation is removed, which
eventually caused a crash due to inability to construct a query plan.

Fixed by making Item_subselect::walk() also walk arguments of Table
Functions.

Use a function instead of lambda

(TODO: squash this with previous patch for MDEV-25346)

add_table_to_list - remove the table_function argument.

mysql_derived_prepare() sets Name_resolution_context::outer_context=NULL
for the WHERE clause's context.
Do the same for all ON expressions, too.

Add testcase