[MCOL-690] order by clause in dml deletes not supported Created: 2017-04-28 Updated: 2021-01-17 Resolved: 2021-01-17 |
|
| Status: | Closed |
| Project: | MariaDB ColumnStore |
| Component/s: | DMLProc, ExeMgr |
| Affects Version/s: | 1.0.8 |
| Fix Version/s: | N/A |
| Type: | New Feature | Priority: | Major |
| Reporter: | Seungmin Yang | Assignee: | Todd Stoffel (Inactive) |
| Resolution: | Won't Do | Votes: | 0 |
| Labels: | None | ||
| Environment: |
RHEL 6.6 |
||
| Epic Link: | ColumnStore Compatibility Improvements |
| Description |
|
I want to delete rows using order by clause, but MariaDB says it is not currently supported. My test cases are belows: MariaDB [input_test]> delete from test_log where stime < '2017-03-19 00:00:00' limit 1000; MariaDB [input_test]> delete from test_log where stime < '2017-03-19 00:00:00' order by stime limit 1000; MariaDB [input_test]> delete from test_log order by stime asc limit 1000; MariaDB [input_test]> delete from test_log where stime in (select * from test_log order by stime limit 1000); Are you willing to support this kind of query later? Or is there other way to use delete query with order by clause? |
| Comments |
| Comment by Andrew Hutchings (Inactive) [ 2017-04-28 ] |
|
DML writes with ORDER BY won't be supported in 1.0 or 1.1. This is because we currently use the MariaDB server to execute ORDER BY (in most cases) before returning the data to the user. By the time the ColumnStore primitive engine has the delete query it is too late for the MariaDB server to execute the ORDER BY. We have an idea on how to resolve this in future versions but it is a non-trivial task. As for the last query, ColumnStore cannot support this until MariaDB does. I can't think of a reliable way to do what you are trying to do in ColumnStore but if your deletes are a lot larger and the data is loaded in as close to stime order as possible you can drop the partitions of a certain date range which is very fast. |
| Comment by David Thompson (Inactive) [ 2017-04-28 ] |
|
Please see: https://mariadb.com/kb/en/mariadb/columnstore-partition-management/ for details on partition based deletes, this is the best way to perform large scale deletes and data management as it actually deletes the partition & extent files. DML deletes are soft deletes. |