What to do when the GetRange API returns data outside the specified range - Tablestore

If the GetRange API returns data outside the specified range, create a secondary index to reorder the primary keys, then query the index instead of the base table.

Symptoms

When running range queries — through the console, an SDK, Tablestore foreign tables, or Tablestore Reader — the GetRange API returns rows outside the range you specified.

Possible causes

The query condition does not follow the leftmost prefix matching rule.

Tablestore physically sorts rows by primary key columns in the order they are defined in the table schema. A range set on a primary key column is honored only when all preceding columns have exact (non-range) values. If any preceding column uses a range, the ranges set on subsequent columns are silently ignored.

Important

Leftmost prefix matching rule — what works and what does not:

Works: Setting an exact value for the first primary key column and a range for the second column returns only rows where the second column falls within that range.
Does not work: Setting a range for the first primary key column and a range (or exact value) for the second column — the condition on the second column is ignored.

For example, if beginPrimaryKey = INF_MIN and endPrimaryKey = INF_MAX for the first column, and beginPrimaryKey = 10 and endPrimaryKey = 10 for the second column, the query returns all rows in the table, not just rows where the second column equals 10. The first column range covers the entire key space, so the second column condition has no effect.

Solutions

Create a secondary index to reorder the primary keys so the column you want to filter on becomes the leading key. Query the secondary index directly instead of the base table. This lets you apply an effective range condition without changing your table schema.

Choose the approach that matches your scenario:

Console or SDK range queries — Without a secondary index, you must scan the entire table and discard unwanted rows. With a secondary index, run the range query directly on the index. For step-by-step instructions, see Use a secondary index from the console or Use a secondary index with an SDK.
Tablestore foreign table queries — Set the table name in the CREATE TABLE statement to the name of the secondary index. This routes the foreign table query through the index and applies your range condition correctly. For details, see Tablestore foreign tables.
Data synchronization with Tablestore Reader — Set the table name in the synchronization script to the name of the secondary index. The synchronization job then reads from the index and respects the range you defined. For details, see Configure Tablestore (OTS) Reader.