subquery data type in delete statement should be boolean

[BohuTANG]

Summary

Setup

https://github.com/datafuselabs/wizard/blob/main/checksb/sql/updates/bend/setup.sql

Query

-- UPDATE-A2: Mark all 'pending' orders as 'completed' for ETH, where the price is above average.
UPDATE orders
SET status = 'completed'
WHERE asset_type = 'ETH' AND status = 'pending'
AND price > (SELECT AVG(price) FROM orders WHERE asset_type = 'ETH');

Error:

1001=>subquery data type in delete statement should be boolean

This works in snowflake.

[BohuTANG]

cc @SkyFan2002

[BohuTANG]

Another issue:

-- UPDATE-A3: Double the quantity of XRP in transactions for users who have more than 2 transactions.
UPDATE transactions
SET quantity = quantity * 2
WHERE user_id IN (SELECT user_id FROM transactions GROUP BY user_id HAVING COUNT(*) > 2) AND asset_type = 'XRP';

Errors:

1104=>called `Result::unwrap()` on an `Err` value: BadArguments. Code: 1006, Text = Unable to get field named "asset_type". Valid fields: ["user_id", "_row_id"].

<Backtrace disabled by default. Please use RUST_BACKTRACE=1 to enable> 

[BohuTANG]

Delete issue:

-- DELETE-A5: Delete orders with a quantity greater than the average quantity of all orders
DELETE FROM orders
WHERE quantity > (SELECT AVG(quantity) FROM orders);

Error:

1001=>subquery data type in delete statement should be boolean