Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.

Delete doesn't work with LIMIT and Hints and I don't find any specific reason behind this. Even not in documentation.

MariaDB [test]> DELETE t1.* FROM test1 t1 WHERE ID=1;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> DELETE t1.* FROM test1 t1 WHERE ID=1 LIMIT 10;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 10' at line 1

MariaDB [test]> 

MariaDB [test]> DELETE t1.* FROM test1 t1 use index(ix_id) WHERE ID = 1 limit 10;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 10' at line 1

MariaDB [test]> 

Doc says, "For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT> cannot be used." But here there are no multiple tables.
https://mariadb.com/kb/en/delete/

Patch details

(based on a talk with Sanja):

The patch adds two pieces of functionality:

1. Index hints for UPDATE/DELETE
2. ORDER BY LIMIT support for multi-table DELETE (UPDATEs are already supported).

1. Index Hints.

Single-table mysql_update/mysql_delete do not have the code to handle
hints.

The patch switches to multi-table update/delete code path whenever
the updated table uses hints (Yes. )

2. ORDER BY LIMIT support for multi-table DELETE

2.1 Multi-table UPDATE already supports ORDER BY LIMIT

Multi-table UPDATE supports "buffered" operation:

• first, collect rowids of rows to be updated and new column values.
• then, sort the resultset and apply LIMIT if necessary.
• then, read the temporary table and apply the updates.

Initially, this was done to avoid the "Halloween problem" for UPDATE without ORDER BY.
It also made it easy to support ORDER BY ... LIMIT. It was done in MDEV-13911, https://github.com/MariaDB/server/commit/26ff92f7ac2dc373769b8053e936e4593a2ee302.
Note that there are no restrictions on which tables are updated, or which tables can be referred from ORDER BY.

2.2 Adding ORDER BY ...LIMIT support to multi-table DELETE

Multi-table DELETE also supports a smaller variant of buffered mode (as it doesn't need to store the updated column values) It doesn't take into account that ORDER BY ... LIMIT code can put the row combinations into a temporary table and then sort it.

Sanja's patch tries to fix this but is not finished.