Commit a11192689cd for woocommerce
commit a11192689cdd7eee5da495c31df32dbf135163b8
Author: Mike Jolley <mike.jolley@me.com>
Date: Tue Jun 9 14:00:04 2026 +0100
Fix OrdersTableQuery LIMIT for SQLite compatibility (#64396)
* Fix OrdersTableQuery LIMIT for SQLite compatibility
When limit=-1 is requested, the previous implementation emitted
`LIMIT 0, 18446744073709551615` (MySQL's unsigned bigint max). That
literal overflows SQLite's signed 64-bit INTEGER type and triggers a
"datatype mismatch" error under the WordPress SQLite integration.
Mirror WP_Query's `nopaging` behavior instead: when row_count is -1 and
there is no offset, omit the LIMIT clause entirely. For the
offset-to-end case (limit=-1 + positive offset) — which MySQL still
requires a row count for — emit PHP_INT_MAX, which fits within both
MySQL's unsigned bigint range and SQLite's signed 64-bit INTEGER range.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
* Skip COUNT query and pagination calc when row_count is unlimited
When limit=-1 the LIMIT clause is omitted but $this->limits remained
truthy, causing run_query() to issue a redundant COUNT query and
compute a negative max_num_pages from `ceil(N / -1)`. Gate the
pagination branch on a positive row_count and source the divisor from
$this->limits to keep it consistent with build_query().
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
* Fix found order count for unbounded offset queries
* Reconcile SQLite LIMIT fix with trunk query optimization (#65413)
Trunk #65413 added a groupby shortcut and a strip-LIMIT block that
references self::MYSQL_MAX_UNSIGNED_BIGINT. This branch removes that
constant, so after rebasing the reference dangled (undefined constant).
Remove the now-redundant strip-LIMIT block (the PHP_INT_MAX path already
omits the LIMIT clause for unlimited/no-offset queries, and additionally
covers unlimited+offset which #65413 left emitting a SQLite-incompatible
value). Keep #65413's groupby optimization. Unify $this->limits access in
build_query() and run_query() for consistency.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 4.7 <noreply@anthropic.com>
diff --git a/plugins/woocommerce/changelog/fix-orders-query-sqlite-limit b/plugins/woocommerce/changelog/fix-orders-query-sqlite-limit
new file mode 100644
index 00000000000..18fffc0a084
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-orders-query-sqlite-limit
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Omit the LIMIT clause in OrdersTableQuery when an unlimited (-1) row count is requested, matching WP_Query nopaging behavior. Fixes a datatype mismatch error when the orders query runs against a SQLite database.
diff --git a/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php b/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
index 7b9241f2ada..0c4313c0e98 100644
--- a/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
+++ b/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableQuery.php
@@ -31,13 +31,6 @@ class OrdersTableQuery {
*/
public const REGEX_SHORTHAND_DATES = '/([^.<>]*)(>=|<=|>|<|\.\.\.)([^.<>]+)/';
- /**
- * Highest possible unsigned bigint value (unsigned bigints being the type of the `id` column).
- *
- * This is deliberately held as a string, rather than a numeric type, for inclusion within queries.
- */
- private const MYSQL_MAX_UNSIGNED_BIGINT = '18446744073709551615';
-
/**
* Names of all COT tables (orders, addresses, operational_data, meta) in the form 'table_id' => 'table name'.
*
@@ -856,9 +849,20 @@ class OrdersTableQuery {
$limits = '';
if ( ! empty( $this->limits ) && count( $this->limits ) === 2 ) {
- list( $offset, $row_count ) = $this->limits;
- $row_count = -1 === $row_count ? self::MYSQL_MAX_UNSIGNED_BIGINT : (int) $row_count;
- $limits = 'LIMIT ' . (int) $offset . ', ' . $row_count;
+ $offset = (int) ( $this->limits[0] ?? 0 );
+ $row_count = (int) ( $this->limits[1] ?? 0 );
+
+ if ( -1 === $row_count ) {
+ // For "unlimited" (-1) queries, mirror WP_Query's nopaging behavior and
+ // omit the LIMIT clause. When an offset is specified, MySQL requires a
+ // row count, so emit PHP_INT_MAX — portable across MySQL (well below
+ // its unsigned bigint max) and SQLite (its signed 64-bit max).
+ if ( $offset > 0 ) {
+ $limits = 'LIMIT ' . $offset . ', ' . PHP_INT_MAX;
+ }
+ } else {
+ $limits = 'LIMIT ' . $offset . ', ' . $row_count;
+ }
}
// GROUP BY.
@@ -904,9 +908,6 @@ class OrdersTableQuery {
if ( '' === $join && "{$orders_table}.id" === $fields ) {
$groupby = '';
}
- if ( 'LIMIT 0, ' . self::MYSQL_MAX_UNSIGNED_BIGINT === $limits ) {
- $limits = '';
- }
$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";
@@ -1440,9 +1441,14 @@ class OrdersTableQuery {
return;
}
- if ( $this->limits ) {
+ $offset = (int) ( $this->limits[0] ?? 0 );
+ $row_count = (int) ( $this->limits[1] ?? 0 );
+
+ if ( $row_count > 0 || $offset > 0 ) {
$this->found_orders = absint( $wpdb->get_var( $this->count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
- $this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
+ $this->max_num_pages = $row_count > 0
+ ? (int) ceil( $this->found_orders / $row_count )
+ : 0;
} else {
$this->found_orders = count( $this->orders );
}
diff --git a/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableDataStoreTests.php b/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableDataStoreTests.php
index 31dc525f432..f585d46763d 100644
--- a/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableDataStoreTests.php
+++ b/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/OrdersTableDataStoreTests.php
@@ -1296,6 +1296,16 @@ class OrdersTableDataStoreTests extends \HposTestCase {
);
$this->assertCount( 12, $query->orders, 'A limit of -1 can successfully be combined with an offset.' );
$this->assertEquals( array_slice( $test_orders, 18 ), $query->orders, 'The expected dataset is supplied when an offset is combined with a limit of -1.' );
+ $this->assertEquals(
+ 30,
+ $query->found_orders,
+ 'A limit of -1 combined with an offset still calculates all found orders.'
+ );
+ $this->assertEquals(
+ 0,
+ $query->max_num_pages,
+ 'A limit of -1 combined with an offset is treated as unpaged.'
+ );
$query = new OrdersTableQuery( array( 'limit' => 5 ) );
$this->assertCount( 5, $query->orders, 'Limits are respected when applied.' );