TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE `p` (
`groupid` bigint(20) DEFAULT NULL,
KEY `k1` (`groupid`)
);
CREATE TABLE `g` (
`groupid` bigint(20) DEFAULT NULL,
`parentid` bigint(20) NOT NULL,
KEY `k1` (`parentid`),
KEY `k2` (`groupid`,`parentid`)
);
set tidb_opt_enable_hash_join=off;
WITH RECURSIVE w(gid) AS (
SELECT
groupId
FROM
p
UNION
SELECT
g.groupId
FROM
g
JOIN w ON g.parentId = w.gid
)
SELECT
1
FROM
g
WHERE
g.groupId IN (
SELECT
gid
FROM
w
);
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
no error
3. What did you see instead (Required)
4. What is your TiDB version? (Required)
Master