Merge branch 'ticket_3516'

7 files changed, 528 insertions, 34 deletions

diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py
index f19177c2a..85513167c 100644
--- a/test/dialect/mysql/test_query.py
+++ b/test/dialect/mysql/test_query.py
@@ -5,7 +5,6 @@ from sqlalchemy import *
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL
 from sqlalchemy import testing
 
-
 class IdiosyncrasyTest(fixtures.TestBase, AssertsCompiledSQL):
     __only_on__ = 'mysql'
     __backend__ = True
@@ -177,3 +176,57 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
         eq_([1, 3, 5], [r.id for r in results])
 
 
+class AnyAllTest(fixtures.TablesTest, AssertsCompiledSQL):
+    __only_on__ = 'mysql'
+    __backend__ = True
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            'stuff', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('value', Integer)
+        )
+
+    @classmethod
+    def insert_data(cls):
+        stuff = cls.tables.stuff
+        testing.db.execute(
+            stuff.insert(),
+            [
+                {'id': 1, 'value': 1},
+                {'id': 2, 'value': 2},
+                {'id': 3, 'value': 3},
+                {'id': 4, 'value': 4},
+                {'id': 5, 'value': 5},
+            ]
+        )
+
+    def test_any_w_comparator(self):
+        stuff = self.tables.stuff
+        stmt = select([stuff.c.id]).where(
+            stuff.c.value > any_(select([stuff.c.value])))
+
+        eq_(
+            testing.db.execute(stmt).fetchall(),
+            [(2,), (3,), (4,), (5,)]
+        )
+
+    def test_all_w_comparator(self):
+        stuff = self.tables.stuff
+        stmt = select([stuff.c.id]).where(
+            stuff.c.value >= all_(select([stuff.c.value])))
+
+        eq_(
+            testing.db.execute(stmt).fetchall(),
+            [(5,)]
+        )
+
+    def test_any_literal(self):
+        stuff = self.tables.stuff
+        stmt = select([4 == any_(select([stuff.c.value]))])
+
+        is_(
+            testing.db.execute(stmt).scalar(), True
+        )
+
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 1489fe24c..0407dcb81 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -693,7 +693,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self._test_array_zero_indexes(False)
 
     def test_array_literal_type(self):
-        is_(postgresql.array([1, 2]).type._type_affinity, postgresql.ARRAY)
+        isinstance(postgresql.array([1, 2]).type, postgresql.ARRAY)
         is_(postgresql.array([1, 2]).type.item_type._type_affinity, Integer)
 
         is_(postgresql.array([1, 2], type_=String).
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index 9d5cb4d91..a625e1cee 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -7,7 +7,7 @@ from sqlalchemy import testing
 import datetime
 from sqlalchemy import Table, MetaData, Column, Integer, Enum, Float, select, \
     func, DateTime, Numeric, exc, String, cast, REAL, TypeDecorator, Unicode, \
-    Text, null, text, column
+    Text, null, text, column, Array, any_, all_
 from sqlalchemy.sql import operators
 from sqlalchemy import types
 import sqlalchemy as sa
@@ -754,7 +754,6 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase):
             checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5}
         )
 
-
     def test_array_slice_index(self):
         col = column('x', postgresql.ARRAY(Integer))
         self.assert_compile(
@@ -784,13 +783,19 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase):
     def test_array_index_map_dimensions(self):
         col = column('x', postgresql.ARRAY(Integer, dimensions=3))
         is_(
-            col[5].type._type_affinity, postgresql.ARRAY
+            col[5].type._type_affinity, Array
+        )
+        assert isinstance(
+            col[5].type, postgresql.ARRAY
         )
         eq_(
             col[5].type.dimensions, 2
         )
         is_(
-            col[5][6].type._type_affinity, postgresql.ARRAY
+            col[5][6].type._type_affinity, Array
+        )
+        assert isinstance(
+            col[5][6].type, postgresql.ARRAY
         )
         eq_(
             col[5][6].type.dimensions, 1
@@ -816,8 +821,43 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase):
             Column('intarr', postgresql.ARRAY(Integer)),
             Column('strarr', postgresql.ARRAY(String)),
         )
