MySQL/MariaDB: add support for ORDER BY and LIMIT clauses in UPDATE/DELETE statements

[capcom6]

This PR adds support for ORDER BY and LIMIT clauses in DELETE statements specifically for MySQL and MariaDB databases.

Changes:

• Extract orderQuery and limitOffsetQuery from SelectQuery, see query_base.go and query_select.go
• Include orderQuery and limitOffsetQuery in DeleteQuery, see query_delete.go
• Implement Order and Limit methods on DeleteQuery, see query_delete.go
• Add dbtest cases 168-170, see query_test.go

Example usage:

db.NewDelete().Model(new(Model)).WherePK().Order("id")
db.NewDelete().Model(new(Model)).WherePK().Order("id").Limit(1)

This feature enhances the flexibility of DELETE operations in MySQL and MariaDB, allowing for more precise control over which rows are deleted and in which order.

Questions: I have some questions about which I'm unsure:

1. PR #1009 has allowed Limit without Order for MSSQL. As a side effect, it has introduced a dependency between these parts of the query, which in my PR is separated. Before #1009, my implementations fully moved ORDER and LIMIT...OFFSET parts into reusable and independent parts. Now I had to "override" appendQuery in SelectQuery to support this MSSQL behavior.

2. Because only MySQL/MariaDB supports syntax with LIMIT and ORDER BY for DELETE, I'm unsure where to return an error for other dialects. For now, I set err in Order, OrderExpr, and Limit methods of DeleteQuery.

3. This syntax can't be used with multiple tables. The only place to validate this that I found is AppendQuery. Is this correct?

Additional context:

• For now, I implemented this feature by directly checking the dialect and not by features because, as far as I know, only MySQL/MariaDB supports this syntax..

References:

• MySQL 5.7
• MySQL 8.0
• MySQL 9.1
• MariaDB

[j2gg0s]

Thanks for your work.

I only have a rough understanding of the background for now and don’t have any well-developed ideas yet. But I have some simple ideas:

1. MySQL's Update also support ORDER BY. Maybe we should add a feature flag to represent it.
2. Where ORDER is supported, LIMIT&OFFSET is always supported as well, so we might not need to split it into two class in our code.

[capcom6]

@j2gg0s , thanks for your comment.

I apologize for not mentioning the background earlier. The syntax DELETE ... ORDER BY ... LIMIT ... RETURNING ... can be used to implement the Transactional outbox pattern, allowing for reading messages in a single query with deletion and specified order. Additionally, ORDER BY... in DELETE and UPDATE statements can be helpful when working with relations to control the order of changes and prevent foreign key constraint violations.

Regarding your ideas:

1. I've added support for this syntax to the UPDATE statement and introduced feature flags for syntax support in both UPDATE and DELETE.
2. Agreed. I've merged the structs and moved #1009 from SelectQuery to orderLimitOffsetQuery.

P.S. For now, I've created distinct commits for changes to simplify the review process. I'll edit the commit history later.

[j2gg0s]

Looking forward to you squashing the commits and officially submitting the changes.