implement multi-element expression constructs

Improved the construction of SQL binary expressions to allow for very long
expressions against the same associative operator without special steps
needed in order to avoid high memory use and excess recursion depth. A
particular binary operation ``A op B`` can now be joined against another
element ``op C`` and the resulting structure will be "flattened" so that
the representation as well as SQL compilation does not require recursion.

To implement this more cleanly, the biggest change here is that
column-oriented lists of things are broken away from ClauseList
in a new class ExpressionClauseList, that also forms the basis
of BooleanClauseList. ClauseList is still used for the generic
"comma-separated list" of things such as Tuple and things like
ORDER BY, as well as in some API endpoints.

Also adds __slots__ to the TypeEngine-bound Comparator
classes.   Still can't really do __slots__ on ClauseElement.

Fixes: #7744
Change-Id: I81a8ceb6f8f3bb0fe52d58f3cb42e4b6c2bc9018

1 files changed, 189 insertions, 4 deletions

diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index e5176713a..88d1ea053 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -165,6 +165,21 @@ class DefaultColumnComparatorTest(
         loop = LoopOperate()
         is_(operator(loop, *arg), operator)
 
+    @testing.combinations(
+        operators.add,
+        operators.and_,
+        operators.or_,
+        operators.mul,
+        argnames="op",
+    )
+    def test_nonsensical_negations(self, op):
+
+        opstring = compiler.OPERATORS[op]
+        self.assert_compile(
+            select(~op(column("x"), column("q"))),
+            f"SELECT NOT (x{opstring}q) AS anon_1",
+        )
+
     def test_null_true_false_is_sanity_checks(self):
 
         d = default.DefaultDialect()
@@ -328,6 +343,176 @@ class DefaultColumnComparatorTest(
             )
 
 
+class MultiElementExprTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    @testing.combinations(True, False, argnames="reverse")
+    @testing.combinations(True, False, argnames="negate")
+    def test_associatives_mismatched_type(self, reverse, negate):
+        """test we get two separate exprs if the types dont match, operator
+        is not lost.
+
+        the expressions here don't generally make sense from a SQL
+        perspective, we are checking just that the operators / parenthesis /
+        negation works out in the SQL string to reasonably correspond to
+        what the Python structures look like.
+
+        """
+
+        expr1 = column("i1", Integer) + column("i2", Integer)
+
+        expr2 = column("d1", String) + column("d2", String)
+
+        if reverse:
+            expr = expr2 + expr1
+
+            self.assert_compile(
+                select(expr), "SELECT (d1 || d2) + i1 + i2 AS anon_1"
+            )
+        else:
+            expr = expr1 + expr2
+
+            self.assert_compile(
+                select(expr), "SELECT i1 + i2 + d1 || d2 AS anon_1"
+            )
+
+    @testing.combinations(
+        operators.add,
+        operators.and_,
+        operators.or_,
+        operators.mul,
+        argnames="op",
+    )
+    @testing.combinations(True, False, argnames="reverse")
+    @testing.combinations(True, False, argnames="negate")
+    def test_associatives(self, op, reverse, negate):
+        t1 = table("t", column("q"), column("p"))
+
+        num = 500
+
+        expr = op(t1.c.q, t1.c.p)
+
+        if reverse:
+            for i in range(num - 1, -1, -1):
+                expr = op(column(f"d{i}"), expr)
+        else:
+            for i in range(num):
+                expr = op(expr, column(f"d{i}"))
+
+        opstring = compiler.OPERATORS[op]
+        exprs = opstring.join(f"d{i}" for i in range(num))
+
+        if negate:
+            self.assert_compile(
+                select(~expr),
+                f"SELECT NOT (t.q{opstring}t.p{opstring}{exprs}) "
+                "AS anon_1 FROM t"
+                if not reverse
+                else f"SELECT NOT ({exprs}{opstring}t.q{opstring}t.p) "
+                "AS anon_1 FROM t",
+            )
+        else:
+            self.assert_compile(
+                select(expr),
+                f"SELECT t.q{opstring}t.p{opstring}{exprs} AS anon_1 FROM t"
+                if not reverse
+                else f"SELECT {exprs}{opstring}t.q{opstring}t.p "
+                f"AS anon_1 FROM t",
+            )
+
+    @testing.combinations(
+        operators.gt,
+        operators.eq,
+        operators.le,
+        operators.sub,
+        argnames="op",
+    )
+    @testing.combinations(True, False, argnames="reverse")
+    @testing.combinations(True, False, argnames="negate")
+    def test_non_associatives(self, op, reverse, negate):
+        """similar tests as test_associatives but for non-assoc
+        operators.
+
+        the expressions here don't generally make sense from a SQL
+        perspective, we are checking just that the operators / parenthesis /
+        negation works out in the SQL string to reasonably correspond to
+        what the Python structures look like.
+
+        """
+        t1 = table("t", column("q"), column("p"))
+
+        num = 5
+
+        expr = op(t1.c.q, t1.c.p)
+
+        if reverse:
+            for i in range(num - 1, -1, -1):
+                expr = op(column(f"d{i}"), expr)
+        else:
+            for i in range(num):
+                expr = op(expr, column(f"d{i}"))
+
+        opstring = compiler.OPERATORS[op]
+        exprs = opstring.join(f"d{i}" for i in range(num))
+
+        if negate:
+            negate_op = {
+                operators.gt: operators.le,
+                operators.eq: operators.ne,
+                operators.le: operators.gt,
+            }.get(op, None)
+
+            if negate_op:
+                negate_opstring = compiler.OPERATORS[negate_op]
+                if reverse:
+                    str_expr = (
+                        f"d0{negate_opstring}(d1{opstring}(d2{opstring}"
+                        f"(d3{opstring}(d4{opstring}(t.q{opstring}t.p)))))"
+                    )
+                else:
+                    str_expr = (
+                        f"(((((t.q{opstring}t.p){opstring}d0){opstring}d1)"
+                        f"{opstring}d2){opstring}d3){negate_opstring}d4"
+                    )
+            else:
+                if reverse:
+                    str_expr = (
+                        f"NOT (d0{opstring}(d1{opstring}(d2{opstring}"
+                        f"(d3{opstring}(d4{opstring}(t.q{opstring}t.p))))))"
+                    )
+                else:
+                    str_expr = (
+                        f"NOT ((((((t.q{opstring}t.p){opstring}d0)"
+                        f"{opstring}d1){opstring}d2){opstring}d3){opstring}d4)"
+                    )
+
+            self.assert_compile(
+                select(~expr),
+                f"SELECT {str_expr} AS anon_1 FROM t"
+                if not reverse
+                else f"SELECT {str_expr} AS anon_1 FROM t",
+            )
+        else:
+
+            if reverse:
+                str_expr = (
+                    f"d0{opstring}(d1{opstring}(d2{opstring}"
+                    f"(d3{opstring}(d4{opstring}(t.q{opstring}t.p)))))"
+                )
+            else:
+                str_expr = (
+                    f"(((((t.q{opstring}t.p){opstring}d0)"
+                    f"{opstring}d1){opstring}d2){opstring}d3){opstring}d4"
+                )
+
+            self.assert_compile(
+                select(expr),
+                f"SELECT {str_expr} AS anon_1 FROM t"
+                if not reverse
+                else f"SELECT {str_expr} AS anon_1 FROM t",
+            )
+
+
 class CustomUnaryOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = "default"
 
@@ -2954,7 +3139,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_contains_concat(self):
         self.assert_compile(
             column("x").contains("y"),
-            "x LIKE concat(concat('%%', %s), '%%')",
+            "x LIKE concat('%%', %s, '%%')",
             checkparams={"x_1": "y"},
             dialect=mysql.dialect(),
         )
@@ -2962,7 +3147,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_not_contains_concat(self):
         self.assert_compile(
             ~column("x").contains("y"),
-            "x NOT LIKE concat(concat('%%', %s), '%%')",
+            "x NOT LIKE concat('%%', %s, '%%')",
             checkparams={"x_1": "y"},
             dialect=mysql.dialect(),
         )
@@ -2970,7 +3155,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_contains_literal_concat(self):
         self.assert_compile(
             column("x").contains(literal_column("y")),
-            "x LIKE concat(concat('%%', y), '%%')",
+            "x LIKE concat('%%', y, '%%')",
             checkparams={},
             dialect=mysql.dialect(),
         )
@@ -2978,7 +3163,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_contains_text_concat(self):
         self.assert_compile(
             column("x").contains(text("y")),
-            "x LIKE concat(concat('%%', y), '%%')",
+            "x LIKE concat('%%', y, '%%')",
             checkparams={},
             dialect=mysql.dialect(),
         )