Hi folks
While debugging a query that was returning far fewer records than
expected, I narrowed the issue down to what I think might be a Pg bug. I
thought I'd check here in case I'm just missing something obvious before
sending anything to the bugs list.
test=> select version();
version
--------------------------------------------------------------------
PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Ubuntu 4.3.2-1ubuntu11) 4.3.2
(1 row)
I managed to simplify the original huge query down to a few simple test
statements. First:
The following query should return 2 records, but returns zero instead:
test=> select x, regexp_matches(x::text, 'm')
test-> FROM generate_series(1,2) AS x;
x | regexp_matches
---+----------------
(0 rows)
The match regexp may be anything, as may the input, so long as the
regexp does not match. If it does match, an array is output as one would
expect.
Surely regexp_matches should be returning a null or empty array, rather
than somehow suppressing the output of that record entirely if there's
no match?
Another case (closer to what I was originally facing in a much larger
query) looks like this:
test=> select x, ''||((regexp_matches(x::text, E'\\d'))[0])
test-> FROM generate_series(1,2) AS x;
x | ?column?
---+----------
(0 rows)
Yes, that's a zero index, and no the issue doesn't arise when correctly
indexing from one. Surely, though, the incorrect index should result in
an error or a null value rather than silent suppression of a record?
Also, it doesn't happen when you take the zero'th index of ANY null arry
(see below), only one from regexp_matches.
Note also that the pre-concatenation of '' is significant. Without that,
the output is:
test=> \pset null NULL
Null display is "NULL".
test=> select x, (regexp_matches(x::text, E'\\d'))[0] FROM
generate_series(1,2) AS x;
x | regexp_matches
---+----------------
1 | NULL
2 | NULL
(2 rows)
Yet other queries that also attempt to get the zeroth index of an array,
concat a null, etc all work how you'd expect:
test=> SELECT x, ''||(null::text) from generate_series(1,2) AS x;
x | ?column?
---+----------
1 | NULL
2 | NULL
(2 rows)
test=> SELECT x, ''||(('{4}'::text[])[0])
test-> FROM generate_series(1,2) AS x;
x | ?column?
---+----------
1 | NULL
2 | NULL
(2 rows)
test=> SELECT x, ''||((null::text[])[1]) from generate_series(1,2) AS x;
x | ?column?
---+----------
1 | NULL
2 | NULL
(2 rows)
I'm confused. Am I missing something obvious, or are things not working
how they should be?
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
