You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Hari Krishna Dara (JIRA)" <ji...@apache.org> on 2014/10/17 09:00:51 UTC
[jira] [Updated] (PHOENIX-1362) Min/max query on CHAR columns
containing values with '0' as prefix always returns null
[ https://issues.apache.org/jira/browse/PHOENIX-1362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hari Krishna Dara updated PHOENIX-1362:
---------------------------------------
Description:
- 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.
was:
- 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.
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 |
+------------+------------+
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.
> 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)