Skip to content

opt: placeholder fast path should respect index hints #147363

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
mgartner opened this issue May 27, 2025 · 0 comments · Fixed by #147368
Closed

opt: placeholder fast path should respect index hints #147363

mgartner opened this issue May 27, 2025 · 0 comments · Fixed by #147368
Assignees
Labels
branch-release-21.2 Used to mark GA and release blockers, technical advisories, and bugs for 21.2 C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team target-release-25.1.8 target-release-25.2.2 target-release-25.3.0

Comments

@mgartner
Copy link
Collaborator

mgartner commented May 27, 2025

The placeholder fast-path, used for simple prepared statements, does not consider index hints when choosing an index for the placeholder scan. It may pick an index that was not forced. This causes an error when the query is executed because the optimizer did not find a plan that uses the forced index.

For example:

CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  PRIMARY KEY (a, b),
  INDEX i (a, b)
);

SELECT * FROM t@i WHERE a = 11 AND b = 22;
--  a | b | c
-- ----+---+----
-- (0 rows)

PREPARE p AS
SELECT * FROM t@i WHERE a = $1 AND b = $2;

EXECUTE p(11, 22);
-- ERROR: index "i" cannot be used for this query
-- SQLSTATE: 42809

This bug is present since the placeholder fast path was introduced in v21.2.0.

Jira issue: CRDB-51019

@mgartner mgartner self-assigned this May 27, 2025
@mgartner mgartner added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team labels May 27, 2025
@github-project-automation github-project-automation bot moved this to Triage in SQL Queries May 27, 2025
@mgartner mgartner added the branch-release-21.2 Used to mark GA and release blockers, technical advisories, and bugs for 21.2 label May 27, 2025
@cockroachdb cockroachdb deleted a comment from blathers-crl bot May 27, 2025
@mgartner mgartner moved this from Triage to Active in SQL Queries May 27, 2025
craig bot pushed a commit that referenced this issue May 28, 2025
147355: go.mod: bump Pebble to a38c80fb488e r=sumeerbhola a=jbowens

Changes:

 * [`a38c80fb`](cockroachdb/pebble@a38c80fb) metrics: fix w-amp calculation w/ blob files
 * [`d742eb87`](cockroachdb/pebble@d742eb87) sstable: fix layout footer printing
 * [`bbcf0346`](cockroachdb/pebble@bbcf0346) crossversion: pass --previous-ops flag
 * [`20dbec1e`](cockroachdb/pebble@20dbec1e) metamorphic: sanity check initial-state and previous-ops flags
 * [`1b64b32e`](cockroachdb/pebble@1b64b32e) metamorphic: fix code around WAL recovery directories
 * [`3ec1b8da`](cockroachdb/pebble@3ec1b8da) metamorphic: use store-relative paths
 * [`138ec7ca`](cockroachdb/pebble@138ec7ca) db: support store-relative paths for WAL dirs
 * [`bd648617`](cockroachdb/pebble@bd648617) metamorphic: add random number to external object names
 * [`07c9d319`](cockroachdb/pebble@07c9d319) metamorphic: support CreateOnShared on existing store
 * [`2768d798`](cockroachdb/pebble@2768d798) metamorphic: use FS-based remote storage
 * [`4a2b3b81`](cockroachdb/pebble@4a2b3b81) metamorphic: update error blacklist

Release note: none.
Epic: none.

147368: opt: fix hints for placeholder fast path r=mgartner a=mgartner

Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.


Co-authored-by: Jackson Owens <[email protected]>
Co-authored-by: Marcus Gartner <[email protected]>
@craig craig bot closed this as completed in 572025d May 28, 2025
@github-project-automation github-project-automation bot moved this from Active to Done in SQL Queries May 28, 2025
blathers-crl bot pushed a commit that referenced this issue May 28, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
blathers-crl bot pushed a commit that referenced this issue May 28, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
blathers-crl bot pushed a commit that referenced this issue May 28, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
blathers-crl bot pushed a commit that referenced this issue May 28, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
blathers-crl bot pushed a commit that referenced this issue May 28, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
mgartner added a commit that referenced this issue May 29, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
mgartner added a commit that referenced this issue May 29, 2025
Fixes #147363

Release note (bug fix): A bug has been fixed that cause the optimizer to
ignore index hints when optimizing some forms of prepared statements.
This could result in one of two unexepcted behaviors: a query errors
with the message "index cannot be used for this query" when the index
can actually be used, or query using an index that does not adhere to
the hint. The hints relevant to this bug are regular index hints, e.g.,
`SELECT * FROM tab@index`, `FORCE_INVERTED_INDEX` and `FORCE_ZIGZAG`.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
branch-release-21.2 Used to mark GA and release blockers, technical advisories, and bugs for 21.2 C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team target-release-25.1.8 target-release-25.2.2 target-release-25.3.0
Projects
Status: Done
Development

Successfully merging a pull request may close this issue.

1 participant