org.apache.phoenix.exception.PhoenixSQLException: Table MYSCHEMA.MYTABLE does not exist – metadata lookup failure

Question

The goal is to clarify how Apache Phoenix secondary indexes treat rows where the indexed column contains NULL. Specifically, we need to determine whether such rows are omitted from the index structure or are stored with a special sentinel value that allows the index to be used for queries filtering on NULL. The official documentation does not specify this behavior, and anecdotal reports suggest a change between Phoenix 4.x and 5.x, leaving administrators uncertain about index coverage for NULL‑valued columns.

This uncertainty affects query planning because a covered index scan may return different results than a full table scan when the query includes IS NULL predicates. Without a clear definition, it is difficult to predict whether an index‑only scan will be valid or whether a fallback to the base table is required. To resolve this, we need answers to the following: Does Phoenix include NULL values in secondary indexes? If so, what sentinel or encoding is used to represent NULL? Does the handling differ between Phoenix 4.x and 5.x releases?

Tasadduq Burney · Answer

Handling of NULLs in Phoenix Secondary Indexes
In Apache Phoenix, rows where the indexed column contains a NULL value are omitted from the secondary index structure. They are not stored with a sentinel value, nor are they indexed as a special case.

Impact on Query Planning
Because NULL values are not present in the index, the Phoenix query optimizer cannot use a secondary index to satisfy a predicate filtering for NULL. This leads to the following behaviors:

IS NULL Predicates: A query using WHERE column IS NULL will trigger a full table scan (or a scan of the primary index) because the secondary index contains no entries for these rows.
  IS NOT NULL Predicates: An index can be used to find all non-null values, but the optimizer must still ensure it hasn't missed any data if the query logic allows for NULL results.
  Index-Only Scans: If a query is designed as a covered index scan but filters for NULL, the optimizer will fall back to the base table to ensure correctness.

Version Differences (4.x vs 5.x)
The fundamental behavior—that NULL values are not indexed—has remained consistent across Phoenix 4.x and 5.x. While there have been numerous optimizations to the query planner and index scan efficiency in the 5.x series, the underlying storage mechanism for secondary indexes still excludes NULL entries to save space and maintain alignment with HBase's sparse column nature.

Verification Method
To verify this behavior in your specific environment, you can run a comparison between a covered index query and a full table scan using the EXPLAIN command:
-- This should show a full table scan (Full Scan) because NULLs aren't indexed
EXPLAIN SELECT * FROM MYSCHEMA.MYTABLE WHERE INDEXED_COL IS NULL;

-- This should show an index scan (Index Scan) for non-null values
EXPLAIN SELECT * FROM MYSCHEMA.MYTABLE WHERE INDEXED_COL = 'some_value';

Diagnostic Note: To provide a more tailored recommendation, please specify if you are using Global or Local indexes, as this affects how the fallback to the base table is executed during a scan.

org.apache.phoenix.exception.PhoenixSQLException: Table MYSCHEMA.MYTABLE does not exist – metadata lookup failure

1 answer

1 question comment

Handling of NULLs in Phoenix Secondary Indexes

Impact on Query Planning

Version Differences (4.x vs 5.x)

Verification Method

Your perspective belongs here.