3.1.3 子查询union all 后，字段查询不到

💬 StarRocks 用户问答
#1

【业务影响】
【是否存算分离】否
【StarRocks版本】例如：3.1.3

报错内容是： ERROR 1064 (HY000): invalid field name: cost_tax
查询的sql是：
SELECT cast( date_trunc(‘day’,date(substr(orderdate,1,10))) AS varchar ) AS category
,ordercategory AS dim
,SUM(trip_tickets) AS trip_tickets
,SUM(trip_profit) AS trip_profit
,SUM(trip_profit) * 1.0 / SUM(trip_tickets) AS pre_trip_profit
,SUM(trip_orders) AS trip_orders
,SUM(markup) AS add_price
,SUM(markup) * 1.0 / SUM(trip_tickets) AS pre_cashprofit
,SUM(x_profit) * 1.0 / SUM(trip_tickets) AS pre_x_profit
,SUM(trip_profit) * 1.0 / SUM(price_tax) AS profit_rate
,SUM(markup) * 1.0 / SUM(cost_tax) AS cashprofit_rate
,SUM(cost_notax) AS cost_notax
,SUM(price_tax) * 1.0 / SUM(trip_tickets) AS pre_price_tax
,(SUM(markup) + SUM(x_profit)) * 1.0 / SUM(price_tax) AS take_rate
,SUM(deductionamount) / SUM(price_tax) AS app_tejia_rate
FROM
(
SELECT *
FROM
(
SELECT *
,‘ALL’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
UNION ALL
SELECT *
,‘1-Direct’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
AND COALESCE(channel, ‘’) <> ‘Direct’
UNION ALL
SELECT *
,‘1-Meta’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
AND COALESCE(channel, ‘’) NOT IN (‘SC’, ‘其他Meta’)
UNION ALL
SELECT *
,‘Meta’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
AND COALESCE(channel, ‘’) IN (‘SC’, ‘其他Meta’)
) t2
) t1

WHERE 1 = 1
GROUP BY  1
         ,2

去掉union all 后正常查询

#2

SELECT *
FROM
(
SELECT *
,‘ALL’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
UNION ALL
SELECT *
,‘1-Direct’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
AND COALESCE(channel, ‘’) <> ‘Direct’
UNION ALL
SELECT *
,‘1-Meta’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
AND COALESCE(channel, ‘’) NOT IN (‘SC’, ‘其他Meta’)
UNION ALL
SELECT *
,‘Meta’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2024-07-16’ AND ‘2024-07-22’
AND COALESCE(channel, ‘’) IN (‘SC’, ‘其他Meta’)
) t2

并且子查询这段执行是成功的

#3

这个查询结果中有cost_tax这个字段么？请发下explain +原查询 的执行计划。

#4

有cost_tax这个字段的。

explain.txt (44.9 KB)

#5

不知道是不是我执行了explain 的原因，目前可以正常查了

#6

这个问题又出现了：
SELECT ordercategory AS dim
,SUM(trip_tickets) AS sortnum
FROM
(
SELECT trip_tickets, ordercategory
FROM
(
SELECT *
,‘ALL’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2023-07-02’ AND ‘2023-08-01’
AND ( upper(market) IN (‘FR’))
AND upper(source) IN (‘ONLINE’)
UNION ALL
SELECT *
,‘1-Direct’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2023-07-02’ AND ‘2023-08-01’
AND ( upper(market) IN (‘FR’))
AND upper(source) IN (‘ONLINE’)
AND COALESCE(channel, ‘’) <> ‘Direct’
UNION ALL
SELECT *
,‘1-Meta’ AS ordercategory
FROM dw_fltdb.adm_fltinsight_pricingkpi_orderdetail_monitor3
WHERE 1 = 1
AND orderdate BETWEEN ‘2023-07-02’ AND ‘2023-08-01’
AND ( upper(market) IN (‘FR’))
AND upper(source) IN (‘ONLINE’)
AND COALESCE(channel, ‘’) NOT IN (‘SC’, ‘其他Meta’)
) channel1
) channel2
WHERE 1 = 1
AND ordercategory IN (‘1-Meta’, ‘Meta’)
GROUP BY 1
报错如下：
ERROR 1064 (HY000): invalid field name: trip_tickets

explain信息：
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:295: expr | 296: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 5:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 295: expr |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 05 |
| UNPARTITIONED |
| |
| 4:AGGREGATE (merge finalize) |
| | output: sum(296: sum) |
| | group by: 295: expr |
| | |
| 3:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 295: expr |
| |
| 2:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(275: trip_tickets) |
| | group by: 295: expr |
| | |
| 1:Project |
| | <slot 275> : 199: trip_tickets |
| | <slot 295> : ‘1-Meta’ |
| | |
| 0:OlapScanNode |
| TABLE: adm_fltinsight_pricingkpi_orderdetail_monitor3 |
| PREAGGREGATION: OFF. Reason: Predicates include the value column |
| PREDICATES: 153: orderdate >= ‘2023-07-02’, 153: orderdate <= ‘2023-08-01’, upper(156: market) = ‘FR’, upper(163: source) = ‘ONLINE’, coalesce(164: channel, ‘’) NOT IN (‘SC’, ‘其他Meta’) |
| partitions=1/1 |
| rollup: adm_fltinsight_pricingkpi_orderdetail_monitor3_mv |
| tabletRatio=3/3 |
| tabletList=39284874,39284878,39284882 |
| cardinality=6033682 |
| avgRowSize=36.993958 |
| numNodes=0 |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

#7

会是这个问题么

京ICP备2022026421号-1