You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Maryann Xue <ma...@gmail.com> on 2015/01/16 04:43:12 UTC
Re: Phoenix Subqueries with ‘IN’
Hi Xiaoguo,
Do you mean you have hit a bug in Phoenix? The query is expected to return
nothing but returns all rows?
Thanks,
Maryann
On Thu, Jan 15, 2015 at 9:02 PM, 【小郭】 <gu...@qq.com> wrote:
> Hi guys:
> When using the subquery with 'IN',if the subquery return no rows,the
> query whill find all rows.
> for example:
> Query:
> SELECT * FROM SYS_USER WHERE ID IN (SELECT USER_ID FROM SYS_ORG
> WHERE ID = 0);
> If the subquery 'SELECT USER_ID FROM SYS_ORG WHERE ID = 0' have no result,the
> Query will return all rows of table 'SYS_USER'.
>
> How could i get the result like oracle if the subquery have no result?
>
> Hbase 0.98
> Phoenix 4.2
>
> Thank you for your help,
> xiaoguo
>
>
>
>
>
Re: Phoenix Subqueries with ‘IN’
Posted by Maryann Xue <ma...@gmail.com>.
Hi Xiaoguo,
Thanks a lot for reporting this bug. Have filed a corresponding JIRA:
https://issues.apache.org/jira/browse/PHOENIX-1610.
Thanks,
Maryann
On Thu, Jan 22, 2015 at 9:50 PM, 【小郭】 <gu...@qq.com> wrote:
> Thank you,Maryann.
> And I hit another bug.
> for example:
> Query1:
> select count(1) from (
> SELECT * FROM SYS_USER WHERE ID IN (800000502,800000604)
> ) as tmp_count;
> return1：+------------------------------------------+
> | COUNT(1) |
> +------------------------------------------+
> | 2 |
> +------------------------------------------+
>
>
> Query2:
> select count(1) from (
> SELECT * FROM SYS_USER WHERE ID IN (
> SELECT ID FROM SYS_USER WHERE ID IN (800000502,800000604))
> ) as tmp_count;
> return2：+------------------------------------------+
> | COUNT(1) |
> +------------------------------------------+
> | 0 |
> | 0 |
> +------------------------------------------+
>
> The "Query2" return not right，it should return as same as the "Query1"
>
>
> ------------------ 原始邮件 ------------------
> *发件人:* "Maryann Xue";<ma...@gmail.com>;
> *发送时间:* 2015年1月16日(星期五) 中午12:09
> *收件人:* "user@phoenix.apache.org"<us...@phoenix.apache.org>;
> *主题:* Re: Phoenix Subqueries with ‘IN’
>
> This has been verified as a bug. Just filed
> https://issues.apache.org/jira/browse/PHOENIX-1591 for it.
> Thank you very much for reporting this, Xiaoguo! You can expect it to be
> fixed in Phoenix 4.3.
>
> On Thu, Jan 15, 2015 at 10:43 PM, Maryann Xue <ma...@gmail.com>
> wrote:
>
>> Hi Xiaoguo,
>>
>> Do you mean you have hit a bug in Phoenix? The query is expected to
>> return nothing but returns all rows?
>>
>>
>> Thanks,
>> Maryann
>>
>> On Thu, Jan 15, 2015 at 9:02 PM, 【小郭】 <gu...@qq.com> wrote:
>>
>>> Hi guys:
>>> When using the subquery with 'IN',if the subquery return no
>>> rows,the query whill find all rows.
>>> for example:
>>> Query:
>>> SELECT * FROM SYS_USER WHERE ID IN (SELECT USER_ID FROM SYS_ORG
>>> WHERE ID = 0);
>>> If the subquery 'SELECT USER_ID FROM SYS_ORG WHERE ID = 0' have
>>> no result,the Query will return all rows of table 'SYS_USER'.
>>>
>>> How could i get the result like oracle if the subquery have no result?
>>>
>>>
>>> Hbase 0.98
>>> Phoenix 4.2
>>>
>>> Thank you for your help,
>>> xiaoguo
>>>
>>>
>>>
>>>
>>>
>>
>>
>
回复： Phoenix Subqueries with ‘IN’
Posted by 【小郭】 <gu...@qq.com>.
Thank you,Maryann.
And I hit another bug.
for example:
Query1:
select count(1) from (
SELECT * FROM SYS_USER WHERE ID IN (800000502,800000604)
) as tmp_count;
return1：+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 2 |
+------------------------------------------+
Query2:
select count(1) from (
SELECT * FROM SYS_USER WHERE ID IN (
SELECT ID FROM SYS_USER WHERE ID IN (800000502,800000604))
) as tmp_count;
return2：+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 0 |
| 0 |
+------------------------------------------+
The "Query2" return not right，it should return as same as the "Query1"
------------------ 原始邮件 ------------------
发件人: "Maryann Xue";<ma...@gmail.com>;
发送时间: 2015年1月16日(星期五) 中午12:09
收件人: "user@phoenix.apache.org"<us...@phoenix.apache.org>;
主题: Re: Phoenix Subqueries with ‘IN’
This has been verified as a bug. Just filed https://issues.apache.org/jira/browse/PHOENIX-1591 for it.
Thank you very much for reporting this, Xiaoguo! You can expect it to be fixed in Phoenix 4.3.
On Thu, Jan 15, 2015 at 10:43 PM, Maryann Xue <ma...@gmail.com> wrote:
Hi Xiaoguo,
Do you mean you have hit a bug in Phoenix? The query is expected to return nothing but returns all rows?
Thanks,
Maryann
On Thu, Jan 15, 2015 at 9:02 PM, 【小郭】 <gu...@qq.com> wrote:
Hi guys:
When using the subquery with 'IN',if the subquery return no rows,the query whill find all rows.
for example:
Query:
SELECT * FROM SYS_USER WHERE ID IN (SELECT USER_ID FROM SYS_ORG WHERE ID = 0);
If the subquery 'SELECT USER_ID FROM SYS_ORG WHERE ID = 0' have no result,the Query will return all rows of table 'SYS_USER'.
How could i get the result like oracle if the subquery have no result?
Hbase 0.98
Phoenix 4.2
Thank you for your help,
xiaoguo
Re: Phoenix Subqueries with ‘IN’
Posted by Maryann Xue <ma...@gmail.com>.
This has been verified as a bug. Just filed
https://issues.apache.org/jira/browse/PHOENIX-1591 for it.
Thank you very much for reporting this, Xiaoguo! You can expect it to be
fixed in Phoenix 4.3.
On Thu, Jan 15, 2015 at 10:43 PM, Maryann Xue <ma...@gmail.com> wrote:
> Hi Xiaoguo,
>
> Do you mean you have hit a bug in Phoenix? The query is expected to return
> nothing but returns all rows?
>
>
> Thanks,
> Maryann
>
> On Thu, Jan 15, 2015 at 9:02 PM, 【小郭】 <gu...@qq.com> wrote:
>
>> Hi guys:
>> When using the subquery with 'IN',if the subquery return no
>> rows,the query whill find all rows.
>> for example:
>> Query:
>> SELECT * FROM SYS_USER WHERE ID IN (SELECT USER_ID FROM SYS_ORG
>> WHERE ID = 0);
>> If the subquery 'SELECT USER_ID FROM SYS_ORG WHERE ID = 0' have no result,the
>> Query will return all rows of table 'SYS_USER'.
>>
>> How could i get the result like oracle if the subquery have no result?
>>
>>
>> Hbase 0.98
>> Phoenix 4.2
>>
>> Thank you for your help,
>> xiaoguo
>>
>>
>>
>>
>>
>
>