开发者社区 > 大数据与机器学习 > 实时数仓 Hologres > 正文

Hologres出现这个报错是什么意思?子查询没问题,但合并查询后就报错了?

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]

展开
收起
真的很搞笑 2024-04-06 08:44:52 70 0
1 条回答
写回答
取消 提交回答
  • 看起来是因为查询走了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交流群”

    2024-04-06 12:41:12
    赞同 1 展开评论 打赏

本技术圈将为大家分析有关阿里云产品Hologres的最新产品动态、技术解读等,也欢迎大家加入钉钉群--实时数仓Hologres交流群32314975

相关产品

  • 实时数仓 Hologres
  • 相关电子书

    更多
    实时数仓Workshop(广州站)- 李佳林 立即下载
    阿里云实时数仓Hologres技术揭秘2.0 立即下载
    实时数仓Hologres技术实战一本通2.0版(下) 立即下载