在select 子语句中使用标量子查询执行查询正常，创建为视图执行报错

Question

示例sql:
SELECT
ranking,
p.assignee_name,
work_month,
(
SELECT
median AS median
FROM
day_p AS day_p
WHERE
work_month = p.work_month) AS median_p
FROM
p_data AS p
GROUP BY
p.assignee_name,
p.work_month,
p.leave_days,
p.work_days

徐振超@SelectDB(可以直接加微信Faith_xzc) · Answer

请提供一下可以复现的案例以及使用的Doris版本

xycheng · Answer

复现案例：

create or replace view internal.temp.ads_team_noair_043_view as

WITH gtp_done_issue(assignee_name, story_point, label, done_time) AS (
SELECT '张三', CAST(5.0 AS DOUBLE), '急', CAST('2025-01-10 10:00:00' AS DATETIME) UNION ALL
SELECT '李四', CAST(3.0 AS DOUBLE), '运维任务', CAST('2025-01-22 14:00:00' AS DATETIME) UNION ALL
SELECT '王五', CAST(8.0 AS DOUBLE), '重要任务', CAST('2025-02-05 09:00:00' AS DATETIME) UNION ALL
SELECT '张三', CAST(2.0 AS DOUBLE), '普通', CAST('2025-02-18 16:00:00' AS DATETIME) UNION ALL
SELECT '李四', CAST(5.0 AS DOUBLE), '难', CAST('2025-02-25 11:00:00' AS DATETIME) UNION ALL
SELECT '赵六', CAST(4.0 AS DOUBLE), '普通', CAST('2025-02-28 09:00:00' AS DATETIME)
),
org(user_name,org_name) as (
select '张三','部门1' UNION ALL
select '李四','部门2'
)
select *,(select org_name from org where user_name = t1.assignee_name) as org_name from gtp_done_issue t1

直接执行查询语句正常，创建为view 支持报错

doris版本：4.0.2

在select 子语句中使用标量子查询执行查询正常，创建为视图执行报错

2 Answers