Description
Given following table and data:
CREATE TABLE test ( pk1 INTEGER NOT NULL , pk2 INTEGER NOT NULL, pk3 INTEGER NOT NULL, pk4 INTEGER NOT NULL, v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4)) )
Noticed pk3 is DESC.
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
If we execute the following sql:
select * from test where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
the returned result is empty, but obviously, the above inserted row (1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we clip the (pk3,pk4) < (5,7) because pk3 is DESC by following line 260 in WhereOptimizer.pushKeyExpressionsToScan , (pk3,pk4) < (5,7) is clipped to pk3 <= 5 and pk4 < 7 .
257 List<KeyRange> leftRanges = clipLeft(schema, slot.getPKPosition() 258 + slotOffset - clipLeftSpan, clipLeftSpan, keyRanges, ptr); 259 keyRanges = 260 clipRight(schema, slot.getPKPosition() + slotOffset - 1, keyRanges, 261 leftRanges, ptr); 262 if (prevSortOrder == SortOrder.DESC) { 263 leftRanges = invertKeyRanges(leftRanges); 264 } 265 slotSpanArray[cnf.size()] = clipLeftSpan-1; 266 cnf.add(leftRanges); 267 clipLeftSpan = 0; 268 prevSortOrder = sortOrder; 269 // since we have to clip the portion with the same sort order, we can no longer 270 // extract the nodes from the where clause 271 // for eg. for the schema A VARCHAR DESC, B VARCHAR ASC and query 272 // WHERE (A,B) < ('a','b') 273 // the range (* - a\xFFb) is converted to (~a-*)(*-b) 274 // so we still need to filter on A,B 275 stopExtracting = true; 276 }
Eventually after we completed the WhereOptimizer.pushKeyExpressionsToScan, the result
ScanRanges.ranges is [[[1 - 2]], [[3 - 4]], [5 - *), [(* - 7)]], ScanRanges.useSkipScanFilter is true and SkipScanFilter is also [[[1 - 2]], [[3 - 4]], [5 - *), [(* - 7)]], so the the above inserted row (1,3,4,10,1) could not be retrieved.
But as we know, (pk3,pk4) < (5,7) is not semantically equals to pk3 <= 5 and pk4 < 7 , we could only have
pk3 <= 5 but not pk4 < 7, so when we clipped (pk3,pk4) < (5,7) to pk3 <= 5 , we could simply skip remaining columns of this RVC.
Attachments
Attachments
Issue Links
- is caused by
-
PHOENIX-3383 Comparison between descending row keys used in RVC is reverse
- Closed
- is related to
-
PHOENIX-4841 Filters that uses RVC with pk columns where with DESC sort order don't work correctly
- Closed
- links to