in my_print_defaults

Analysis: --defaults* option is recognized anywhere in the commandline
instead of only at the beginning because handle_options() recognizes
options in any order.
Fix: use get_defaults_options() which recognizes --defaults* options only at
the beginning. After this is done, we only want to recognize other options
given in any order which can be done using handle_options(). So only skip
--defaults* options and pass rest of them to handle_options().
Also, removed -e, -g and -c because only my_print_defaults supports them.

take descending indexes into account when generating a query

take descending indexes into account when generating a query

also fixes MDEV-27617

use index_first for DESC keys

also fix handler::get_auto_increment()

when searching for the last auto-inc value, it's HA_READ_PREFIX_LAST for
the ASC keypart, but HA_READ_PREFIX for the DESC one

also fixes MDEV-27585

MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results

detect if merge children are "differently defined" regarding ASC/DESC

just like in SHOW KEYS, suppress DESC in SHOW CREATE if not HA_READ_ORDER

replace the assert with an if().

asserts should not be used on the input (even without DESC indexes
the table could've been corrupted)

MDEV-27309 Server crash or ASAN memcpy-param-overlap upon INSERT into Aria/MyISAM table with DESC key

MyiSAM and Aria, indexes with prefix compression, where the first keypart
could be NULL - in this case they didn't expect the next key after the
not NULL key to be NULL.

Expect the first keypart of the next key to have zero length even
if store_not_null==1, this combination means keypart is NULL,
don't pack it.

also fixes MDEV-27340

optimized prefix search didn't take into account descending indexes

also fixes MDEV-27330

MDEV-27396 DESC index attribute remains in Archive table definition, despite being apparently ignored

disallow descending indexes in archive

disallow descending indexes in connect

disallow descending indexes in mroonga

disallow descending indexes in rocksdb

ROR-index_merge relies on Rowid-ordered-retrieval property: a ROR scan,
e.g. a scan on equality range

  tbl.key=const

should return rows ordered by their Rowid. Also, handler->cmp_ref() should
compare rowids according to the Rowid ordering.

When the table's primary key uses DESC keyparts, ROR scans return rows
according to the PK's ordering.

But ha_innobase::cmp_ref() compared rowids as if PK used ASC keyparts.
This caused wrong query results with index_merge.

Fixed this by making ha_innobase::cmp_ref() compare according to the PK
defintion, including keypart's DESC property.

Make QUICK_RANGE_SELECT::cmp_next() aware of reverse-ordered key parts.

(QUICK_RANGE_SELECT::cmp_prev() uses key_cmp() and so it already works
correctly)

When printing a range into optimizer trace, print DESC for columns
that are reverse-ordered, for example:

   "(4) <= (key1 DESC) <= (2)"

Instead, Get the "is_ascending" value from the array of KEY_PART
structures that describes the [pseudo-]index that is being analyzed.

- Code cleanup
- Disable "Using index for GROUP BY" over indexes with DESC keyparts

Extend the fix for MDEV-25858 to handle non-reverse-ordered ORDER BY:

If test_if_skip_sort_order() decides to use an index to produce rows
in the required ordering, it should disable "Range Checked for Each Record".

The fix needs to be backported to earlier versions.

Make the Range Optimizer support descending index key parts.

We follow the approach taken in MySQL-8.

See HowRangeOptimizerHandlesDescKeyparts for the description.

* preserve DESC index property in the parser
* store it in the frm (only for HA_KEY_ALG_BTREE)
* read it from the frm
* show it in SHOW CREATE
* skip DESC indexes in opt_range.cc and opt_sum.cc
* ORDER BY test

This includes a fix of MDEV-27432.

This is loosely based on the InnoDB changes in
mysql/mysql-server@97fd8b1b6993340b361fa7f85da86a308f0b5e0c
that I had developed in 2015 or 2016.

For each B-tree key field, we will allow a flag ASC/DESC to be associated.
When PRIMARY KEY fields are internally appended to secondary indexes,
the ASC/DESC attribute will be inherited, so that covering index scans
will work as expected.

Note: Until the subsequent commit, the DESC attribute will be ignored
(no HA_REVERSE_SORT flag will be written to .frm files).

dict_field_t::descending: A new flag to denote descending order.

cmp_data(), cmp_dfield_dfield(): Add a new parameter descending.

cmp_dtuple_rec(), cmp_dtuple_rec_with_match(): Add a parameter "index".

dtuple_coll_eq(): Replaces dtuple_coll_cmp().

cmp_dfield_dfield_eq_prefix(): Replaces cmp_dfield_dfield_like_prefix().

dict_index_t::is_btree(): Check whether the index is a regular
B-tree index (not SPATIAL, FULLTEXT, or the ibuf.index,
or a corrupted index.

btr_cur_search_to_nth_level_func(): Only attempt to use
the adaptive hash index if index->is_btree().
This function may also be invoked on ibuf.index, and
cmp_dtuple_rec_with_match_bytes() will no longer work on ibuf.index
because it assumes that the index and record fields exactly match.
The ibuf.index is a special variadic index tree.

Thanks to Thirunarayanan Balathandayuthapani for fixing some bugs:
MDEV-27439, MDEV-27374/MDEV-27445.

* combine two test files with seemingly the same name
* comments

innodb_flush_log_on_trx_commit=2 means we want to write
redo using filesystem buffering, and not  flushing each time.

Also simplify code, remove gotos, and comment on a bug
in mariabackup that produces redo of odd sizes, that must
be worked around by forcing file to be buffered.

Changes:

1. Enabling IN/OUT/INOUT mode for sql_mode=DEFAULT,
   adding tests for sql_mode=DEFAULT based by mostly
   translating compat/oracle.sp-inout.test to SQL/PSM
   with minor changes (e.g. testing trigger OLD.column and
   NEW.column as IN/OUT parameters).

2. Removing duplicate grammar:
   sp_pdparam and sp_fdparam implemented exactly the same syntax after
   - the first patch for MDEV-10654 (for sql_mode=ORACLE)
   - the change #1 from this patch (for sql_mode=DEFAULT)
   Removing separate rules and adding a single "sp_param" rule instead,
   which now covers both PRDEDURE and FUNCTION parameters
   (and CURSOR parameters as well!).

3. Adding a helper rule sp_param_name_and_mode, which is a combination
   of the parameter name and the IN/OUT/INOUT mode. It allows to simplify
   the grammer a bit.

4. The first patch unintentionally allowed IN/OUT/INOUT mode
  to be specified in CURSOR parameters.
  This is good for the IN keyword - it is allowed in PL/SQL CURSORs.
  This is not good the the OUT/INOUT keywords - they should not be allowed.
  Adding a additional symantic post-check.

Problem: Currently stored function does not support IN/OUT/INOUT parameter qualifiers.
This is needed for Oracle compatibility (sql_mode = ORACLE).

Solution: Implemented parameter qualifier support to CREATE FUNCTION (reference: CREATE PROCEDURE)
Implemented return by reference for OUT/INOUT parameters in execute_function() (reference: execute_procedure())

Files changed:
sql/sql_yacc.yy: Added IN, OUT, INOUT parameter qualifiers for CREATE FUNCTION.
sql/sp_head.cc: Added input and output parameter binding for IN/OUT/INOUT parameters in execute_function() so that OUT/INOUT can return by reference.
sql/share/errmsg-utf8.txt: Added error message to restrict OUT/INOUT parameters while function being called from SQL query.
mysql-test/suite/compat/oracle/t/sp-inout.test: Added test cases
mysql-test/suite/compat/oracle/r/sp-inout.result: Added test results

Reviewed-by: iqbal@hasprime.com

Add NULL check to SPIDER_OPTION_STR_LIST.

Remove the dead-code, in Spider, which is related to the Spider's
HandlerSocket support. The code has been disabled for a long time
and it is unlikely that the code will be enabled.

We introduce engine-defined attributes to specify remote data nodes.

The engine attributes do not cover all the existing DSN parameters
because most of them need not be specified at the table level.
We introduce the following three attributes: REMOTE_SERVER,
REMOTE_DATABASE, REMOTE_TABLE.

One cannot specify both DSN parameter, in COMMENT or CONNECT, and
engine-defined attribute that are for the same SPIDER_SHARE attribute.
For example, Spider returns an error if both COMMENT='table "t1"'
and REMOTE_TABLE="t2" are specified for a single Spider table or
a single partition in a Spider table.

Make it possible to specify engine-defined attributes on partitions
as well as tables.

If an engine-defined attribute is only specified at the table level,
it applies to all the partitions in the table.
This is a backward-compatible behavior.

If the same attribute is specified both at the table level and the
partition level, the per-partition one takes precedence.
So, we can consider per-table attributes as default values.

One cannot specify engine-defined attributes on subpartitions.

Implementation details:

* We store per-partition attributes in the partition_element class
  because we already have the part_comment field, which is for
  per-partition comments.

* In the case of ALTER TABLE statements, the partition_elements in
  table->part_info is set up by mysql_unpack_partition().
  So, we parse per-partition attributes after the call of the function.

More tests depending on 'Completed resizing buffer pool.' output

Cleaned up the log messages as suggested, with a minor code
formatting change.

On bullet point 13, I decided to not include timestamp in output
message. In most (all?) cases, the output goes to the log file,
which has timestamp already.