sql: CockroachDB panics when executing SELECT statement with JOIN and ill-formed AS FOR SYSTEM TIME

Describe the problem

The latest version of the CockroachDB (v24.2.4 and the latest master commit: f15ee646b5111) crashes when executing the following query:

CREATE TABLE v00 (c01 INT);
SELECT ALL FROM ( v00 AS ta1401 NATURAL JOIN v00 AS ta1402 ) WITH ORDINALITY AS ta1403 AS OF SYSTEM TIME b'any_bytes' BETWEEN SYMMETRIC 'abc' AND 'abc';

To Reproduce

In operating system Ubuntu 20.04 LTS, download the pre-build CockroachDB binaries (v24.2.4) from link
Run ./cockroach demo, and then paste the PoC query to the cockroach cli environment.
Observe the crash and log the stack information.

Expected behavior The CockroachDB should return error from the statement as the AS FOR SYSTEM TIME is ill-formed.

Additional data / screenshots

This is the stack frame from the crash (v24.2.4):

*
* ERROR: a SQL panic has occurred while executing the following statement:
* SELECT  FROM (v00 AS ta1401 NATURAL JOIN v00 AS ta1402) WITH ORDINALITY AS ta1403 AS OF SYSTEM TIME 'any_bytes' BETWEEN SYMMETRIC 'abc' AND '\x616263'
*
*
* ERROR: a panic has occurred!
* lookup for ComparisonExpr (('any_bytes')[string] >= ('\x616263')[bytes])[bool]'s CmpOp failed (<=(BYTES,STRING))
* (1) attached stack trace
*   -- stack trace:
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:961
*   | runtime.gopanic
*   |   GOROOT/src/runtime/panic.go:770
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.MemoizeComparisonExprOp
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/tree/expr.go:492
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.NewTypedComparisonExpr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/tree/expr.go:401
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeRangeCond.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:410
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeRangeCond
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:440
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeExpr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:40
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.(*Visitor).VisitPost
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/visitor.go:80
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.WalkExpr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:841
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.Expr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/visitor.go:27
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/asof.Eval
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/asof/as_of.go:196
*   | github.com/cockroachdb/cockroach/pkg/sql.(*planner).EvalAsOfTimestamp
*   |   github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:2092
*   | github.com/cockroachdb/cockroach/pkg/sql.(*planner).isAsOf
*   |   github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:2141
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).handleAOST
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1333
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1020
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execWithProfiling
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3436
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2316
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2321
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2238
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:963
*   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommands
*   |   github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:256
*   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*Server).serveImpl.func4
*   |   github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1136
* Wraps: (2) while executing: SELECT  FROM (_ AS _ NATURAL JOIN _ AS _) WITH ORDINALITY AS _ AS OF SYSTEM TIME _ BETWEEN SYMMETRIC _ AND _
* Wraps: (3) attached stack trace
*   -- stack trace:
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:961
*   | runtime.gopanic
*   |   GOROOT/src/runtime/panic.go:770
*   | [...repeated from below...]
* Wraps: (4) assertion failure
* Wraps: (5) attached stack trace
*   -- stack trace:
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.MemoizeComparisonExprOp
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/tree/expr.go:492
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.NewTypedComparisonExpr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/tree/expr.go:401
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeRangeCond.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:410
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeRangeCond
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:440
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeExpr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:40
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.(*Visitor).VisitPost
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/visitor.go:80
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.WalkExpr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:841
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.Expr
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/visitor.go:27
*   | github.com/cockroachdb/cockroach/pkg/sql/sem/asof.Eval
*   |   github.com/cockroachdb/cockroach/pkg/sql/sem/asof/as_of.go:196
*   | github.com/cockroachdb/cockroach/pkg/sql.(*planner).EvalAsOfTimestamp
*   |   github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:2092
*   | github.com/cockroachdb/cockroach/pkg/sql.(*planner).isAsOf
*   |   github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:2141
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).handleAOST
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1333
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1020
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execWithProfiling
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3436
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2316
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2321
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2238
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
*   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:963
*   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommands
*   |   github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:256
*   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*Server).serveImpl.func4
*   |   github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1136
*   | runtime.goexit
*   |   src/runtime/asm_amd64.s:1695
* Wraps: (6) lookup for ComparisonExpr (('any_bytes')[string] >= ('\x616263')[bytes])[bool]'s CmpOp failed (<=(BYTES,STRING))
* Error types: (1) *withstack.withStack (2) *safedetails.withSafeDetails (3) *withstack.withStack (4) *assert.withAssertionFailure (5) *withstack.withStack (6) *errutil.leafError
*
panic: lookup for ComparisonExpr (('any_bytes')[string] >= ('\x616263')[bytes])[bool]'s CmpOp failed (<=(BYTES,STRING)) [recovered]
    panic: lookup for ComparisonExpr (('any_bytes')[string] >= ('\x616263')[bytes])[bool]'s CmpOp failed (<=(BYTES,STRING))

goroutine 5021 [running]:
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).closeWrapper(0xc006d6ef88, {0x836b750, 0xc00469e0f0}, {0x63752e0?, 0xc004489630?})
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1234 +0x19d
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1({0x836b750, 0xc00469e0f0}, {0xc00909fbe0?})
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:961 +0x45
panic({0x63752e0?, 0xc004489630?})
    GOROOT/src/runtime/panic.go:770 +0x132
