LIKE query in Phoenix

[#1]

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

[#2]

@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.

[#3]

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.

[#4]

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);