Insights proposed index wrong SQL statement

[gerwim]

Describe the problem

Creating an index fails:

The error (from network tab):

{"error":{"code":"42601","message":"parsing statement 1: at or near \"_\": syntax error","detail":"source SQL:\nCREATE INDEX IF NOT EXISTS \"offers\"_\"startdate\"_storing_rec_idx ON whatsuper_tst.\"Offers\".\"Offers\" (\"StartDate\") STORING (\"Name\", \"SubcategoryId\", \"SupermarketId\", \"PriceShort\", \"PriceOld\", \"PriceLabel\", \"PriceUnit\", \"ExtraText\", \"ImageUrl\", \"ImageUrlOriginal\", \"EndDate\", \"MetadataId\", \"NeedsReview\", \"Hidden\", \"ScraperRunId\")\n                                   ^\n--\nCREATE INDEX IF NOT EXISTS \"offers\"_\"startdate\"_storing_rec_idx ON whatsuper_tst.\"Offers\".\"Offers\" (\"StartDate\") STORING (\"Name\", \"SubcategoryId\", \"SupermarketId\", \"PriceShort\", \"PriceOld\", \"PriceLabel\", \"PriceUnit\", \"ExtraText\", \"ImageUrl\", \"ImageUrlOriginal\", \"EndDate\", \"MetadataId\", \"NeedsReview\", \"Hidden\", \"ScraperRunId\")","hint":"try \\h CREATE INDEX","severity":"ERROR","source":{"file":"lexer.go","line":404,"function":"Error"}}}

To Reproduce

This happens when applying a suggested index.

If possible, provide steps to reproduce the behavior:

1. Go to insights
2. Go to schema insights
3. filter on insight typoe "create index"
4. Click "create index"

Expected behavior
The index is created

Additional data / screenshots
Replace index is broken too, but I'm not sure if it's related (didn't look into it, but I assume it is).

Generated SQL:

CREATE INDEX IF NOT EXISTS "offers"_"startdate"_storing_rec_idx ON whatsuper_tst."Offers"."Offers" ("StartDate") STORING ("Name", "SubcategoryId", "SupermarketId", "PriceShort", "PriceOld", "PriceLabel", "PriceUnit", "ExtraText", "ImageUrl", "ImageUrlOriginal", "EndDate", "MetadataId", "NeedsReview", "Hidden", "ScraperRunId"); 

The issue is caused by the double quotes around offers and startdate. This is a valid SQL statement:

CREATE INDEX IF NOT EXISTS offers_startdate_storing_rec_idx ON whatsuper_tst."Offers"."Offers" ("StartDate") STORING ("Name", "SubcategoryId", "SupermarketId", "PriceShort", "PriceOld", "PriceLabel", "PriceUnit", "ExtraText", "ImageUrl", "ImageUrlOriginal", "EndDate", "MetadataId", "NeedsReview", "Hidden", "ScraperRunId");

Environment:

• CockroachDB version: v23.2.1
• Server OS: Debian 12, running the docker container cockroachdb/cockroach:v23.2.1
• Client app: dashboard

Jira issue: CRDB-36229

[blathers-crl]

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-foundations (found keywords: SQL statement)

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.

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

[koorosh]

Some additional context:

1. It appears that part of index creation statement is built on FE:
   

   cockroach/pkg/ui/workspaces/cluster-ui/src/insights/indexActionBtn.tsx

   Lines 215 to 227 in 178be57

   	// createIdxName creates an index name, roughly following
   	// Postgres's conventions for naming anonymous indexes.
   	// For example:
   	//
   	// CREATE INDEX ON t (a)
   	// => t_a_rec_idx
   	//
   	// CREATE INDEX ON t ((a + b), c, lower(d))
   	// => t_expr_c_expr1_rec_idx
   	//
   	// Adding an extra _rec to the name, so we can identify statistics on indexes
   	// that were creating using this feature.
   	export function createIdxName(statement: string): string {

2. Index recommendation from crdb_internal.cluster_execution_insights suggests an index name with . (dots) which is not allowed if not wrapped with quotes.
   
3. Could reproduce similar issue when suggested index should be created on table with name like "t2.1"
   

Assume that logic on UI should be reduced to consume CREATE INDEX... query as is from the server.