[
https://issues.apache.org/jira/browse/PHOENIX-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15114481#comment-15114481
]
James Taylor commented on PHOENIX-2296:
---------------------------------------
[~maryannxue] - would you mind verifying this?
> Subqueries with in clause on non varchar columns is not working
> ---------------------------------------------------------------
>
> Key: PHOENIX-2296
> URL: https://issues.apache.org/jira/browse/PHOENIX-2296
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.5.0, 4.5.2
> Reporter: Ni la
> Assignee: Maryann Xue
> Priority: Critical
> Labels: in, verify
> Fix For: 4.8.0
>
>
> When using "IN" clause with limit in a sub query, the results are not coming
> correctly. The result is bringing some of the records that are not valid as
> part of the sub query result.
> eg:
> In the given example, the first four(always four records and only on second
> request in the limit) records in the first limit are copied to second page
> and last 4 records are not displayed.
> select ATTR_ID, NAME from TEST where ID = 289024 and DIM_ID = 0 and NAME is
> not null and NAME NOT IN (select NAME from TEST where ID = 289024 and DIM_ID
> = 0 and NAME is not null order by NAME limit 0 ) order by NAME limit 10;
> +------------------------------------------+------------------------------------------+
> | ATTR_ID | NAME |
> +------------------------------------------+------------------------------------------+
> | 289039 | black
> |
> | 292055 | black1
> |
> | 292056 | black10
> |
> | 292057 | black100
> |
> | 292058 | black101
> |
> | 292059 | black103
> |
> | 292060 | black11
> |
> | 292061 | black12
> |
> | 292062 | black13
> |
> | 292063 | black14
> |
> +------------------------------------------+------------------------------------------+
> 10 rows selected (1.04 seconds)
> select ATTR_ID, NAME from TEST where ID = 289024 and DIM_ID = 0 and NAME is
> not null and NAME NOT IN (select NAME from TEST where ID = 289024 and DIM_ID
> = 0 and NAME is not null order by NAME limit 10 ) order by NAME limit 10;
> +------------------------------------------+------------------------------------------+
> | ATTR_ID | NAME |
> +------------------------------------------+------------------------------------------+
> | 292060 | black11
> |
> | 292061 | black12
> |
> | 292062 | black13
> |
> | 292063 | black14
> |
> | 292064 | black15
> |
> | 292065 | black16
> |
> | 292066 | black17
> |
> | 292067 | black18
> |
> | 292068 | black19
> |
> | 292069 | black2
> |
> +------------------------------------------+------------------------------------------+
> 10 rows selected (1.683 seconds)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
