def test_annotate_fromlist_preservation(self):
        """test the FROM list in select still works
        even when multiple annotate runs have created
        copies of the same selectable

        #2453, continued

        """
        table1 = table('table1', column('x'))
        table2 = table('table2', column('y'))
        a1 = table1.alias()
        s = select([a1.c.x]).select_from(
                a1.join(table2, a1.c.x==table2.c.y)
            )

        assert_s = select([select([s])])
        for fn in (
            sql_util._deep_deannotate,
            lambda s: sql_util._deep_annotate(s, {'foo':'bar'}),
            lambda s:visitors.cloned_traverse(s, {}, {}),
            lambda s:visitors.replacement_traverse(s, {}, lambda x:None)
        ):

            sel = fn(select([fn(select([fn(s)]))]))
            eq_(str(assert_s), str(sel))

    def lazy_clause(self, state, reverse_direction=False, alias_secondary=False, adapt_source=None):
        if state is None:
            return self._lazy_none_clause(reverse_direction, adapt_source=adapt_source)
            
        if not reverse_direction:
            (criterion, bind_to_col, rev) = (self.__lazywhere, self.__bind_to_col, self._equated_columns)
        else:
            (criterion, bind_to_col, rev) = LazyLoader._create_lazy_clause(self.parent_property, reverse_direction=reverse_direction)

        if reverse_direction:
            mapper = self.parent_property.mapper
        else:
            mapper = self.parent_property.parent

        def visit_bindparam(bindparam):
            if bindparam.key in bind_to_col:
                # use the "committed" (database) version to get query column values
                # also its a deferred value; so that when used by Query, the committed value is used
                # after an autoflush occurs
                o = state.obj() # strong ref
                bindparam.value = lambda: mapper._get_committed_attr_by_column(o, bind_to_col[bindparam.key])

        if self.parent_property.secondary is not None and alias_secondary:
            criterion = sql_util.ClauseAdapter(self.parent_property.secondary.alias()).traverse(criterion)

        criterion = visitors.cloned_traverse(criterion, {}, {'bindparam':visit_bindparam})
        if adapt_source:
            criterion = adapt_source(criterion)
        return criterion

    def test_annotate_unique_traversal(self):
        """test that items are copied only once during
        annotate, deannotate traversal

        #2453
        """
        table1 = table('table1', column('x'))
        table2 = table('table2', column('y'))
        a1 = table1.alias()
        s = select([a1.c.x]).select_from(
                a1.join(table2, a1.c.x==table2.c.y)
            )

        for sel in (
            sql_util._deep_deannotate(s),
            sql_util._deep_annotate(s, {'foo':'bar'}),
            visitors.cloned_traverse(s, {}, {}),
            visitors.replacement_traverse(s, {}, lambda x:None)
        ):
            # the columns clause isn't changed at all
            assert sel._raw_columns[0].table is a1
            # the from objects are internally consistent,
            # i.e. the Alias at position 0 is the same
            # Alias in the Join object in position 1
            assert sel._froms[0] is sel._froms[1].left
            eq_(str(s), str(sel))

    def _lazy_none_clause(self, reverse_direction=False, adapt_source=None):
        if not reverse_direction:
            (criterion, bind_to_col, rev) = (self.__lazywhere, self.__bind_to_col, self._equated_columns)
        else:
            (criterion, bind_to_col, rev) = LazyLoader._create_lazy_clause(
                self.parent_property, reverse_direction=reverse_direction
            )

        def visit_binary(binary):
            mapper = reverse_direction and self.parent_property.mapper or self.parent_property.parent
            if isinstance(binary.left, expression._BindParamClause) and binary.left.key in bind_to_col:
                # reverse order if the NULL is on the left side
                binary.left = binary.right
                binary.right = expression.null()
                binary.operator = operators.is_
                binary.negate = operators.isnot
            elif isinstance(binary.right, expression._BindParamClause) and binary.right.key in bind_to_col:
                binary.right = expression.null()
                binary.operator = operators.is_
                binary.negate = operators.isnot

        criterion = visitors.cloned_traverse(criterion, {}, {"binary": visit_binary})
        if adapt_source:
            criterion = adapt_source(criterion)
        return criterion

    def lazy_clause(self, state, reverse_direction=False, 
                                alias_secondary=False, 
                                adapt_source=None):
        if state is None:
            return self._lazy_none_clause(
                                        reverse_direction, 
                                        adapt_source=adapt_source)

        if not reverse_direction:
            criterion, bind_to_col, rev = \
                                            self.__lazywhere, \
                                            self.__bind_to_col, \
                                            self._equated_columns
        else:
            criterion, bind_to_col, rev = \
                                LazyLoader._create_lazy_clause(
                                        self.parent_property,
                                        reverse_direction=reverse_direction)

        if reverse_direction:
            mapper = self.parent_property.mapper
        else:
            mapper = self.parent_property.parent

        o = state.obj() # strong ref
        dict_ = attributes.instance_dict(o)

        # use the "committed state" only if we're in a flush
        # for this state.

        sess = sessionlib._state_session(state)
        if sess is not None and sess._flushing:
            def visit_bindparam(bindparam):
                if bindparam.key in bind_to_col:
                    bindparam.callable = \
                                lambda: mapper._get_committed_state_attr_by_column(
                                        state, dict_, bind_to_col[bindparam.key])
        else:
            def visit_bindparam(bindparam):
                if bindparam.key in bind_to_col:
                    bindparam.callable = lambda: mapper._get_state_attr_by_column(
                                            state, dict_, bind_to_col[bindparam.key])


        if self.parent_property.secondary is not None and alias_secondary:
            criterion = sql_util.ClauseAdapter(
                                self.parent_property.secondary.alias()).\
                                traverse(criterion)

        criterion = visitors.cloned_traverse(
                                criterion, {}, {'bindparam':visit_bindparam})

        if adapt_source:
            criterion = adapt_source(criterion)
        return criterion

 def visit_join(join):
     if join.isouter:
         def visit_binary(binary):
             if binary.operator == sql_operators.eq:
                 if binary.left.table is join.right:
                     binary.left = _OuterJoinColumn(binary.left)
                 elif binary.right.table is join.right:
                     binary.right = _OuterJoinColumn(binary.right)
         clauses.append(visitors.cloned_traverse(join.onclause, {}, {'binary':visit_binary}))
     else:
         clauses.append(join.onclause)

    def test_union_cte_aliases(self):
        orders = table("orders", column("region"), column("amount"))

        regional_sales = (
            select([orders.c.region, orders.c.amount])
            .cte("regional_sales")
            .alias("rs")
        )

        s = select([regional_sales.c.region]).where(
            regional_sales.c.amount > 500
        )

        self.assert_compile(
            s,
            "WITH regional_sales AS "
            "(SELECT orders.region AS region, "
            "orders.amount AS amount FROM orders) "
            "SELECT rs.region "
            "FROM regional_sales AS rs WHERE "
            "rs.amount > :amount_1",
        )

        s = s.union_all(
            select([regional_sales.c.region]).where(
                regional_sales.c.amount < 300
            )
        )
        self.assert_compile(
            s,
            "WITH regional_sales AS "
            "(SELECT orders.region AS region, "
            "orders.amount AS amount FROM orders) "
            "SELECT rs.region FROM regional_sales AS rs "
            "WHERE rs.amount > :amount_1 "
            "UNION ALL SELECT rs.region "
            "FROM regional_sales AS rs WHERE "
            "rs.amount < :amount_2",
        )

        cloned = cloned_traverse(s, {}, {})
        self.assert_compile(
            cloned,
            "WITH regional_sales AS "
            "(SELECT orders.region AS region, "
            "orders.amount AS amount FROM orders) "
            "SELECT rs.region FROM regional_sales AS rs "
            "WHERE rs.amount > :amount_1 "
            "UNION ALL SELECT rs.region "
            "FROM regional_sales AS rs WHERE "
            "rs.amount < :amount_2",
        )

    def test_annotated_visit(self):
        table1 = table('table1', column("col1"), column("col2"))
        
        bin = table1.c.col1 == bindparam('foo', value=None)
        assert str(bin) == "table1.col1 = :foo"
        def visit_binary(b):
            b.right = table1.c.col2
            
        b2 = visitors.cloned_traverse(bin, {}, {'binary':visit_binary})
        assert str(b2) == "table1.col1 = table1.col2"

        b3 = visitors.cloned_traverse(bin._annotate({}), {}, {'binary':visit_binary})
        assert str(b3) == "table1.col1 = table1.col2"

        def visit_binary(b):
            b.left = bindparam('bar')
        
        b4 = visitors.cloned_traverse(b2, {}, {'binary':visit_binary})
        assert str(b4) == ":bar = table1.col2"

        b5 = visitors.cloned_traverse(b3, {}, {'binary':visit_binary})
        assert str(b5) == ":bar = table1.col2"

    def test_as_comparison_many_argument(self):

        fn = func.some_comparison("x", "y", "z", "p", "q", "r").as_comparison(
            2, 5
        )
        is_(fn.type._type_affinity, Boolean)

        self.assert_compile(
            fn.left,
            ":some_comparison_1",
            checkparams={"some_comparison_1": "y"},
        )
        self.assert_compile(
            fn.right,
            ":some_comparison_1",
            checkparams={"some_comparison_1": "q"},
        )

        from sqlalchemy.sql import visitors

        fn_2 = visitors.cloned_traverse(fn, {}, {})
        fn_2.right = literal_column("ABC")

        self.assert_compile(
            fn,
            "some_comparison(:some_comparison_1, :some_comparison_2, "
            ":some_comparison_3, "
            ":some_comparison_4, :some_comparison_5, :some_comparison_6)",
            checkparams={
                "some_comparison_1": "x",
                "some_comparison_2": "y",
                "some_comparison_3": "z",
                "some_comparison_4": "p",
                "some_comparison_5": "q",
                "some_comparison_6": "r",
            },
        )

        self.assert_compile(
            fn_2,
            "some_comparison(:some_comparison_1, :some_comparison_2, "
            ":some_comparison_3, "
            ":some_comparison_4, ABC, :some_comparison_5)",
            checkparams={
                "some_comparison_1": "x",
                "some_comparison_2": "y",
                "some_comparison_3": "z",
                "some_comparison_4": "p",
                "some_comparison_5": "r",
            },
        )

