Commit 3212874a847 for woocommerce

commit 3212874a847102a7f7a3602e20dc9413715ab703
Author: Mike Jolley <mike.jolley@me.com>
Date:   Thu Oct 22 12:33:50 2026 +0100

    Fix pending order sync checks with temporary tables (#69864)

    * Fix pending order sync checks with temporary tables

    * Add changelog entry for temporary-table order sync checks

    * Improve pending order existence checks

    * Explain prepared SQL suppressions in order sync checks

diff --git a/plugins/woocommerce/changelog/fix-53091-temporary-order-sync-tables b/plugins/woocommerce/changelog/fix-53091-temporary-order-sync-tables
new file mode 100644
index 00000000000..104ae72ea4e
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-53091-temporary-order-sync-tables
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Prevent database errors when checking order synchronization with temporary tables.
diff --git a/plugins/woocommerce/src/Internal/DataStores/Orders/DataSynchronizer.php b/plugins/woocommerce/src/Internal/DataStores/Orders/DataSynchronizer.php
index 147eb413462..951135226f9 100644
--- a/plugins/woocommerce/src/Internal/DataStores/Orders/DataSynchronizer.php
+++ b/plugins/woocommerce/src/Internal/DataStores/Orders/DataSynchronizer.php
@@ -589,25 +589,23 @@ $limit_clause",
 			$operator = '<';
 		}

-		$sql = $wpdb->prepare(
+		$changed_orders_count_sql = $wpdb->prepare(
 			"
-SELECT(
-	COALESCE(($missing_orders_count_sql), 0)
-	+
-	(SELECT COUNT(1) FROM (
-		SELECT orders.id FROM $orders_table orders
-		JOIN $wpdb->posts posts on posts.ID = orders.id
-		WHERE
-		  posts.post_type IN ($order_post_type_placeholder)
-		  AND orders.date_updated_gmt $operator posts.post_modified_gmt
-	) x)
-) count",
+SELECT $count_clause FROM $orders_table orders
+JOIN $wpdb->posts posts ON posts.ID = orders.id
+WHERE
+  posts.post_type IN ($order_post_type_placeholder)
+  AND orders.date_updated_gmt $operator posts.post_modified_gmt
+$limit_clause",
 			$order_post_types
 		);
 		// phpcs:enable

-		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
-		$pending_count = (int) $wpdb->get_var( $sql );
+		// MySQL cannot reference the same temporary table twice in one query.
+		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query prepared above.
+		$pending_count = (int) $wpdb->get_var( $missing_orders_count_sql );
+		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query prepared above.
+		$pending_count += (int) $wpdb->get_var( $changed_orders_count_sql );

 		$deleted_from_table = $this->get_current_deletion_record_meta_value();

diff --git a/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/DataSynchronizerTests.php b/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/DataSynchronizerTests.php
index 04c2cd54b0f..3bb990120b5 100644
--- a/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/DataSynchronizerTests.php
+++ b/plugins/woocommerce/tests/php/src/Internal/DataStores/Orders/DataSynchronizerTests.php
@@ -99,6 +99,50 @@ class DataSynchronizerTests extends \HposTestCase {
 		parent::tearDown();
 	}

+	/**
+	 * @testdox Should count and detect missing and changed orders when HPOS uses a temporary table.
+	 * @testWith [false, true]
+	 *           [true, true]
+	 *           [false, false]
+	 *           [true, false]
+	 *
+	 * @param bool $hpos_is_authoritative Whether HPOS is authoritative.
+	 * @param bool $full_count Whether to request the count or check for pending orders.
+	 */
+	public function test_pending_sync_with_temporary_orders_table( bool $hpos_is_authoritative, bool $full_count ): void {
+		global $wpdb;
+
+		update_option( CustomOrdersTableController::CUSTOM_ORDERS_TABLE_USAGE_ENABLED_OPTION, $hpos_is_authoritative ? 'yes' : 'no' );
+		update_option( DataSynchronizer::ORDERS_DATA_SYNC_ENABLED_OPTION, 'no' );
+
+		$changed_order = OrderHelper::create_order();
+		$this->sut->process_batch( array( $changed_order->get_id() ) );
+		OrderHelper::create_order();
+
+		$orders_table = OrdersTableDataStore::get_orders_table_name();
+		$wpdb->update( $orders_table, array( 'date_updated_gmt' => $hpos_is_authoritative ? '2025-01-02 00:00:00' : '2025-01-01 00:00:00' ), array( 'id' => $changed_order->get_id() ) );
+		$wpdb->update( $wpdb->posts, array( 'post_modified_gmt' => $hpos_is_authoritative ? '2025-01-01 00:00:00' : '2025-01-02 00:00:00' ), array( 'ID' => $changed_order->get_id() ) );
+
+		$orders = $wpdb->get_results( "SELECT * FROM $orders_table", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Internal table name.
+		$schema = $wpdb->get_row( "SHOW CREATE TABLE $orders_table", ARRAY_N ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Internal table name.
+
+		// Shadow the permanent table without committing the test transaction.
+		$this->assertNotFalse( $wpdb->query( str_replace( 'CREATE TABLE', 'CREATE TEMPORARY TABLE', $schema[1] ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Schema returned by the database.
+		try {
+			foreach ( $orders as $order ) {
+				$wpdb->insert( $orders_table, $order );
+			}
+
+			if ( $full_count ) {
+				$this->assertSame( 2, $this->sut->get_current_orders_pending_sync_count(), 'Both the missing order and the changed order should be counted.' );
+			} else {
+				$this->assertTrue( $this->sut->has_orders_pending_sync(), 'Temporary tables must not hide pending orders.' );
+			}
+		} finally {
+			$wpdb->query( "DROP TEMPORARY TABLE $orders_table" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Internal table name.
+		}
+	}
+
 	/**
 	 * Test that orders are synced properly where there are orders to migrate from posts table.
 	 */