Druid Cannot Pushdown Offset #17957

Affected Version

29.0.1 and below (have not tested newer versions)

The Druid version where the problem was encountered.
29.0.1

Description

For the following SQL query, Druid throws an error: cannot pushdown offset.
SELECT SUM(sale_quantity) AS sale_quantity, SUM(sale_total_amount) AS sale_total_amount, SUM(sale_revenue) AS sale_revenue, SUM(sale_revenue) AS sale_percent_of_revenue, SUM(refund_quantity) AS refund_quantity, SUM(refund_total_amount) AS refund_total_amount, SUM(refund_revenue) AS refund_revenue, SUM(refund_revenue) AS refund_percent_of_revenue, SUM(sale_quantity) - SUM(refund_quantity) AS netsale_quantity, SUM(sale_revenue) - SUM(refund_revenue) AS netsale_revenue, SUM(sale_revenue) - SUM(refund_revenue) AS netsale_percent_of_revenue, FLOOR(TIME_PARSE (event_time, NULL) to MINUTE) AS event_time_group, tag_name FROM ( SELECT DISTINCT __time, CASE WHEN (NVL (base_product_id, '0') = '0') THEN variant_product_id ELSE base_product_id END as tag_product_id, od_parent_id, m1.od_id, event_time, tag_name, sale_quantity, sale_total_amount, sale_subtotal_amount, sale_item_cost_amount, sale_discount_amount, sale_taxable_revenue, sale_non_taxable_revenue, sale_tax_amount, sale_revenue, sale_gross_revenue, sale_profitability, refund_quantity, refund_total_amount, refund_subtotal_amount, refund_item_cost_amount, refund_discount_amount, refund_taxable_revenue, refund_non_taxable_revenue, refund_tax_amount, refund_revenue, refund_gross_revenue, refund_profitability FROM master_order_detail_primary AS m1 LEFT JOIN ( SELECT DISTINCT od_id, CASE WHEN (NVL (base_product_id, '0') = '0') THEN product_id ELSE base_product_id END as variant_product_id FROM master_order_detail_primary WHERE __time >= xxx AND __time < xxx AND event_time >= xxx AND event_time < xxx AND merchant_id = xxx AND od_type = xxx ) AS m2 ON m1.od_parent_id = m2.od_id JOIN ( SELECT t.tag_id, t.tag_name, t.tag_type, NVL ( ( CASE WHEN (NVL (base_product_id, '0') = '0') THEN product_id ELSE base_product_id END ), t.entity_id ) as combo_product_id FROM tag_map t LEFT JOIN ( SELECT distinct category_id, product_id, base_product_id FROM master_order_detail_primary WHERE __time >= xxx AND __time < xxx AND event_time >= xxx AND event_time < xxx AND merchant_id = xxx AND od_type = xxx ) p ON t.entity_id = p.category_id WHERE NOT ( t.tag_type = xxx AND p.product_id IS NOT NULL ) AND t.merchant_id = xxx ) AS tg ON CASE WHEN (NVL (variant_product_id, '0') = '0') THEN ( CASE WHEN (NVL (base_product_id, '0') = '0') THEN product_id ELSE base_product_id END ) ELSE variant_product_id END = tg.combo_product_id WHERE __time >= xxx AND __time < xxx AND event_time >= xxx AND event_time < xxx AND merchant_id = xxx AND od_type = xxx ) GROUP BY FLOOR(TIME_PARSE (event_time, NULL) to MINUTE), tag_name limit 9 offset 1

However, if I remove the outer-most layer (the SUM and the GROUP BY), then Druid can successfully paginate (use offset)