cdc: some expressions cause a nil pointer exception in optbuilder

[HonoreDB]

When I found #90416 it was through trying to simplify the following randomly-generated expression:

CREATE CHANGEFEED WITH schema_change_policy='stop' AS SELECT rowid FROM seed WHERE (_bool OR EXISTS (SELECT '01020000C00200000020D08CC05133BB41102230818D4EC3C12AF59AB88A08FEC18CE8911A2ADCE3C1B05D2B2C9646C0C18D8CD57F6658F4C1DABBE2867188E2C19CBB716AF8B6F5C1':::GEOMETRY AS col_412 FROM d.public.seed AS seed WHERE _bool ORDER BY _int8));

That expression doesn't crash the database but only because the panic is caught:

root@localhost:26257/? ?> CREATE CHANGEFEED WITH schema_change_policy='stop' AS SELECT rowid FROM seed WHERE (_bool OR EXISTS (SELECT '01020000C00200000020D08CC05133BB41102230818D4EC3C12AF59AB88A08FEC18CE8911A2ADCE3C1B05D2B2C9646C0C18D8CD57F6658F4C1DABBE2867188E2C19CBB716AF8B6F5C1':::GEOMETRY AS col_412 FROM d.public.seed AS seed WHERE _bool ORDER BY _int8));
warning: connection lost!
opening new connection: all session settings will be lost
ERROR: internal error: runtime error: invalid memory address or nil pointer dereference
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/util/errorutil/catch.go:29: ShouldCatch()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scalar.go:1025: func1()
GOROOT/src/runtime/panic.go:884: gopanic()
GOROOT/src/runtime/panic.go:260: panicmem()
GOROOT/src/runtime/signal_unix.go:835: sigpanic()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/util.go:643: resolveDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:110: buildDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:66: buildDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1223: buildFromTablesRightDeep()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1200: buildFromTables()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1127: buildFrom()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1048: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:996: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:965: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:964: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:308: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/subquery.go:224: buildSubquery()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/subquery.go:100: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:1956: typeCheckAndRequire()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:1950: typeCheckAndRequireBoolean()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:1394: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:1407: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:272: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:468: resolveType()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scalar.go:1033: Build()
github.com/cockroachdb/cockroach/pkg/sql/constraint.go:90: ConstrainPrimaryIndexSpanByExpr()
github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/cdceval/constraint.go:98: constrainSpansBySelectClause()
github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/cdceval/validation.go:105: NormalizeAndValidateSelectForTarget()
github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/changefeed_stmt.go:887: validateAndNormalizeChangefeedExpression()
github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/changefeed_stmt.go:411: createChangefeedJobRecord()
github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/changefeed_stmt.go:173: func2()

Not sure whether to mark this as a GA blocker, but #90416 is and hopefully it'll be the same fix for both anyway.

Jira issue: CRDB-20758

Epic CRDB-17161

[blathers-crl]

cc @cockroachdb/cdc

[HonoreDB]

Another one:

changefeed_test.go:888: WHERE clause (_bool OR EXISTS (SELECT _int8 AS col_344)) caused error pq: could not decorrelate subquery

[miretskiy]

This is bad. I was sure subselects were not allowed

…

On Thu, Oct 20, 2022, 8:28 PM Aaron Zinger ***@***.***> wrote: Another one: changefeed_test.go:888: WHERE clause (_bool OR EXISTS (SELECT _int8 AS col_344)) caused error pq: could not decorrelate subquery — Reply to this email directly, view it on GitHub <#90418 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANA4FVFEOXBJMV3A6RKVNFLWEHPS7ANCNFSM6AAAAAARKVFRZI> . You are receiving this because you are on a team that was mentioned.Message ID: ***@***.***>

[miretskiy]

I wish we had original table schema around. Trying with simpler "SELECT 1 WHERE EXISTS (SELECT true)" on master seems to work fine. I wonder if we can close this issue.

[mgartner]

I'm not sure if you're still running into the could not decorrelate subquery error, but FYI I'm currently working on solving that for all EXISTS subqueries. It should be resolve in a week or so.

[miretskiy]

I don't think we are running into this... but I maybe wrong.

…

On Fri, Jan 20, 2023 at 10:54 AM Marcus Gartner ***@***.***> wrote: I'm not sure if you're still running into the could not decorrelate subquery error, but FYI I'm currently working on solving that for all EXISTS subqueries. It should be resolve in a week or so. — Reply to this email directly, view it on GitHub <#90418 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANA4FVA5FRTJ33UBLDHB2NTWTKYKRANCNFSM6AAAAAARKVFRZI> . You are receiving this because you are on a team that was mentioned.Message ID: ***@***.***>

[miretskiy]

Closing; will reopen if we encounter this again.