• Tom Lane's avatar
    Fix mishandling of equivalence-class tests in parameterized plans. · 207d5a65
    Tom Lane authored
    Given a three-or-more-way equivalence class, such as X.Y = Y.Y = Z.Z,
    it was possible for the planner to omit one of the quals needed to
    enforce that all members of the equivalence class are actually equal.
    This only happened in the case of a parameterized join node for two
    of the relations, that is a plan tree like
    
    	Nested Loop
    	  ->  Scan X
    	  ->  Nested Loop
    	    ->  Scan Y
    	    ->  Scan Z
    	          Filter: Z.Z = X.X
    
    The eclass machinery normally expects to apply X.X = Y.Y when those
    two relations are joined, but in this shape of plan tree they aren't
    joined until the top node --- and, if the lower nested loop is marked
    as parameterized by X, the top node will assume that the relevant eclass
    condition(s) got pushed down into the lower node.  On the other hand,
    the scan of Z assumes that it's only responsible for constraining Z.Z
    to match any one of the other eclass members.  So one or another of
    the required quals sometimes fell between the cracks, depending on
    whether consideration of the eclass in get_joinrel_parampathinfo()
    for the lower nested loop chanced to generate X.X = Y.Y or X.X = Z.Z
    as the appropriate constraint there.  If it generated the latter,
    it'd erroneously suppose that the Z scan would take care of matters.
    To fix, force X.X = Y.Y to be generated and applied at that join node
    when this case occurs.
    
    This is *extremely* hard to hit in practice, because various planner
    behaviors conspire to mask the problem; starting with the fact that the
    planner doesn't really like to generate a parameterized plan of the
    above shape.  (It might have been impossible to hit it before we
    tweaked things to allow this plan shape for star-schema cases.)  Many
    thanks to Alexander Kirkouski for submitting a reproducible test case.
    
    The bug can be demonstrated in all branches back to 9.2 where parameterized
    paths were introduced, so back-patch that far.
    207d5a65
join.sql 49.1 KB