BUG #7550: NULL result when coercing a subquery result into an array
The following bug has been logged on the website:
Bug reference: 7550
Logged by: Tom Forbes
Email address: tom@tomforb.es
PostgreSQL version: 9.2.0
Operating system: Windows 8
Description:
Hello,
I wasn't sure how to title this report so apologies if it is incorrect or
misleading.
I have two queries:
http://pgsql.privatepaste.com/7d1473defa
http://pgsql.privatepaste.com/85e1d43b7a
The first query returns NULL and the second one returns an int[] with 623
elements in it. The only difference between the two queries is the last
string - 'J. P. Bickella' returns NULL and 'J. P. Bickell' returns an
int[].
The base query with no values can be found here:
http://pgsql.privatepaste.com/1ebe2d7646
And the EXPLAIN ANALYZE VERBOSE of the queries can be found here:
http://pgsql.privatepaste.com/a4b8b622c5 <-- Returns int[]
http://pgsql.privatepaste.com/525fda6340 <-- Returns NULL
In summary: Changing the last (or second to last) element of the second IN
clause causes the query to return an array as expected, whereas setting it
to 'J. P. Bickella' causes it to return NULL.
Executing the query without wrapping it in an array() returns the expected
results.
If needed I can host the database this query is executing on for people to
download upon request, but it is 1.2gb in size.
tom@tomforb.es writes:
I have two queries:
http://pgsql.privatepaste.com/7d1473defa
http://pgsql.privatepaste.com/85e1d43b7a
The first query returns NULL and the second one returns an int[] with 623
elements in it. The only difference between the two queries is the last
string - 'J. P. Bickella' returns NULL and 'J. P. Bickell' returns an
int[].
That's ... bizarre. I assume you were running this same query without
issues on earlier PG versions? Which?
If needed I can host the database this query is executing on for people to
download upon request, but it is 1.2gb in size.
I suspect that you could reproduce the problem with a much smaller
extract from the table, perhaps a couple thousand rows. Please try
to create a self-contained test case along those lines --- it'll be
easier all around than dealing with 1GB of data.
regards, tom lane
Ok, I will try. I apologize, I made a mistake in the original post:
'J. P. Bickella' returns the correct array and 'J. P. Bickell' returns
NULL. That's what you get for posting in a hurry!
This is the first time I have run this query, so I don't know if it
affects previous versions.
~Tom
Show quoted text
On Mon, Sep 17, 2012 at 9:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
tom@tomforb.es writes:
I have two queries:
http://pgsql.privatepaste.com/7d1473defa
http://pgsql.privatepaste.com/85e1d43b7aThe first query returns NULL and the second one returns an int[] with 623
elements in it. The only difference between the two queries is the last
string - 'J. P. Bickella' returns NULL and 'J. P. Bickell' returns an
int[].That's ... bizarre. I assume you were running this same query without
issues on earlier PG versions? Which?If needed I can host the database this query is executing on for people to
download upon request, but it is 1.2gb in size.I suspect that you could reproduce the problem with a much smaller
extract from the table, perhaps a couple thousand rows. Please try
to create a self-contained test case along those lines --- it'll be
easier all around than dealing with 1GB of data.regards, tom lane
I can't seem to reproduce this on a Ubuntu box on 9.1 or 9.2, nor can
I seem to reproduce this with less than ~8 million rows on a windows
one. I can reliably reproduce this with that many rows and the
database dump is only ~120mb so if anyone is interested I can host it
for them, otherwise I guess its not that serious or interesting.
Out of interest is there a debug build I can install or any steps I
can take to investigate this? I'm just pretty curious as to why this
would be occurring.
~Tom
Show quoted text
On Mon, Sep 17, 2012 at 9:44 PM, tom Tom <tom@tomforb.es> wrote:
Ok, I will try. I apologize, I made a mistake in the original post:
'J. P. Bickella' returns the correct array and 'J. P. Bickell' returns
NULL. That's what you get for posting in a hurry!This is the first time I have run this query, so I don't know if it
affects previous versions.~Tom
On Mon, Sep 17, 2012 at 9:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
tom@tomforb.es writes:
I have two queries:
http://pgsql.privatepaste.com/7d1473defa
http://pgsql.privatepaste.com/85e1d43b7aThe first query returns NULL and the second one returns an int[] with 623
elements in it. The only difference between the two queries is the last
string - 'J. P. Bickella' returns NULL and 'J. P. Bickell' returns an
int[].That's ... bizarre. I assume you were running this same query without
issues on earlier PG versions? Which?If needed I can host the database this query is executing on for people to
download upon request, but it is 1.2gb in size.I suspect that you could reproduce the problem with a much smaller
extract from the table, perhaps a couple thousand rows. Please try
to create a self-contained test case along those lines --- it'll be
easier all around than dealing with 1GB of data.regards, tom lane
tom Tom <tom@tomforb.es> writes:
I can't seem to reproduce this on a Ubuntu box on 9.1 or 9.2, nor can
I seem to reproduce this with less than ~8 million rows on a windows
one. I can reliably reproduce this with that many rows and the
database dump is only ~120mb so if anyone is interested I can host it
for them, otherwise I guess its not that serious or interesting.
Well, at least that's 10x better than 1GB. I'll take a look if you'll
put the dump somewhere I can grab it.
Out of interest is there a debug build I can install or any steps I
can take to investigate this? I'm just pretty curious as to why this
would be occurring.
Me too, but it'll likely take a lot less time for someone who's familiar
with the code to find it. We have only another day to get a fix into
9.2.1, so I don't want to delay.
regards, tom lane