fix linter JOIN logic; fix PostgreSQL ARRAY op comparison

Adjusted the "from linter" warning feature to accommodate for a chain of
joins more than one level deep where the ON clauses don't explicitly match
up the targets, such as an expression such as "ON TRUE". This mode of use
is intended to cancel the cartesian product warning simply by the fact that
there's a JOIN from "a to b", which was not working for the case where the
chain of joins had more than one element.

this incurs a bit more compiler overhead that comes out in profiling
but is not extensive.

Added the "is_comparison" flag to the PostgreSQL "overlaps",
"contained_by", "contains" operators, so that they work in relevant ORM
contexts as well as in conjunction with the "from linter" feature.

Fixes: #6886
Change-Id: I078dc3fe6d4f7871ffe4ebac3e71e62f3f213d12

1 files changed, 33 insertions, 0 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index d2c81e63f..0b56d8987 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1508,6 +1508,39 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             checkparams={"param_1": 7},
         )
 
+    @testing.combinations(
+        (lambda c: c.overlap, "&&"),
+        (lambda c: c.contains, "@>"),
+        (lambda c: c.contained_by, "<@"),
+    )
+    def test_overlap_no_cartesian(self, op_fn, expected_op):
+        """test #6886"""
+        t1 = table(
+            "t1",
+            column("id", Integer),
+            column("ancestor_ids", postgresql.ARRAY(Integer)),
+        )
+
+        t1a = t1.alias()
+        t1b = t1.alias()
+
+        stmt = (
+            select(t1, t1a, t1b)
+            .where(op_fn(t1a.c.ancestor_ids)(postgresql.array((t1.c.id,))))
+            .where(op_fn(t1b.c.ancestor_ids)(postgresql.array((t1.c.id,))))
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT t1.id, t1.ancestor_ids, t1_1.id AS id_1, "
+            "t1_1.ancestor_ids AS ancestor_ids_1, t1_2.id AS id_2, "
+            "t1_2.ancestor_ids AS ancestor_ids_2 "
+            "FROM t1, t1 AS t1_1, t1 AS t1_2 "
+            "WHERE t1_1.ancestor_ids %(op)s ARRAY[t1.id] "
+            "AND t1_2.ancestor_ids %(op)s ARRAY[t1.id]" % {"op": expected_op},
+            from_linting=True,
+        )
+
     @testing.combinations((True,), (False,))
     def test_array_zero_indexes(self, zero_indexes):
         c = Column("x", postgresql.ARRAY(Integer, zero_indexes=zero_indexes))