-        is_(arrtable.c.intarr[1:3].type._type_affinity, postgresql.ARRAY)
-        is_(arrtable.c.strarr[1:3].type._type_affinity, postgresql.ARRAY)
+
+        # type affinity is Array...
+        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, postgresql.ARRAY)
+        assert isinstance(arrtable.c.strarr[1:3].type, postgresql.ARRAY)
+
+    def test_array_functions_plus_getitem(self):
+        """test parenthesizing of functions plus indexing, which seems
+        to be required by Postgresql.
+
+        """
+        stmt = select([
+            func.array_cat(
+                array([1, 2, 3]),
+                array([4, 5, 6]),
+                type_=postgresql.ARRAY(Integer)
+            )[2:5]
+        ])
+        self.assert_compile(
+            stmt,
+            "SELECT (array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], "
+            "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))"
+            "[%(param_7)s:%(param_8)s] AS anon_1"
+        )
+
+        self.assert_compile(
+            func.array_cat(
+                array([1, 2, 3]),
+                array([4, 5, 6]),
+                type_=postgresql.ARRAY(Integer)
+            )[3],
+            "(array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], "
+            "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(param_7)s]"
+        )
 
 
 class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults):
@@ -876,6 +916,89 @@ class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults):
         assert isinstance(tbl.c.intarr.type.item_type, Integer)
         assert isinstance(tbl.c.strarr.type.item_type, String)
 
