sql: hash sharded column shows up in CREATE TABLE, interferes with proper sharded index creation #68031
Description
This issue tracks two observed problems:
- SHOW CREATE shows the internal shard column when we have a sharded index
- If we try to create a table with this statement, the column is tolerated and creation succeeds. However, the table is lacking the check constraint (which is necessary for good plans for certain queries).
create table t (a int, index (a) using hash with bucket_count = 10);
select @2 from [ show create table t ];
?column?
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE public.t (
a INT8 NULL,
crdb_internal_a_shard_10 INT4 NOT VISIBLE NOT NULL AS (mod(fnv32(COALESCE(CAST(a AS STRING), '':::STRING)), 10:::INT8)) STORED,
rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
CONSTRAINT "primary" PRIMARY KEY (rowid ASC),
INDEX t_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10,
FAMILY "primary" (a, crdb_internal_a_shard_10, rowid)
)
(1 row)CREATE TABLE is showing the crdb_internal_a_shard_10 column.
This table has the expected check constraint:
explain (opt, catalog) select * from t;
info
-------------------------------------------------------------------------------------------------------------------------------------------------
TABLE t
├── a int
├── crdb_internal_a_shard_10 int4 not null as (mod(fnv32(COALESCE(CAST(a AS STRING), '':::STRING)), 10:::INT8)) stored [hidden]
├── rowid int not null default (unique_rowid()) [hidden]
├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
├── tableoid oid [hidden] [system]
├── CHECK (crdb_internal_a_shard_10 IN (0:::INT8, 1:::INT8, 2:::INT8, 3:::INT8, 4:::INT8, 5:::INT8, 6:::INT8, 7:::INT8, 8:::INT8, 9:::INT8))
├── PRIMARY INDEX primary
│ └── rowid int not null default (unique_rowid()) [hidden]
└── INDEX t_a_idx
├── crdb_internal_a_shard_10 int4 not null as (mod(fnv32(COALESCE(CAST(a AS STRING), '':::STRING)), 10:::INT8)) stored [hidden]
├── a int
└── rowid int not null default (unique_rowid()) [hidden]
scan t
├── check constraint expressions
│ └── crdb_internal_a_shard_10 IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
└── computed column expressions
└── crdb_internal_a_shard_10
└── mod(fnv32(COALESCE(a::STRING, '')), 10)::INT4
Now if I drop the table and try to recreate it using the CREATE TABLE above:
drop table t;
CREATE TABLE public.t (
a INT8 NULL,
crdb_internal_a_shard_10 INT4 NOT VISIBLE NOT NULL AS (mod(fnv32(COALESCE(CAST(a AS STRING), '':::STRING)), 10:::INT8)) STORED,
rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
CONSTRAINT "primary" PRIMARY KEY (rowid ASC),
INDEX t_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10,
FAMILY "primary" (a, crdb_internal_a_shard_10, rowid)
);
explain (opt, catalog) select * from t;
info
-----------------------------------------------------------------------------------------------------------------------------------------
TABLE t
├── a int
├── crdb_internal_a_shard_10 int4 not null as (mod(fnv32(COALESCE(CAST(a AS STRING), '':::STRING)), 10:::INT8)) stored [hidden]
├── rowid int not null default (unique_rowid()) [hidden]
├── crdb_internal_mvcc_timestamp decimal [hidden] [system]
├── tableoid oid [hidden] [system]
├── PRIMARY INDEX primary
│ └── rowid int not null default (unique_rowid()) [hidden]
└── INDEX t_a_idx
├── crdb_internal_a_shard_10 int4 not null as (mod(fnv32(COALESCE(CAST(a AS STRING), '':::STRING)), 10:::INT8)) stored [hidden]
├── a int
└── rowid int not null default (unique_rowid()) [hidden]
scan t
└── computed column expressions
└── crdb_internal_a_shard_10
└── mod(fnv32(COALESCE(a::STRING, '')), 10)::INT4
(16 rows)
Epic: CRDB-7363
gz#9232