Re: regexp_matches bug in 9.3.4 and 9.4.1

Thanks for the quick response.

From my end of things, I would expect either a) an exception; b) 3 matches; or c) 0 matches. The current behavior (returning one match and ignoring the global flag) strikes me as being misleading and challenging to debug.

Jeff

From: David G. Johnston [mailto:david(dot)g(dot)johnston(at)gmail(dot)com]
Sent: Monday, May 4, 2015 3:37 PM
To: Jeff Certain
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] regexp_matches bug in 9.3.4 and 9.4.1

Please note...

On Mon, May 4, 2015 at 9:56 AM, Jeff Certain <gcertain(at)dynamicsignal(dot)com<mailto:gcertain(at)dynamicsignal(dot)com>> wrote:
Repro:
drop table if exists regex_test;
create table regex_test(filter citext);
insert into regex_test(filter) values ('Filter Filter Filter');
select filter, regexp_matches(filter, 'Filter', 'g') from regex_test;

​"filter" is of type "citext"​

select filter, regexp_matches('Filter Filter Filter', 'Filter', 'g') from regex_test;

​'Filter Filter Filter' is of type "text"​

Expected result:
Both select statements should return the same number of rows. Specifically, in this case, I expect to get back 3 rows.

​The following non-citext version of your first query works just fine on 9.3

​select filter, regexp_matches(filter, 'Filter', 'g') from (values ('Filter Filter Filter'::text)) regex_test (filter);

​regexp_matches is not mentioned on the following page in the documentation:

​http://www.postgresql.org/docs/9.4/interactive/citext.html

Actual result:
When referencing the filter column, only one row is ever returned. When supplying the string that is the same as the data in the filter column, the correct number of rows are returned.

Result 1 (incorrect):
select filter, regexp_matches(filter, 'Filter', 'g') from regex_test;
Filter Filter Filter {Filter}

Result 2 (correct):
select filter, regexp_matches('Filter Filter Filter', 'Filter', 'g') from regex_test;
Filter Filter Filter {Filter}
Filter Filter Filter {Filter}
Filter Filter Filter {Filter}

​This fails on 9.3 if the citext type is used instead of text.

Seems the however the citext version of regexp_matches is invoked it ignores the global flag. Haven't explored deeper.

David J.
​