You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Ni la (JIRA)" <ji...@apache.org> on 2015/09/30 14:46:04 UTC
[jira] [Created] (PHOENIX-2296) Subqueries with in clause on non
varchar columns is not working
Ni la created PHOENIX-2296:
------------------------------
Summary: 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
Priority: Critical
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)