cockroachdb / cockroachdb/cockroach

sql: statement hints do not apply inside routines

Open
#162,627 3 comments 0 reactions 1 assignee Claimed by @ZhouXing19 View on GitHub
A-plan-management A-sql-routine A-sql-udf 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 within routines do not currently respect hint injections. Here's an example:

```sql
CREATE TABLE olympians (
name STRING,
sport STRING,
INDEX (name) STORING (sport)
);

EXPLAIN SELECT sport FROM olympians WHERE name = 'Apolo Ohno';
-- constrained scan of olympians_name_idx

SELECT information_schema.crdb_rewrite_inline_hints(
'SELECT sport FROM olympians WHERE name = _',
'SELECT sport FROM olympians@olympians_pkey WHERE name = _'
);

EXPLAIN ANALYZE SELECT sport FROM olympians WHERE name = 'Apolo Ohno';
-- full scan of olympians_pkey

CREATE FUNCTION ohno() RETURNS STRING LANGUAGE SQL STABLE AS $$
SELECT sport FROM olympians WHERE name = 'Apolo Ohno';
$$;

SELECT ohno();
-- does not use olympians_pkey
```

Jira issue: CRDB-59564

Epic CRDB-66209

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.