Sanjeet Malhotra created PHOENIX-7773:
-----------------------------------------
Summary: IS NULL in WHERE clause for range, skip scans and RVCs
containing nulls not giving correct results
Key: PHOENIX-7773
URL: https://issues.apache.org/jira/browse/PHOENIX-7773
Project: Phoenix
Issue Type: Improvement
Reporter: Sanjeet Malhotra
Assignee: Sanjeet Malhotra
# If we execute a query which involves {{IS NULL}} check on trailing PK columns
in {{WHERE}} clause and {{WHERE}} clause doesn't include condition on all PK
columns i.e. range scan or skip scan. Then the rows returned are not correct.
In fact based on the sort order of PK columns, whether table is salted or not
the no. of rows vary!! Sometimes some extra rows can get returned for which the
column that was supposed to be {{NULL}} is actually not {{NULL}} while other
times lesser than expected rows will be returned!!
## I have covered this in one of the test cases in
[PR|https://github.com/apache/phoenix/pull/2377] to highlight range/skip scan
with {{IS NULL}} on trailing PK columns in {{WHERE}} clause is already broken.
## Test case:
{{WhereOptimizerForArrayAnyNullablePKIT#testRangeScanAndSkipScanWithIsNullOnSecondToLastPK()}}
# If all the PK columns are in ascending sort order and someone executes
{{SELECT * FROM TABLE OFFSET (PK1, PK2) = ('1', null)}} then the result
returned should be same as running query {{SELECT * FROM TABLE WHERE (PK1, PK2)
> ('1', null)}} but that doesn't happen!! From PHOENIX-4845 I got understanding
that both {{OFFSET}} and {{>}} when all PK are ASC sort order should give same
results. If we assume that {{NULL}} always sort on top then {{OFFSET}} gives
correct result but {{>}} doesn't.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
