Support UPDATE LIMIT

[adamziel]

WordPress cron fails because it cannot execute this query:

UPDATE wp_posts SET post_password = '971f813efffc0aa3250b', post_modified_gmt = '2023-03-24 15:41:06', post_modified = '2023-03-24 15:41:06' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2023-03-24 15:41:06' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25

The problem is with the trailing LIMIT 25. In SQLite, it should be split into a SELECT query and an UPDATE query – just like DELETE from a,b is currently handled.

[MocioF]

I think there is a problem with LIMIT on DELETE queries too:
DELETE FROM mytable WHERE mydatefield < DATE_SUB( NOW(), INTERVAL 10 DAY) LIMIT 1000 does trigger a sintax error, while
DELETE FROM mytable WHERE mydatefield < DATE_SUB( NOW(), INTERVAL 10 DAY), works

Error message was: SQLSTATE[HY000]: General error: 1 near "LIMIT": syntax error.

[kozer]

I'm currently come up with this issue while I was working at https://github.com/Automattic/dotcom-forge/issues/6479. Can you please assign it to me?

[adamziel]

Capturing notes from my conversation with @kozer

Issue:

• WordPress cron fails because it cannot execute UPDATE queries with a trailing LIMIT clause.
• SQLite requires separating the logic into two queries: a SELECT to fetch IDs and a subsequent UPDATE using those IDs in a WHERE clause.

Implementation Approach:

1. Leverage the WP_SQLite_Translator class within the plugin.
2. Track the table name and WHERE clause during query parsing.
3. Upon encountering LIMIT:
   • Option A: Construct a SELECT query with the same WHERE clause and LIMIT, followed by an UPDATE with WHERE ID IN (SELECT results).
   • Option B (alternative): Rewrite the UPDATE into a single statement using WHERE ID IN (SELECT ... LIMIT 25).

Considerations:

• Option B might not work for tables without a primary key (PK) column.
• A fallback approach could utilize the unique 64-bit rowid available in most SQLite tables.

$rewriter API:

• This API is used for token manipulation during query translation.
• It allows consuming, skipping, and peeking at tokens (objects with value, type, and flags).
• Offers methods for injecting custom tokens and retrieving the modified query.

Challenge:

• LIMIT resides at the query's end, requiring backtracking or preemptive measures.
• A simpler solution involves always initiating a nested SELECT after UPDATE ... WHERE, regardless of LIMIT.

[kozer]

Update: Created PR #93