Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql: need better validation against creating hash sharded index on computed columns #73622

Closed
chengxiong-ruan opened this issue Dec 8, 2021 · 1 comment
Assignees
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@chengxiong-ruan
Copy link
Contributor

Describe the problem
It crashes when trying to create hash sharded index on a virtual computed column or an expression:

ERROR: internal error: could not find column family for the first column in the index column set
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/create_index.go:105: setupFamilyAndConstraintForShard()
github.com/cockroachdb/cockroach/pkg/sql/create_index.go:258: MakeIndexDescriptor()
github.com/cockroachdb/cockroach/pkg/sql/create_index.go:632: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:515: func2()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:112: func1()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:295: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:79: visit()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:43: walkPlan()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:518: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:142: Start()
github.com/cockroachdb/cockroach/pkg/sql/colexec/columnarizer.go:151: Init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:87: Init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:235: func1()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:91: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:234: init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:275: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:259: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:550: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1365: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1385: execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1062: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:700: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:129: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1750: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1752: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1674: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:681: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:666: func1()
runtime/asm_amd64.s:1581: goexit()

To Reproduce

create table poor_t (
id int primary key,
a int not null,
b int as ((a*2)) virtual
);

create index idx_poor_t_bad_index on poor_t (b) using hash with bucket_count = 8;
-- then it crashes...
@chengxiong-ruan chengxiong-ruan added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Dec 8, 2021
@chengxiong-ruan chengxiong-ruan self-assigned this Dec 8, 2021
@blathers-crl blathers-crl bot added the T-sql-schema-deprecated Use T-sql-foundations instead label Dec 8, 2021
chengxiong-ruan added a commit to chengxiong-ruan/cockroach that referenced this issue Dec 14, 2021
fixes cockroachdb#61564 , also fixes cockroachdb#73622 as a bonus

Previously we use `STORED` computed column for shard column of
hash index. This pr makes it use virtual column instead. The
major change is actually to allow virtual column in primary key
by loosening some index validation with extrac checks.

Release note (sql change): Virtual column is now allowed in
primary key and shard column of hash-sharded index is now a
virtual column.
@chengxiong-ruan
Copy link
Contributor Author

this is fixed with #74138

@exalate-issue-sync exalate-issue-sync bot added T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) and removed T-sql-schema-deprecated Use T-sql-foundations instead labels May 10, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant