phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-2296) Subqueries with in clause on non varchar columns is not working
Date Sun, 24 Jan 2016 19:31:39 GMT

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

James Taylor updated PHOENIX-2296:
----------------------------------
    Labels: in verify  (was: in)

> 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)

Mime
View raw message