search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.35k stars 5.85k forks source link

CTE hang or result wrong when multiple Apply is used #55551

Closed XuHuaiyu closed 3 months ago

XuHuaiyu commented 3 months ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists table_a, table_b, table_c, table_d, table_e;
CREATE TABLE `table_a` (
  `col_1` varchar(40) DEFAULT NULL,
  `col_2` varchar(40) DEFAULT NULL,
  `col_3` varchar(500) DEFAULT NULL,
  `col_4` varchar(500) DEFAULT NULL,
  `col_5` varchar(500) DEFAULT NULL,
  `col_6` varchar(500) DEFAULT NULL,
  `col_7` decimal(38,6) DEFAULT NULL,
  `col_8` decimal(38,6) DEFAULT NULL,
  `col_9` decimal(38,6) DEFAULT NULL,
  `col_10` decimal(38,6) DEFAULT NULL,
  `col_11` decimal(38,6) DEFAULT NULL,
  `col_12` decimal(38,6) DEFAULT NULL,
  `col_13` decimal(38,6) DEFAULT NULL,
  `col_14` decimal(38,6) DEFAULT NULL,
  `col_15` decimal(38,6) DEFAULT NULL,
  `col_16` decimal(38,6) DEFAULT NULL,
  `col_17` decimal(38,6) DEFAULT NULL,
  `col_18` decimal(38,6) DEFAULT NULL,
  `col_19` varchar(40) DEFAULT NULL,
  `col_20` varchar(100) DEFAULT NULL,
  `col_21` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  KEY `index_col_1` (`col_1`),
  KEY `index_col_2_3` (`col_2`,`col_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_b` (
  `col_1` varchar(8) NOT NULL,
  `col_2` varchar(100) NOT NULL,
  `col_3` varchar(200) DEFAULT NULL,
  `col_4` varchar(10) NOT NULL,
  `col_5` decimal(38,6) DEFAULT NULL,
  `col_6` decimal(38,6) DEFAULT NULL,
  `col_7` decimal(38,6) DEFAULT NULL,
  `col_8` decimal(38,6) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  KEY `index_col_1_2` (`col_1`,`col_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_c` (
  `col_1` varchar(50) DEFAULT NULL,
  `col_2` varchar(50) DEFAULT NULL,
  `col_3` varchar(30) DEFAULT NULL,
  `col_4` varchar(100) DEFAULT NULL,
  `col_5` varchar(50) DEFAULT NULL,
  `col_6` varchar(100) DEFAULT NULL,
  `col_7` decimal(38,6) DEFAULT NULL,
  `col_8` decimal(38,6) DEFAULT NULL,
  `col_9` decimal(38,6) DEFAULT NULL,
  `col_10` decimal(38,6) DEFAULT NULL,
  `col_11` decimal(38,6) DEFAULT NULL,
  `col_12` decimal(38,6) DEFAULT NULL,
  `col_13` decimal(38,6) DEFAULT NULL,
  `col_14` decimal(38,6) DEFAULT NULL,
  `col_15` decimal(38,6) DEFAULT NULL,
  `col_16` decimal(38,6) DEFAULT NULL,
  `col_17` varchar(50) DEFAULT NULL,
  `col_18` varchar(50) DEFAULT NULL,
  `col_19` varchar(50) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  KEY `index_col_1_3` (`col_1`,`col_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_d` (
  `col_1` decimal(10,0) DEFAULT NULL,
  `col_2` varchar(1) DEFAULT NULL,
  `col_3` date NOT NULL,
  `col_4` varchar(13) DEFAULT NULL,
  `col_5` varchar(6) DEFAULT NULL,
  `col_6` varchar(255) DEFAULT NULL,
  `col_7` decimal(10,0) DEFAULT NULL,
  `col_8` varchar(12) DEFAULT NULL,
  `col_9` decimal(10,0) DEFAULT NULL,
  `col_10` varchar(17) DEFAULT NULL,
  `col_11` decimal(10,0) DEFAULT NULL,
  `col_12` varchar(7) DEFAULT NULL,
  `col_13` date DEFAULT NULL,
  `col_14` date DEFAULT NULL,
  `col_15` date DEFAULT NULL,
  `col_16` date DEFAULT NULL,
  `col_17` date DEFAULT NULL,
  `col_18` date DEFAULT NULL,
  `col_19` date DEFAULT NULL,
  `col_20` varchar(1) DEFAULT NULL,
  `col_21` varchar(1) DEFAULT NULL,
  `col_22` decimal(1,0) DEFAULT NULL,
  `col_23` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table_e` (
  `col_1` varchar(8) NOT NULL,
  `col_2` varchar(100) NOT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  `col_4` varchar(100) NOT NULL,
  `col_5` varchar(100) DEFAULT NULL,
  `col_6` varchar(100) DEFAULT NULL,
  `col_7` decimal(38,12) DEFAULT NULL,
  `col_8` varchar(100) DEFAULT NULL,
  `col_9` varchar(100) DEFAULT NULL,
  `col_10` varchar(100) DEFAULT NULL,
  `col_11` varchar(100) DEFAULT NULL,
  `col_12` varchar(8) DEFAULT NULL,
  `col_13` decimal(38,12) DEFAULT NULL,
  `col_14` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `col_15` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`col_2`, `col_1`, `col_4`),
  KEY `index_col_5_6` (`col_5`, `col_6`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `table_a`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`,
 `col_14`, `col_15`, `col_16`, `col_17`, `col_18`, `col_19`,
 `col_20`, `col_21`, `created_at`)
VALUES
('20230628', '20230628', 'Portfolio A', 'Product B', 'Direct', 'USD',
 200000, 150000, 50000, 100000,
 50000, 10000, 5000, 1.2, 0.1,
 0.15, 0.08, 0.02, '2023-06-28',
 '2023-06-28', '2025-06-28', CURRENT_TIMESTAMP);

 INSERT INTO `table_b`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`, `col_8`, `created_at`)
VALUES
('20240628', 'DR201800093', 'Product A', '申购', 1000, 100000, 95000, 1.1, CURRENT_TIMESTAMP);

INSERT INTO `table_c`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`,
 `col_14`, `col_15`, `col_16`, `col_17`, `col_18`, `col_19`, `created_at`)
VALUES
('20230628', 'Dept A', 'DR201800093', 'Product A', '孵化', 'Strategy 1',
 100000, 100000, 120000, 100, 1.2,
 0.2, 0.15, 0.1, 0.05, 0.08,
 '2023-06-28', '2025-06-28', '2Y', CURRENT_TIMESTAMP);

INSERT INTO `table_d`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`, `col_14`,
 `col_15`, `col_16`, `col_17`, `col_18`, `col_19`, `col_20`, `col_21`,
 `col_22`, `col_23`)
VALUES
('20240628', '1', '2024-06-28', 'Friday', '28', 'End of Month', 202406,
 'June', 20242, 'Q2', 2024, '2024', '2024-06-27', '2024-05-28',
 '2024-03-28', '2023-06-28', '2024-06-27', '2024-06-27', '2024-06-27',
 '1', '1', '1', '1');

 INSERT INTO `table_e`
(`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`,
 `col_8`, `col_9`, `col_10`, `col_11`, `col_12`, `col_13`, `col_14`,
 `created_at`, `col_15`)
VALUES
('20230628', 'CFETS_MID', 'Mid', 'USD/CNY', 'USD', 'CNY',
 7.0, 'Source A', 'Unit A', 'Region A', '2023-06-28 15:00:00', '20230627',
 6.9, 'user_001', CURRENT_TIMESTAMP, 'Exchange rate on 2023-06-28');

use test;
desc analyze WITH date_table AS (
  SELECT
        d.col_1 AS date,
        (SELECT MAX(col_1)
            FROM table_c a
           WHERE col_1 <=
                 CONCAT(YEAR(DATE_SUB(d.col_1, INTERVAL 1 YEAR)),
                        '1231')
             AND EXISTS (SELECT 1
                    FROM table_d d
                   WHERE a.col_1 = d.col_1
                     AND d.col_2 = 1)) AS date1,
        (SELECT MAX(col_1)
            FROM table_a a
           WHERE col_1 <= CONCAT(YEAR(DATE_SUB(d.col_1, INTERVAL 1 YEAR)),
                                '1231')
             AND EXISTS (SELECT 1
                    FROM table_d d
                   WHERE a.col_1 = d.col_1
                     AND d.col_2 = 1)) AS date2,
        (SELECT MAX(col_1)
            FROM table_c
           WHERE col_1 <= d.col_1) AS date3,
        (SELECT MAX(col_1)
            FROM table_a
           WHERE col_1 <= d.col_1) AS date4
    FROM table_d d
   WHERE d.col_1 = '20240628'
),
rm_am_champs_ex_risk_portfolio_seed_money_1 AS (
  SELECT b.col_2
    FROM table_a b
    LEFT JOIN table_e rb
      ON rb.col_1 = b.col_19
     AND b.col_6 = rb.col_3
   WHERE b.col_2 = (SELECT date4 FROM date_table)
),

rm_am_champs_ex_risk_portfolio_seed_money_2 AS (
  SELECT b.col_2
  FROM table_a b
  LEFT JOIN table_e rb
      ON rb.col_1 = b.col_19
     AND b.col_6 = rb.col_3
),

product_base AS (
  SELECT DISTINCT t.col_3, col_4, 'ML' AS is_do
    FROM table_c t
),

product_detail AS (
  SELECT t.col_4,
        "3集合" AS nature_investment
    FROM product_base t
    LEFT JOIN date_table dt
      ON 1 = 1
    LEFT JOIN table_c a
      ON t.col_4 = a.col_4
     AND a.col_1 = dt.date3
)

SELECT col_4
  FROM (
        SELECT col_4
          FROM product_detail
        UNION ALL
        SELECT col_4
          FROM product_detail
) a;

2. What did you expect to see? (Required)

query execute successfully

3. What did you see instead (Required)

query hang

6 @ 0x189d5ae 0x18af1f8 0x18af1cf 0x18cdd85 0x18e25c8 0x428962b 0x41a6fe8 0x41a68ad 0x41bd22c 0x428978f 0x41c77bb 0x4119824 0x439b610 0x443bf96 0x1c97b65 0x442e9c2 0x442d115 0x44288c5 0x44251ba 0x445d6e7 0x18d2461
# labels: {"plan_digest":"rzj\xeeY\"\xaf\x1c\xd9K\a\xe1\x0f\xa3\x90\x18\x92\x8en1K\x1a\xfa\xf3\xe6\xce\x1d\x96\xb7\xe72\xc1", "sql_digest":"\xd9W\xa4\xd5=5O0\xc0\xa6^r\xec\x04\x92R^\xa3\x12\xef/\xfa2:\x8bd\xb9(\xad\x02\xb4\xe3"}
#   0x18cdd84   sync.runtime_Semacquire+0x24                        /home/xhy/.gvm/gos/go1.21.5/src/runtime/sema.go:62
#   0x18e25c7   sync.(*WaitGroup).Wait+0x47                     /home/xhy/.gvm/gos/go1.21.5/src/sync/waitgroup.go:116
#   0x428962a   github.com/pingcap/tidb/executor.(*ProjectionExec).Close+0x6a       /home/xhy/Development/github.com/pingcap/tidb/executor/projection.go:313
#   0x41a6fe7   github.com/pingcap/tidb/executor.(*cteProducer).closeProducer+0x27  /home/xhy/Development/github.com/pingcap/tidb/executor/cte.go:218
#   0x41a68ac   github.com/pingcap/tidb/executor.(*CTEExec).Close+0x4c          /home/xhy/Development/github.com/pingcap/tidb/executor/cte.go:119
#   0x41bd22b   github.com/pingcap/tidb/executor.(*baseExecutor).Close+0x6b     /home/xhy/Development/github.com/pingcap/tidb/executor/executor.go:214
#   0x428978e   github.com/pingcap/tidb/executor.(*ProjectionExec).Close+0x1ce      /home/xhy/Development/github.com/pingcap/tidb/executor/projection.go:334
#   0x41c77ba   github.com/pingcap/tidb/executor.(*UnionExec).Close+0x11a       /home/xhy/Development/github.com/pingcap/tidb/executor/executor.go:1936
#   0x4119823   github.com/pingcap/tidb/executor.(*recordSet).Close+0x23        /home/xhy/Development/github.com/pingcap/tidb/executor/adapter.go:184
#   0x439b60f   github.com/pingcap/tidb/session.(*execStmtResult).Close+0x2f        /home/xhy/Development/github.com/pingcap/tidb/session/session.go:2455
#   0x443bf95   github.com/pingcap/tidb/server.(*tidbResultSet).Close+0x35      /home/xhy/Development/github.com/pingcap/tidb/server/driver_tidb.go:476
#   0x1c97b64   github.com/pingcap/tidb/parser/terror.Call+0x24             /home/xhy/Development/github.com/pingcap/tidb/parser/terror/terror.go:306
#   0x442e9c1   github.com/pingcap/tidb/server.(*clientConn).handleStmt+0x461       /home/xhy/Development/github.com/pingcap/tidb/server/conn.go:2168
#   0x442d114   github.com/pingcap/tidb/server.(*clientConn).handleQuery+0x8f4      /home/xhy/Development/github.com/pingcap/tidb/server/conn.go:1985
#   0x44288c4   github.com/pingcap/tidb/server.(*clientConn).dispatch+0xf84     /home/xhy/Development/github.com/pingcap/tidb/server/conn.go:1416
#   0x44251b9   github.com/pingcap/tidb/server.(*clientConn).Run+0x239          /home/xhy/Development/github.com/pingcap/tidb/server/conn.go:1165
#   0x445d6e6   github.com/pingcap/tidb/server.(*Server).onConn+0xa86           /home/xhy/Development/github.com/pingcap/tidb/server/server.go:648

4. What is your TiDB version? (Required)

v6.5.6

guo-shaoge commented 3 months ago

Check here for case that may got wrong result