Thread: Odd array issue

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

Craig Ringer <craig@postnewspapers.com.au> writes:
> 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)

No, that's correct.  The SRF returns an empty set, so there are no
output records.  The behavior you seem to be imagining would make it
impossible to distinguish empty set from a single row containing NULL.

            regards, tom lane

Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
>> 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)
>
> No, that's correct.  The SRF returns an empty set, so there are no
> output records.  The behavior you seem to be imagining would make it
> impossible to distinguish empty set from a single row containing NULL.

Aah, that makes sense. For some reason I'd been thinking of
regexp_matches as returning a single array, rather than returning a set
of scalar values. RTFM (better) I guess. Sorry.

Thanks for taking a look, it's appreciated.

--
Craig Ringer