[
https://issues.apache.org/jira/browse/PHOENIX-6960?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17759627#comment-17759627
]
Viraj Jasani commented on PHOENIX-6960:
---------------------------------------
Another way to reproduce the issue:
DESC:
{code:java}
CREATE TABLE t3 (id varchar, name varchar, type decimal, status integer
CONSTRAINT pk PRIMARY KEY(id desc, type));
UPSERT INTO T3 VALUES('abc', 'abc', 1, 1);
SELECT * FROM T3 where type = 1 and id like 'ab%';
+----+------+------+--------+
| ID | NAME | TYPE | STATUS |
+----+------+------+--------+
+----+------+------+--------+
{code}
ASC:
{code:java}
CREATE TABLE t4 (id varchar, name varchar, type decimal, status integer
CONSTRAINT pk PRIMARY KEY(id, type));
UPSERT INTO T4 VALUES('abc', 'abc', 1, 1);
SELECT * FROM T4 where type = 1 and id like 'ab%';
+-----+------+------+--------+
| ID | NAME | TYPE | STATUS |
+-----+------+------+--------+
| abc | abc | 1 | 1 |
+-----+------+------+--------+
{code}
{code:java}
EXPLAIN SELECT * FROM T3 where type = 1 and id like 'ab%';
+----------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| PLAN
| EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+----------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN SKIP SCAN ON 1 RANGE OVER T3
[~'ab',1] - [~'aa',1] | null | null | null |
+----------------------------------------------------------------------------------------------+----------------+---------------+-------------+
{code}
{code:java}
EXPLAIN SELECT * FROM T4 where type = 1 and id like 'ab%';
+--------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| PLAN
| EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+--------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN SKIP SCAN ON 1 RANGE OVER T4
['ab',1] - ['ac',1] | null | null | null |
+--------------------------------------------------------------------------------------------+----------------+---------------+-------------+
{code}
The inverted range used by T3 is incorrect.
> Scan range is wrong when query desc columns
> -------------------------------------------
>
> Key: PHOENIX-6960
> URL: https://issues.apache.org/jira/browse/PHOENIX-6960
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 5.2.0, 5.1.3
> Reporter: fanartoria
> Assignee: Jing Yu
> Priority: Critical
>
> Ways to reproduce
> {code}
> 0: jdbc:phoenix:> create table sts(id integer primary key, name varchar, type
> integer, status integer);
> No rows affected (1.259 seconds)
> 0: jdbc:phoenix:> create index sts_name_desc on sts(status, type desc, name
> desc);
> ^[[ANo rows affected (6.376 seconds)
> 0: jdbc:phoenix:> create index sts_name_asc on sts(type desc, name) include
> (status);
> No rows affected (6.377 seconds)
> 0: jdbc:phoenix:> upsert into sts values(1, 'test10.txt', 1, 1);
> 1 row affected (0.026 seconds)
> 0: jdbc:phoenix:>
> 0: jdbc:phoenix:>
> 0: jdbc:phoenix:> explain select * from sts where type = 1 and name like
> 'test10%';
> +------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
> | PLAN
> | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
> +------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER STS_NAME_ASC
> [~1,'test10'] - [~1,'test11'] | null | null | null |
> +------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
> 1 row selected (0.023 seconds)
> 0: jdbc:phoenix:> select * from sts where type = 1 and name like 'test10%';
> +----+------------+------+--------+
> | ID | NAME | TYPE | STATUS |
> +----+------------+------+--------+
> | 1 | test10.txt | 1 | 1 |
> +----+------------+------+--------+
> 1 row selected (0.033 seconds)
> 0: jdbc:phoenix:> explain select * from sts where status = 1 and type = 1 and
> name like 'test10%';
> +-------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
> | PLAN
> | EST_BYTES_READ | EST_ROWS_READ |
> EST_INFO_TS |
> +-------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER STS_NAME_DESC
> [1,~1,~'test10'] - [1,~1,~'test1/'] | null | null | null
> |
> | SERVER FILTER BY FIRST KEY ONLY AND "NAME" LIKE 'test10%'
> | null | null | null
> |
> +-------------------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
> 2 rows selected (0.022 seconds)
> 0: jdbc:phoenix:> select * from sts where status = 1 and type = 1 and name
> like 'test10%';
> +----+------+------+--------+
> | ID | NAME | TYPE | STATUS |
> +----+------+------+--------+
> +----+------+------+--------+
> No rows selected (0.04 seconds)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
