search

apache / doris

Apache Doris is an easy-to-use, high performance and unified analytics database.
https://doris.apache.org
Apache License 2.0
12.2k stars 3.19k forks source link

[Bug] Multiple subqueries executing Lateral VIEW explore_ Split causes data to return null #30066

Open cjj2010 opened 7 months ago

cjj2010 commented 7 months ago

Search before asking

Version

2.0.3

What's Wrong?

Multiple subqueries executing Lateral VIEW explore_ Split causes data to return null

SELECT
  `t4`.`57CE5E02901753F7_06a28`,
  `t4`.`57CE5E02520653F7_06a28`,
  `t4`.`65F695F4519253F7_06a28`,
  `t4`.`65E5671F659C6760_06a28`,
  `t4`.`57CE5E027A7A683C_06a28`,
  `t4`.`57CE5E02767E520653F7_06a28a4`,
  `t4`.`65705B575C0F657070B9_06a28a4`,
  `t4`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
  `t4`.`5F88591A7B2653F7_06a28`,
  `t4`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`,
  `ltv4`.`57CE5E02901753F7_62C65206884C7ED3679C1_06a28a5a17a39`
FROM
  (
    SELECT
      `fise21`.`57CE5E02901753F7_06a28` AS `57CE5E02901753F7_06a28`,
      `fise21`.`57CE5E02520653F7_06a28` AS `57CE5E02520653F7_06a28`,
      `fise21`.`65F695F4519253F7_06a28` AS `65F695F4519253F7_06a28`,
      `fise21`.`65E5671F659C6760_06a28` AS `65E5671F659C6760_06a28`,
      `fise21`.`57CE5E027A7A683C_06a28` AS `57CE5E027A7A683C_06a28`,
      `fise21`.`57CE5E02767E520653F7_06a28a4` AS `57CE5E02767E520653F7_06a28a4`,
      `fise21`.`65705B575C0F657070B9_06a28a4` AS `65705B575C0F657070B9_06a28a4`,
      `fise21`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` AS `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
      `fise21`.`5F88591A7B2653F7_06a28` AS `5F88591A7B2653F7_06a28`,
      `fise21`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39` AS `57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
    FROM
      (
        SELECT
          `t2`.`57CE5E02901753F7_06a28`,
          `t2`.`57CE5E02520653F7_06a28`,
          `t2`.`65F695F4519253F7_06a28`,
          `t2`.`65E5671F659C6760_06a28`,
          `t2`.`57CE5E027A7A683C_06a28`,
          `t2`.`57CE5E02767E520653F7_06a28a4`,
          `t2`.`65705B575C0F657070B9_06a28a4`,
          `t2`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
          `t2`.`5F88591A7B2653F7_06a28`,
          `ltv2`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
        FROM
          (
            SELECT
              `fise01`.`57CE5E02901753F7_06a28` AS `57CE5E02901753F7_06a28`,
              `fise01`.`57CE5E02520653F7_06a28` AS `57CE5E02520653F7_06a28`,
              `fise01`.`65F695F4519253F7_06a28` AS `65F695F4519253F7_06a28`,
              `fise01`.`65E5671F659C6760_06a28` AS `65E5671F659C6760_06a28`,
              `fise01`.`57CE5E027A7A683C_06a28` AS `57CE5E027A7A683C_06a28`,
              `fise01`.`57CE5E02767E520653F7_06a28a4` AS `57CE5E02767E520653F7_06a28a4`,
              `fise01`.`65705B575C0F657070B9_06a28a4` AS `65705B575C0F657070B9_06a28a4`,
              `fise01`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` AS `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
              `fise01`.`5F88591A7B2653F7_06a28` AS `5F88591A7B2653F7_06a28`
            FROM
              (
                SELECT
                  `t0`.`57CE5E02901753F7_06a28`,
                  `t0`.`57CE5E02520653F7_06a28`,
                  `t0`.`65F695F4519253F7_06a28`,
                  `t0`.`65E5671F659C6760_06a28`,
                  `t0`.`57CE5E027A7A683C_06a28`,
                  `t0`.`57CE5E02767E520653F7_06a28a4`,
                  `t0`.`65705B575C0F657070B9_06a28a4`,
                  `t0`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
                  `t0`.`5F88591A7B2653F7_06a28`
                FROM
                  `ods_42378777c342d2da36d05db875393811` `t0`
              ) `fise01`
          ) `t2` LATERAL VIEW explode_split(`t2`.`57CE5E02901753F7_06a28`, ',') `ltv2` AS `57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
      ) `fise21`
  ) `t4` LATERAL VIEW explode_split(`t4`.`57CE5E02901753F7_06a28`, ',') `ltv4` AS `57CE5E02901753F7_62C65206884C7ED3679C1_06a28a5a17a39`

image

