ON DUPLICATE KEY UPDATE translation issue

Hi there,

I was currently testing the WP playground implementation which is using this plugin to store the db. I’ve discovered an issue with a query used by WooCommerce to update/create orders:

INSERT INTO wp_wc_orders
            (
                        billing_email,
                        currency,
                        customer_id,
                        customer_note,
                        date_created_gmt,
                        date_updated_gmt,
                        id,
                        ip_address,
                        parent_order_id,
                        payment_method,
                        payment_method_title,
                        status,
                        tax_amount,
                        total_amount,
                        transaction_id,
                        type,
                        user_agent
            )
            VALUES
            (
                        '[email protected]',
                        'EUR',
                        1,
                        '',
                        '2023-12-01 11:20:31',
                        '2023-12-01 11:20:31',
                        33,
                        '::1',
                        0,
                        '',
                        '',
                        'wc-checkout-draft',
                        1.596639,
                        0.000000,
                        '',
                        'shop_order',
                        'test'
            )
on duplicate KEYUPDATE billing_email=VALUES
       (
              billing_email
       )
       ,
       currency=VALUES
       (
              currency
       )
       ,
       customer_id=VALUES
       (
              customer_id
       )
       ,
       customer_note=VALUES
       (
              customer_note
       )
       ,
       date_created_gmt=VALUES
       (
              date_created_gmt
       )
       ,
       date_updated_gmt=VALUES
       (
              date_updated_gmt
       )
       ,
       id=VALUES
       (
              id
       )
       ,
       ip_address=VALUES
       (
              ip_address
       )
       ,
       parent_order_id=VALUES
       (
              parent_order_id
       )
       ,
       payment_method=VALUES
       (
              payment_method
       )
       ,
       payment_method_title=VALUES
       (
              payment_method_title
       )
       ,
       status=VALUES
       (
              status
       )
       ,
       tax_amount=VALUES
       (
              tax_amount
       )
       ,
       total_amount=VALUES
       (
              total_amount
       )
       ,
       transaction_id=VALUES
       (
              transaction_id
       )
       ,
       type=VALUES
       (
              type
       )
       ,
       user_agent=VALUES
       (
              user_agent
       )

This gets translated to the following SQLite query:

INSERT INTO wp_wc_orders ( billing_email, currency, customer_id, customer_note, date_created_gmt, date_updated_gmt, id, ip_address, parent_order_id, payment_method, payment_method_title, status, tax_amount, total_amount, transaction_id, type, user_agent )
VALUES ( :param0 , :param1 , 1, :param2 , :param3 , :param4 , 40, :param5 , 0, :param6 , :param7 , :param8 , 1.596639, 0.000000, :param9 , :param10 , :param11  )
ON   CONFLICT ("id", ) DO UPDATE SET  billing_email = excluded. billing_email , currency = excluded. currency , customer_id = excluded. customer_id , customer_note = excluded. customer_note , date_created_gmt = excluded. date_created_gmt , date_updated_gmt = excluded. date_updated_gmt , id = excluded. id , ip_address = excluded. ip_address , parent_order_id = excluded. parent_order_id , payment_method = excluded. payment_method , payment_method_title = excluded. payment_method_title , status = excluded. status , tax_amount = excluded. tax_amount , total_amount = excluded. total_amount , transaction_id = excluded. transaction_id , type = excluded. type , user_agent = excluded. user_agent 

The issue is the ON CONFLICT ("id", ) DO UPDATE SET line. This issue is due to a missing array key reset in the WP_SQLite_Translator::translate_on_duplicate_key() method. Adding an additional:

/**
* Reset array keys to prevent parsing issues.
*/
$conflict_columns = array_values( $conflict_columns );

After parsing the columns fixes the issue.

Best,
Dennis