REGEXP not matching properly - returns unique items regardless of regexp.

[Vince Cutting]

REGEXP in the following query returns all of the unique values in the column instead of just those that should be matched against the regexp:

select dimension as c0
from fact_table
group by dimension having UPPER(c0) REGEXP ".*SEARCHTERM.*"
order by ISNULL(dimension ) ASC, dimension ASC;

I have tested the same query against an identical MySQL databse the the query works correctly by only returning the REGEXP matched values, but returns all uniques values in Infinidb.

I've tried chopping bits from this query to identify where it falls down, and I'm fairly confident it's the REGEXP part. I've tried replacing the REGEXP with a LIKE("%SEARCHTERM%") and this works correctly but much slower than REGEXP.

I'm running Calpont InfiniDB Community 2.2.6 Final (COSS LA) on a Windows 2008 R2 Server with 36GB of RAM on a 6-core/12-thread Xeon X5650, with Pentaho installed on the same server.

NOTE: I've also posted this in the forums at http://infinidb.org/forum/10-data-warehousing-and-analytics/2922-regexp-on-column-not-working

[Robert Adams (radams-calpont)]

Hi,

REGEXP should work. We have opened a bug and are investigating the issue.

Thank you,

Robert
Calpont

[Robert Adams (radams-calpont)]

Fix has been released in the 2.2.8 package.