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

=E2=80=8B"filter" is of type "citext"=E2=80=8B

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

=E2=80=8B'Filter Filter Filter' is of type "text"=E2=80=8B

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

=E2=80=8BThe following non-citext version of your first query works just fi=
ne on 9.3

=E2=80=8Bselect filter, regexp_matches(filter, 'Filter', 'g') from (values =
('Filter
Filter Filter'::text)) regex_test (filter);

=E2=80=8Bregexp_matches is not mentioned on the following page in the docum=
entation:

=E2=80=8Bhttp://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, t=
he
> 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}
>

=E2=80=8BThis 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.
=E2=80=8B