Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
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.
Attachments
Issue Links
- causes
-
MDEV-35568 reintroduce delete_while_scanning to multi_delete
-
- Closed
-
-
MDEV-35848 Multi-table DELETE with order by...limit works incorrect
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
What about hints? Single table delete doesn't work with hints like "use index"
MariaDB [test]> DELETE FROM test1 use index(ix_id) WHERE ID=1;
|
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 'use index(ix_id) WHERE ID=1' at line 1
|
|
|
MariaDB [test]> DELETE FROM test1 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 'use index(ix_id) WHERE ID=1 LIMIT 10' at line 1
|
MariaDB [test]>
|
If we can try to use alias then it works.
MariaDB [test]> DELETE t1.* FROM test1 t1 use index(ix_id) WHERE ID=1;
|
Query OK, 0 rows affected (0.001 sec)
|
But then LIMIT doesn't work with aliases.
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]>
|
So what if we want to use both Hints and LIMIT with Delete?
We could probably support with very little work:
DELETE t1.* FROM test1 t1 use index(ix_id) WHERE ID = 1 limit 10;
When there is only one table from which we delete.
For this case we only have to add limit support to multi_delete::send_data()
and add some checks that only one table is used.
In theory it is possible to add hints to single table delete, but I think for this case it is easier to add limit to multi-table delete
igor Do I remember correctly that you made switching to SELECT-Like DELETE in some conditions, maybe we can do the same in case hints and LIMIT?
There are two ways to solve this request:
- Add hints to single table delete and single table update.
- Add support for ORDER BY and LIMIT to multi table delete syntax using only one table. We need to also support ORDER
BY as otherwise the statement is is not replication safe.
I am leaning to adding hints to single table delete and single table update as this is easier to do and should solve the problem for the user
I looked on the support tickets, it is clear that user need ORDER BY (and use USE INDEX instead) to ordering for LIMIT
BTW maybe it is really syntax error becouse this is working:
create table t1 (id int primary key);
|
DELETE FROM t1 WHERE ID=1 ORDER BY id LIMIT 10;
|
drop table t1;
|
and hints are working:
create table t2 (id int, index xid(id));
|
create table t1 (id int primary key);
|
DELETE t1.* FROM t1,t2 use index(xid);
|
DELETE t2.* FROM t2 use index(xid);
|
drop table t1, t2;
|
So it can be 2 separate asks:
- add LIMIT / ORDER BY to multi delete statements
- add hints support to single table delete
niljoshi which of two or both the user need?
Looking on the examples it looks like first part is needed, but I will not be surprised if both are
Actually hints are for joining tables, so I suspect they are not useful in single table operation.
psergei Can be hints (like USE INDEX(XXX)) be somehow useful for singletable SELECT/UPDATE/DELETE (for example used for WHERE condition resolwing , i.e. used table access method) ?
update from psergei : the hints can be useful for single table operation
Hi sanja, customer was looking for single table delete syntax with using index and psergei also confirmed that hints can be useful for single table operation too so I think it would be great if both single delete and multi-delete scenario will be fixed.
niljoshi why then all examples made by you in the description are multidelete syntax? Please next time make your part of work of descripting what is really needed.
sanja The original issue was about LIMIT not working together with a table alias in a single table statement. The original query also had a hint (USE INDEX) in the same single table DELETE statement.
So both should work with LIMIT.
bb-11.4-MDEV-30469 contains what I have done
it require to bring to delete the same pocessing as in update.
Overall the patch looks very good but there are some things which better to fix (in random order):
1. DA_EMPTY means there was no EOF/OK or error but it should be (even if nothing was deleted).
It should be fixed to make DELETE sending OK (and the assert returned it its place).
2. It is better to make refactoring as a separate commits before the patch, let say as
prerequisites 
(I like the fixes)
3. You better setup visibility of unneeded spaces in editor or check "git citool"
(it highlight them, as well as git show in terminal) here there are speces
^on empty line:
+ goto err;
+ }
+
+ DBUG_ASSERT(!tmp_table->field[0]->is_null());
+ String rowid;
+ tmp_table->field[0]->val_str(&rowid);
4. It would be nice to keep delete_while_scanning functionality, it is more efficient
to delete "on the fly" if our "SELECT" allow it.
Hi sanja,
Thank you for your review feedback, I appreciate it a lot. I fixed (1) and (3).
I think for (4) it would be best to re-introduce it later because the changes will be intrusive. I tend to think that the new functionality is more valuable overall than the specific optimization, but maybe we have some perf data somewhere? Alternatively, I could make a change where if the query has no ORDER BY, no LIMIT, and no hints, it uses the prior implementation with on-the-fly deletion. But that seems awkward for long term maintenance.
For (2), would you like me to split the one commit into two commits now (or just remember for the future)?
Thanks,
Dave
It is better split it now should not took a lot of time I hope.
if you want to postpone (4) then it is better to see jira issue connected to this one with task description and high enough priority (I suspect we will need it really soon because performance degradation)
OK no problem, I will split it now. I will create a new task for (4) and link it to this issue.
As asan we have cleared, so please add the ASSERT, change name of one of the commits (or both) and send it to testing.
It looks like the wrong query plan is being used for cases with order by NULL:
Testcase:
CREATE TABLE t1 (a INT, b INT, |
PRIMARY KEY (a), |
KEY i2(a,b)); |
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); |
ANALYZE TABLE t1; |
|
|
EXPLAIN format=json SELECT a FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2; |
EXPLAIN format=json DELETE FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2; |
For these queries we have the following plans (same for select and delete):
EXPLAIN format=json SELECT a FROM t1 USE INDEX (i2) ORDER BY (a) LIMIT 2; |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.006896702, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"access_type": "index", |
"key": "i2", |
"key_length": "9", |
"used_key_parts": ["a", "b"], |
"loops": 1, |
"rows": 2, |
"cost": 0.006896702, |
"filtered": 100, |
"using_index": true |
}
|
}
|
]
|
}
|
}
|
EXPLAIN format=json DELETE FROM t1 USE INDEX (i2) ORDER BY (a) LIMIT 2; |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.006896702, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"access_type": "index", |
"key": "i2", |
"key_length": "9", |
"used_key_parts": ["a", "b"], |
"loops": 1, |
"rows": 2, |
"cost": 0.006896702, |
"filtered": 100 |
}
|
}
|
]
|
}
|
}
|
But for next queries (with order by NULL) plans for select and delete are differ. In spite of "USE INDEX (i2)" is set, index is ignored for delete:
EXPLAIN format=json SELECT a FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2; |
EXPLAIN format=json DELETE FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2; |
Plans:
EXPLAIN format=json SELECT a FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2; |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.006896702, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"access_type": "index", |
"key": "i2", |
"key_length": "9", |
"used_key_parts": ["a", "b"], |
"loops": 1, |
"rows": 8, |
"cost": 0.006896702, |
"filtered": 100, |
"using_index": true |
}
|
}
|
]
|
}
|
}
|
EXPLAIN format=json DELETE FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2; |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.006896702, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"access_type": "ALL", |
"loops": 1, |
"rows": 8, |
"cost": 0.006896702, |
"filtered": 100 |
}
|
}
|
]
|
}
|
}
|
ORDER BY NULL means basically "do not order"...
I don't think SELECT actually follows the hint here. I modify the query: add and use a new column so that the SELECT cannot use an index-only full index scan. And it stops using it:
alter table t1 add z int;
|
EXPLAIN format=json SELECT a,z FROM t1 USE INDEX (i2) ORDER BY NULL LIMIT 2;
|
{
|
"query_block": {
|
"select_id": 1,
|
"cost": 0.0121588,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"loops": 1,
|
"rows": 8,
|
"cost": 0.0121588,
|
"filtered": 100
|
}
|
}
|
]
|
}
|
}
|
The same plan as with DELETE. DELETE cannot use "index-only" plans (not sure about the exact rationale behind this, but the code explicitly disables them).
Yes, I saw this in JOIN::optimize_stage2 and assumed it was intentional:
/*
|
If we are using ORDER BY NULL or ORDER BY const_expression,
|
return result in any order (even if we are using a GROUP BY)
|
*/
|
if (!order && org_order)
|
skip_sort_order= 1;
|
FWIW MySQL shows NULL first in a resultset (by default).
The documentation you quoted did not say you cannot use LIMIT with multiple tables, it said you cannot use LIMIT with multi-table DELETE syntax.
is the syntax for multi-table DELETE. The single-table DELETE syntax would've been