The following bug has been logged online:

Bug reference: 4070
Logged by: Marcello Ceschia
Email address: Marcello.Ceschia@medizin.uni-leipzig.de
PostgreSQL version: 8.2.5 and 8.3.0
Operating system: Windows XP
Description: Join more then ~15 tables let postgreSQL produces wrong
data
Details:

We have an situation where we must join ~30 tables to get a general table.
Don't ask why we do this this way.

After an unknown number of columns the joind values are not correct. We get
only the value from column 1.
If I separete the query and generate an temporary table with the first 20
tables and join them later with the rest one, all works fine.
All values in the result table are correct.

If you need more information, contact me I can send some example data.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Marcello Ceschia wrote:
> If you need more information, contact me I can send some example data.

Yes, we need more information. Please send a minimal test case with
CREATE TABLE statements and data required to reproduce the problem.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Ceschia, Marcello wrote:
> Can I send a backup with some data?

pg_dump output will do just fine, but please try to reduce the test case
to a simpler one. And send the query that's not behaving as expected as
well, and describe what output you expected.

And please keep the mailing list CC'd so that others can help.

>
> -----Ursprüngliche Nachricht-----
> Von: Heikki Linnakangas [mailto:hlinnaka@gmail.com] Im Auftrag von Heikki Linnakangas
> Gesendet: Montag, 31. März 2008 14:58
> An: Ceschia, Marcello
> Cc: pgsql-bugs@postgresql.org
> Betreff: Re: [BUGS] BUG #4070: Join more then ~15 tables let postgreSQL produces wrong data
>
> Marcello Ceschia wrote:
>> If you need more information, contact me I can send some example data.
>
> Yes, we need more information. Please send a minimal test case with
> CREATE TABLE statements and data required to reproduce the problem.
>


--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> writes:
>> On second thought, expanding AttrNumber to int32, wholesale, might not
>> be a good idea,
>
> No, it wouldn't. For one thing it'd be a protocol break --- column
> numbers are int16 ---

I wasn't planning to change that.

> and for another, we'd have terrible performance
> problems with such wide rows.

Yes, we probably would :-). Though if there's any nasty O(n^2) behavior
left in there, we should look at optimizing it anyway to speed up more
reasonably sized queries, in the range of a few hundred columns.

> Actually rows are supposed to be limited
> to ~1600 columns, anyway, because of HeapTupleHeader limitations.

The trick is that that limitation doesn't apply to the intermediate
virtual tuples we move around in the executor. Those are just arrays of
Datums, and can have more than MaxTupleAttributeNumber attributes, as
long as you project away enough attributes, bringing it below that
limit, before returning it to the client or materializing it into a
HeapTuple or MinimalTuple in the executor.

> Apparently you've found a path where that restriction isn't enforced
> correctly, but I haven't seen the referenced message yet ...

Enforcing the limit for virtual tuples as well, and checking for the
limit in the planner is one option, but it would cripple the ability to
join extremely wide tables. For example, if you had 10 tables with 200
columns each, you couldn't join them together even for the purposes of
COUNT(*). Granted, that's not a very common thing to do, this is the
first time this bug is reported after all, but I'd prefer to keep the
capability if possible.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tom Lane wrote:

> I still haven't seen the actual bug description come by here, and the
> pgsql-bugs archive hasn't got it either.

http://archives.postgresql.org/pgsql...3/msg00351.php

and continues in April here

http://archives.postgresql.org/pgsql...4/msg00031.php

(apparently some mails on that thread are missing ...)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Donnerstag, 3. April 2008 21:33
An: Heikki Linnakangas
Cc: Alvaro Herrera; pgsql-patches; Ceschia, Marcello; PostgreSQL Bugs
Betreff: Re: [PATCHES] Re: [BUGS] BUG #4070: Join more then ~15 tables let postgreSQL produces wrong data


> What I propose we do is throw error for the moment, and make a TODO
> note to revisit the question after redesigning outer-join planning.
> Which is something I do intend to do for 8.4.


For me that's a good solution.
My motivation for reporting this bug was to notice the developer about the problem.

Of course the query we did is a strange way to get a result, but it was possible without warning/error. At the moment we use a function to join the tables one by one and for the moment it is working.

Thank you for your helps

Marcello

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs