Skip to content

sql: error on internal validation query for foreign keys referencing hidden columns #59582

Closed
#59659
Closed
sql: error on internal validation query for foreign keys referencing hidden columns#59582
#59659
Labels
C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.
@thoszhang

Description

@thoszhang
thoszhang
opened on Jan 29, 2021

This came up in the context of adding user-defined hidden columns in #58923, but also applies to rowid now. On master, adding foreign key constraints referencing hidden columns doesn't work:

root@:26257/defaultdb> CREATE TABLE t4(c INT);
CREATE TABLE
root@:26257/defaultdb> CREATE TABLE t5(a INT);
CREATE TABLE
root@:26257/defaultdb> ALTER TABLE t4 ADD FOREIGN KEY (c) REFERENCES t5 (rowid);
ERROR: validate fk constraint: column "t.rowid" does not exist
SQLSTATE: 42703

This comes from the internal executor validating the constraint for existing rows. Here's the query we're actually executing:

I210129 15:59:27.481167 3052 sql/check.go:290 ⋮ [n1,job=628705089054212097,scExec,id=52,mutation=1] 118  validating FK ‹"fk_c_ref_t5"› (‹"t4"› [‹[c rowid]›] -> ‹"t5"› [‹[rowid]›]) with query ‹"SELECT s.c, s.rowid FROM \n\t\t  (SELECT c, rowid FROM [52 AS src]@{IGNORE_FOREIGN_KEYS} WHERE c IS NOT NULL) AS s\n\t\t\tLEFT OUTER JOIN\n\t\t\t(SELECT * FROM [53 AS target]) AS t\n\t\t\tON s.c = t.rowid\n\t\t WHERE t.rowid IS NULL  LIMIT 1"›

This query also doesn't work on its own, so the problem isn't isolated to the schema change:

root@:26257/defaultdb> SELECT s.c, s.rowid
  FROM (SELECT c, rowid FROM [52 AS src]@{IGNORE_FOREIGN_KEYS} WHERE c IS NOT NULL) AS s
       LEFT JOIN (SELECT * FROM [53 AS target]) AS t ON s.c = t.rowid
 WHERE t.rowid IS NULL
 LIMIT 1;
ERROR: column "t.rowid" does not exist
SQLSTATE: 42703

Here's the stack trace for the error when running it in the internal executor:

I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/catalog/colinfo.NewUndefinedColumnError
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/catalog/colinfo/column_resolver.go:210
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*scope).Resolve
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:887
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.(*ColumnItem).Resolve
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/sem/tree/name_resolution.go:237
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*scope).VisitPre
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:931
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*scope).VisitPre
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:928
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.WalkExpr
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:714
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.(*ComparisonExpr).Walk
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:191
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/sem/tree.WalkExpr
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:717
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*scope).walkExprTree
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:412
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*scope).resolveAndRequireType
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:470
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildJoin
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/join.go:125
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildDataSource
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:71
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildFromTablesRightDeep
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1183
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildFromTables
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1160
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildFrom
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1087
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildSelectClause
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1008
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildSelectStmtWithoutParens
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:956
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildSelect.func1
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:929
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).processWiths
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:29
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildSelect
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:928
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildStmt
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:265
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).buildStmtAtRoot
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:229
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder.(*Builder).Build
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:200
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).buildExecMemo
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:507
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*planner).makeOptimizerPlan
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:193
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).makeExecPlan
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:910
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:796
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:641
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:119
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1446
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1448
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
I210129 16:03:01.820361 6475 jobs/registry.go:1135 ⋮ [n1] 212 +  | 	/Users/lucy/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1373

Should this query work? Are we generating the incorrect query in the first place?

Metadata

Metadata

Assignees

No one assigned

    Labels

    C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions