[jira] [Commented] (PHOENIX-1362) Min/max query on CHAR columns containing values with '0' as prefix always returns null

["Hari Krishna Dara (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-1362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14174801#comment-14174801 ] 

Hari Krishna Dara commented on PHOENIX-1362:
--------------------------------------------

I just found a workaround, feel free to lower the priority:

{noformat}
0: jdbc:phoenix:isthbase01-mnds2-1-crd> select min(cast(VAL2 as VARCHAR)), max(cast(VAL2 as VARCHAR)) from TT;
+-----------------------+-----------------------+
| MIN(TO_VARCHAR(VAL2)) | MAX(TO_VARCHAR(VAL2)) |
+-----------------------+-----------------------+
| 00                    | 02                    |
+-----------------------+-----------------------+
{noformat}

> Min/max query on CHAR columns containing values with '0' as prefix always returns null
> --------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-1362
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1362
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.1
>         Environment: HBase 0.98.4
> RHEL 6.5
>            Reporter: Hari Krishna Dara
>              Labels: aggregate, char
>
> - Create a table with CHAR type and insert a few strings that start with 0.
> - Select min()/max() on the column, you always get null value.
> {noformat}
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> create table TT(VAL1 integer not null, VAL2 char(2), val3 varchar, VAL4 varchar constraint PK primary key (VAL1));
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> upsert into TT values (0, '00', '00', '0');
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> upsert into TT values (1, '01', '01', '1');
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> upsert into TT values (2, '02', '02', '2');
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> select * from TT;
> +------------+------+------------+------------+
> |    VAL1    | VAL2 |    VAL3    |    VAL4    |
> +------------+------+------------+------------+
> | 0          | 00   | 00         | 0          |
> | 1          | 01   | 01         | 1          |
> | 2          | 02   | 02         | 2          |
> +------------+------+------------+------------+
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> select min(VAL1), max(VAL1) from TT;
> +------------+------------+
> | MIN(VAL1)  | MAX(VAL1)  |
> +------------+------------+
> | 0          | 2          |
> +------------+------------+
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> select min(VAL2), max(VAL2) from TT;
> +------------+------------+
> | MIN(VAL2)  | MAX(VAL2)  |
> +------------+------------+
> | null       | null       |
> +------------+------------+
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> select min(VAL3), max(VAL3) from TT;
> +------------+------------+
> | MIN(VAL3)  | MAX(VAL3)  |
> +------------+------------+
> | 00         | 02         |
> +------------+------------+
> 0: jdbc:phoenix:isthbase01-mnds2-1-crd> select min(VAL4), max(VAL4) from TT;
> +------------+------------+
> | MIN(VAL4)  | MAX(VAL4)  |
> +------------+------------+
> | 0          | 2          |
> +------------+------------+
> {noformat}
> As you can see, the query on VAL2 which is of type CHAR(2) returns null, while the same exact values on VAL3 which is of type VARCHAR work as expected.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)