Joins on over 10 tables returns unreliable results
Jeff Seaman (jeffery.l.seaman@intel.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Joins on over 10 tables returns unreliable results
Long Description
Running Postgres version 7.01 on Redhat linux 6.2. When joining on more than 10 tables not all of the expected results are returned. The query can be repeated several times, with different result sets when the data itself is static. The most common result is the return of only one record. 11 tables seems to be the magic limit. Joins on 10 tables works without a hitch. This join was based on a column id that exists in all of the tables.
Sample Code
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
Running Postgres version 7.01 on Redhat linux 6.2. When joining on
more than 10 tables not all of the expected results are returned.
The query can be repeated several times, with different result sets
when the data itself is static. The most common result is the return
of only one record. 11 tables seems to be the magic limit. Joins on
10 tables works without a hitch. This join was based on a column id
that exists in all of the tables.
This seems to be a problem in the genetic query optimizer. But to examine
closer we'd really need to get sample data and queries to reproduce this.
For now you can SET geqo TO 'off'; to disable this.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
pgsql-bugs@postgresql.org writes:
Running Postgres version 7.01 on Redhat linux 6.2. When joining on
more than 10 tables not all of the expected results are returned.
Do you get a query plan involving mergejoins for these queries?
If so it's probably fixed for 7.0.3, please try that as soon as
it's out...
regards, tom lane