[ https://issues.apache.org/jira/browse/PHOENIX-6999?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Istvan Toth updated PHOENIX-6999: --------------------------------- Summary: Point lookups fail with reverse scan (was: Uncovered index with skip-scan-join plan doesn't return result for reverse scan) > Point lookups fail with reverse scan > ------------------------------------ > > Key: PHOENIX-6999 > URL: https://issues.apache.org/jira/browse/PHOENIX-6999 > Project: Phoenix > Issue Type: Bug > Components: core > Affects Versions: 5.2.0 > Reporter: Istvan Toth > Assignee: Istvan Toth > Priority: Major > > Actually, this is much more basic bug: > {noformat} > create table d (k1 varchar primary key, v1 varchar, v2 varchar); > -- create index i on d(v1); > upsert into d values ('a','a','a'); > upsert into d values ('b','b','b'); > upsert into d values ('c','c','c'); > {noformat} > {noformat} > 0: jdbc:phoenix:localhost:53422> select * from d where k1='b'; > +----+----+----+ > | K1 | V1 | V2 | > +----+----+----+ > | b | b | b | > +----+----+----+ > 1 row selected (0.035 seconds) > 0: jdbc:phoenix:localhost:53422> select * from d where k1='b' order by k1 > desc; > +----+----+----+ > | K1 | V1 | V2 | > +----+----+----+ > No rows selected (0.016 seconds) > 0: jdbc:phoenix:localhost:53422> explain select * from d where k1='b'; > > > +-----------------------------------------------------------------------------------------+----------------+---------------+-------------+ > | PLAN > | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | > +-----------------------------------------------------------------------------------------+----------------+---------------+-------------+ > | CLIENT 1-CHUNK 1 ROWS 201 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON > 1 KEY OVER D | 201 | 1 | 0 | > +-----------------------------------------------------------------------------------------+----------------+---------------+-------------+ > 1 row selected (0.025 seconds) > 0: jdbc:phoenix:localhost:53422> explain select * from d where k1='b' order > by k1 desc; > > > +-------------------------------------------------------------------------------------+----------------+---------------+-------------+ > | PLAN > | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | > +-------------------------------------------------------------------------------------+----------------+---------------+-------------+ > | CLIENT 1-CHUNK 1 ROWS 201 BYTES PARALLEL 1-WAY REVERSE POINT LOOKUP ON 1 > KEY OVER D | 201 | 1 | 0 | > +-------------------------------------------------------------------------------------+----------------+---------------+-------------+ > 1 row selected (0.014 seconds) > 0: jdbc:phoenix:localhost:53422> > {noformat} > > This was the original repro with uncovered indexes, but the the problem is > not the index, but with the basic scan. > {noformat} > create table d (k1 varchar primary key, v1 varchar, v2 varchar); > create index i on d(v1); > upsert into d values ('a','a','a'); > upsert into d values ('b','b','b'); > upsert into d values ('c','c','c'); > {noformat} > {noformat} > 0: jdbc:phoenix:localhost:55375> select /*+ INDEX(d i) NO_INDEX_SERVER_MERGE > */ * from d where v1='b' order by k1 asc; > +----+----+----+ > | K1 | V1 | V2 | > +----+----+----+ > | b | b | b | > +----+----+----+ > 1 row selected (0.035 seconds) > 0: jdbc:phoenix:localhost:55375> select /*+ INDEX(d i) NO_INDEX_SERVER_MERGE > */ * from d where v1='b' order by k1 desc; > +----+----+----+ > | K1 | V1 | V2 | > +----+----+----+ > No rows selected (0.03 seconds) > 0: jdbc:phoenix:localhost:55375> explain select /*+ INDEX(d i) > NO_INDEX_SERVER_MERGE */ * from d where v1='b' order by k1 asc; > +---------------------------------------------------------------------------+----------------+---------------+-------------+ > | PLAN | > EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | > +---------------------------------------------------------------------------+----------------+---------------+-------------+ > | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER D | > null | null | null | > | SKIP-SCAN-JOIN TABLE 0 | > null | null | null | > | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER I ['b'] | > null | null | null | > | SERVER FILTER BY FIRST KEY ONLY | > null | null | null | > | DYNAMIC SERVER FILTER BY "D.K1" IN ($107.$109) | > null | null | null | > +---------------------------------------------------------------------------+----------------+---------------+-------------+ > 5 rows selected (0.029 seconds) > 0: jdbc:phoenix:localhost:55375> explain select /*+ INDEX(d i) > NO_INDEX_SERVER_MERGE */ * from d where v1='b' order by k1 desc; > +---------------------------------------------------------------------------+----------------+---------------+-------------+ > | PLAN | > EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | > +---------------------------------------------------------------------------+----------------+---------------+-------------+ > | CLIENT 1-CHUNK PARALLEL 1-WAY REVERSE FULL SCAN OVER D | > null | null | null | > | SKIP-SCAN-JOIN TABLE 0 | > null | null | null | > | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER I ['b'] | > null | null | null | > | SERVER FILTER BY FIRST KEY ONLY | > null | null | null | > | DYNAMIC SERVER FILTER BY "D.K1" IN ($113.$115) | > null | null | null | > +---------------------------------------------------------------------------+----------------+---------------+-------------+ > 5 rows selected (0.029 seconds) > 0: jdbc:phoenix:localhost:55375> > {noformat} > -- This message was sent by Atlassian Jira (v8.20.10#820010)