X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id D54EFD1DB94
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Mar 2004 14:03:43 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 03672-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Mar 2004 10:03:45 -0400 (AST)
Received: from amigos44.diku.dk (amigos44.diku.dk [192.38.109.174])
	by svr1.postgresql.org (Postfix) with ESMTP id 5B0FFD1DB81
	for <pgsql-hackers@postgresql.org>;
	Mon,  8 Mar 2004 10:03:41 -0400 (AST)
Received: from [127.0.0.1] (helo=diku.dk ident=davh)
	by amigos44.diku.dk with esmtp (Exim 3.36 #1 (Debian))
	id 1B0LM5-0005yX-00; Mon, 08 Mar 2004 15:03:41 +0100
Message-ID: <404C7D3C.50908@diku.dk>
Date: Mon, 08 Mar 2004 15:03:40 +0100
From: Dennis Haney <davh@diku.dk>
Organization: Datalogisk Institut =?ISO-8859-1?Q?K=F8benhavns_Universit?=
	=?ISO-8859-1?Q?et?=
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.6) Gecko/20040122 Debian/1.6-1
X-Accept-Language: da
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: IN joining
References: <4048B8D6.70401@diku.dk> <11205.1078520699@sss.pgh.pa.us>
	<4048F1D3.20405@diku.dk> <12170.1078528135@sss.pgh.pa.us>
In-Reply-To: <12170.1078528135@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------030602080002000207030703"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/251
X-Sequence-Number: 50843

This is a multi-part message in MIME format.
--------------030602080002000207030703
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

>Dennis Haney <davh@diku.dk> writes:
>  
>
>>>Joining {b,c} to {a} does not meet any of those four allowed cases.
>>>
>>>      
>>>
>>Exactly my point... So why ever bother creating the {b,c} node which is 
>>legal by the above definition?
>>    
>>
>
>We don't, because there is no such join clause.
>
>  
>
No, but we create the equality via the implied equality mechanism...

select * from a, b where a.id = b.id3 and a.id in (select c.id2 from c);

rtable is (after in-optimization):
resno   refname         relid   inFromCl
-----   ---------       -----   --------
1       a     17143           inFromCl
2       b     17151           inFromCl
3       IN_subquery     [subquery]
4       c     17147           inFromCl

in gdb:
break joinrels.c:563
commands
call bms_is_subset(ininfo->lefthand, rel1->relids)
call bms_equal(ininfo->righthand, rel2->relids)
call bms_is_subset(ininfo->lefthand, rel2->relids)
call bms_equal(ininfo->righthand, rel1->relids)
x/t rel1->relids.words
x/t rel2->relids.words
x/t joinrelids.words
p jointype
printf "%s\n", 
pretty_format_node_dump(nodeToString(((RestrictInfo*)((RestrictInfo*)restrictlist)->clause)->clause))
end

then we get this join:

Breakpoint 4, make_join_rel (root=0x8307bc8, rel1=0x8316920, 
rel2=0x8316b10, jointype=JOIN_UNIQUE_INNER)
    at joinrels.c:563
563             switch (jointype)
$92 = 0 '\0'
$93 = 1 '\001'
$94 = 0 '\0'
$95 = 0 '\0'
0x83169ac:      00000000000000000000000000000100
0x8316b9c:      00000000000000000000000000010000
0x832670c:      00000000000000000000000000010100
$96 = JOIN_UNIQUE_INNER
   {OPEXPR
   :opno 96
   :opfuncid 0
   :opresulttype 16
   :opretset false
   :args (
      {VAR
      :varno 4
      :varattno 1
      :vartype 23
      :vartypmod -1
      :varlevelsup 0
      :varnoold 4
      :varoattno 1
      }

      {VAR
      :varno 2
      :varattno 1
      :vartype 23
      :vartypmod -1
      :varlevelsup 0
      :varnoold 2
      :varoattno 1
      }
   )
   }


-- 
Dennis


--------------030602080002000207030703
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid12170.1078528135@sss.pgh.pa.us" type="cite">
  <pre wrap="">Dennis Haney <a class="moz-txt-link-rfc2396E" href="mailto:davh@diku.dk">&lt;davh@diku.dk&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">Joining {b,c} to {a} does not meet any of those four allowed cases.

      </pre>
    </blockquote>
    <pre wrap="">Exactly my point... So why ever bother creating the {b,c} node which is 
legal by the above definition?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
We don't, because there is no such join clause.

  </pre>
</blockquote>
No, but we create the equality via the implied equality mechanism...<br>
<br>
select * from a, b where a.id = b.id3 and a.id in (select c.id2 from c);<br>
<br>
rtable is (after in-optimization):<br>
resno&nbsp;&nbsp; refname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; relid&nbsp;&nbsp; inFromCl<br>
-----&nbsp;&nbsp; ---------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -----&nbsp;&nbsp; --------<br>
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp; &nbsp; 17143&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inFromCl<br>
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp; &nbsp; 17151&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inFromCl<br>
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IN_subquery&nbsp;&nbsp;&nbsp;&nbsp; [subquery]<br>
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c&nbsp;&nbsp; &nbsp; 17147&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inFromCl<br>
<br>
in gdb:<br>
break joinrels.c:563<br>
commands<br>
call bms_is_subset(ininfo-&gt;lefthand, rel1-&gt;relids)<br>
call bms_equal(ininfo-&gt;righthand, rel2-&gt;relids)<br>
call bms_is_subset(ininfo-&gt;lefthand, rel2-&gt;relids)<br>
call bms_equal(ininfo-&gt;righthand, rel1-&gt;relids)<br>
x/t rel1-&gt;relids.words<br>
x/t rel2-&gt;relids.words<br>
x/t joinrelids.words<br>
p jointype<br>
printf "%s\n",
pretty_format_node_dump(nodeToString(((RestrictInfo*)((RestrictInfo*)restrictlist)-&gt;clause)-&gt;clause))<br>
end<br>
<br>
then we get this join:<br>
<br>
Breakpoint 4, make_join_rel (root=0x8307bc8, rel1=0x8316920,
rel2=0x8316b10, jointype=JOIN_UNIQUE_INNER)<br>
&nbsp;&nbsp;&nbsp; at joinrels.c:563<br>
563&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; switch (jointype)<br>
$92 = 0 '\0'<br>
$93 = 1 '\001'<br>
$94 = 0 '\0'<br>
$95 = 0 '\0'<br>
0x83169ac:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00000000000000000000000000000100<br>
0x8316b9c:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00000000000000000000000000010000<br>
0x832670c:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00000000000000000000000000010100<br>
$96 = JOIN_UNIQUE_INNER<br>
&nbsp;&nbsp; {OPEXPR<br>
&nbsp;&nbsp; :opno 96<br>
&nbsp;&nbsp; :opfuncid 0<br>
&nbsp;&nbsp; :opresulttype 16<br>
&nbsp;&nbsp; :opretset false<br>
&nbsp;&nbsp; :args (<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {VAR<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varno 4<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varattno 1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :vartype 23<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :vartypmod -1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varlevelsup 0<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varnoold 4<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varoattno 1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {VAR<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varno 2<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varattno 1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :vartype 23<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :vartypmod -1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varlevelsup 0<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varnoold 2<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :varoattno 1<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp; )<br>
&nbsp;&nbsp; }<br>
<br>
<br>
<pre class="moz-signature" cols="72">-- 
Dennis
</pre>
</body>
</html>

--------------030602080002000207030703--