when I execute one Lateral VIEW explore_ Split is normal

 SELECT
      `fise21`.`57CE5E02901753F7_06a28` AS `57CE5E02901753F7_06a28`,
      `fise21`.`57CE5E02520653F7_06a28` AS `57CE5E02520653F7_06a28`,
      `fise21`.`65F695F4519253F7_06a28` AS `65F695F4519253F7_06a28`,
      `fise21`.`65E5671F659C6760_06a28` AS `65E5671F659C6760_06a28`,
      `fise21`.`57CE5E027A7A683C_06a28` AS `57CE5E027A7A683C_06a28`,
      `fise21`.`57CE5E02767E520653F7_06a28a4` AS `57CE5E02767E520653F7_06a28a4`,
      `fise21`.`65705B575C0F657070B9_06a28a4` AS `65705B575C0F657070B9_06a28a4`,
      `fise21`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` AS `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
      `fise21`.`5F88591A7B2653F7_06a28` AS `5F88591A7B2653F7_06a28`,
      `fise21`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39` AS `57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
    FROM
      (
        SELECT
          `t2`.`57CE5E02901753F7_06a28`,
          `t2`.`57CE5E02520653F7_06a28`,
          `t2`.`65F695F4519253F7_06a28`,
          `t2`.`65E5671F659C6760_06a28`,
          `t2`.`57CE5E027A7A683C_06a28`,
          `t2`.`57CE5E02767E520653F7_06a28a4`,
          `t2`.`65705B575C0F657070B9_06a28a4`,
          `t2`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
          `t2`.`5F88591A7B2653F7_06a28`,
          `ltv2`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
        FROM
          (
            SELECT
              `fise01`.`57CE5E02901753F7_06a28` AS `57CE5E02901753F7_06a28`,
              `fise01`.`57CE5E02520653F7_06a28` AS `57CE5E02520653F7_06a28`,
              `fise01`.`65F695F4519253F7_06a28` AS `65F695F4519253F7_06a28`,
              `fise01`.`65E5671F659C6760_06a28` AS `65E5671F659C6760_06a28`,
              `fise01`.`57CE5E027A7A683C_06a28` AS `57CE5E027A7A683C_06a28`,
              `fise01`.`57CE5E02767E520653F7_06a28a4` AS `57CE5E02767E520653F7_06a28a4`,
              `fise01`.`65705B575C0F657070B9_06a28a4` AS `65705B575C0F657070B9_06a28a4`,
              `fise01`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` AS `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
              `fise01`.`5F88591A7B2653F7_06a28` AS `5F88591A7B2653F7_06a28`
            FROM
              (
                SELECT
                  `t0`.`57CE5E02901753F7_06a28`,
                  `t0`.`57CE5E02520653F7_06a28`,
                  `t0`.`65F695F4519253F7_06a28`,
                  `t0`.`65E5671F659C6760_06a28`,
                  `t0`.`57CE5E027A7A683C_06a28`,
                  `t0`.`57CE5E02767E520653F7_06a28a4`,
                  `t0`.`65705B575C0F657070B9_06a28a4`,
                  `t0`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
                  `t0`.`5F88591A7B2653F7_06a28`
                FROM
                  `ods_42378777c342d2da36d05db875393811` `t0`
              ) `fise01`
          ) `t2` LATERAL VIEW explode_split(`t2`.`57CE5E02901753F7_06a28`, ',') `ltv2` AS `57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
      ) `fise21`

image

What You Expected?

Return to normal data

How to Reproduce?

1、Create table 


CREATE TABLE `ods_42378777c342d2da36d05db875393811` (
  `57CE5E02901753F7_06a28` varchar(*) NULL,
  `57CE5E02520653F7_06a28` varchar(*) NULL,
  `65F695F4519253F7_06a28` varchar(*) NULL,
  `65E5671F659C6760_06a28` varchar(*) NULL,
  `57CE5E027A7A683C_06a28` varchar(*) NULL,
  `57CE5E02767E520653F7_06a28a4` varchar(*) NULL,
  `65705B575C0F657070B9_06a28a4` varchar(*) NULL,
  `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` DECIMAL(38, 10) NULL,
  `5F88591A7B2653F7_06a28` varchar(*) NULL
) ENGINE=OLAP
DUPLICATE KEY(`57CE5E02901753F7_06a28`)
COMMENT 'OLAP'
DISTRIBUTED BY RANDOM BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

2、execute dml

INSERT INTO ods_42378777c342d2da36d05db875393811 (`57CE5E02901753F7_06a28`,`57CE5E02520653F7_06a28`,`65F695F4519253F7_06a28`,`65E5671F659C6760_06a28`,`57CE5E027A7A683C_06a28`,`57CE5E02767E520653F7_06a28a4`,`65705B575C0F657070B9_06a28a4`,qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_,`5F88591A7B2653F7_06a28`) VALUES
         ('云浮,肇庆,韶关,佛山','云浮;肇庆;韶关;佛山','1899-12-31 15:11:22','2024-01-12 00:00:00','云浮 肇庆 韶关 佛山','云浮%肇庆%韶关%佛山','33..33.*.@3',3.3300000000,''),
         ('北京,上海,广州,深圳','北京;上海;广州;深圳','1899-12-31 00:11:22','2024-01-10 00:00:00','北京 上海 广州 深圳','北京%上海%广州%深圳','99..99.*.@9',1.1100000000,'~！@#￥%……&*（）——+=【】；‘，。、、'),
         ('清远,河源,江门,湛江','清远;河源;江门;湛江','1899-12-31 12:11:22','2024-01-11 00:00:00','清远 河源 江门 湛江','清远%河源%江门%湛江','22..22.*.@2',2.2200000000,'131.0');

3、execute query

`SELECT
  `t4`.`57CE5E02901753F7_06a28`,
  `t4`.`57CE5E02520653F7_06a28`,
  `t4`.`65F695F4519253F7_06a28`,
  `t4`.`65E5671F659C6760_06a28`,
  `t4`.`57CE5E027A7A683C_06a28`,
  `t4`.`57CE5E02767E520653F7_06a28a4`,
  `t4`.`65705B575C0F657070B9_06a28a4`,
  `t4`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
  `t4`.`5F88591A7B2653F7_06a28`,
  `t4`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`,
  `ltv4`.`57CE5E02901753F7_62C65206884C7ED3679C1_06a28a5a17a39`
FROM
  (
    SELECT
      `fise21`.`57CE5E02901753F7_06a28` AS `57CE5E02901753F7_06a28`,
      `fise21`.`57CE5E02520653F7_06a28` AS `57CE5E02520653F7_06a28`,
      `fise21`.`65F695F4519253F7_06a28` AS `65F695F4519253F7_06a28`,
      `fise21`.`65E5671F659C6760_06a28` AS `65E5671F659C6760_06a28`,
      `fise21`.`57CE5E027A7A683C_06a28` AS `57CE5E027A7A683C_06a28`,
      `fise21`.`57CE5E02767E520653F7_06a28a4` AS `57CE5E02767E520653F7_06a28a4`,
      `fise21`.`65705B575C0F657070B9_06a28a4` AS `65705B575C0F657070B9_06a28a4`,
      `fise21`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` AS `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
      `fise21`.`5F88591A7B2653F7_06a28` AS `5F88591A7B2653F7_06a28`,
      `fise21`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39` AS `57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
    FROM
      (
        SELECT
          `t2`.`57CE5E02901753F7_06a28`,
          `t2`.`57CE5E02520653F7_06a28`,
          `t2`.`65F695F4519253F7_06a28`,
          `t2`.`65E5671F659C6760_06a28`,
          `t2`.`57CE5E027A7A683C_06a28`,
          `t2`.`57CE5E02767E520653F7_06a28a4`,
          `t2`.`65705B575C0F657070B9_06a28a4`,
          `t2`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
          `t2`.`5F88591A7B2653F7_06a28`,
          `ltv2`.`57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
        FROM
          (
            SELECT
              `fise01`.`57CE5E02901753F7_06a28` AS `57CE5E02901753F7_06a28`,
              `fise01`.`57CE5E02520653F7_06a28` AS `57CE5E02520653F7_06a28`,
              `fise01`.`65F695F4519253F7_06a28` AS `65F695F4519253F7_06a28`,
              `fise01`.`65E5671F659C6760_06a28` AS `65E5671F659C6760_06a28`,
              `fise01`.`57CE5E027A7A683C_06a28` AS `57CE5E027A7A683C_06a28`,
              `fise01`.`57CE5E02767E520653F7_06a28a4` AS `57CE5E02767E520653F7_06a28a4`,
              `fise01`.`65705B575C0F657070B9_06a28a4` AS `65705B575C0F657070B9_06a28a4`,
              `fise01`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_` AS `qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
              `fise01`.`5F88591A7B2653F7_06a28` AS `5F88591A7B2653F7_06a28`
            FROM
              (
                SELECT
                  `t0`.`57CE5E02901753F7_06a28`,
                  `t0`.`57CE5E02520653F7_06a28`,
                  `t0`.`65F695F4519253F7_06a28`,
                  `t0`.`65E5671F659C6760_06a28`,
                  `t0`.`57CE5E027A7A683C_06a28`,
                  `t0`.`57CE5E02767E520653F7_06a28a4`,
                  `t0`.`65705B575C0F657070B9_06a28a4`,
                  `t0`.`qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq_`,
                  `t0`.`5F88591A7B2653F7_06a28`
                FROM
                  `ods_42378777c342d2da36d05db875393811` `t0`
              ) `fise01`
          ) `t2` LATERAL VIEW explode_split(`t2`.`57CE5E02901753F7_06a28`, ',') `ltv2` AS `57CE5E02901753F7_62C65206884C7ED3679C_06a28a5a17a39`
      ) `fise21`
  ) `t4` LATERAL VIEW explode_split(`t4`.`57CE5E02901753F7_06a28`, ',') `ltv4` AS `57CE5E02901753F7_62C65206884C7ED3679C1_06a28a5a17a39``

Anything Else?

No response

Are you willing to submit PR?

Code of Conduct

zclllyybb commented 7 months ago

https://github.com/apache/doris/pull/30150 will fix it