【已记录】map类型数据查询出错

Question

t_attendance_employee_month_fba8b77df8.c_custom_data 是我们自己定义的一个map数据类型
在执行sql的时候，如果我们在select中选择了这个字段，sql执行就会报错，如果不查询这个字段中的数据，sql是能够正常执行的。选择这个字段后，报错信息如下：

[HY000][1105] errCode = 2, detailMessage = (10.21.16.3)[CANCELLED][E-123][E-123] Bad cast from type doris::vectorized::ColumnNullable to doris::vectorized::ColumnArray 0. /root/src/doris-2.0/be/src/common/stack_trace.cpp:302: StackTrace::tryCapture() @ 0x000000000b8a51a7 in /opt/apache-doris/be/lib/doris_be 1. /root/src/doris-2.0/be/src/common/stack_trace.h:0: doris::get_stack_traceabi:cxx11 @ 0x000000000b8a377d in /opt/apache-doris/be/lib/doris_be 2. /var/local/ldb-toolchain/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/bits/unique_ptr.h:173: doris::Exception::Exception(int, std::basic_string_view) @ 0x000000000b349b3e in /opt/apache-doris/be/lib/doris_be 3. /root/src/doris-2.0/be/src/vec/common/typeid_cast.h:0: doris::vectorized::ColumnArray& typeid_cast(doris::vectorized::IColumn&) @ 0x000000000d96e450 in /opt/apache-doris/be/lib/doris ...

报错的sql如下：

SELECT              t_attendance_employee_month_fba8b77df8.c_company_id,
       cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ASYS20001'] as DECIMAL),
       case  when t_attendance_employee_month_fba8b77df8.c_custom_data is not null then
                    t_attendance_employee_month_fba8b77df8.c_custom_data['ASYS20001']
           else 'array(1,2)' end as custom_data_keys,
                   `t_personnel_employee_info_14a3f6be8c`.`c_date`                                             AS `c_date`


            FROM (SELECT *
                  FROM (select *,
                               get_explode_date_list('2024-03-18', '2024-03-25', c_effective_dt, c_invalid_dt, 3,
                                                     2) as dt
                        from dashboard.personnel_employee_info
                        WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                       ) x LATERAL VIEW explode(dt) tableName AS c_date
                  WHERE c_date != '') `t_personnel_employee_info_14a3f6be8c`
                     inner join (select *
                                 from (
                                          SELECT *,
                                                 case
                                                     when c_belong_date > '2024-02-29 23:59:59' and
                                                          c_belong_date <= '2024-03-31 23:59:59' then '2024-03-01'
                                                     end c_date


                                          FROM dashboard.attendance_employee_month
                                          WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                                      ) t
                                 where c_date is not null
            ) `t_attendance_employee_month_fba8b77df8` on
                        `t_personnel_employee_info_14a3f6be8c`.c_date = `t_attendance_employee_month_fba8b77df8`.c_date
                     inner join (SELECT *
                                 FROM (select *,
                                              get_explode_date_list('2024-03-18', '2024-03-25', c_effective_dt,
                                                                    c_invalid_dt, 3, 2) as dt
                                       from dashboard.personnel_department
                                       WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                                         and `c_department_id` IN
                                             ('1f03fb94386046da8379e35c3b3c60d8', '096339341f6f4d65bf528252c2fcd85d',
                                              '23f117e21e5344848ddc232e333ba5fa', '366e7b43b8d84e41b170c2db4b6e5d4e',
                                              '1471098923084ee4868d6f4101d4f774', '67b997fabc714530b23c83eea99fdf86',
                                              'ace8883be6c54bf98ed2a47155716268')
                                      ) x LATERAL VIEW explode(dt) tableName AS c_date
                                 WHERE c_date != '') `t_personnel_department_a8bdf95420` on
                        `t_personnel_employee_info_14a3f6be8c`.c_date = `t_personnel_department_a8bdf95420`.c_date;

可正常执行的sql如下：