+    @testing.provide_metadata
+    def test_array_agg(self):
+        values_table = Table('values', self.metadata, Column('value', Integer))
+        self.metadata.create_all(testing.db)
+        testing.db.execute(
+            values_table.insert(),
+            [{'value': i} for i in range(1, 10)]
+        )
+
+        stmt = select([func.array_agg(values_table.c.value)])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            list(range(1, 10))
+        )
+
+        stmt = select([func.array_agg(values_table.c.value)[3]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            3
+        )
+
+        stmt = select([func.array_agg(values_table.c.value)[2:4]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            [2, 3, 4]
+        )
+
+    def test_array_index_slice_exprs(self):
+        """test a variety of expressions that sometimes need parenthesizing"""
+
+        stmt = select([array([1, 2, 3, 4])[2:3]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            [2, 3]
+        )
+
+        stmt = select([array([1, 2, 3, 4])[2]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            2
+        )
+
+        stmt = select([(array([1, 2]) + array([3, 4]))[2:3]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            [2, 3]
+        )
+
+        stmt = select([array([1, 2]) + array([3, 4])[2:3]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            [1, 2, 4]
+        )
+
+        stmt = select([array([1, 2])[2:3] + array([3, 4])])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            [2, 3, 4]
+        )
+
+        stmt = select([
+            func.array_cat(
+                array([1, 2, 3]),
+                array([4, 5, 6]),
+                type_=postgresql.ARRAY(Integer)
+            )[2:5]
+        ])
+        eq_(
+            testing.db.execute(stmt).scalar(), [2, 3, 4, 5]
+        )
+
+    def test_any_all_exprs(self):
+        stmt = select([
+            3 == any_(func.array_cat(
+                array([1, 2, 3]),
+                array([4, 5, 6]),
+                type_=postgresql.ARRAY(Integer)
+            ))
+        ])
+        eq_(
+            testing.db.execute(stmt).scalar(), True
+        )
+
     def test_insert_array(self):
         arrtable = self.tables.arrtable
         arrtable.insert().execute(intarr=[1, 2, 3], strarr=[util.u('abc'),
@@ -1587,7 +1710,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
     def test_where_defined(self):
         self._test_where(
             self.hashcol.defined('foo'),
-            "defined(test_table.hash, %(param_1)s)"
+            "defined(test_table.hash, %(defined_1)s)"
         )
 
     def test_where_contains(self):
@@ -1618,7 +1741,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
     def test_cols_delete_single_key(self):
         self._test_cols(
             self.hashcol.delete('foo'),
-            "delete(test_table.hash, %(param_1)s) AS delete_1",
+            "delete(test_table.hash, %(delete_2)s) AS delete_1",
             True
         )
 
@@ -1633,7 +1756,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
     def test_cols_delete_matching_pairs(self):
         self._test_cols(
             self.hashcol.delete(hstore('1', '2')),
-            ("delete(test_table.hash, hstore(%(param_1)s, %(param_2)s)) "
+            ("delete(test_table.hash, hstore(%(hstore_1)s, %(hstore_2)s)) "
              "AS delete_1"),
             True
         )
@@ -1649,7 +1772,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
     def test_cols_hstore_pair_text(self):
         self._test_cols(
             hstore('foo', '3')['foo'],
-            "hstore(%(param_1)s, %(param_2)s) -> %(hstore_1)s AS anon_1",
+            "hstore(%(hstore_1)s, %(hstore_2)s) -> %(hstore_3)s AS anon_1",
             False
         )
 
@@ -1674,14 +1797,14 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
         self._test_cols(
             self.hashcol.concat(hstore(cast(self.test_table.c.id, Text), '3')),
             ("test_table.hash || hstore(CAST(test_table.id AS TEXT), "
-             "%(param_1)s) AS anon_1"),
+             "%(hstore_1)s) AS anon_1"),
             True
         )
 
     def test_cols_concat_op(self):
         self._test_cols(
             hstore('foo', 'bar') + self.hashcol,
-            "hstore(%(param_1)s, %(param_2)s) || test_table.hash AS anon_1",
+            "hstore(%(hstore_1)s, %(hstore_2)s) || test_table.hash AS anon_1",
             True
         )
 
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index ccc9b2dcd..51cfcb919 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -1,8 +1,8 @@
-from sqlalchemy.testing import eq_
+from sqlalchemy.testing import eq_, is_
 import datetime
 from sqlalchemy import func, select, Integer, literal, DateTime, Table, \
     Column, Sequence, MetaData, extract, Date, String, bindparam, \
-    literal_column
+    literal_column, Array, Numeric
 from sqlalchemy.sql import table, column
 from sqlalchemy import sql, util
 from sqlalchemy.sql.compiler import BIND_TEMPLATES
@@ -52,7 +52,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             self.assert_compile(
                 fake_func('foo'),
                 "fake_func(%s)" %
-                bindtemplate % {'name': 'param_1', 'position': 1},
+                bindtemplate % {'name': 'fake_func_1', 'position': 1},
                 dialect=dialect)
 
     def test_use_labels(self):
@@ -89,7 +89,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
     def test_generic_annotation(self):
         fn = func.coalesce('x', 'y')._annotate({"foo": "bar"})
         self.assert_compile(
-            fn, "coalesce(:param_1, :param_2)"
+            fn, "coalesce(:coalesce_1, :coalesce_2)"
         )
 
     def test_custom_default_namespace(self):
@@ -140,7 +140,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
         self.assert_compile(
             func.my_func(1, 2),
-            "my_func(:param_1, :param_2, :param_3)"
+            "my_func(:my_func_1, :my_func_2, :my_func_3)"
         )
 
     def test_custom_registered_identifier(self):
@@ -178,7 +178,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
         self.assert_compile(
             myfunc(1, 2, 3),
-            "myfunc(:param_1, :param_2, :param_3)"
+            "myfunc(:myfunc_1, :myfunc_2, :myfunc_3)"
         )
 
     def test_namespacing_conflicts(self):
@@ -188,7 +188,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         assert isinstance(func.count().type, sqltypes.Integer)
 
         self.assert_compile(func.count(), 'count(*)')
-        self.assert_compile(func.count(1), 'count(:param_1)')
+        self.assert_compile(func.count(1), 'count(:count_1)')
         c = column('abc')
         self.assert_compile(func.count(c), 'count(abc)')
 
@@ -378,7 +378,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
     def test_funcfilter_empty(self):
         self.assert_compile(
             func.count(1).filter(),
-            "count(:param_1)"
+            "count(:count_1)"
         )
 
     def test_funcfilter_criterion(self):
@@ -386,7 +386,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             func.count(1).filter(
                 table1.c.name != None
             ),
-            "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)"
+            "count(:count_1) FILTER (WHERE mytable.name IS NOT NULL)"
         )
 
     def test_funcfilter_compound_criterion(self):
@@ -395,7 +395,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                 table1.c.name == None,
                 table1.c.myid > 0
             ),
-            "count(:param_1) FILTER (WHERE mytable.name IS NULL AND "
+            "count(:count_1) FILTER (WHERE mytable.name IS NULL AND "
             "mytable.myid > :myid_1)"
         )
 
@@ -404,7 +404,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             select([func.count(1).filter(
                 table1.c.description != None
             ).label('foo')]),
-            "SELECT count(:param_1) FILTER (WHERE mytable.description "
+            "SELECT count(:count_1) FILTER (WHERE mytable.description "
             "IS NOT NULL) AS foo FROM mytable"
         )
 
@@ -429,7 +429,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                     table1.c.name == 'name'
                 )
             ]),
-            "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) "
+            "SELECT count(:count_1) FILTER (WHERE mytable.name = :name_1) "
             "AS anon_1 FROM mytable"
         )
 
@@ -443,7 +443,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                     table1.c.description == 'description'
                 )
             ]),
