[
https://issues.apache.org/jira/browse/PHOENIX-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15116553#comment-15116553
]
Maryann Xue commented on PHOENIX-2296:
--------------------------------------
Hi, [~Nilansg], I don't quite get what you mean by "second request" and "second
page" and the two queries you listed seem to be identical. Can you explain in
more details? Also, would you mind giving a complete description (incl. DDL and
sample data) with which I can reproduce the issue?
> 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)
