SR 2.3.0 MultiCastPlanFragment don't support return result

U_魔咒 · 2022年08月24日 02:54

查询sql时异常，《ERROR 1064 (HY000): MultiCastPlanFragment don’t support return result》
sql定位分析：

可能原因是因为上述图片中标记部分做了or原因导致的，因为第一个调整已经没有排查到结果数据，导致第二个条件异常，如果将两个条件随便去除一个则是正常的，
完整SQL:
WITH TIME_AREA_TMP AS (SELECT COLUMN_0 AS AD_TYPE, COLUMN_1 AS AD_BEGIN FROM (VALUES (9, ‘2022-01-19’), (-2, ‘2021-01-19’), (9, ‘2022-02-03’), (-2, ‘2021-02-03’), (9, ‘2022-02-22’), (-2, ‘2021-02-22’), (9, ‘2022-03-18’), (-2, ‘2021-03-18’), (9, ‘2022-04-20’), (-2, ‘2021-04-20’), (9, ‘2022-05-12’), (-2, ‘2021-05-12’), (9, ‘2022-06-22’), (-2, ‘2021-06-22’), (9, ‘2022-07-20’), (-2, ‘2021-07-20’), (9, ‘2022-08-23’), (-2, ‘2021-08-23’)) AS T), temp_table_SDP_D_M AS (SELECT TENANT_ID AS TENANT_ID, RESORT AS RESORT, SHOP_ID AS SHOP_ID, IDX AS IDX, MARKET_CLASS_ZH AS MARKET_CLASS_ZH, IDX_NUM AS IDX_NUM, MARKET_GROUP AS MARKET_GROUP, MARKET_CLASS AS MARKET_CLASS, split_part(IDX_CN_EN, ‘$mls$’, 1) AS IDX_CN_EN, IDX_EN AS IDX_EN, MARKET_CODE AS MARKET_CODE, IDX_CLASS_NUM AS IDX_CLASS_NUM, IDX_DESC_CLASS_CN AS IDX_DESC_CLASS_CN, split_part(IDX_CLASS_GROUP2_CN_EN, ‘$mls$’, 1) AS IDX_CLASS_GROUP2_CN_EN, split_part(IDX_CLASS_GROUP3_CN_EN, ‘$mls$’, 1) AS IDX_CLASS_GROUP3_CN_EN, split_part(IDX_DESC_CLASS_CN_EN, ‘$mls$’, 1) AS IDX_DESC_CLASS_CN_EN, split_part(IDX_DESC_GROUP_CN_EN, ‘$mls$’, 1) AS IDX_DESC_GROUP_CN_EN, DATA_DAY AS DATA_DAY, DATA_YEAR AS DATA_YEAR, DATA_YEAR_MONTH AS DATA_YEAR_MONTH, DAY AS DAY, SHOP_NAME AS SHOP_NAME, NO_OF_STAYS_DAY AS NO_OF_STAYS_DAY, NO_OF_STAYS_LAST_YEAR_DAY AS NO_OF_STAYS_LAST_YEAR_DAY, BOOK_LEAD_DAYS_DAY AS BOOK_LEAD_DAYS_DAY, BOOK_LEAD_DAYS_LAST_YEAR_DAY AS BOOK_LEAD_DAYS_LAST_YEAR_DAY, NIGHTS_DAY AS NIGHTS_DAY, MEALS_DAY AS MEALS_DAY, NIGHTS_DAY_OF_SEGMENT AS NIGHTS_DAY_OF_SEGMENT, NIGHTS_DAY_OF_RESORT AS NIGHTS_DAY_OF_RESORT, NIGHTS_LAST_YEAR_DAY AS NIGHTS_LAST_YEAR_DAY, NIGHTS_WEEK AS NIGHTS_WEEK, NIGHTS_LAST_WEEK AS NIGHTS_LAST_WEEK, NIGHTS_LAST_YEAR_WEEK AS NIGHTS_LAST_YEAR_WEEK, NIGHTS_MONTH AS NIGHTS_MONTH, NIGHTS_LAST_MONTH AS NIGHTS_LAST_MONTH, NIGHTS_LAST_MONTH_DAY AS NIGHTS_LAST_MONTH_DAY, NIGHTS_LAST_YEAR_MONTH AS NIGHTS_LAST_YEAR_MONTH, NIGHTS_LAST_YEAR_WHOLE_MONTH AS NIGHTS_LAST_YEAR_WHOLE_MONTH, NIGHTS_YEAR AS NIGHTS_YEAR, NIGHTS_LAST_YEAR AS NIGHTS_LAST_YEAR, STAY_ROOMS_DAY AS STAY_ROOMS_DAY, STAY_ROOMS_LAST_YEAR_DAY AS STAY_ROOMS_LAST_YEAR_DAY, STAY_ROOMS_WEEK AS STAY_ROOMS_WEEK, STAY_ROOMS_LAST_WEEK AS STAY_ROOMS_LAST_WEEK, STAY_ROOMS_LAST_YEAR_WEEK AS STAY_ROOMS_LAST_YEAR_WEEK, STAY_ROOMS_MONTH AS STAY_ROOMS_MONTH, STAY_ROOMS_LAST_MONTH AS STAY_ROOMS_LAST_MONTH, STAY_ROOMS_LAST_YEAR_MONTH AS STAY_ROOMS_LAST_YEAR_MONTH, STAY_ROOMS_YEAR AS STAY_ROOMS_YEAR, STAY_ROOMS_LAST_YEAR AS STAY_ROOMS_LAST_YEAR, PHYSICAL_ROOM_DAY AS PHYSICAL_ROOM_DAY, PHYSICAL_ROOM_OF_MONTH AS PHYSICAL_ROOM_OF_MONTH, PHYSICAL_ROOM_MONTH AS PHYSICAL_ROOM_MONTH, PHYSICAL_ROOM_LAST_YEAR_WHOLE_MONTH AS PHYSICAL_ROOM_LAST_YEAR_WHOLE_MONTH, OOO_ROOMS_DAY AS OOO_ROOMS_DAY, OOO_ROOMS_MONTH AS OOO_ROOMS_MONTH, OOO_ROOMS_LAST_YEAR_WHOLE_MONTH AS OOO_ROOMS_LAST_YEAR_WHOLE_MONTH, OCC_ROOM_DAY AS OCC_ROOM_DAY, AVAIL_ROOM_DAY AS AVAIL_ROOM_DAY, AVAIL_ROOM_LAST_YEAR_DAY AS AVAIL_ROOM_LAST_YEAR_DAY, AVAIL_ROOM_BUDGET_DAY AS AVAIL_ROOM_BUDGET_DAY, PHYSICAL_ROOM_BUDGET_MONTH AS PHYSICAL_ROOM_BUDGET_MONTH, PHYSICAL_ROOM_BUDGET_WHOLE_MONTH AS PHYSICAL_ROOM_BUDGET_WHOLE_MONTH, OO_ROOM_BUDGET_DAY AS OO_ROOM_BUDGET_DAY, OO_ROOM_BUDGET_MONTH AS OO_ROOM_BUDGET_MONTH, OO_ROOM_BUDGET_WHOLE_MONTH AS OO_ROOM_BUDGET_WHOLE_MONTH, NIGHTS_BUDGET_DAY AS NIGHTS_BUDGET_DAY, NIGHTS_BUDGET_LAST_YEAR_DAY AS NIGHTS_BUDGET_LAST_YEAR_DAY, NIGHTS_BUDGET_WEEK AS NIGHTS_BUDGET_WEEK, NIGHTS_BUDGET_LAST_WEEK AS NIGHTS_BUDGET_LAST_WEEK, NIGHTS_BUDGET_LAST_YEAR_WEEK AS NIGHTS_BUDGET_LAST_YEAR_WEEK, NIGHTS_BUDGET_MONTH AS NIGHTS_BUDGET_MONTH, NIGHTS_BUDGET_WHOLE_MONTH AS NIGHTS_BUDGET_WHOLE_MONTH, NIGHTS_BUDGET_LAST_MONTH AS NIGHTS_BUDGET_LAST_MONTH, NIGHTS_BUDGET_LAST_YEAR_MONTH AS NIGHTS_BUDGET_LAST_YEAR_MONTH, NIGHTS_BUDGET_YEAR AS NIGHTS_BUDGET_YEAR, NIGHTS_BUDGET_LAST_YEAR AS NIGHTS_BUDGET_LAST_YEAR, NIGHTS_FORECAST_DAY AS NIGHTS_FORECAST_DAY, NIGHTS_FORECAST_LAST_YEAR_DAY AS NIGHTS_FORECAST_LAST_YEAR_DAY, NIGHTS_FORECAST_WEEK AS NIGHTS_FORECAST_WEEK, NIGHTS_FORECAST_LAST_WEEK AS NIGHTS_FORECAST_LAST_WEEK, NIGHTS_FORECAST_LAST_YEAR_WEEK AS NIGHTS_FORECAST_LAST_YEAR_WEEK, NIGHTS_FORECAST_MONTH AS NIGHTS_FORECAST_MONTH, NIGHTS_FORECAST_LAST_MONTH AS NIGHTS_FORECAST_LAST_MONTH, NIGHTS_FORECAST_LAST_YEAR_MONTH AS NIGHTS_FORECAST_LAST_YEAR_MONTH, NIGHTS_FORECAST_YEAR AS NIGHTS_FORECAST_YEAR, NIGHTS_FORECAST_LAST_YEAR AS NIGHTS_FORECAST_LAST_YEAR, IDX_1 AS IDX_1, REGION AS REGION, OCC_ROOM_BUDGET_DAY AS OCC_ROOM_BUDGET_DAY, PHYSICAL_ROOM_BUDGET_DAY AS PHYSICAL_ROOM_BUDGET_DAY, PHYSICAL_ROOM_YEAR AS PHYSICAL_ROOM_YEAR, PHYSICAL_ROOM_LAST_YEAR_MONTH AS PHYSICAL_ROOM_LAST_YEAR_MONTH, OCC_ROOM_BUDGET_YEAR AS OCC_ROOM_BUDGET_YEAR, IS_BUDGET AS IS_BUDGET, GREATER_THAN_OPEN_DATE AS GREATER_THAN_OPEN_DATE, PROPERTYCURRENCY666_ROOM_REVENUE_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_DAY_OF_SEGMENT AS PROPERTYCURRENCY666_ROOM_REVENUE_DAY_OF_SEGMENT, PROPERTYCURRENCY666_ROOM_REVENUE_DAY_OF_RESORT AS PROPERTYCURRENCY666_ROOM_REVENUE_DAY_OF_RESORT, PROPERTYCURRENCY666_ROOM_REVENUE_LAST_YEAR_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_LAST_YEAR_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_LAST_MONTH_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_LAST_MONTH_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WHOLE_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WHOLE_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_YEAR AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR AS PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_DAY_OF_SEGMENT AS ENTERPRISECURRENCY666_ROOM_REVENUE_DAY_OF_SEGMENT, PROPERTYCURRENCY666_ROOM_REVENUE_DAY_OF_RESORT AS ENTERPRISECURRENCY666_ROOM_REVENUE_DAY_OF_RESORT, PROPERTYCURRENCY666_ROOM_REVENUE_LAST_YEAR_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_LAST_YEAR_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_LAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_LAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_LAST_MONTH_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_LAST_MONTH_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WHOLE_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR_WHOLE_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_YEAR AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR AS ENTERPRISECURRENCY666_ROOM_REVENUE_DATA_LAST_YEAR, PROPERTYCURRENCY666_TOTAL_REVENUE_DAY AS PROPERTYCURRENCY666_TOTAL_REVENUE_DAY, PROPERTYCURRENCY666_TOTAL_REVENUE_LAST_YEAR_DAY AS PROPERTYCURRENCY666_TOTAL_REVENUE_LAST_YEAR_DAY, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_WEEK AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_WEEK, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_WEEK AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_WEEK, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_WEEK AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_WEEK, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_MONTH AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_MONTH, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_MONTH AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_MONTH, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_MONTH AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_MONTH, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_YEAR AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_YEAR, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR AS PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR, PROPERTYCURRENCY666_TOTAL_REVENUE_DAY AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DAY, PROPERTYCURRENCY666_TOTAL_REVENUE_LAST_YEAR_DAY AS ENTERPRISECURRENCY666_TOTAL_REVENUE_LAST_YEAR_DAY, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_WEEK AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_WEEK, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_WEEK AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_LAST_WEEK, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_WEEK AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_WEEK, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_MONTH AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_MONTH, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_MONTH AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_LAST_MONTH, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_MONTH AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR_MONTH, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_YEAR AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_YEAR, PROPERTYCURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR AS ENTERPRISECURRENCY666_TOTAL_REVENUE_DATA_LAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_WHOLE_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_WHOLE_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_YEAR AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR AS PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_LAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_WHOLE_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_WHOLE_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_LAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_YEAR AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR AS ENTERPRISECURRENCY666_ROOM_REVENUE_BUDGET_LAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_DAY AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_WEEK AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_MONTH AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_YEAR AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR AS PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_DAY AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_DAY, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_LAST_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_WEEK AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_WEEK, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_LAST_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_MONTH AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR_MONTH, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_YEAR AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_YEAR, PROPERTYCURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR AS ENTERPRISECURRENCY666_ROOM_REVENUE_FORECAST_LAST_YEAR FROM ds_pms_hotel_market_idx_stat GUI WHERE (GUI.tenant_id = ‘t00513’ AND 1 = 1 AND (DATA_DAY IN (‘2021-01-19’, ‘2021-02-03’, ‘2021-02-22’, ‘2021-03-18’, ‘2021-04-20’, ‘2021-05-12’, ‘2021-06-22’, ‘2021-07-20’, ‘2021-08-23’) OR DATA_DAY IN (SELECT X.AD_BEGIN FROM TIME_AREA_TMP X)) AND (SHOP_ID IN (‘101003198’, ‘101003268’, ‘101003258’, ‘101003238’, ‘101102008’) AND IDX IN (‘Rack Rate’, ‘Special Offers’, ‘Business Partner’, ‘Discount Other’, ‘Discounts’, ‘Package’, ‘Internet’, ‘Promotion’, ‘OTAs’, ‘Wholesale’, ‘Group - Meetings’, ‘Group - Incentive’, ‘Group - Convention & Exhibition’, ‘Group - Association’, ‘Group - Wedding’, ‘Group - Others’, ‘Commercial Series’, ‘Leisure Group’, ‘Corporate A’, ‘Corporate b’, ‘Corporate C’, ‘Corporate D’, ‘Government and Embassy’, ‘Long Term’, ‘Airline Crew’, ‘Owner Special’, ‘Complimentary’, ‘Complimentary TA Fam’, ‘Complimentary Media’, ‘Complimentary Staff Entitlement’, ‘Complimentary Owers only’, ‘House use’, ‘Complimentary Coupons’, ‘Run of House’, ‘Business Group’, ‘Non revenue’, ‘Corporate’, ‘Contract’, ‘High qualit’, ‘Walk in’))) AND GUI.shop_id IN (‘100001’, ‘100002’, ‘1000020’, ‘100008’, ‘100016’, ‘100017’, ‘100018’, ‘100019’, ‘100021’, ‘100005’, ‘100009’, ‘106001038’, ‘107001018’, ‘100003’, ‘100004’, ‘100006’, ‘100010’, ‘100022’, ‘100007’, ‘102001038’, ‘100020’, ‘100023’))
select * from temp_table_SDP_D_M limit 200;

