mariadb/mysql-test/suite/sql_sequence
Dave Gosselin 5001300bd4 MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE
We now allow multitable queries with order by and limit, such as:
  delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
To predict what rows will be deleted, run the equivalent select:
  select t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
Additionally, index hints are now supported with single table delete statements:
  delete from t2 use index(xid) order by (id) limit 2;

This approach changes the multi_delete SELECT result interceptor to use a temporary
table to collect row ids pertaining to the rows that will be deleted, rather than
directly deleting rows from the target table(s).  Row ids are collected during
send_data, then read during send_eof to delete target rows.  In the event that the
temporary table created in memory is not big enough for all matching rows, it is
converted to an aria table.

Other changes:
  - Deleting from a sequence now affects zero rows instead of emitting an error

Limitations:
  - The federated connector does not create implicit row ids, so we to use a key
  when conditionally deleting.  See the change in federated_maybe_16324629.test
2025-02-05 10:12:27 -05:00
..
alter.result MariaDB 11.4.4 release 2024-11-08 07:17:00 +01:00
alter.test MariaDB 11.4.4 release 2024-11-08 07:17:00 +01:00
alter_notembedded.result MDEV-28548: ER_TABLEACCESS_DENIED_ERROR is missing information about DB 2022-09-30 08:48:57 +02:00
alter_notembedded.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
aria.result MDEV-13005: Fixing bugs in SEQUENCE, part 3, 2/5 2022-03-30 15:12:43 +05:30
aria.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
auto_increment.result MDEV-33836 Compute modulus correctly in sequence 2024-08-19 11:16:32 +10:00
auto_increment.test MDEV-33836 Compute modulus correctly in sequence 2024-08-19 11:16:32 +10:00
binlog.result MDEV-33938 Analyze table on sequences should be prohibited 2024-05-27 12:39:03 +02:00
binlog.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
concurrent_create.result MDEV-33836 Fix version markers in tests relating to MDEV-28152 2024-08-19 11:16:31 +10:00
concurrent_create.test MDEV-33836 Fix version markers in tests relating to MDEV-28152 2024-08-19 11:16:31 +10:00
create.result MariaDB 11.4.4 release 2024-11-08 07:17:00 +01:00
create.test MariaDB 11.4.4 release 2024-11-08 07:17:00 +01:00
debug_sync.opt sql_sequence.debug_sync fails upon server startup 2018-05-09 02:05:25 +03:00
debug_sync.result Added test case for MDEV-13029 2018-05-08 13:29:41 +03:00
debug_sync.test Added test case for MDEV-13029 2018-05-08 13:29:41 +03:00
default.result MDEV-19123 Change default charset from latin1 to utf8mb4 2024-07-11 10:21:07 +04:00
default.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
disabled.def Make SEQUENCE working with replication 2017-05-23 21:12:27 +03:00
grant.result MDEV-28548: ER_TABLEACCESS_DENIED_ERROR is missing information about DB 2022-09-30 08:48:57 +02:00
grant.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
gtid-master.opt MDEV-10139 Support for SEQUENCE objects 2017-04-07 18:09:56 +04:00
gtid-slave.opt MDEV-10139 Support for SEQUENCE objects 2017-04-07 18:09:56 +04:00
gtid.result Merge branch '10.9' into 10.10 2022-10-04 13:32:38 +02:00
gtid.test Merge branch '10.9' into 10.10 2023-08-05 20:34:09 +02:00
kill.result MDEV-16929 Assertion ... in close_thread_tables upon killing connection 2020-07-21 15:12:53 +03:00
kill.test MDEV-16929 Assertion ... in close_thread_tables upon killing connection 2020-07-21 15:12:53 +03:00
lock.result MDEV-15970 Crash when doing truncate on locked sequence 2018-05-14 10:08:05 +03:00
lock.test MDEV-15970 Crash when doing truncate on locked sequence 2018-05-14 10:08:05 +03:00
mysqldump.result Merge branch '11.5' into 11.6 2024-08-21 15:13:47 +02:00
mysqldump.test MDEV-32250 Enable --no-autocommit by default in mysqldump 2024-12-03 20:25:04 +05:30
next.result MariaDB 11.4.4 release 2024-11-08 07:17:00 +01:00
next.test MDEV-33836 Fix version markers in tests relating to MDEV-28152 2024-08-19 11:16:31 +10:00
other.result MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE 2025-02-05 10:12:27 -05:00
other.test MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE 2025-02-05 10:12:27 -05:00
partition.result MDEV-13715 Server crashes in ha_partition::engine_name 2017-10-22 20:20:32 +03:00
partition.test MDEV-13715 Server crashes in ha_partition::engine_name 2017-10-22 20:20:32 +03:00
read_only.result Write information about restart in .result 2019-04-01 19:47:24 +03:00
read_only.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
rebuild.result Merge branch 10.3 into 10.4 2021-07-02 17:17:33 +10:00
rebuild.test Merge 10.4 into 10.5 2020-04-29 15:40:51 +03:00
rename.result Fix for MDEV-15105 (memory loss with LOCK sequence) 2018-02-19 11:27:21 +02:00
rename.test Fix for MDEV-15105 (memory loss with LOCK sequence) 2018-02-19 11:27:21 +02:00
replication-master.opt MDEV-10139 Support for SEQUENCE objects 2017-04-07 18:09:56 +04:00
replication-slave.opt MDEV-10139 Support for SEQUENCE objects 2017-04-07 18:09:56 +04:00
replication.result MDEV-33145 Add FLUSH GLOBAL STATUS 2024-05-27 12:39:03 +02:00
replication.test MDEV-33145 Add FLUSH GLOBAL STATUS 2024-05-27 12:39:03 +02:00
replication_drop.result Fix for MDEV-15812 Assert in SEQUENCE when forcing STATEMEMT format 2018-05-06 19:39:48 +03:00
replication_drop.test Fix for MDEV-15812 Assert in SEQUENCE when forcing STATEMEMT format 2018-05-06 19:39:48 +03:00
replication_mixed.result MDEV-16234 CREATE TABLE .. SELECT LASTVAL breaks replication 2018-05-22 18:33:18 +03:00
replication_mixed.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
setval.result MDEV-33836 Compute modulus correctly in sequence 2024-08-19 11:16:32 +10:00
setval.test MDEV-33836 Compute modulus correctly in sequence 2024-08-19 11:16:32 +10:00
slave_nextval.result MDEV-14092 NEXTVAL fails on slave 2017-10-19 13:25:02 +03:00
slave_nextval.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
temporary.result Added test case for MDEV-13007 ALTER .. ENGINE on temporary sequence may go wrong 2018-05-07 16:39:53 +03:00
temporary.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00
view.result Merge pull request #1261 from robertbindar/mdev-17978 2019-12-05 18:26:52 +02:00
view.test MDEV-31003: Second execution for ps-protocol 2023-07-26 17:15:00 +07:00