Re: IN joining

Tom Lane wrote:

>Dennis Haney <davh(at)diku(dot)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