Hi folks,
I am not sure if I it is by design, or it is a [known] bug in
phoenix-4.7.0-HBase-1.1.
It looks like when I create a table with salt_buckets and primary key desc, the
where clauses are not behaving as expected.
The behaviour is normal in case that I drop the salt buckets or "desc" in
primary key.
Would you please assist?
The followings are the queries that I am using:
I am creating a simple table like this:
Create table TEST.TEST (
id VARCHAR NOT NULL PRIMARY KEY DESC
)
SALT_BUCKETS = 60;
And insert two records like these:
UPSERT INTO TEST.TEST VALUES ('1487108338648-SAMPLE1');
UPSERT INTO TEST.TEST VALUES ('1487108338648-SAMPLE2');
When I am trying to select using ID, very strange behaviour will happen:
1. Select * behave as expected:
0: jdbc:phoenix:localhost> SELECT * FROM TEST.TEST;
+------------------------+
| ID |
+------------------------+
| 1487108338648-SAMPLE1 |
| 1487108338648-SAMPLE2 |
+------------------------+
2 rows selected (0.071 seconds)
2. Select with = in where clause does not return any records:
0: jdbc:phoenix:localhost> SELECT * FROM TEST.TEST where id =
'1487108338648-SAMPLE1';
+-----+
| ID |
+-----+
+-----+
No rows selected (0.013 seconds)
3. Select with "in" for a single value doesn't return any records:
0: jdbc:phoenix:localhost> SELECT * FROM TEST.TEST where id in
('1487108338648-SAMPLE1');
+-----+
| ID |
+-----+
+-----+
No rows selected (0.015 seconds)
4. Select with "in" for a multiple values return as expected
0: jdbc:phoenix:localhost> SELECT * FROM TEST.TEST where id in
('1487108338648-SAMPLE1', '1487108338648-SAMPLE2');
+------------------------+
| ID |
+------------------------+
| 1487108338648-SAMPLE1 |
| 1487108338648-SAMPLE2 |
+------------------------+
2 rows selected (0.05 seconds)
5. Select with "like" returns as expected:
0: jdbc:phoenix:localhost> SELECT * FROM TEST.TEST where id like
'%1487108338648-SAMPLE1';
+------------------------+
| ID |
+------------------------+
| 1487108338648-SAMPLE1 |
+------------------------+
1 row selected (0.068 seconds)
0: jdbc:phoenix:localhost> SELECT * FROM TEST.TEST where id like
'1487108338648-SAMPLE1%';
+------------------------+
| ID |
+------------------------+
| 1487108338648-SAMPLE1 |
+------------------------+
1 row selected (0.058 seconds)
Best,
Afshin
