You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Daniel Wong (Jira)" <ji...@apache.org> on 2022/03/03 01:34:00 UTC
[jira] [Commented] (PHOENIX-6659) RVC with AND clauses return incorrect result
[ https://issues.apache.org/jira/browse/PHOENIX-6659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17500447#comment-17500447 ]
Daniel Wong commented on PHOENIX-6659:
--------------------------------------
[~yanxinyi] good catch but one thing you need to be careful on is your reduction isn't always going to be valid.
SELECT * FROM DUMMY WHERE (PK1 = 'a') AND (PK1,PK2,PK3) <= ('a',3,1);
and
SELECT * FROM DUMMY WHERE (PK1 = 'a') AND (PK2,PK3) <= (3,1);
works but consider
SELECT * FROM DUMMY WHERE (PK1 = 'a' OR PK1 = 'b' ) AND (PK1,PK2,PK3) <= ('a',3,1);
SELECT * FROM DUMMY WHERE (PK1 = 'a' OR PK1 = 'b' ) AND (PK2,PK3) <= (3,1);
these are not equivalent.
> RVC with AND clauses return incorrect result
> --------------------------------------------
>
> Key: PHOENIX-6659
> URL: https://issues.apache.org/jira/browse/PHOENIX-6659
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.16.1
> Reporter: Xinyi Yan
> Priority: Critical
> Attachments: Screen Shot 2022-03-01 at 1.26.44 PM.png
>
>
> CREATE TABLE DUMMY (PK1 VARCHAR NOT NULL, PK2 BIGINT NOT NULL, PK3 BIGINT NOT NULL CONSTRAINT PK PRIMARY KEY (PK1,PK2,PK3));
> UPSERT INTO DUMMY VALUES ('a',0,1);
> UPSERT INTO DUMMY VALUES ('a',1,1);
> UPSERT INTO DUMMY VALUES ('a',2,1);
> UPSERT INTO DUMMY VALUES ('a',3,1);
> UPSERT INTO DUMMY VALUES ('a',3,2);
> UPSERT INTO DUMMY VALUES ('a',4,1);
>
> {code:java}
> 0: jdbc:phoenix:localhost> SELECT * FROM DUMMY WHERE (PK1 = 'a') AND (PK1,PK2,PK3) <= ('a',3,1);
> +------------------------------------------+------------------------------------------+--------------------------------+
> | PK1 | PK2 | PK3 |
> +------------------------------------------+------------------------------------------+--------------------------------+
> +------------------------------------------+------------------------------------------+--------------------------------+
> No rows selected (0.045 seconds)
> 0: jdbc:phoenix:localhost> explain SELECT * FROM DUMMY WHERE (PK1 = 'a') AND (PK1,PK2,PK3) <= ('a',3,1);
> +------------------------------------------+------------------------------------------+--------------------------------+
> | PLAN | EST_BYTES_READ | EST_ROWS_READ |
> +------------------------------------------+------------------------------------------+--------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER DUMMY ['a',*] - ['a',-9187343239835811840] | null |
> | SERVER FILTER BY FIRST KEY ONLY | null | null |
> +------------------------------------------+------------------------------------------+--------------------------------+
> 2 rows selected (0.012 seconds)
> 0: jdbc:phoenix:localhost> SELECT * FROM DUMMY WHERE (PK1 = 'a') AND (PK2,PK3) <= (3,1);
> +------------------------------------------+------------------------------------------+--------------------------------+
> | PK1 | PK2 | PK3 |
> +------------------------------------------+------------------------------------------+--------------------------------+
> | a | 0 | 1 |
> | a | 1 | 1 |
> | a | 2 | 1 |
> | a | 3 | 1 |
> +------------------------------------------+------------------------------------------+--------------------------------+
> 4 rows selected (0.014 seconds)
> 0: jdbc:phoenix:localhost> EXPLAIN SELECT * FROM DUMMY WHERE (PK1 = 'a') AND (PK2,PK3) <= (3,1);
> +------------------------------------------+------------------------------------------+--------------------------------+
> | PLAN | EST_BYTES_READ | EST_ROWS_READ |
> +------------------------------------------+------------------------------------------+--------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER DUMMY ['a',*] - ['a',3] | null |
> | SERVER FILTER BY FIRST KEY ONLY | null | null |
> +------------------------------------------+------------------------------------------+--------------------------------+
> 2 rows selected (0.004 seconds) {code}
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)