cockroachdb / cockroachdb/cockroach
sql: statement hints do not apply to view statements
- 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
Assessment
This issue has not been assessed yet.