Thread: BUG #11211: regexp_matches acts like a WHERE

The following bug has been logged on the website:

Bug reference:      11211
Logged by:          Eduard Wulff
Email address:      mail@eduard-wulff.de
PostgreSQL version: 9.4beta2
Operating system:   Debian testing AMD64
Description:

 SELECT vorgangt.id,
  (regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
    regexp_replace(vorgangt.text, '\n', '⁋', 'g') AS text_in_einer_zeile
 FROM vorgangt ORDER BY 1

This works to get the first match if there is one.

BUT: it also _eliminates_ all rows that do not match from the result set

EXPECTED: return NULL or at least an empty string if there is no match

mail@eduard-wulff.de writes:
>  SELECT vorgangt.id,
>   (regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
>     regexp_replace(vorgangt.text, '\n', '⁋', 'g') AS text_in_einer_zeile
>  FROM vorgangt ORDER BY 1

> This works to get the first match if there is one.

> BUT: it also _eliminates_ all rows that do not match from the result set

I see no bug here.  You've got a set-returning function in the target
list, and when it returns zero rows, you get zero rows (from that source
row).  You seem to wish it would return a scalar NULL for no match,
but that's not how the function is defined.

You could do it like this instead:

 SELECT vorgangt.id,
  (SELECT regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
    regexp_replace(vorgangt.text, '\n', '⁋', 'g') AS text_in_einer_zeile
 FROM vorgangt ORDER BY 1

Or you could wrap regexp_matches in a non-set-returning function.

            regards, tom lane

Thanks Tom,

your sql worked as I intended it - now I even read it in the documentat=
ion=20
(9.7.3) as a "Tip". I expected a scalar NULL as you wrote.

I did not grok the difference(?) between array and set. I even wondered=
 about=20
my "syntax-solution" ()[n].

I am not the only one being on the wrong road:
http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHE=
RE-clause-td5733684.html

Regards,

Eduard


Am Dienstag, 19. August 2014, 18:20:03 schrieb Tom Lane:
> mail@eduard-wulff.de writes:
> >  SELECT vorgangt.id,
> > =20
> >   (regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
> >  =20
> >     regexp_replace(vorgangt.text, '\n', '=E2=81=8B', 'g') AS text_i=
n_einer_zeile
> > =20
> >  FROM vorgangt ORDER BY 1
> >=20
> > This works to get the first match if there is one.
> >=20
> > BUT: it also _eliminates_ all rows that do not match from the resul=
t set
>=20
> I see no bug here.  You've got a set-returning function in the target=

> list, and when it returns zero rows, you get zero rows (from that sou=
rce
> row).  You seem to wish it would return a scalar NULL for no match,
> but that's not how the function is defined.
>=20
> You could do it like this instead:
>=20
>  SELECT vorgangt.id,
>   (SELECT regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sern=
r,
>     regexp_replace(vorgangt.text, '\n', '=E2=81=8B', 'g') AS text_in_=
einer_zeile
>  FROM vorgangt ORDER BY 1
>=20
> Or you could wrap regexp_matches in a non-set-returning function.
>=20
> =09=09=09regards, tom lane