建表语句:
CREATE TABLE ds_pms_hotel_market_idx_stat (
shop_id varchar(65533) NULL COMMENT “”,
data_day varchar(65533) NULL COMMENT “”,
resort varchar(65533) NULL COMMENT “”,
idx varchar(65533) NULL COMMENT “”,
market_class_zh varchar(32) NULL COMMENT “”,
idx_num int(11) NULL COMMENT “”,
market_group varchar(65533) NULL COMMENT “”,
market_class varchar(65533) NULL COMMENT “”,
idx_cn_en varchar(65533) NULL COMMENT “”,
idx_en varchar(65533) NULL COMMENT “”,
market_code varchar(65533) NULL COMMENT “”,
idx_class_num varchar(65533) NULL COMMENT “”,
idx_desc_class_cn varchar(65533) NULL COMMENT “”,
idx_class_group2_cn_en varchar(65533) NULL COMMENT “”,
idx_class_group3_cn_en varchar(65533) NULL COMMENT “”,
idx_desc_class_cn_en varchar(65533) NULL COMMENT “”,
idx_desc_group_cn_en varchar(65533) NULL COMMENT “”,
data_year varchar(65533) NULL COMMENT “”,
data_year_month varchar(65533) NULL COMMENT “”,
day varchar(65533) NULL COMMENT “”,
shop_name varchar(65533) NULL COMMENT “”,
no_of_stays_day bigint(20) NULL COMMENT “”,
no_of_stays_last_year_day bigint(20) NULL COMMENT “”,
book_lead_days_day bigint(20) NULL COMMENT “”,
book_lead_days_last_year_day bigint(20) NULL COMMENT “”,
nights_day decimal128(38, 6) NULL COMMENT “”,
no_comp_houseuse_room_nights decimal128(38, 4) NULL COMMENT “”,
meals_day decimal128(38, 8) NULL COMMENT “”,
nights_day_of_segment decimal128(38, 6) NULL COMMENT “”,
nights_day_of_resort decimal128(38, 6) NULL COMMENT “”,
nights_last_year_day decimal128(38, 6) NULL COMMENT “”,
nights_week decimal128(38, 6) NULL COMMENT “”,
nights_last_week decimal128(38, 6) NULL COMMENT “”,
nights_last_year_week decimal128(38, 6) NULL COMMENT “”,
nights_month decimal128(38, 6) NULL COMMENT “”,
nights_last_month decimal128(38, 6) NULL COMMENT “”,
nights_last_month_day decimal128(38, 6) NULL COMMENT “”,
nights_last_year_month decimal128(38, 6) NULL COMMENT “”,
nights_last_year_whole_month decimal128(38, 6) NULL COMMENT “”,
nights_year decimal128(38, 6) NULL COMMENT “”,
nights_last_year decimal128(38, 6) NULL COMMENT “”,
stay_rooms_day decimal128(38, 4) NULL COMMENT “”,
stay_rooms_last_year_day decimal128(38, 4) NULL COMMENT “”,
stay_rooms_week decimal128(38, 4) NULL COMMENT “”,
stay_rooms_last_week decimal128(38, 4) NULL COMMENT “”,
stay_rooms_last_year_week decimal128(38, 4) NULL COMMENT “”,
stay_rooms_month decimal128(38, 4) NULL COMMENT “”,
stay_rooms_last_month decimal128(38, 4) NULL COMMENT “”,
stay_rooms_last_year_month decimal128(38, 4) NULL COMMENT “”,
stay_rooms_year decimal128(38, 4) NULL COMMENT “”,
stay_rooms_last_year decimal128(38, 4) NULL COMMENT “”,
physical_room_day decimal128(38, 6) NULL COMMENT “”,
physical_room_of_month decimal128(38, 6) NULL COMMENT “”,
physical_room_month decimal128(38, 6) NULL COMMENT “”,
physical_room_last_year_whole_month decimal128(38, 6) NULL COMMENT “”,
ooo_rooms_day decimal128(38, 6) NULL COMMENT “”,
ooo_rooms_month decimal128(38, 6) NULL COMMENT “”,
ooo_rooms_last_year_whole_month decimal128(38, 6) NULL COMMENT “”,
occ_room_day decimal128(38, 6) NULL COMMENT “”,
avail_room_day decimal128(38, 6) NULL COMMENT “”,
avail_room_last_year_day decimal128(38, 6) NULL COMMENT “”,
avail_room_budget_day decimal128(38, 6) NULL COMMENT “”,
physical_room_budget_month decimal128(38, 6) NULL COMMENT “”,
physical_room_budget_whole_month decimal128(38, 6) NULL COMMENT “”,
oo_room_budget_day decimal64(15, 4) NULL COMMENT “”,
oo_room_budget_month decimal128(38, 4) NULL COMMENT “”,
oo_room_budget_whole_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_day decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_day_of_segment decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_day_of_resort decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_last_year_day decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_week decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_last_week decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_last_year_week decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_month decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_last_month decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_last_month_day decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_last_year_month decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_last_year_whole_month decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_year decimal128(38, 6) NULL COMMENT “”,
propertycurrency666_room_revenue_data_last_year decimal128(38, 6) NULL COMMENT “”,
enterprisecurrency666_room_revenue_day decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_day_of_segment decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_day_of_resort decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_last_year_day decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_week decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_last_week decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_last_year_week decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_month decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_last_month decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_last_month_day decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_last_year_month decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_last_year_whole_month decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_year decimal128(38, 10) NULL COMMENT “”,
enterprisecurrency666_room_revenue_data_last_year decimal128(38, 10) NULL COMMENT “”,
propertycurrency666_total_revenue_day decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_last_year_day decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_last_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_last_year_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_last_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_last_year_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_year decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_total_revenue_data_last_year decimal128(38, 4) NULL COMMENT “”,
enterprisecurrency666_total_revenue_day decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_last_year_day decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_last_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_last_year_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_last_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_last_year_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_year decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_total_revenue_data_last_year decimal128(38, 8) NULL COMMENT “”,
nights_budget_day decimal64(15, 4) NULL COMMENT “”,
nights_budget_last_year_day decimal128(38, 4) NULL COMMENT “”,
nights_budget_week decimal128(38, 4) NULL COMMENT “”,
nights_budget_last_week decimal128(38, 4) NULL COMMENT “”,
nights_budget_last_year_week decimal128(38, 4) NULL COMMENT “”,
nights_budget_month decimal128(38, 4) NULL COMMENT “”,
nights_budget_whole_month decimal128(38, 4) NULL COMMENT “”,
nights_budget_last_month decimal128(38, 4) NULL COMMENT “”,
nights_budget_last_year_month decimal128(38, 4) NULL COMMENT “”,
nights_budget_year decimal128(38, 4) NULL COMMENT “”,
nights_budget_last_year decimal128(38, 4) NULL COMMENT “”,
nights_forecast_day decimal64(15, 4) NULL COMMENT “”,
nights_forecast_last_year_day decimal128(38, 4) NULL COMMENT “”,
nights_forecast_week decimal128(38, 4) NULL COMMENT “”,
nights_forecast_last_week decimal128(38, 4) NULL COMMENT “”,
nights_forecast_last_year_week decimal128(38, 4) NULL COMMENT “”,
nights_forecast_month decimal128(38, 4) NULL COMMENT “”,
nights_forecast_last_month decimal128(38, 4) NULL COMMENT “”,
nights_forecast_last_year_month decimal128(38, 4) NULL COMMENT “”,
nights_forecast_year decimal128(38, 4) NULL COMMENT “”,
nights_forecast_last_year decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_day decimal64(15, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_last_year_day decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_last_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_last_year_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_whole_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_last_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_last_year_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_year decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_budget_last_year decimal128(38, 4) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_day decimal128(30, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_last_year_day decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_last_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_last_year_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_whole_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_last_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_last_year_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_year decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_budget_last_year decimal128(38, 8) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_day decimal64(15, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_last_year_day decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_last_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_last_year_week decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_last_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_last_year_month decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_year decimal128(38, 4) NULL COMMENT “”,
propertycurrency666_room_revenue_forecast_last_year decimal128(38, 4) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_day decimal128(30, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_last_year_day decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_last_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_last_year_week decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_last_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_last_year_month decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_year decimal128(38, 8) NULL COMMENT “”,
enterprisecurrency666_room_revenue_forecast_last_year decimal128(38, 8) NULL COMMENT “”,
idx_1 varchar(65533) NULL COMMENT “”,
region varchar(65533) NULL COMMENT “”,
occ_room_budget_day decimal128(38, 6) NULL COMMENT “”,
physical_room_budget_day decimal128(38, 6) NULL COMMENT “”,
physical_room_year decimal128(38, 6) NULL COMMENT “”,
physical_room_last_year_month decimal128(38, 6) NULL COMMENT “”,
occ_room_budget_year decimal128(38, 6) NULL COMMENT “”,
is_budget varchar(16) NULL COMMENT “”,
greater_than_open_date varchar(16) NULL COMMENT “”,
tenant_id varchar(65533) NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(shop_id, data_day)
COMMENT “OLAP”
DISTRIBUTED BY HASH(tenant_id) BUCKETS 4
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

U_魔咒 · 2022年08月24日 02:53

空表可以复现该问题

zhaoheng · 2022年08月24日 03:03

版本号麻烦提供一下

U_魔咒 · 2022年08月24日 03:04

版本 2.3.0

Seaven · 2022年08月24日 11:18

https://github.com/StarRocks/starrocks/pull/10397

这个PR会修复你的问题

U_魔咒 · 2022年08月25日 01:53

大佬，这个pr能否基于现在的2.3.1的版本单独提供个包么？这个问题目前是线上问题需要急速处理才行

zhaoheng · 2022年08月25日 02:08

2.3.2 会尽快发布包含这个

U_魔咒 · 2022年08月25日 02:10

多谢

U_魔咒 · 2022年08月30日 02:20

大佬有具体2.3.2的上线时间没？这个问题有点急