-            "SELECT count(:param_1) FILTER (WHERE "
+            "SELECT count(:count_1) FILTER (WHERE "
             "mytable.name = :name_1 AND mytable.description = :description_1) "
             "AS anon_1 FROM mytable"
         )
@@ -477,6 +477,70 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "AS anon_1 FROM mytable"
         )
 
+    def test_funcfilter_within_group(self):
+        stmt = select([
+            table1.c.myid,
+            func.percentile_cont(0.5).within_group(
+                table1.c.name
+            )
+        ])
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+            "WITHIN GROUP (ORDER BY mytable.name) "
+            "AS anon_1 "
+            "FROM mytable",
+            {'percentile_cont_1': 0.5}
+        )
+
+    def test_funcfilter_within_group_multi(self):
+        stmt = select([
+            table1.c.myid,
+            func.percentile_cont(0.5).within_group(
+                table1.c.name, table1.c.description
+            )
+        ])
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+            "WITHIN GROUP (ORDER BY mytable.name, mytable.description) "
+            "AS anon_1 "
+            "FROM mytable",
+            {'percentile_cont_1': 0.5}
+        )
+
+    def test_funcfilter_within_group_desc(self):
+        stmt = select([
+            table1.c.myid,
+            func.percentile_cont(0.5).within_group(
+                table1.c.name.desc()
+            )
+        ])
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+            "WITHIN GROUP (ORDER BY mytable.name DESC) "
+            "AS anon_1 "
+            "FROM mytable",
+            {'percentile_cont_1': 0.5}
+        )
+
+    def test_funcfilter_within_group_w_over(self):
+        stmt = select([
+            table1.c.myid,
+            func.percentile_cont(0.5).within_group(
+                table1.c.name.desc()
+            ).over(partition_by=table1.c.description)
+        ])
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+            "WITHIN GROUP (ORDER BY mytable.name DESC) "
+            "OVER (PARTITION BY mytable.description) AS anon_1 "
+            "FROM mytable",
+            {'percentile_cont_1': 0.5}
+        )
+
     def test_incorrect_none_type(self):
         class MissingType(FunctionElement):
             name = 'mt'
@@ -490,6 +554,45 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         )
 
 
+class ReturnTypeTest(fixtures.TestBase):
+
+    def test_array_agg(self):
+        expr = func.array_agg(column('data', Integer))
+        is_(expr.type._type_affinity, Array)
+        is_(expr.type.item_type._type_affinity, Integer)
+
+    def test_mode(self):
+        expr = func.mode(0.5).within_group(
+            column('data', Integer).desc())
+        is_(expr.type._type_affinity, Integer)
+
+    def test_percentile_cont(self):
+        expr = func.percentile_cont(0.5).within_group(column('data', Integer))
+        is_(expr.type._type_affinity, Integer)
+
+    def test_percentile_cont_array(self):
+        expr = func.percentile_cont(0.5, 0.7).within_group(
+            column('data', Integer))
+        is_(expr.type._type_affinity, Array)
+        is_(expr.type.item_type._type_affinity, Integer)
+
+    def test_percentile_cont_array_desc(self):
+        expr = func.percentile_cont(0.5, 0.7).within_group(
+            column('data', Integer).desc())
+        is_(expr.type._type_affinity, Array)
+        is_(expr.type.item_type._type_affinity, Integer)
+
+    def test_cume_dist(self):
+        expr = func.cume_dist(0.5).within_group(
+            column('data', Integer).desc())
+        is_(expr.type._type_affinity, Numeric)
+
+    def test_percent_rank(self):
+        expr = func.percent_rank(0.5).within_group(
+            column('data', Integer))
+        is_(expr.type._type_affinity, Numeric)
+
+
 class ExecuteTest(fixtures.TestBase):
 
     @engines.close_first
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_selectable.py b/test/sql/test_selectable.py
index 4a332a4d1..b9cbbf480 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -932,10 +932,10 @@ class AnonLabelTest(fixtures.TestBase):
         c1 = func.count('*')
         assert c1.label(None) is not c1
 
-        eq_(str(select([c1])), "SELECT count(:param_1) AS count_1")
+        eq_(str(select([c1])), "SELECT count(:count_2) AS count_1")
         c2 = select([c1]).compile()
 
-        eq_(str(select([c1.label(None)])), "SELECT count(:param_1) AS count_1")
+        eq_(str(select([c1.label(None)])), "SELECT count(:count_2) AS count_1")
 
     def test_named_labels_named_column(self):
         c1 = column('x')
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