NATURAL JOIN of more than two tables doesn't work - Mailing list pgsql-bugs

Morning Thomas,

This seems to be your turf so I'm passing it on.  The proximate cause
of the coredump is that l_name is NULL at line 505 of parse_clause.c
(the loop that's trying to match left and right column names).  This
is certain to happen at the second join, because l_name is set from
the first join node's alias field (at line 471) and that value has
not been set anywhere.

I speculate that what should be happening is for the inner invocation
of parseFromClause to set its node's alias field with the constructed
field list, but I'm unclear on how that interacts with setting
pstate->p_alias.  (For that matter, I don't understand what p_alias is
for --- seems like a parse-global field to hold aliases associated with
a particular join node is wrong by definition.)

Finally, line 483 looks awfully like a cut-and-paste typo to me;
shouldn't it look like this?

*** parse_clause.c~    Tue May 30 00:26:46 2000
--- parse_clause.c    Thu Jun  1 02:12:19 2000
***************
*** 458,464 ****
              RangeTblEntry *l_rte,
                         *r_rte;
              Attr       *l_name,
!                        *r_name = NULL;
              JoinExpr   *j = (JoinExpr *) n;

              if (j->alias != NULL)
--- 458,464 ----
              RangeTblEntry *l_rte,
                         *r_rte;
              Attr       *l_name,
!                        *r_name;
              JoinExpr   *j = (JoinExpr *) n;

              if (j->alias != NULL)
***************
*** 480,486 ****
              if (IsA(j->rarg, JoinExpr))
              {
                  parseFromClause(pstate, lcons(j->rarg, NIL));
!                 l_name = ((JoinExpr *) j->larg)->alias;
              }
              else
              {
--- 480,486 ----
              if (IsA(j->rarg, JoinExpr))
              {
                  parseFromClause(pstate, lcons(j->rarg, NIL));
!                 r_name = ((JoinExpr *) j->rarg)->alias;
              }
              else
              {

But that's not the source of this bug since that path is not entered in
this example.

Anyway, I'm afraid that this routine needs some nontrivial fixes to work
properly with more than one JoinExpr node, and I don't understand it
well enough to fix it.  So I'm punting to you...

            regards, tom lane


------- Forwarded Message

Date:    Thu, 1 Jun 2000 01:07:26 -0400 (EDT)
From:    "Alexander H. Iliev" <iliev@nimbus.dartmouth.edu>
To:      tgl@sss.pgh.pa.us (Tom Lane)
cc:      pgsql-sql@postgresql.org
Subject: Re: [SQL] question on diagnostics

> > oh, btw this select refused to use an SQL natural join among the 3
> > relations - the server gives up and disconnects without warning.
>
> That sounds like a garden-variety bug.  I'd be willing to look at it
> if I had a complete example to follow, but I don't want to try to
> reverse-engineer your table definitions...

a join with 3 tables never seems to work:

test=# create table a(a int);
CREATE
test=# create table b(a int);
CREATE
test=# create table c(a int);
CREATE
test=# insert into a values (1);
INSERT 23734 1
test=# insert into b values (1);
INSERT 23744 1
test=# insert into c values (1);
INSERT 23736 1
test=# select *
test-# from a, b, c
test-# where a.a = b.a AND
test-#       b.a = c.a;
 a | a | a
---+---+---
 1 | 1 | 1
(1 row)
test=# select * from a natural inner join b natural inner join c;
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


alex

------- End of Forwarded Message