Question
org.apache.phoenix.exception.PhoenixSQLException: Table MYSCHEMA.MYTABLE does not exist – metadata lookup failure
Sora Moss
0 reputation · 27 Jan 2021, 15:02 UTC
78.3K views1
Loading…
0 reputation · 27 Jan 2021, 15:02 UTC
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?
34775 reputation · 27 Jan 2021, 17:23 UTC
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.
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:
phoenixWHERE 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.NULL results.NULL, the optimizer will fall back to the base table to ensure correctness.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.
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.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 27 Jan 2021, 21:16 UTC
Apache Phoenix builds each secondary index as a separate HBase table where the indexed column value forms part of the row key. Because HBase only stores actual cells, a NULL indexed column does not create a cell, so the corresponding row is absent from the index. This omission is intentional and matches HBase's sparse storage model; no sentinel or tombstone is written for NULL values. Consequently, any query that includes IS NULL cannot be satisfied by an index‑only scan and the optimizer falls back to a full table scan (or primary index scan). If you need NULLs to be searchable via an index, you must create a functional index that replaces NULL with a constant sentinel (e.g., NVL(col, '_NULL_')) and adjust the predicate accordingly.