Known-Limitation: Equality lookups on Primary/Unique Keys fail during EXPLAIN
- Dominant language
- Python
- Stars
- 149
- Forks
- 27
- PR merge metrics
- No merged PRs in 30d
Description
### Feature Description and Motivation
## Problem Description
When a query performs an equality lookup on a Primary Key (PK) or a Unique Key (UK) (e.g., `WHERE pk_column = constant`), the MySQL/MariaDB optimizer identifies this as a "const" or "ref" access that will return at most one row. As an optimization, it attempts to execute a direct `index_read` during the `EXPLAIN` phase itself to fetch the row.
Because the VIDEX storage engine does not contain actual data, this `index_read` operation fails, causing the entire `EXPLAIN` command to error out.
### System Version
MySQL 5.7, MySQL 8.0, MariaDB 11.8
### Use Case Scenario
## Steps to Reproduce
1. Load the TPC-H schema into VIDEX, where `part.P_PARTKEY` is the Primary Key.
2. Run the following query:
```sql
EXPLAIN SELECT COUNT(*) FROM part WHERE P_PARTKEY = 1;
```
## Expected Behavior
VIDEX should successfully generate an `EXPLAIN` plan without executing the read. The plan should indicate a "const" or "ref" access type with `rows=1`, correctly simulating the optimizer's choice.
## Current Behavior
The query fails with a storage engine error:
`(1031, "Table storage engine for 'part' doesn't have this option")`
### Proposed Implementation (Optional)
1. **Mock the `index_read` handler (Preferred)**: Implement a mock version of the `index_read` function within the VIDEX storage engine (`ha_videx.cc`). When this function is called, instead of trying to read data, it should simply:
* Return a success status.
* Fulfill the `char*` buffer meeting condition requirements (as expected for a unique lookup).
But the mock may be very complicated.
2. **Bypass the `index_read` Optimization**: Investigate using an `optimizer_switch` variable to disable this specific short-circuit evaluation. This would force the optimizer to rely on standard cost-based estimation.
* **Drawback**: This approach may cause the plan generated by VIDEX to differ from the plan generated on a native instance, which contradicts the primary goal of VIDEX to perfectly simulate the original optimizer. Therefore, option 1 is strongly preferred.
Contributor guide
No contributing guide indexed for this repository
Research direction
Reproduce the failure with `EXPLAIN SELECT COUNT(*) FROM part WHERE P_PARTKEY = 1` using the TPC-H schema. Inspect the VIDEX storage engine implementation in `ha_videx.cc`, focusing on how equality lookups invoke `index_read` during EXPLAIN. Done means EXPLAIN succeeds and reports const or ref access with rows=1 without reading actual data.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, mariadb, mysql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100