pingcap
selection failed with message Some columns of IndexHashJoin_45 cannot find the reference from its child(ren)
Please answer these questions before submitting your issue. Thanks!
- Minimal reproduce step (Required)
CREATE TABLE IF NOT EXISTS t (
col1 varchar(4) NOT NULL ,
col2 varchar(20) NOT NULL ,
col3 varchar(20) NOT NULL ,
col4 varchar(32) NOT NULL ,
col5 varchar(10) DEFAULT NULL ,
col6 varchar(100) DEFAULT NULL ,
col7 varchar(100) DEFAULT NULL,
UNIQUE KEY cadre_information_index1 (col1,col4,col2),
KEY cadre_information_index2 (col4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=125450;
WITH ta AS ( SELECT col1, sum(num) num FROM (SELECT c.col1,c.col2, CASE WHEN c.col3 LIKE '%AA%' THEN N'AA' ELSE N'AAA' END AS col3, count(c.col4) num FROM t c WHERE col5 = 'AA' AND c.col6 LIKE '%AA%' AND col7 IS NULL GROUP BY c.col1, c.col2, CASE WHEN c.col3 LIKE '%AA%' THEN N'AA' ELSE N'AAA' END ORDER BY c.col1, c.col2, col3 ) p WHERE 1 = 1 GROUP BY col1 ) , tb AS ( SELECT col1, sum(num) num FROM ( SELECT c.col1, c.col2, CASE WHEN c.col3 LIKE '%AA%' THEN N'AA' ELSE N'AAA' END AS col3, count(c.col4) num FROM t c WHERE col7 IS NULL GROUP BY c.col1, c.col2, CASE WHEN c.col3 LIKE '%AA%' THEN N'AA' ELSE N'AAA' END ORDER BY c.col1, c.col2, col3 ) p WHERE 1 = 1 GROUP BY col1 ) SELECT ta.col1, ta.num anum , tb.num bnum , ta.num / tb.num AS zhanbi FROM ta LEFT JOIN tb ON ta.col1 = tb.col1 GROUP BY ta.col1
- What did you expect to see? (Required)
Execution was successful without any errors.
- What did you see instead (Required)
ERROR 1105 (HY000): Some columns of IndexHashJoin_45 cannot find the reference from its child(ren)
- What is your TiDB version? (Required)
Release Version: v8.5.1 Edition: Community Git Commit Hash: fea86c8e35ad4a86a5e1160701f99493c2ee547c Git Branch: HEAD UTC Build Time: 2025-01-16 07:40:33 GoVersion: go1.23.2 Race Enabled: false Check Table Before Drop: false Store: tikv
