cockroachdb / cockroachdb/cockroach
sql: internal error `inner-join RelExpr children have intersecting columns` in UDF
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Reduced regression test added in #162512
```sql
CREATE TABLE t161993 (
id INT8 NOT NULL DEFAULT 0,
k VARCHAR(50) NULL,
CONSTRAINT t1_pkey PRIMARY KEY (id ASC)
);
CREATE TABLE u161993 (
code VARCHAR(20) NOT NULL,
name VARCHAR NULL,
active BOOL NULL DEFAULT true,
flag BOOL NULL DEFAULT false,
CONSTRAINT u161993_pkey PRIMARY KEY (code ASC)
);
CREATE FUNCTION f161993(p_in JSONB, OUT p_out JSONB)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
v1 BOOL;
v2 BOOL;
v3 TIMESTAMP;
v4 INT8;
v5 JSONB;
v6 JSONB;
v7 JSONB;
v8 VARCHAR;
v9 BOOL;
v10 VARCHAR;
v11 VARCHAR;
BEGIN
v2 := true;
v5 := p_in;
v8 := COALESCE(btrim(v5->>'code'), '1')::VARCHAR;
v10 := '';
v11 := '';
-- Block 1
IF v2 = true THEN
SELECT id FROM t161993 WHERE k = split_part(v10, '|', 5) INTO v4;
v2 := true;
END IF;
-- Block 2
IF v4 IN (11, 16, 89) THEN
v1 := COALESCE(btrim((v5->>'flag')), 'false')::BOOL;
END IF;
-- Block 3
IF v2 = true THEN
v7 := '{}'::JSONB;
END IF;
-- Block 4
IF v2 = true THEN
v8 := CASE WHEN EXISTS (SELECT 1 FROM u161993 AS x WHERE (x.code = v8) AND (x.active = true)) THEN v8 ELSE '1' END;
END IF;
-- Block 5
IF v2 = true THEN
v6 := '{}'::JSONB;
END IF;
-- Block 6
IF (v2 = true) AND (v9::BOOL = true) THEN
SELECT COALESCE(flag, false) FROM u161993 WHERE name = split_part(v11, '|', 4) INTO v9;
END IF;
END;
$$;
EXPLAIN SELECT f161993('{}')
```
results in an internal error
```
(XX000) internal error: inner-join RelExpr children have intersecting columns: (112)
check_expr.go:532: in checkOutputCols()
DETAIL: stack trace:
pkg/sql/opt/memo/check_expr.go:532: checkOutputCols()
pkg/sql/opt/memo/check_expr.go:399: CheckExpr()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/memo/expr.og.go:20803: MemoizeInnerJoin()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:4271: ConstructInnerJoin()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:3822: ConstructInnerJoin()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:3011: ConstructInnerJoin()
pkg/sql/opt/norm/decorrelate_funcs.go:492: ConstructNonApplyJoin()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:7228: ConstructInnerJoinApply()
pkg/sql/opt/norm/decorrelate_funcs.go:1009: hoistAll()
pkg/sql/opt/norm/decorrelate_funcs.go:1062: func1()
pkg/sql/opt/norm/decorrelate_funcs.go:1079: constructConditionalExpr()
pkg/sql/opt/norm/decorrelate_funcs.go:1025: hoistAll()
pkg/sql/opt/norm/decorrelate_funcs.go:340: HoistProjectSubquery()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:2810: ConstructProject()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:24646: Replace()
pkg/sql/opt/norm/inline_funcs.go:73: func1()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:24988: Replace()
pkg/sql/opt/norm/inline_funcs.go:73: func1()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:25587: Replace()
pkg/sql/opt/norm/inline_funcs.go:73: func1()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:26466: replaceScalarListExpr()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:25578: Replace()
pkg/sql/opt/norm/inline_funcs.go:73: func1()
pkg/sql/opt/norm/inline_funcs.go:75: inlineConstants()
pkg/sql/opt/norm/inline_funcs.go:35: InlineProjectionConstants()
bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:1987: ConstructProject()
pkg/sql/opt/optbuilder/project.go:57: constructProject()
pkg/sql/opt/optbuilder/project.go:32: constructProjectForScope()
pkg/sql/opt/optbuilder/plpgsql.go:803: buildPLpgSQLStatements()
pkg/sql/opt/optbuilder/plpgsql.go:580: buildBlock()
pkg/sql/opt/optbuilder/plpgsql.go:444: buildRootBlock()
pkg/sql/opt/optbuilder/create_function.go:466: func8()
```
Jira issue: CRDB-61716
Contributor guide
Assessment
This issue has not been assessed yet.