Re: 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>
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.
​