- build out a new base type for Array, as well as new any/all operators

- any/all work for Array as well as subqueries, accepted by MySQL
- Postgresql ARRAY now subclasses Array
- fixes #3516

2 files changed, 220 insertions, 5 deletions

diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index f3dfd2daf..03c0f89be 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1,7 +1,8 @@
 from sqlalchemy.testing import fixtures, eq_, is_, is_not_
 from sqlalchemy import testing
 from sqlalchemy.testing import assert_raises_message
-from sqlalchemy.sql import column, desc, asc, literal, collate, null, true, false
+from sqlalchemy.sql import column, desc, asc, literal, collate, null, \
+    true, false, any_, all_
 from sqlalchemy.sql.expression import BinaryExpression, \
     ClauseList, Grouping, \
     UnaryExpression, select, union, func, tuple_
@@ -14,7 +15,7 @@ from sqlalchemy.sql.elements import _literal_as_text
 from sqlalchemy.schema import Column, Table, MetaData
 from sqlalchemy.sql import compiler
 from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \
-    Boolean, NullType, MatchType, Indexable, Concatenable
+    Boolean, NullType, MatchType, Indexable, Concatenable, Array
 from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \
     sqlite, mssql
 from sqlalchemy import util
@@ -2262,3 +2263,154 @@ class TupleTypingTest(fixtures.TestBase):
         eq_(len(expr.right.clauses), 2)
         for elem in expr.right.clauses:
             self._assert_types(elem)
+
+
+class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def _fixture(self):
+        m = MetaData()
+
+        t = Table(
+            'tab1', m,
+            Column('arrval', Array(Integer)),
+            Column('data', Integer)
+        )
+        return t
+
+    def test_any_array(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 == any_(t.c.arrval),
+            ":param_1 = ANY (tab1.arrval)",
+            checkparams={"param_1": 5}
+        )
+
+    def test_all_array(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 == all_(t.c.arrval),
+            ":param_1 = ALL (tab1.arrval)",
+            checkparams={"param_1": 5}
+        )
+
+    def test_any_comparator_array(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 > any_(t.c.arrval),
+            ":param_1 > ANY (tab1.arrval)",
+            checkparams={"param_1": 5}
+        )
+
+    def test_all_comparator_array(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 > all_(t.c.arrval),
+            ":param_1 > ALL (tab1.arrval)",
+            checkparams={"param_1": 5}
+        )
+
+    def test_any_comparator_array_wexpr(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            t.c.data > any_(t.c.arrval),
+            "tab1.data > ANY (tab1.arrval)",
+            checkparams={}
+        )
+
+    def test_all_comparator_array_wexpr(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            t.c.data > all_(t.c.arrval),
+            "tab1.data > ALL (tab1.arrval)",
+            checkparams={}
+        )
+
+    def test_illegal_ops(self):
+        t = self._fixture()
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "Only comparison operators may be used with ANY/ALL",
+            lambda: 5 + all_(t.c.arrval)
+        )
+
+        # TODO:
+        # this is invalid but doesn't raise an error,
+        # as the left-hand side just does its thing.  Types
+        # would need to reject their right-hand side.
+        self.assert_compile(
+            t.c.data + all_(t.c.arrval),
+            "tab1.data + ALL (tab1.arrval)"
+        )
+
+    def test_any_array_comparator_accessor(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            t.c.arrval.any(5, operator.gt),
+            ":param_1 > ANY (tab1.arrval)",
+            checkparams={"param_1": 5}
+        )
+
+    def test_all_array_comparator_accessor(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            t.c.arrval.all(5, operator.gt),
+            ":param_1 > ALL (tab1.arrval)",
+            checkparams={"param_1": 5}
+        )
+
+    def test_any_array_expression(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 == any_(t.c.arrval[5:6] + postgresql.array([3, 4])),
+            "%(param_1)s = ANY (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || "
+            "ARRAY[%(param_2)s, %(param_3)s])",
+            checkparams={
+                'arrval_2': 6, 'param_1': 5, 'param_3': 4,
+                'arrval_1': 5, 'param_2': 3},
+            dialect='postgresql'
+        )
+
+    def test_all_array_expression(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 == all_(t.c.arrval[5:6] + postgresql.array([3, 4])),
+            "%(param_1)s = ALL (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || "
+            "ARRAY[%(param_2)s, %(param_3)s])",
+            checkparams={
+                'arrval_2': 6, 'param_1': 5, 'param_3': 4,
+                'arrval_1': 5, 'param_2': 3},
+            dialect='postgresql'
+        )
+
+    def test_any_subq(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 == any_(select([t.c.data]).where(t.c.data < 10)),
+            ":param_1 = ANY (SELECT tab1.data "
+            "FROM tab1 WHERE tab1.data < :data_1)",
+            checkparams={'data_1': 10, 'param_1': 5}
+        )
+
+    def test_all_subq(self):
+        t = self._fixture()
+
+        self.assert_compile(
+            5 == all_(select([t.c.data]).where(t.c.data < 10)),
+            ":param_1 = ALL (SELECT tab1.data "
+            "FROM tab1 WHERE tab1.data < :data_1)",
+            checkparams={'data_1': 10, 'param_1': 5}
+        )
+
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index d562c83ce..e32126a18 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -10,7 +10,7 @@ from sqlalchemy import (
     and_, func, Date, LargeBinary, literal, cast, text, Enum,
     type_coerce, VARCHAR, Time, DateTime, BigInteger, SmallInteger, BOOLEAN,
     BLOB, NCHAR, NVARCHAR, CLOB, TIME, DATE, DATETIME, TIMESTAMP, SMALLINT,
-    INTEGER, DECIMAL, NUMERIC, FLOAT, REAL)
+    INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, Array)
 from sqlalchemy.sql import ddl
 from sqlalchemy import inspection
 from sqlalchemy import exc, types, util, dialects