SELECT              t_attendance_employee_month_fba8b77df8.c_company_id,
                   `t_personnel_employee_info_14a3f6be8c`.`c_date`                                             AS `c_date`


            FROM (SELECT *
                  FROM (select *,
                               get_explode_date_list('2024-03-18', '2024-03-25', c_effective_dt, c_invalid_dt, 3,
                                                     2) as dt
                        from dashboard.personnel_employee_info
                        WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                       ) x LATERAL VIEW explode(dt) tableName AS c_date
                  WHERE c_date != '') `t_personnel_employee_info_14a3f6be8c`
                     inner join (select *
                                 from (
                                          SELECT *,
                                                 case
                                                     when c_belong_date > '2024-02-29 23:59:59' and
                                                          c_belong_date <= '2024-03-31 23:59:59' then '2024-03-01'
                                                     end c_date


                                          FROM dashboard.attendance_employee_month
                                          WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                                      ) t
                                 where c_date is not null
            ) `t_attendance_employee_month_fba8b77df8` on
                        `t_personnel_employee_info_14a3f6be8c`.c_date = `t_attendance_employee_month_fba8b77df8`.c_date
                     inner join (SELECT *
                                 FROM (select *,
                                              get_explode_date_list('2024-03-18', '2024-03-25', c_effective_dt,
                                                                    c_invalid_dt, 3, 2) as dt
                                       from dashboard.personnel_department
                                       WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                                         and `c_department_id` IN
                                             ('1f03fb94386046da8379e35c3b3c60d8', '096339341f6f4d65bf528252c2fcd85d',
                                              '23f117e21e5344848ddc232e333ba5fa', '366e7b43b8d84e41b170c2db4b6e5d4e',
                                              '1471098923084ee4868d6f4101d4f774', '67b997fabc714530b23c83eea99fdf86',
                                              'ace8883be6c54bf98ed2a47155716268')
                                      ) x LATERAL VIEW explode(dt) tableName AS c_date
                                 WHERE c_date != '') `t_personnel_department_a8bdf95420` on
                        `t_personnel_employee_info_14a3f6be8c`.c_date = `t_personnel_department_a8bdf95420`.c_date;

请问下这个该怎么处理？

amory · Accepted Answer

你好可以加一下我的微信 amorynan 详细看下。

张彬华 · Answer

【问题状态】处理中
【问题处理】内部定位中，有进展会更新回帖

wskg · Answer

我们使用的建表语句是这样的，c_custom_data是个map字段

CREATE TABLE IF NOT EXISTS dashboard.attendance_employee_month
(
    `c_company_id` char(32) NOT NULL COMMENT '企业id',
    `c_emp_id` char(32) NULL COMMENT '员工id',
    `c_department_id` char(32) NULL COMMENT '部门id',
    `c_record_id` VARCHAR(32) NULL COMMENT '主键id',
    `c_month` char(32) NULL COMMENT '年',
    `c_year` char(32) NULL COMMENT '月',
    `c_belong_date` DATETIME NULL COMMENT '归属日期',
    `c_custom_data` MAP< STRING, STRING> NULL COMMENT '自定义数据'
    )
    UNIQUE KEY(`c_company_id`,`c_emp_id`,`c_department_id`,`c_record_id`)
    DISTRIBUTED BY HASH(`c_company_id`) BUCKETS 32
    PROPERTIES ('replication_allocation' = 'tag.location.default: 3',
                'bloom_filter_columns' = 'c_company_id, c_emp_id, c_department_id'
               );

现在的情况是，我们做了3表的关联join，如果我们将attendance_employee_month这个表放在中间位置join查询，就会报错，如果我们将这个表的使用顺序调换一下，放置在最后面，那就不会报错了。对应的两条sql如下：

