sql: Internal Error when checking foreign data wrapper privilege

[SteveLeungYL]

Describe the problem

The latest release alpha version of the CockroachDB (v23.1.0-alpha.2) shows Internal Error when executing the following query.

CREATE TABLE v0 (c1 BOOL);
UPDATE v0 SET c1 = HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE( 'regress_test_role', (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname = 'foo'), 'USAGE');

To Reproduce

1. In operating system Ubuntu 20.04 LTS, download the CockroachDB source code from the github source.
2. Download the pre-build CockroachDB binaries (v22.2.3 and v23.1.0 alpha) from link
3. Run ./cockroach demo, and then paste the PoC query to the cockroach cli environment.
4. Observe the Internal Error and log the stack information.

Expected behavior The UPDATE statement should be executed successfully.

Additional data / screenshots

Here is the stack trace from v23.1.0-alpha.2:

ERROR: internal error: locking cannot be used with virtual table
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/exec_factory_util.go:263: constructVirtualScan()
github.com/cockroachdb/cockroach/pkg/sql/opt_exec_factory.go:192: constructVirtualScan()
github.com/cockroachdb/cockroach/pkg/sql/opt_exec_factory.go:122: ConstructScan()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/exec/explain/plan_gist_factory.og.go:28: ConstructScan()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/exec/explain/explain_factory.og.go:34: ConstructScan()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:813: buildScan()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:206: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:902: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:212: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:985: buildProject()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:215: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:2706: buildMax1Row()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:255: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:815: buildSubquery()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:108: buildScalar()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:290: buildFunction()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:108: buildScalar()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1002: buildProject()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:215: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/mutation.go:41: buildMutationInput()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/mutation.go:339: buildUpdate()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:273: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:310: build()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:257: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:640: runExecBuilder()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:307: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1536: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1143: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:745: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:131: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2530: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:130: execStmt()

HINT: You have encountered an unexpected error.

Environment:

• CockroachDB version: v23.1.0-alpha.2
• Server OS: Ubuntu 20.04 LTS
• Client app: CockroachDB demo command. (./cockroach demo)
• Detailed CockroachDB version output:

cockroach version details:
Build Tag:        v23.1.0-alpha.2
Build Time:       2023/02/13 18:32:33
Distribution:     CCL
Platform:         linux amd64 (x86_64-pc-linux-gnu)
Go Version:       go1.19.4
C Compiler:       gcc 6.5.0
Build Commit ID:  eb158026c50d8fa856e42f928d844831ea9e6b28
Build Type:       release

Additional context

The bug can also be reproduced in v23.1.0-alpha.2, v23.1.0-alpha.1, v22.1.13, v22.2.3 and v21.2.17. We haven't tested earlier versions before v21.2.17.

Jira issue: CRDB-24657

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: Optimizer,plan)
• @cockroachdb/sql-sessions (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rytaft]

Looks like another case where this should be a normal error rather than an internal error.