Create inverted index ignores the case of the column name #42944
Description
Describe the problem
No inverted index creation is possible, if column name is case-sensitive.
To Reproduce
Table:
CREATE TABLE phone (
id UUID NOT NULL DEFAULT gen_random_uuid(),
"idPerms" JSONB NOT NULL
);Create index ignores the case (see column name in the error):
create inverted index on phone ("idPerms");
pq: verify-inverted-idx-count: column "idperms" does not existBut for non existing field another error appears:
create inverted index on phone ("Test");
pq: index "phone_Test_idx" contains unknown column "Test"For secondary index case matters and I get the right error message:
create index on phone ("idPerms");
pq: unimplemented: column idPerms is of type jsonb and thus is not indexable
HINT: You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/35730It works, if column name is case-sensitive, but lower cased:
CREATE TABLE phone2 (
id UUID NOT NULL DEFAULT gen_random_uuid(),
"idperms" JSONB NOT NULL
);
CREATE TABLE
Time: 14.222225ms
create inverted index on phone2 ("idperms");
CREATE INDEXExpected behavior
Yeah, that I can create an index for JSONB column with case-sensitive name.
Additional data / screenshots
Environment:
- CockroachDB version: cockroachdb/cockroach:v19.2.0 from docker hub
- Server OS: ubuntu 18.04
- Client app:
cockroach sql
Additional context
No index creation possible for a standard field of every table.