def adapt_criterion_to_null(crit, nulls):
    """given criterion containing bind params, convert selected elements to IS NULL."""

    def visit_binary(binary):
        if isinstance(binary.left, expression._BindParamClause) and binary.left._identifying_key in nulls:
            # reverse order if the NULL is on the left side
            binary.left = binary.right
            binary.right = expression.null()
            binary.operator = operators.is_
            binary.negate = operators.isnot
        elif isinstance(binary.right, expression._BindParamClause) and binary.right._identifying_key in nulls:
            binary.right = expression.null()
            binary.operator = operators.is_
            binary.negate = operators.isnot

    return visitors.cloned_traverse(crit, {}, {"binary": visit_binary})

    def lazy_clause(
        self, state, reverse_direction=False, alias_secondary=False, adapt_source=None, detect_transient_pending=False
    ):
        if state is None:
            return self._lazy_none_clause(reverse_direction, adapt_source=adapt_source)

        if not reverse_direction:
            criterion, bind_to_col, rev = self.__lazywhere, self.__bind_to_col, self._equated_columns
        else:
            criterion, bind_to_col, rev = LazyLoader._create_lazy_clause(
                self.parent_property, reverse_direction=reverse_direction
            )

        if reverse_direction:
            mapper = self.parent_property.mapper
        else:
            mapper = self.parent_property.parent

        o = state.obj()  # strong ref
        dict_ = attributes.instance_dict(o)

        def visit_bindparam(bindparam):
            if bindparam.key in bind_to_col:
                # using a flag to enable "detect transient pending" so that
                # the slightly different usage paradigm of "dynamic" loaders
                # continue to work as expected, i.e. that all pending objects
                # should use the "post flush" attributes, and to limit this
                # newer behavior to the query.with_parent() method.
                # It would be nice to do away with this flag.

                if detect_transient_pending and (not state.key or not state.session_id):
                    bindparam.value = mapper._get_state_attr_by_column(state, dict_, bind_to_col[bindparam.key])
                else:
                    # send value as a lambda so that the value is
                    # acquired after any autoflush occurs.
                    bindparam.value = lambda: mapper._get_committed_state_attr_by_column(
                        state, dict_, bind_to_col[bindparam.key]
                    )

        if self.parent_property.secondary is not None and alias_secondary:
            criterion = sql_util.ClauseAdapter(self.parent_property.secondary.alias()).traverse(criterion)

        criterion = visitors.cloned_traverse(criterion, {}, {"bindparam": visit_bindparam})
        if adapt_source:
            criterion = adapt_source(criterion)
        return criterion

        def visit_join(join):
            if join.isouter:

                def visit_binary(binary):
                    if binary.operator == sql_operators.eq:
                        if binary.left.table is join.right:
                            binary.left = _OuterJoinColumn(binary.left)
                        elif binary.right.table is join.right:
                            binary.right = _OuterJoinColumn(binary.right)

                clauses.append(visitors.cloned_traverse(join.onclause, {}, {"binary": visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)

        def visit_join(join):
            if join.isouter:
                def visit_binary(binary):
                    if binary.operator == sql_operators.eq:
                        if join.right.is_derived_from(binary.left.table):
                            binary.left = _OuterJoinColumn(binary.left)
                        elif join.right.is_derived_from(binary.right.table):
                            binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(join.onclause, {},
                                {'binary': visit_binary}))
            else:
                clauses.append(join.onclause)

            for j in join.left, join.right:
                if isinstance(j, expression.Join):
                    visit_join(j)
                elif isinstance(j, expression.FromGrouping):
                    visit_join(j.element)