2.1.7 视图使用GROUP_CONCAT、group by查询报错

Viewed 47

errCode = 2, detailMessage = Unknown column 'id' in 'zzt_record.t'

库:zzt_record 建表语句

create table tb_record_project_key_indicators
(
    id                 varchar(300) null,
    performance_type   varchar(30)  null,
    contract_type      varchar(30)  null,
    qualification_code varchar(192) null,
    qualification_name varchar(192) null,
    project_level      varchar(60)  null,
    prj_num            varchar(192) null,
    tenant_id          bigint       null,
    project_name       varchar(765) null,
    start_time         datetime     null,
    end_time           datetime     null,
    data_provider      varchar(30)  null,
    prj_guid           varchar(192) null,
    perf_num           varchar(150) null,
    data_level         varchar(3)   null
)
    engine = Doris
    collate = `utf-8` partition by unpartitioned
(
    NULL
) subpartition by null
(
    NULL
);

库:zzt_business 建表语句

create table tb_log_table_operation
(
    id             bigint       null,
    data_row_guid  varchar(765) null,
    table_name     varchar(765) null,
    table_content string null,
    tenant_id      bigint       null,
    operation_type varchar(30)  null,
    fk_row_guid    varchar(300) null
)
    engine = Doris
    collate = `utf-8` partition by unpartitioned
(
    NULL
) subpartition by null
(
    NULL
);
create view tb_project_key_indicators as
select (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.id')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.id'))
            else `internal`.`zzt_record`.`t`.`id`
    end)                                           as `id`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.performanceType')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.performanceType'))
            else `internal`.`zzt_record`.`t`.`performance_type`
           end)                                    as `performance_type`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.contractType')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.contractType'))
            else `internal`.`zzt_record`.`t`.`contract_type`
           end)                                    as `contract_type`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.qualificationCode')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.qualificationCode'))
            else `internal`.`zzt_record`.`t`.`qualification_code`
           end)                                    as `qualification_code`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.qualificationName')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.qualificationName'))
            else `internal`.`zzt_record`.`t`.`qualification_name`
           end)                                    as `qualification_name`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.projectLevel')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.projectLevel'))
            else `internal`.`zzt_record`.`t`.`project_level`
           end)                                    as `project_level`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.prjNum')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.prjNum'))
            else `internal`.`zzt_record`.`t`.`prj_num`
           end)                                    as `prj_num`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.projectName')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.projectName'))
            else `internal`.`zzt_record`.`t`.`project_name`
           end)                                    as `project_name`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.startTime')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.startTime'))
            else `internal`.`zzt_record`.`t`.`start_time`
           end)                                    as `start_time`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.endTime')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.endTime'))
            else `internal`.`zzt_record`.`t`.`end_time`
           end)                                    as `end_time`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.prjGuid')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.prjGuid'))
            else `internal`.`zzt_record`.`t`.`prj_guid`
           end)                                    as `prj_guid`,
       (case
            when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
                and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                               '$.dataLevel')) is not null)) then json_unquote(json_extract(
                    `internal`.`zzt_business`.`lt`.`table_content`,
                    '$.dataLevel'))
            else `internal`.`zzt_record`.`t`.`data_level`
           end)                                    as `data_level`,
       '[]'                                        as `technical_index_item`,
       `internal`.`zzt_record`.`t`.`data_provider` as `data_provider`,
       `internal`.`zzt_record`.`t`.`tenant_id`     as `tenant_id`
from `internal`.`zzt_record`.`tb_record_project_key_indicators` `t`
         left join `internal`.`zzt_business`.`tb_log_table_operation` `lt` on
    `internal`.`zzt_record`.`t`.`id` = `internal`.`zzt_business`.`lt`.`data_row_guid`
        and `internal`.`zzt_business`.`lt`.`table_name` = 'tb_project_key_indicators'
union all
select `internal`.`zzt_business`.`lt`.`data_row_guid`    as `id`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.performanceType'))   as `performance_type`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.contractType'))      as `contract_type`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.qualificationCode')) as `qualification_code`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.qualificationName')) as `qualification_name`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.projectLevel'))      as `project_level`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.prjNum'))            as `prj_num`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.projectName'))       as `project_name`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.startTime'))         as `start_time`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.endTime'))           as `end_time`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.prjGuid'))           as `prj_guid`,
       json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
                                 '$.dataLevel'))         as `data_level`,
       '[]'                                              as `technical_index_item`,
       null                                              as `data_provider`,
       `internal`.`zzt_business`.`lt`.`tenant_id`        as `tenant_id`
from `internal`.`zzt_business`.`tb_log_table_operation` `lt`
where ((`internal`.`zzt_business`.`lt`.`operation_type` = 'insert')
    and (`internal`.`zzt_business`.`lt`.`table_name` = 'tb_project_key_indicators'));


查询语句

SELECT CASE
           WHEN tpki.performance_type = '5' THEN '4'
           WHEN tpki.performance_type = '7' THEN '3'
           WHEN tpki.performance_type = '1' THEN '1'
           WHEN tpki.performance_type = '2' THEN '2' END                           AS performance_type,
       tpki.qualification_name,
       tpki.project_level,
       tpi.prj_name,
       tpki.id,
       tpi.row_guid                                                                AS prj_row_guid,
       GROUP_CONCAT(DISTINCT CASE
                                 WHEN tiru.primary_duty = '1' THEN '项目负责人'
                                 WHEN tiru.primary_duty = '2' THEN '技术负责人'
                                 WHEN tiru.primary_duty = '3' THEN '专业负责人'
                                 WHEN tiru.primary_duty = '4' THEN '主要设计人'
                                 WHEN tiru.primary_duty = '5' THEN '总监理工程师'
                                 WHEN tiru.primary_duty = '6' THEN '项目经理' END) AS primary_duty,
       tpki.data_provider
FROM tb_project_key_indicators tpki
         JOIN tb_project_info tpi ON tpki.prj_guid = tpi.row_guid
         JOIN tb_indicators_relation_user tiru
              ON tiru.indicators_id = tpki.id AND tiru.person_name = 'xxx' AND tiru.idcard_dese = 'xxx'
WHERE (tpki.tenant_id = 18 OR tpki.tenant_id = 0 OR tpki.tenant_id IS NULL OR tpki.tenant_id IN (2))
GROUP BY tpki.id
ORDER BY tpi.prj_name

视图中的字段只要在group by以及GROUP_CONCAT中使用就会报Unknown column 'id' in 'zzt_record.t'

1 Answers

这个视图是什么时候创建的呀,可能是在老优化器上创建的,2.1.7 默认走的新优化器,可能会有些问题,重新基于新优化器创建下,看看还有报错不

确定 show variables like "%experimental_enable_nereids_planner%"; 结果为true。

如果还有问题的话,您私聊我下主页微信下,我们一起看下的