sql,parser: STORING clause for hash sharded indexes appears in the wrong place in pg_indexes #161516
Description
The STORING clause for CREATE INDEX .. WITH HASH is in the wrong place when shown in pg_indexes.
demo@127.0.0.1:26257/demoapp/movr> CREATE INDEX idx1 ON store_columns_test (c1, c2) USING HASH STORING (c3);
NOTICE: waiting for job(s) to complete: 1143167557831557121
If the statement is canceled, jobs will continue in the background.
CREATE INDEX
Time: 514ms total (execution 513ms / network 1ms)
demo@127.0.0.1:26257/demoapp/movr> SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'store_columns_test' and indexname = 'idx1';
indexname | indexdef
------------+----------------------------------------------------------------------------------------------------------------------------------
idx1 | CREATE INDEX idx1 ON movr.public.store_columns_test USING btree (c1 ASC, c2 ASC) USING HASH WITH (bucket_count=16) STORING (c3)
(1 row)
Time: 7ms total (execution 7ms / network 1ms)
demo@127.0.0.1:26257/demoapp/movr> CREATE INDEX idx2 ON movr.public.store_columns_test USING btree (c1 ASC, c2 ASC) USING HASH WITH (bucket_count=16) STORING (c3);
ERROR: statement ignored: at or near "storing": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
CREATE INDEX idx2 ON movr.public.store_columns_test USING btree (c1 ASC, c2 ASC) USING HASH WITH (bucket_count=16) STORING (c3)
The index statement is created by this function:
cockroach/pkg/sql/pg_catalog.go
Line 2186 in fedd714
To test it, we can add a test case in an appropriate part of the logictest: https://github.com/cockroachdb/cockroach/blob/519aef4f522d26ed911ac167e21f9efe591fc349/pkg/sql/logictest/testdata/logic_test/hash_sharded_index. The test can look at the output from SHOW CREATE TABLE, or if needed it can query pg_indexes directly.
Another place where we should add testing is: https://github.com/cockroachdb/cockroach/blob/82d91e556c56a202343d1ee22ed6c2000045e4da/pkg/sql/parser/testdata/create_index. These are parser-only tests, which may not reproduce the bug, but I noticed that hash-sharded indexes are not tested at all here, so we should add them while working on this. You can easily regenerate the "expected" output by running with --rewrite: ./dev test pkg/sql/parser -f=TestParseDataDriven/create_index --rewrite, and verifying manually that the expected output is what it should be.
Jira issue: CRDB-58921
Epic CRDB-58150