cockroachdb / cockroachdb/cockroach

sql: statement hints do not apply to view statements

Open
#166,782 4 comments 0 reactions 0 assignees View on GitHub
A-plan-management A-statement-hint branch-release-26.1 branch-release-26.2 C-enhancement docs-known-limitation T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Statements in views do not currently respect hint injections from `information_schema.crdb_rewrite_inline_hints`. Here's an example:

```sql
CREATE TABLE bananas (
cultivar STRING NOT NULL,
rating INT NOT NULL,
UNIQUE INDEX (cultivar)
);

INSERT INTO bananas VALUES ('cavendish', 1), ('gros michel', 10), ('pisang raja', 600);

-- uses constrained scan of bananas_cultivar_key
EXPLAIN SELECT cultivar FROM bananas WHERE cultivar != 'cavendish';

SELECT information_schema.crdb_rewrite_inline_hints(
'SELECT cultivar FROM bananas WHERE cultivar != _',
'SELECT cultivar FROM bananas@bananas_pkey WHERE cultivar != _'
);

-- uses full scan of bananas_pkey
EXPLAIN SELECT cultivar FROM bananas WHERE cultivar != 'cavendish';

CREATE VIEW vananas AS SELECT cultivar FROM bananas WHERE cultivar != 'cavendish';

-- ignores hint injection, uses constrained scan of bananas_cultivar_key
EXPLAIN SELECT * FROM vananas;
```

There is an inherent difficulty here, having to do with the "fallback" behavior of hint injection. To ensure safety of hint injections, statements with hint injections are [built and optimized again](https://github.com/cockroachdb/cockroach/blob/fa46189279fcdbbdc0329b7a19cd08a5ac44af54/pkg/sql/plan_opt.go#L322-L328) if optimization with the injected hints fails. This type of fallback isn't possible for views, because the view statement is inlined. (This same problem affects inlined UDFs in #162627.)

Jira issue: CRDB-62084

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.