I was unable to repro this behavior with phoenix 4.9, maybe you can try
using a later version of phoenix?
On Mon, Feb 20, 2017 at 9:17 AM, Afshin Moazami <afshin.moaz...@guavus.com>
wrote:
> 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
>
>