@@ -28,6 +28,7 @@ from sqlalchemy.testing.util import round_decimal
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import mock
 
+
 class AdaptTest(fixtures.TestBase):
 
     def _all_dialect_modules(self):
@@ -138,7 +139,7 @@ class AdaptTest(fixtures.TestBase):
         for is_down_adaption, typ, target_adaptions in adaptions():
             if typ in (types.TypeDecorator, types.TypeEngine, types.Variant):
                 continue
-            elif typ is dialects.postgresql.ARRAY:
+            elif issubclass(typ, Array):
                 t1 = typ(String)
             else:
                 t1 = typ()
@@ -188,7 +189,7 @@ class AdaptTest(fixtures.TestBase):
         for typ in self._all_types():
             if typ in (types.TypeDecorator, types.TypeEngine, types.Variant):
                 continue
-            elif typ is dialects.postgresql.ARRAY:
+            elif issubclass(typ, Array):
                 t1 = typ(String)
             else:
                 t1 = typ()
@@ -1343,6 +1344,68 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults):
         with open(f, mode='rb') as o:
             return o.read()
 
+
+class ArrayTest(fixtures.TestBase):
+
+    def _myarray_fixture(self):
+        class MyArray(Array):
+            pass
+        return MyArray
+
+    def test_array_index_map_dimensions(self):
+        col = column('x', Array(Integer, dimensions=3))
+        is_(
+            col[5].type._type_affinity, Array
+        )
+        eq_(
+            col[5].type.dimensions, 2
+        )
+        is_(
+            col[5][6].type._type_affinity, Array
+        )
+        eq_(
+            col[5][6].type.dimensions, 1
+        )
+        is_(
+            col[5][6][7].type._type_affinity, Integer
+        )
+
+    def test_array_getitem_single_type(self):
+        m = MetaData()
+        arrtable = Table(
+            'arrtable', m,
+            Column('intarr', Array(Integer)),
+            Column('strarr', Array(String)),
+        )
+        is_(arrtable.c.intarr[1].type._type_affinity, Integer)
+        is_(arrtable.c.strarr[1].type._type_affinity, String)
+
+    def test_array_getitem_slice_type(self):
+        m = MetaData()
+        arrtable = Table(
+            'arrtable', m,
+            Column('intarr', Array(Integer)),
+            Column('strarr', Array(String)),
+        )
+        is_(arrtable.c.intarr[1:3].type._type_affinity, Array)
+        is_(arrtable.c.strarr[1:3].type._type_affinity, Array)
+
+    def test_array_getitem_slice_type_dialect_level(self):
+        MyArray = self._myarray_fixture()
+        m = MetaData()
+        arrtable = Table(
+            'arrtable', m,
+            Column('intarr', MyArray(Integer)),
+            Column('strarr', MyArray(String)),
+        )
+        is_(arrtable.c.intarr[1:3].type._type_affinity, Array)
+        is_(arrtable.c.strarr[1:3].type._type_affinity, Array)
+
+        # but the slice returns the actual type
+        assert isinstance(arrtable.c.intarr[1:3].type, MyArray)
+        assert isinstance(arrtable.c.strarr[1:3].type, MyArray)
+
+
 test_table = meta = MyCustomType = MyTypeDec = None