The following bug has been logged on the website:
Bug reference: 15007
Logged by: Will Storey
Email address: will@summercat.com
PostgreSQL version: 10.1
Operating system: Ubuntu 16.04
Description:
Hello,
I am not sure this is a bug. But it is surprising to me and seems to
contradict the documentation in terms of join nesting.
I have a SELECT query with a sub-SELECT in it. The sub-SELECT has a LIMIT
clause. I've found that sometimes I receive more rows (at most one extra in
my testing) than the LIMIT, where I expected only as many rows as the LIMIT.
This depends on the query plan. With some plans it never happens, and with
others it happens frequently.
In looking into this behaviour, I came across hints that this is a known
quirk. I found bug reports related specifically to UPDATE/DELETE that sound
similar to this, but no mention that the behaviour can happen with SELECT:
https://dba.stackexchange.com/questions/69471/postgres-update-limit-1?noredirect=1&lq=1
(note the comments on the accepted answer)
https://www.postgresql.org/message-id/1399649764731-5803406.post%40n5.nabble.com
(and the thread)
https://www.postgresql.org/message-id/1385918761589-5781081.post%40n5.nabble.com
This happens with both PostgreSQL 10.1 on Ubuntu 16.04 (from the PostgreSQL
repos: PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit) as well as on PostgreSQL
9.6.5 (where I initially encountered the behaviour).
Unfortunately my test case is not very clean and it is somewhat long, so
I've put it in a gist on GitHub:
https://gist.github.com/horgh/f3e8ede81d866844e7d162d677968bf0
The SELECT query (run by the Perl program) quickly prints out that it
receives 6 rows.
As you can see in the EXPLAIN ANALYZE output, the innermost Nested Loop has
loops > 1. I believe this is the cause of the behaviour. If I tweak the test
to have a plan where that node runs before the Seq Scan, there are never
more than 5 rows.
I believe a better way to write this query would be to use a CTE.
Thank you for your time!