Re: IN joining - Mailing list pgsql-hackers

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"><davh@diku.dk></a> writes: </pre><blockquote
type="cite"><blockquotetype="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,
bwhere a.id = b.id3 and a.id in (select c.id2 from c);<br /><br /> rtable is (after in-optimization):<br /> resno  
refname        relid   inFromCl<br /> -----   ---------       -----   --------<br /> 1       a     17143          
inFromCl<br/> 2       b     17151           inFromCl<br /> 3       IN_subquery     [subquery]<br /> 4       c    
17147          inFromCl<br /><br /> in gdb:<br /> break joinrels.c:563<br /> commands<br /> call
bms_is_subset(ininfo->lefthand,rel1->relids)<br /> call bms_equal(ininfo->righthand, rel2->relids)<br />
callbms_is_subset(ininfo->lefthand, rel2->relids)<br /> call bms_equal(ininfo->righthand, rel1->relids)<br
/>x/t rel1->relids.words<br /> x/t rel2->relids.words<br /> x/t joinrelids.words<br /> p jointype<br /> printf
"%s\n",pretty_format_node_dump(nodeToString(((RestrictInfo*)((RestrictInfo*)restrictlist)->clause)->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 />     at joinrels.c:563<br /> 563             switch (jointype)<br />
$92= 0 '\0'<br /> $93 = 1 '\001'<br /> $94 = 0 '\0'<br /> $95 = 0 '\0'<br /> 0x83169ac:     
00000000000000000000000000000100<br/> 0x8316b9c:      00000000000000000000000000010000<br /> 0x832670c:     
00000000000000000000000000010100<br/> $96 = JOIN_UNIQUE_INNER<br />    {OPEXPR<br />    :opno 96<br />    :opfuncid
0<br/>    :opresulttype 16<br />    :opretset false<br />    :args (<br />       {VAR<br />       :varno 4<br />      
:varattno1<br />       :vartype 23<br />       :vartypmod -1<br />       :varlevelsup 0<br />       :varnoold 4<br />
     :varoattno 1<br />       }<br /><br />       {VAR<br />       :varno 2<br />       :varattno 1<br />      
:vartype23<br />       :vartypmod -1<br />       :varlevelsup 0<br />       :varnoold 2<br />       :varoattno 1<br />
     }<br />    )<br />    }<br /><br /><br /><pre class="moz-signature" cols="72">-- 
 
Dennis
</pre>