[
https://issues.apache.org/jira/browse/PHOENIX-6999?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Istvan Toth updated PHOENIX-6999:
---------------------------------
Description:
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}
was:
{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}
> Uncovered index with skip-scan-join plan doesn't return result for 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)
