carltal opened a new issue, #17957:
URL: https://github.com/apache/druid/issues/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)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
