hnwkz opened a new issue, #33413: URL: https://github.com/apache/doris/issues/33413
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 2.0.7 ### What's Wrong? 执行如下sql报错： SELECT * FROM (SELECT concat( date_format(to_monday(t_a_0.`dt`), '%m-%d'), '~', date_format(date_add(to_monday(t_a_0.`dt`), 6), '%m-%d')) AS f_ax_0, t_a_0.`period` AS f_ax_1, GROUPING (concat( date_format(to_monday(t_a_0.`dt`), '%m-%d'), '~', date_format(date_add(to_monday(t_a_0.`dt`), 6), '%m-%d')) ) AS f_ax_0_total, GROUPING (t_a_0.`period`) AS f_ax_1_total , (SUM(t_a_0.`monthly_recall_pay_at_range`)/(SUM(t_a_0.`monthly_new_pay_at_range`)+SUM(t_a_0.`monthly_recall_pay_at_range`))) AS f_ay_0 FROM (SELECT x.period, x.game_code, x.media, x.af_prt, x.os_name, x.dt, x.store_day, sum(x.monthly_new_num_at_range) AS monthly_new_num_at_range, sum(x.monthly_recall_num_at_range) AS monthly_recall_num_at_range, sum(x.cost_at_range) AS cost_at_range, sum(x.monthly_pay_at_range) AS monthly_pay_at_range, sum(x.monthly_new_pay_at_range) AS monthly_new_pay_at_range, sum(x.monthly_recall_pay_at_range) AS monthly_recall_pay_at_range, sum(x.pay_roi) AS pay_roi, sum(x.dnu) AS dnu, sum(x.pay_num_1) AS pay_num_1, sum(x.total_pay_amount_1) AS total_pay_amount_1, sum(x.total_pay_amount_7) AS total_pay_amount_7, sum(x.total_pay_amount_30) AS total_pay_amount_30, sum(x.retention_2) AS retention_2, sum(x.pay) AS pay, sum(x.new_user_current_month_pay) AS new_user_current_month_pay, sum(COALESCE (y.new_user_current_pay, 0)) AS new_user_current_pay FROM (SELECT period, game_code, game_id, app_code, app_id, media, af_prt, os_name, dt, store_day, sum(device_id_dnu) AS monthly_new_num_at_range, sum(recall_account) + sum(wake_account) AS monthly_recall_num_at_range, sum(cost) AS cost_at_range, sum(new_add_account_current_month_pay) + sum(recall_account_current_month_pay) + sum(wake_account_current_month_pay) AS monthly_pay_at_range, sum(new_add_account_current_month_pay) AS monthly_new_pay_at_range, sum(recall_account_current_month_pay) + sum(wake_account_current_month_pay) AS monthly_recall_pay_at_range, sum(new_user_current_month_pay) AS pay_roi, sum(dnu) AS dnu, sum(pay_num_1) AS pay_num_1, sum(total_pay_amount_1) AS total_pay_amount_1, sum(total_pay_amount_7) AS total_pay_amount_7, sum(total_pay_amount_30) AS total_pay_amount_30, sum(retention_2) AS retention_2, sum(pay) AS pay, sum(new_user_current_month_pay) AS new_user_current_month_pay FROM (SELECT period, game_code, game_id, app_code, app_id, media, af_prt, os_name, date (dt) AS dt, date (store_day) AS store_day, COALESCE (device_id_dnu, 0) AS device_id_dnu, COALESCE (recall_account, 0) AS recall_account, COALESCE (wake_account, 0) AS wake_account, COALESCE (cost, 0) AS cost, COALESCE (0.7 * new_add_account_current_month_pay, 0) AS new_add_account_current_month_pay, COALESCE (0.7 * recall_account_current_month_pay, 0) AS recall_account_current_month_pay, COALESCE (0.7 * wake_account_current_month_pay, 0) AS wake_account_current_month_pay, COALESCE (dnu, 0) AS dnu, COALESCE (pay_num_1, 0) AS pay_num_1, COALESCE (total_pay_amount_after_proportion_1, 0) AS total_pay_amount_1, COALESCE (total_pay_amount_after_proportion_7, 0) AS total_pay_amount_7, COALESCE (total_pay_amount_after_proportion_30, 0) AS total_pay_amount_30, COALESCE (retention_2, 0) AS retention_2, COALESCE (0.7 * pay, 0) AS pay, COALESCE (0.7 * new_user_current_month_pay, 0) AS new_user_current_month_pay FROM (SELECT '本月' AS `period`, store_day AS `dt`, game_code, game_id, app_code, app_id, media, af_prt, os_name, store_day, device_id_dnu, recall_account, wake_account, cost, new_add_account_current_month_pay, recall_account_cu rrent_month_pay, wake_account_current_month_pay, dnu, pay_num_1, total_pay_amount_after_proportion_1, total_pay_amount_after_proportion_7, total_pay_amount_after_proportion_30, retention_2, pay, new_user_current_month_pay FROM ads.ads_bi_flat_none_ad WHERE game_code IN ('SND') AND 'BI' = 'BI' AND (store_day BETWEEN '2022-01-01' AND '2024-12-31') UNION ALL SELECT '上月' AS `period`, TIMESTAMPADD(MONTH, 1, store_day) AS `dt`, game_code, game_id, app_code, app_id, media, af_prt, os_name, store_day, device_id_dnu, recall_account, wake_account, cost, new_add_account_current_month_pay, recall_account_current_month_pay, wake_account_current_month_pay, dnu, pay_num_1, total_pay_amount_after_proportion_1, total_pay_amount_after_proportion_7, total_pay_amount_after_proportion_30, retention_2, pay, new_user_current_month_pay FROM ads.ads_bi_flat_none_ad WHERE game_code IN ('SND') AND 'BI' = 'BI' AND (store_day BETWEEN '2022-01-01' AND '2024-12-31') AND DAY (TIMESTAMPADD(MONTH, 1, store_day)) = DAY (store_day) UNION ALL SELECT '上年同月' AS `period`, TIMESTAMPADD(YEAR, 1, store_day) AS `dt`, game_code, game_id, app_code, app_id, media, af_prt, os_name, store_day, device_id_dnu, recall_account, wake_account, cost, new_add_account_current_month_pay, recall_account_current_month_pay, wake_account_current_month_pay, dnu, pay_num_1, total_pay_amount_after_proportion_1, total_pay_amount_after_proportion_7, total_pay_amount_after_proportion_30, retention_2, pay, new_user_current_month_pay FROM ads.ads_bi_flat_none_ad WHERE game_code IN ('SND') AND 'BI' = 'BI' AND (store_day BETWEEN '2022-01-01' AND '2024-12-31') AND DAY (TIMESTAMPADD(YEAR, 1, store_day)) = DAY (store_day)) AS a) AS b GROUP BY period, game_code, game_id, app_code, app_id, media, af_prt, os_name, dt, store_day) AS x LEFT JOIN (SELECT game_id, reg_app_id, date (created_at_8) AS dt, media, af_prt, os_name, sum(amount_after_proportion) AS new_user_current_pay FROM ad_raw_data.v_rt_payments_usd WHERE date (creat ed_at_8) BETWEEN '2022-01-01' AND '2024-12-31' AND date_format(created_at_8, '%y-%m') = date_format(reg_created_at_8, '%y-%m') AND status IN ('complete') AND 'BI' = 'BI' GROUP BY game_id, reg_app_id, date (created_at_8), media, af_prt, os_name) AS y ON x.game_id = y.game_id AND x.app_id = y.reg_app_id AND x.store_day = y.dt AND x.media = y.media AND x.af_prt = y.af_prt AND x.os_name = y.os_name GROUP BY x.period, x.game_code, x.media, x.af_prt, x.os_name, x.dt, x.store_day UNION ALL SELECT '月目标' AS period, game_code, channel AS media, channel, os, dt, dt AS store_day, monthly_new_num_at_range, monthly_recall_num_at_range, cost_at_range, monthly_pay_at_range, monthly_new_pay_at_range, monthly_recall_pay_at_range, pay_roi_at_range, dnu_at_range, pay_num_1_at_range, total_pay_amount_1_at_range, total_pay_amount_7_at_range, total_pay_amount_30_at_range, retention_2_at_range, pay_at_range, monthly_new_pay_at_range AS new_user_current_month_pay_at_range, monthly_new_pay_at_range AS new_user_current_pay_at_range FROM (SELECT date (valid_date) AS dt, monthly_new_num / day_cnt AS monthly_new_num_at_range, monthly_recall_num / day_cnt AS monthly_recall_num_at_range, total_budget / day_cnt AS cost_at_range, ((2 * monthly_pay) / (day_cnt * (1 + 10))) * (1 + (day (valid_date) - 1) * (10 - 1) / (day_cnt - 1)) AS monthly_pay_at_range, ((2 * monthly_new_pay) / (day_cnt * (1 + 10))) * (1 + (day (valid_date) - 1) * (10 - 1) / (day_cnt - 1)) AS monthly_new_pay_at_range, ((2 * monthly_recall_pay) / (day_cnt * (1 + 10))) * (1 + (day (valid_date) - 1) * (10 - 1) / (day_cnt - 1)) AS monthly_recall_pay_at_range, ((2 * pay_roi) / (day_cnt * (1 + 10))) * (1 + (day (valid_date) - 1) * (10 - 1) / (day_cnt - 1)) AS pay_roi_at_range, dnu / day_cnt AS dnu_at_range, pay_num_1 / day_cnt AS pay_num_1_at_range, total_pay_amount_1 / day_cnt AS total_pay_amount_1_at_range, total_pay_amount_7 / day_cnt AS total_pay_amount_7_at_range, total_pay_amount_30 / day_cnt AS total_pay_amount_30_at_ra nge, retention_2 / day_cnt AS retention_2_at_range, pay / day_cnt AS pay_at_range, * FROM (SELECT *, date_format(date_add(e1, (row_number () OVER (PARTITION BY e1, id )) - 1), 'yyyy-MM-dd') AS valid_date FROM (SELECT *, days_diff(end_date, start_date) + 1 AS day_cnt FROM (SELECT prop_id AS `id`, MAX(CASE WHEN prop_name = 'game_code' THEN value_string END) AS `game_code`, MAX(CASE WHEN prop_name = 'channel' THEN value_string END) AS `channel`, MAX(CASE WHEN prop_name = 'channel_type' THEN value_string END) AS `channel_type`, MAX(CASE WHEN prop_name = 'os' THEN value_string END) AS `os`, MAX(CASE WHEN prop_name = 'month' THEN value_string END) AS `month`, MAX(CASE WHEN prop_name = 'start_date' THEN value_date END) AS `start_date`, MAX(CASE WHEN prop_name = 'end_date' THEN value_date END) AS `end_date`, MAX(CASE WHEN prop_name = 'monthly_new_num' THEN value_double END) AS `monthly_new_num`, MAX(CASE WHEN prop_name = 'monthly_recall_num' THEN value_double END) AS `monthly_recall_num`, M AX(CASE WHEN prop_name = 'total_budget' THEN value_double END) AS `total_budget`, MAX(CASE WHEN prop_name = 'monthly_pay' THEN value_double END) AS `monthly_pay`, MAX(CASE WHEN prop_name = 'monthly_new_pay' THEN value_double END) AS `monthly_new_pay`, MAX(CASE WHEN prop_name = 'monthly_recall_pay' THEN value_double END) AS `monthly_recall_pay`, MAX(CASE WHEN prop_name = 'pay_roi' THEN value_double END) AS `pay_roi`, MAX(CASE WHEN prop_name = 'dnu' THEN value_double END) AS `dnu`, MAX(CASE WHEN prop_name = 'pay_num_1' THEN value_double END) AS `pay_num_1`, MAX(CASE WHEN prop_name = 'total_pay_amount_1' THEN value_double END) AS `total_pay_amount_1`, MAX(CASE WHEN prop_name = 'total_pay_amount_7' THEN value_double END) AS `total_pay_amount_7`, MAX(CASE WHEN prop_name = 'total_pay_amount_30' THEN value_double END) AS `total_pay_amount_30`, MAX(CASE WHEN prop_name = 'retention_2' THEN value_double END) AS `retention_2`, MAX(CASE WHEN prop_name = 'pay' THEN value_double END) AS `pay` FRO M ga_bi.dim_field WHERE table_id = 329 GROUP BY prop_id) t1 LATERAL VIEW explode(array_repeat(start_date, days_diff(end_date, start_date) + 1)) tmp1 AS e1) t1) t1) t1 WHERE game_code IN ('SND')) t_a_0 WHERE (t_a_0.`dt` BETWEEN '2024-04-01 00:00:00' AND '2024-04-30 23:59:59') GROUP BY CUBE( concat( date_format(to_monday(t_a_0.`dt`), '%m-%d'), '~', date_format(date_add(to_monday(t_a_0.`dt`), 6), '%m-%d')), t_a_0.`period`)) t_a_1 LIMIT 0,1000 [2024-04-09 14:05:30] [HY000][1105] errCode = 2, detailMessage = Column concat(date_format(to_monday(`t_a_0`.`dt`), '%m-%d'), '~', date_format(date_add(to_monday(`t_a_0`.`dt`), 6), '%m-%d')) in GROUP_ID() does not exist in GROUP BY clause. 但是如果把date_add(to_monday(t_a_0.`dt`), 6) 改成 date_add(to_monday(t_a_0.`dt`), interval 6 day ) 则可以执行成功   ### What You Expected? 修复该 bug ### How to Reproduce? _No response_ ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
