bug: Spill is not open for merge into.

[JackTan25]

Search before asking

• I had searched in the issues and found no similar issues.

Version

35f129c

What's Wrong?

in xsmall, when execute

MERGE INTO transactions USING (
    SELECT t.user_id, t.asset_type,
           SUM(t.quantity) AS total_quantity
    FROM transactions t
    GROUP BY t.user_id, t.asset_type
    UNION ALL
    SELECT t.user_id, 'NEW_TRANSACTION' AS asset_type,  -- New transaction type for insert
           30 AS quantity                                -- Example quantity
    FROM transactions t
    WHERE t.user_id % 2 = 0  -- Condition to select subset for new data
    GROUP BY t.user_id
) AS combined_transactions ON transactions.asset_type = combined_transactions.asset_type
    WHEN MATCHED THEN
        UPDATE SET transactions.quantity = combined_transactions.total_quantity
    WHEN NOT MATCHED THEN
        INSERT (user_id, transaction_type, asset_type, quantity, transaction_time)
            VALUES (combined_transactions.user_id, 'trade', combined_transactions.asset_type, combined_transactions.total_quantity, '2023-01-01');

MERGE INTO transactions USING (
  SELECT
    t.user_id,
    t.asset_type,
    SUM(t.quantity) AS total_quantity
  FROM
    transactions t
  GROUP BY
    t.user_id,
    t.asset_type
  UNION
  ALL
  SELECT
    t.user_id,
    'NEW_TRANSACTION' AS asset_type,
    30 AS quantity
  FROM
    transactions t
  WHERE
    t.user_id % 2 = 0
  GROUP BY
    t.user_id
) AS combined_transactions ON transactions.asset_type = combined_transactions.asset_type
WHEN MATCHED THEN
UPDATE
SET
  transactions.quantity = combined_transactions.total_quantity
  WHEN NOT MATCHED THEN
INSERT
  (
    user_id,
    transaction_type,
    asset_type,
    quantity,
    transaction_time
  )
VALUES
  (
    combined_transactions.user_id,
    'trade',
    combined_transactions.asset_type,
    combined_transactions.total_quantity,
    '2023-01-01'
  )

memory oversize.

How to Reproduce?

mergeinto> MERGE INTO transactions USING (
    SELECT t.user_id, t.asset_type,
           SUM(t.quantity) AS total_quantity
    FROM transactions t
    GROUP BY t.user_id, t.asset_type
    UNION ALL
    SELECT t.user_id, 'NEW_TRANSACTION' AS asset_type,  -- New transaction type for insert
           30 AS quantity                                -- Example quantity
    FROM transactions t
    WHERE t.user_id % 2 = 0  -- Condition to select subset for new data
    GROUP BY t.user_id
) AS combined_transactions ON transactions.asset_type = combined_transactions.asset_type
    WHEN MATCHED THEN
        UPDATE SET transactions.quantity = combined_transactions.total_quantity
    WHEN NOT MATCHED THEN
        INSERT (user_id, transaction_type, asset_type, quantity, transaction_time)
            VALUES (combined_transactions.user_id, 'trade', combined_transactions.asset_type, combined_transactions.total_quantity, '2023-01-01');

MERGE INTO transactions USING (
  SELECT
    t.user_id,
    t.asset_type,
    SUM(t.quantity) AS total_quantity
  FROM
    transactions t
  GROUP BY
    t.user_id,
    t.asset_type
  UNION
  ALL
  SELECT
    t.user_id,
    'NEW_TRANSACTION' AS asset_type,
    30 AS quantity
  FROM
    transactions t
  WHERE
    t.user_id % 2 = 0
  GROUP BY
    t.user_id
) AS combined_transactions ON transactions.asset_type = combined_transactions.asset_type
WHEN MATCHED THEN
UPDATE
SET
  transactions.quantity = combined_transactions.total_quantity
  WHEN NOT MATCHED THEN
INSERT
  (
    user_id,
    transaction_type,
    asset_type,
    quantity,
    transaction_time
  )
VALUES
  (
    combined_transactions.user_id,
    'trade',
    combined_transactions.asset_type,
    combined_transactions.total_quantity,
    '2023-01-01'
  )

error happens after fetched 0 rows: APIError: PageError with 1104: memory usage 25.8 GB(25770320620) exceeds limit 25.8 GB(25769803776)
0 row read in 14.572 sec. Processed 2.35 million row, 51.33 MiB (160.98 thousand row/s, 3.52 MiB/s)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[zenus]

/ assign me

[BohuTANG]

@zenus Thanks for taking but this is a bit complex, @JackTan25 already working on it.