Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

LIKE query in Phoenix

LIKE query in Phoenix

New Contributor

Getting some unexpected behavior from a simple query in Phoenix. When using a LIKE in a where clause, the rows are not found unless a wildcard(%) is added

create table t ( ID VARCHAR(290) NOT NULL PRIMARY KEY, NAME VARCHAR(256));

No rows affected (1.386 seconds) 0:

jdbc:phoenix:> upsert into t values ('1','test');

1 row affected (0.046 seconds) 0:

jdbc:phoenix:> select * from t;

+-----+-------+

| ID | NAME |

+-----+-------+

| 1 | test |

+-----+-------+

1 row selected (0.05 seconds) 0:

jdbc:phoenix:> select * from t where name like 'test';

+-----+-------+

| ID | NAME |

+-----+-------+

+-----+-------+

No rows selected (0.016 seconds) 0:

jdbc:phoenix:> select * from t where name like 'test%';

+-----+-------+

| ID | NAME |

+-----+-------+

| 1 | test |

+-----+-------+

1 row selected (0.032 seconds)

This is really unexpected behavior since 'test' is very much like 'test' and I have never seen a relational database that behaves differently. We are on Phoenix version 4.7.0.2.5.3.0. Is this addressed in a later version or is this expected in Phoenix?

Thanks

3 REPLIES 3

Re: LIKE query in Phoenix

@William Prendergast

I've checked in the latest phoenix release(5.0.0.3.0.0.0-1634) and see the same behaviour. You may want to file a jira here with these details. Phoenix community will look into it and do the needful.

Re: LIKE query in Phoenix

Super Collaborator

LIKE without wildcards isn't the same as =. When using LIKE, all characters in the pattern string are compared. This includes leading or trailing spaces. But this should only make a difference with type char (As you have actually the value 'test' right padded with spaces), not with varchar. But then Hbase doesn't have a difference between varchar and char, it only has binary or char encoding and to my understanding is more similar to varchar, so the Phoenix behaviour is strange anyway.

Highlighted

Re: LIKE query in Phoenix

yes @William Prendergast , it seems that when maxlength of the column(varchar(256)) doesn't match with the length of the literal (lenght(test)=4), predicate results to be false.

select * from t where name like 'test';

As a workaround, you can specify columns without maxlength , I have raised https://issues.apache.org/jira/browse/PHOENIX-4850 for the actual fix.

create table t ( ID VARCHAR NOT NULL PRIMARY KEY, NAME VARCHAR);
Don't have an account?
Coming from Hortonworks? Activate your account here