Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@phoenix.apache.org
Date: Mon, 4 Jan 2016 02:50:39 +0000 (UTC)
From: "ChenFangRong (JIRA)" <jira@apache.org>
To: dev@phoenix.incubator.apache.org
Message-ID: <JIRA.12926861.1451875755000.5681.1451875839786@Atlassian.JIRA>
In-Reply-To: <JIRA.12926861.1451875755000@Atlassian.JIRA>
References: <JIRA.12926861.1451875755000@Atlassian.JIRA>
 <JIRA.12926861.1451875755653@arcas>
Subject: [jira] [Updated] (PHOENIX-2559) sql query bug when primary key
 involved to use skipScanFilter
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/PHOENIX-2559?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ChenFangRong updated PHOENIX-2559:
----------------------------------
    Description: 
query results is incorrect when pk involved using '=' .

for example,
crate a table use sql:
create table bugTable(ID varchar primary key,company varchar);
upsert values use sql:
upsert into bugTable values('i1','c1');
upsert into bugTable values('i2','c2');
upsert into bugTable values('i3','c3');

the table is now like below,
+------------+------------+
|     ID     |  COMPANY   |
+------------+------------+
| i1         | c1         |
| i2         | c2         |
| i3         | c3         |
+------------+------------+

then use sql to query result from bugTable,
select * from bugTable where ID = 'i1' or (ID = 'i2' and company = 'c3');

the result expected:
+------------+------------+
|     ID     |  COMPANY   |
+------------+------------+
| i1         | c1         |
+------------+------------+

but, phoenix return the wrong result like below,
+------------+------------+
|     ID     |  COMPANY   |
+------------+------------+
| i1         | c1         |
| i2         | c2         |
+------------+------------+
the condition  company = 'c3' is not used at all !
and then you can find when sql query combine primary key '=' somthing  with any condition,then the condition is not used at all either, it means when the sql like this,
select xxx from xxx where pk = 'xxx' or (pk = 'xx' and any other condition);
the any other condition does not used at all.

I test the sql below all versions in phoenix before(including) version 4.3.1 & 3.3.1, it turns out that all the version sql query result is wrong.

I read the source code, and thought maybe the problem because when compile the sql query, the pushKeyToExpression method lost the condition, and method evaluate in AndOrExpression is not work.

  was:
query results is incorrect when pk involved using '=' .
f
or example,
crate a table use sql:
create table bugTable(ID varchar primary key,company varchar);
upsert values use sql:
upsert into bugTable values('i1','c1');
upsert into bugTable values('i2','c2');
upsert into bugTable values('i3','c3');

the table is now like below,
+------------+------------+
|     ID     |  COMPANY   |
+------------+------------+
| i1         | c1         |
| i2         | c2         |
| i3         | c3         |
+------------+------------+

then use sql to query result from bugTable,
select * from bugTable where ID = 'i1' or (ID = 'i2' and company = 'c3');

the result expected:
+------------+------------+
|     ID     |  COMPANY   |
+------------+------------+
| i1         | c1         |
+------------+------------+

but, phoenix return the wrong result like below,
+------------+------------+
|     ID     |  COMPANY   |
+------------+------------+
| i1         | c1         |
| i2         | c2         |
+------------+------------+
the condition  company = 'c3' is not used at all !
and then you can find when sql query combine primary key '=' somthing  with any condition,then the condition is not used at all either, it means when the sql like this,
select xxx from xxx where pk = 'xxx' or (pk = 'xx' and any other condition);
the any other condition does not used at all.

I test the sql below all versions in phoenix before(including) version 4.3.1 & 3.3.1, it turns out that all the version sql query result is wrong.

I read the source code, and thought maybe the problem because when compile the sql query, the pushKeyToExpression method lost the condition, and method evaluate in AndOrExpression is not work.


> sql query bug when primary key involved to use skipScanFilter
> -------------------------------------------------------------
>
>                 Key: PHOENIX-2559
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2559
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 3.3.1, 4.3.1
>         Environment: centOS
>            Reporter: ChenFangRong
>              Labels: features
>   Original Estimate: 168h
>  Remaining Estimate: 168h
>
> query results is incorrect when pk involved using '=' .
> for example,
> crate a table use sql:
> create table bugTable(ID varchar primary key,company varchar);
> upsert values use sql:
> upsert into bugTable values('i1','c1');
> upsert into bugTable values('i2','c2');
> upsert into bugTable values('i3','c3');
> the table is now like below,
> +------------+------------+
> |     ID     |  COMPANY   |
> +------------+------------+
> | i1         | c1         |
> | i2         | c2         |
> | i3         | c3         |
> +------------+------------+
> then use sql to query result from bugTable,
> select * from bugTable where ID = 'i1' or (ID = 'i2' and company = 'c3');
> the result expected:
> +------------+------------+
> |     ID     |  COMPANY   |
> +------------+------------+
> | i1         | c1         |
> +------------+------------+
> but, phoenix return the wrong result like below,
> +------------+------------+
> |     ID     |  COMPANY   |
> +------------+------------+
> | i1         | c1         |
> | i2         | c2         |
> +------------+------------+
> the condition  company = 'c3' is not used at all !
> and then you can find when sql query combine primary key '=' somthing  with any condition,then the condition is not used at all either, it means when the sql like this,
> select xxx from xxx where pk = 'xxx' or (pk = 'xx' and any other condition);
> the any other condition does not used at all.
> I test the sql below all versions in phoenix before(including) version 4.3.1 & 3.3.1, it turns out that all the version sql query result is wrong.
> I read the source code, and thought maybe the problem because when compile the sql query, the pushKeyToExpression method lost the condition, and method evaluate in AndOrExpression is not work.


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)