github.com/cockroachdb/cockroach/pkg/sql/sem/tree.MemoizeComparisonExprOp(0xc007081090)
    github.com/cockroachdb/cockroach/pkg/sql/sem/tree/expr.go:492 +0x3b3
github.com/cockroachdb/cockroach/pkg/sql/sem/tree.NewTypedComparisonExpr({0xc23bd20?, 0xc0?}, {0x83a3d08, 0xc0070aa498}, {0x83a3cc0, 0xc004489560})
    github.com/cockroachdb/cockroach/pkg/sql/sem/tree/expr.go:401 +0x110
github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeRangeCond.func1({0x83a3cc0, 0xc004489560}, {0x83a3d08, 0xc0070aa4d8})
    github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:410 +0x137
github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeRangeCond(0xc0070aa580, 0xc007080f00)
    github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:440 +0x51f
github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.normalizeExpr(0xc23bf20?, {0x83a3690?, 0xc007080f00?})
    github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/normalize_exprs.go:40 +0xf1
github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.(*Visitor).VisitPost(0xc0070aa580, {0x836c908?, 0xc007080f00?})
    github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/visitor.go:80 +0x73
github.com/cockroachdb/cockroach/pkg/sql/sem/tree.WalkExpr({0x8338eb0, 0xc0070aa580}, {0x836c908, 0xc007080f00})
    github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:841 +0x62
github.com/cockroachdb/cockroach/pkg/sql/sem/normalize.Expr({0x836b718, 0xc007080fa0}, 0xc006d6fb40, {0x83a3690, 0xc007080f00})
    github.com/cockroachdb/cockroach/pkg/sql/sem/normalize/visitor.go:27 +0xdd
github.com/cockroachdb/cockroach/pkg/sql/sem/asof.Eval({0x836b718, 0xc007080fa0}, {{0x836c908?, 0xc007080f00?}}, 0xc006d6fa50, 0xc006d6fb40, {0xc00909e180, 0x1, 0x30?})
    github.com/cockroachdb/cockroach/pkg/sql/sem/asof/as_of.go:196 +0x725
github.com/cockroachdb/cockroach/pkg/sql.(*planner).EvalAsOfTimestamp(0x0?, {0x836b718?, 0xc007080fa0?}, {{0x836c908?, 0xc007080f00?}}, {0xc00909e180?, 0x0?, 0x0?})
    github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:2092 +0x85
github.com/cockroachdb/cockroach/pkg/sql.(*planner).isAsOf(0xc006d6f638, {0x836b718, 0xc007080fa0}, {0x83924e0?, 0xc007080f50?})
    github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:2141 +0x235
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).handleAOST(0xc006d6ef88, {0x836b718, 0xc007080fa0}, {0x83924e0, 0xc007080f50})
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1333 +0x23e
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState(0xc006d6ef88, {0x836b750, 0xc00746d500}, {{0x83924e0, 0xc007080f50}, {0x0, 0x0, 0x0}, {0xc00248ae96, 0x97}, ...}, ...)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1020 +0x3eb0
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt.func1({0x836b750?, 0xc00746d500?})
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146 +0xb0
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execWithProfiling(0xc21f180?, {0x836b750?, 0xc00746d500?}, {0x83924e0?, 0xc007080f50?}, 0xc002570748?, 0x69fac65?)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3436 +0x2ec
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt(0xc006d6ef88, {0x836b750, 0xc00746d500}, {{0x83924e0, 0xc007080f50}, {0x0, 0x0, 0x0}, {0xc00248ae96, 0x97}, ...}, ...)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145 +0x709
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1(0xc00909f820, 0xc00909f8d0, 0xc006d6ef88, 0xc00909f4f8, 0xc00909f958, 0xc00909f948, 0xc00909f978, 0xc00909f990)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2316 +0x3f1
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd(0xc006d6ef88)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2321 +0xa39
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run(0xc006d6ef88, {0x836b750, 0xc00469e0f0}, 0x3c?, 0xc001bfed80?, 0xc001be8820?)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2238 +0x208
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn(0xc001c82960?, {0x836b750?, 0xc00469e0f0?}, {0xc0075eb170?}, 0x2?, 0xc005f9bef0?)
    github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:963 +0xda
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommands(0xc002570008, {0x836b750, 0xc00469e0f0}, {0x0, 0x4, {0x1, {0x69e7fc3, 0x3}, {0xc0034ad390, 0xf}, ...}, ...}, ...)
    github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:256 +0x4a6
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*Server).serveImpl.func4()
    github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1136 +0xec
created by github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*Server).serveImpl in goroutine 5111
    github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1133 +0x5db

Environment:

CockroachDB version: v24.2.4
Server OS: Ubuntu 20.04 LTS
Client app: CockroachDB demo command. (./cockroach demo)
Detailed CockroachDB version output:

cockroach version details:
Build Tag:        v24.2.4
Build Time:       2024/10/14 17:20:56
Distribution:     CCL
Platform:         linux amd64 (x86_64-pc-linux-gnu)
Go Version:       go1.22.5 X:nocoverageredesign
C Compiler:       gcc 6.5.0
Build Commit ID:  8140b8e294fb16a293d0aae662bcf5c1d488c852
Build Type:       release
Enabled Assertions: false

Jira issue: CRDB-43581

cockroachdb / cockroach

sql: CockroachDB panics when executing SELECT statement with JOIN and ill-formed AS FOR SYSTEM TIME #133395