##  原始报错sql
SELECT SUM(cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec7075328477'] as DECIMAL)) AS `WAGE_CUS_11958701594`
     , SUM(cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec7075328479'] as DECIMAL)) AS `WAGE_CUS_73272097792`
     , COUNT(distinct cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec7075328446'] as
                           STRING))                                                                                   AS `WAGE_CUS_95665096430`
     , SUM(cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec707532841a'] as DECIMAL)) AS `WAGE_CUS_44839387895`
     , department_level_convert(`t_personnel_department_a8bdf95420`.`ORG_SYS_00000000002`,
                                1)                                                                                    AS `ORG_SYS_00000000002`
     , `t_personnel_employee_info_14a3f6be8c`.`c_date`                                                                AS `c_date`


FROM (SELECT *
      FROM (select *,
                   get_explode_date_list('2024-02-01', '2024-03-25', c_effective_dt, c_invalid_dt, 3, 2) as dt
            from dashboard.personnel_employee_info
            WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
           ) x LATERAL VIEW explode(dt) tableName AS c_date
      WHERE c_date != '') `t_personnel_employee_info_14a3f6be8c`
         inner join (select *
                     from (
                              SELECT *
                                   , case
                                         when c_belong_date > '2024-01-31 23:59:59' and
                                              c_belong_date <= '2024-02-29 23:59:59' then '2024-02-01'
                                         when c_belong_date > '2024-02-29 23:59:59' and
                                              c_belong_date <= '2024-03-28 23:59:59' then '2024-03-01'
                                  end c_date


                              FROM dashboard.attendance_employee_month
                              WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                          ) t
                     where c_date is not null
) `t_attendance_employee_month_fba8b77df8` on
            `t_personnel_employee_info_14a3f6be8c`.c_employee_id = `t_attendance_employee_month_fba8b77df8`.c_emp_id and
            `t_personnel_employee_info_14a3f6be8c`.c_date = `t_attendance_employee_month_fba8b77df8`.c_date
         inner join (SELECT *
                          , department_level_convert(c_department_id, 1) AS `ORG_SYS_00000000002`


                     FROM (select *,
                                  get_explode_date_list('2024-02-01', '2024-03-25', c_effective_dt, c_invalid_dt, 3,
                                                        2) as dt
                           from dashboard.personnel_department
                           WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
                             and `c_department_id` IN
                                 ('1f03fb94386046da8379e35c3b3c60d8', '096339341f6f4d65bf528252c2fcd85d',
                                  '23f117e21e5344848ddc232e333ba5fa', '366e7b43b8d84e41b170c2db4b6e5d4e',
                                  '1471098923084ee4868d6f4101d4f774', '67b997fabc714530b23c83eea99fdf86',
                                  '7a755ccd25804816a1f4e861cf7a9a88', 'a7092cf40b3b46cbb17f80786e8a073c',
                                  '570ba88f4edb4ba9a7f070f09aac83d9', 'df5d5b5dd92a4533825062aebd7ea297',
                                  '6142c131091841b8948d1d064f422ad5', '939dc5a909044b36b6c5553281eb925f',
                                  '949d57f43d2a448e912a1b4508e6be5b', '1914358acebc4c7c88621d7d1008af39',
                                  'dbda64cf01c340cb9ebed31c18c48a40', '0eb0be8f64b340849aafce46c5c1a589',
                                  'b13d6e1be9c648e78f077f9a74c9e958', '066bd17afbbc46b58ad13ce1a4b70efa',
                                  '30eac4f92fb54c19a4d81f7be01c31da', 'fb4c6fbd696445a280f3be29c4847c63',
                                  'b6a88ff5c2cf4e00b9a7aec951125eab', 'a10ace6e2bbd42789f42c45d32c9137a',
                                  'f219814334da4e41a9de7e918e8353b6', 'bffd60f21f004310b2fdf05c3ac261c0',
                                  '5b15e85f414746ef93545981b5aa2b6e', '7988400971b44c4aa304e9bbc4eedd37',
                                  'eed86b5b87e94355b751743208780a83', 'f10c6899ea3f4aa3b3b1ac78ff493f13',
                                  '8cd574e134c249c0b6a7cc9e967f5bd0', 'bcae8f28d9f341679436fc1c5f6cc3ae',
                                  'f8318c4b574945eeab178513e43bb580', '2cc832e1379442279ef9911138f5610d',
                                  '778acf323000420d9304516a2fb40e15', 'ed26acf0d4de4a5eadf2679bb323fd4c',
                                  '1ccb1606b1d744fda9ab4256d3363594', '0096af33b4fd48468040b9b5f4145030',
                                  '545442e89f5a4eae8765f9e23518695b', '7cead325dab34db5bee43fa3005e571f',
                                  '4f750db3b40f4e3984dba2f2bd870f08', 'afd579ca06704fa5b8ae9d25d411989e',
                                  'dab4ef2b88a74bf49c564eef354adcb7', '6ee77bf6de4f45d8bf274c11303f43fd',
                                  '6d6ef548454a4eadb4c2af854bd9f303', '8eecb4891008477faeda7f09834fe3da',
                                  'ace8883be6c54bf98ed2a47155716268')
                          ) x LATERAL VIEW explode(dt) tableName AS c_date
                     WHERE c_date != '') `t_personnel_department_a8bdf95420` on
            `t_personnel_employee_info_14a3f6be8c`.c_department_id =
            `t_personnel_department_a8bdf95420`.c_department_id and
            `t_personnel_employee_info_14a3f6be8c`.c_date = `t_personnel_department_a8bdf95420`.c_date

GROUP BY `ORG_SYS_00000000002`, `c_date`;

###  调整后运行正常sql
SELECT SUM(cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec7075328477'] as DECIMAL)) AS `WAGE_CUS_11958701594`
     , SUM(cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec7075328479'] as DECIMAL)) AS `WAGE_CUS_73272097792`
     , COUNT(distinct cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec7075328446'] as
                           STRING))                                                                                   AS `WAGE_CUS_95665096430`
     , SUM(cast(`t_attendance_employee_month_fba8b77df8`.`c_custom_data`['ITEM633544581300ec707532841a'] as DECIMAL)) AS `WAGE_CUS_44839387895`
     , department_level_convert(`t_personnel_department_a8bdf95420`.`ORG_SYS_00000000002`,
                                1)                                                                                    AS `ORG_SYS_00000000002`
     , `t_personnel_employee_info_14a3f6be8c`.`c_date`                                                                AS `c_date`


FROM (SELECT *
      FROM (select *,
                   get_explode_date_list('2024-02-01', '2024-03-25', c_effective_dt, c_invalid_dt, 3, 2) as dt
            from dashboard.personnel_employee_info
            WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
           ) x LATERAL VIEW explode(dt) tableName AS c_date
      WHERE c_date != '') `t_personnel_employee_info_14a3f6be8c`
         inner join
     (SELECT *
           , department_level_convert(c_department_id, 1) AS `ORG_SYS_00000000002`


      FROM (select *,
                   get_explode_date_list('2024-02-01', '2024-03-25', c_effective_dt, c_invalid_dt, 3, 2) as dt
            from dashboard.personnel_department
            WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
              and `c_department_id` IN ('1f03fb94386046da8379e35c3b3c60d8', '096339341f6f4d65bf528252c2fcd85d',
                                        '23f117e21e5344848ddc232e333ba5fa', '366e7b43b8d84e41b170c2db4b6e5d4e',
                                        '1471098923084ee4868d6f4101d4f774', '67b997fabc714530b23c83eea99fdf86',
                                        '7a755ccd25804816a1f4e861cf7a9a88', 'a7092cf40b3b46cbb17f80786e8a073c',
                                        '570ba88f4edb4ba9a7f070f09aac83d9', 'df5d5b5dd92a4533825062aebd7ea297',
                                        '6142c131091841b8948d1d064f422ad5', '939dc5a909044b36b6c5553281eb925f',
                                        '949d57f43d2a448e912a1b4508e6be5b', '1914358acebc4c7c88621d7d1008af39',
                                        'dbda64cf01c340cb9ebed31c18c48a40', '0eb0be8f64b340849aafce46c5c1a589',
                                        'b13d6e1be9c648e78f077f9a74c9e958', '066bd17afbbc46b58ad13ce1a4b70efa',
                                        '30eac4f92fb54c19a4d81f7be01c31da', 'fb4c6fbd696445a280f3be29c4847c63',
                                        'b6a88ff5c2cf4e00b9a7aec951125eab', 'a10ace6e2bbd42789f42c45d32c9137a',
                                        'f219814334da4e41a9de7e918e8353b6', 'bffd60f21f004310b2fdf05c3ac261c0',
                                        '5b15e85f414746ef93545981b5aa2b6e', '7988400971b44c4aa304e9bbc4eedd37',
                                        'eed86b5b87e94355b751743208780a83', 'f10c6899ea3f4aa3b3b1ac78ff493f13',
                                        '8cd574e134c249c0b6a7cc9e967f5bd0', 'bcae8f28d9f341679436fc1c5f6cc3ae',
                                        'f8318c4b574945eeab178513e43bb580', '2cc832e1379442279ef9911138f5610d',
                                        '778acf323000420d9304516a2fb40e15', 'ed26acf0d4de4a5eadf2679bb323fd4c',
                                        '1ccb1606b1d744fda9ab4256d3363594', '0096af33b4fd48468040b9b5f4145030',
                                        '545442e89f5a4eae8765f9e23518695b', '7cead325dab34db5bee43fa3005e571f',
                                        '4f750db3b40f4e3984dba2f2bd870f08', 'afd579ca06704fa5b8ae9d25d411989e',
                                        'dab4ef2b88a74bf49c564eef354adcb7', '6ee77bf6de4f45d8bf274c11303f43fd',
                                        '6d6ef548454a4eadb4c2af854bd9f303', '8eecb4891008477faeda7f09834fe3da',
                                        'ace8883be6c54bf98ed2a47155716268')
           ) x LATERAL VIEW explode(dt) tableName AS c_date
      WHERE c_date != '') `t_personnel_department_a8bdf95420` on
                 `t_personnel_employee_info_14a3f6be8c`.c_department_id =
                 `t_personnel_department_a8bdf95420`.c_department_id and
                 `t_personnel_employee_info_14a3f6be8c`.c_date = `t_personnel_department_a8bdf95420`.c_date

         inner join
     (select *
      from (
               SELECT *
                    , case
                          when c_belong_date > '2024-01-31 23:59:59' and c_belong_date <= '2024-02-29 23:59:59'
                              then '2024-02-01'
                          when c_belong_date > '2024-02-29 23:59:59' and c_belong_date <= '2024-03-28 23:59:59'
                              then '2024-03-01'
                   end c_date


               FROM dashboard.attendance_employee_month
               WHERE c_company_id = '6e93233ef19a4220be4816d341afcdc1'
           ) t
      where c_date is not null
     ) `t_attendance_employee_month_fba8b77df8` on
                 `t_personnel_employee_info_14a3f6be8c`.c_employee_id =
                 `t_attendance_employee_month_fba8b77df8`.c_emp_id and
                 `t_personnel_employee_info_14a3f6be8c`.c_date = `t_attendance_employee_month_fba8b77df8`.c_date

GROUP BY `ORG_SYS_00000000002`, `c_date`;

【已记录】map类型数据查询出错

3 Answers