Hologres出现这个报错是什么意思?子查询没问题,但合并查询后就报错了?[17:14:10] 执行结束
[17:14:10] NOTICE: One or more columns in the following table(s) do not have statistics: saas_channel
[17:14:10] [执行失败! 0 row 耗时:11.48s]
[17:14:10] 错误原因:ERROR: status { code: SERVER_INTERNAL_ERROR message: "query next from pg executor failed from 11.199.119.8:26094[query_id:10071409574629053]" err_data { filename: "parse_relation.c" lineno: 3369 funcname: "errorMissingColumn" sqlerrcode: 50360452 message: "column \"c_b03f7278_4903\" does not exist" context: "[query_id:10071409574629053]" message_id: "column \"%s\" does not exist" cursorpos: 0 internalpos: 650 internalquery: "SELECT c_b0525378_5743, c_abc83a38_8068 FROM (SELECT CASE WHEN ((c_b0525678_5747 = 30::INT4) AND CASE WHEN (c_abc87098_8076 > 0::INT8) THEN CASE WHEN (c_abc873b8_8078 = c_abc87098_8076) THEN null::BOOLEAN ELSE TRUE::BOOLEAN END ELSE FALSE::BOOLEAN END AND (c_b05257f8_5749 = 100000::INT4)) THEN c_b05254f8_5745 ELSE null::INT8 END AS c_abc83a38_8068, c_b0525378_5743 FROM (SELECT c_b0525378_5743, c_b05254f8_5745, c_b0525678_5747, c_b05257f8_5749, c_abc87098_8076, c_abc873b8_8078 FROM (SELECT count(c_abc86eb8_8075) AS c_abc87098_8076, sum(c_abc87278_8077) AS c_abc873b8_8078, c_b0525378_5743, c_b05254f8_5745, c_b0525678_5747, c_b05257f8_5749, c_b03f7278_4903, c_b03f8d78_4927, c_b0525438_5744, c_b05255b8_5746, c_b0525738_5748 FROM (SELECT CASE WHEN (c_b05254f8_5745 = c_b09b84f8_2764) IS NULL THEN 1::INT4 ELSE 0::INT4 END AS c_abc87278_8077, c_b0525378_5743, c_b0525438_5744, c_b05254f8_5745, c_b05255b8_5746, c_b0525678_5747, c_b0525738_5748, c_b05257f8_5749, c_abc86eb8_8075 FROM (SELECT c_b09b84f8_2764, c_b0525378_5743, c_b0525438_5744, c_b05254f8_5745, c_b05255b8_5746, c_b0525678_5747, c_b0525738_5748, c_b05257f8_5749, c_abc86eb8_8075 FROM (SELECT FROM hologres.hg_internal_holo_link(\'ClcKJQoECAMQBBIdCL2d1bDM/PERGhIIvZ3VsMz88REQgICQgIAKKAASLgoMMTEuMTk5LjExOS44EJSzAxgDIEIo0MOZqZaB3uMLMPW70cyN4d3jCzjFggM=\', 0) AS T_ab9cf938(c_b0525378_5743 INT8, c_b0525438_5744 INT8, c_b05254f8_5745 INT8, c_b05255b8_5746 CHAR(32), c_b0525678_5747 INT4, c_b0525738_5748 INT8, c_b05257f8_5749 INT4)) AS T_OUTER LEFT JOIN (SELECT FROM hologres.hg_internal_holo_link(\'Cl8KKQoICP////8PEAASHQi9ndWwzPzxERoSCL2d1bDM/PEREICAkIDACigAEjIKDDExLjE5OS4xMTkuOBCUswMY/////w8gCijQw5mploHe4wsw9bvRzI3h3eMLOMWCAw==\', 1) AS T_aba5b638(c_b09b84f8_2764 INT8, c_abc86eb8_8075 BOOLEAN)) T_INNER ON (c_b05254f8_5745 = c_b09b84f8_2764) IS NOT FALSE) as T_RESULT ) AS T_AGG GROUP BY c_b03f7278_4903, c_b03f8d78_4927, c_b0525378_5743, c_b0525438_5744, c_b05254f8_5745, c_b05255b8_5746, c_b0525678_5747, c_b0525738_5748, c_b05257f8_5749) as T_RESULT ) as T_RESULT ) as T_RESULT " } }
Where: [query_id:10071409574629053]
看起来是因为查询走了PQE,将sql中这个地方
count(
distinct CASE
WHEN r.type = 30 and r.consumer_id IN (SELECT consumer_id FROM tiger_dwd.marketing_user_activity_behavior_record WHERE type = 20)
AND o.order_state = 100000 THEN r.consumer_id
END
) AS first_order,
改为:
count(
distinct CASE
WHEN r.type = 30 and exists (SELECT consumer_id FROM tiger_dwd.marketing_user_activity_behavior_record WHERE type = 20 and r.consumer_id = consumer_id)
AND o.order_state = 100000 THEN r.consumer_id
END
) AS first_order,
用in会走pqe,exists走hqe,且性能好
not in和not exists对空值的处理是有区别的,要注意下子查询输出有没有null ,此回答整理自钉群“实时数仓Hologres交流群”
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
本技术圈将为大家分析有关阿里云产品Hologres的最新产品动态、技术解读等,也欢迎大家加入钉钉群--实时数仓Hologres交流群32314975