- merge of ticket_3499 indexed access branch

- The "hashable" flag on special datatypes such as :class:`.postgresql.ARRAY`,
:class:`.postgresql.JSON` and :class:`.postgresql.HSTORE` is now
set to False, which allows these types to be fetchable in ORM
queries that include entities within the row.  fixes #3499
- The Postgresql :class:`.postgresql.ARRAY` type now supports multidimensional
indexed access, e.g. expressions such as ``somecol[5][6]`` without
any need for explicit casts or type coercions, provided
that the :paramref:`.postgresql.ARRAY.dimensions` parameter is set to the
desired number of dimensions. fixes #3487
- The return type for the :class:`.postgresql.JSON` and :class:`.postgresql.JSONB`
when using indexed access has been fixed to work like Postgresql itself,
and returns an expression that itself is of type :class:`.postgresql.JSON`
or :class:`.postgresql.JSONB`.  Previously, the accessor would return
:class:`.NullType` which disallowed subsequent JSON-like operators to be
used. part of fixes #3503
- The :class:`.postgresql.JSON`, :class:`.postgresql.JSONB` and
:class:`.postgresql.HSTORE` datatypes now allow full control over the
return type from an indexed textual access operation, either ``column[someindex].astext``
for a JSON type or ``column[someindex]`` for an HSTORE type,
via the :paramref:`.postgresql.JSON.astext_type` and
:paramref:`.postgresql.HSTORE.text_type` parameters. also part of fixes #3503
- The :attr:`.postgresql.JSON.Comparator.astext` modifier no longer
calls upon :meth:`.ColumnElement.cast` implicitly, as PG's JSON/JSONB
types allow cross-casting between each other as well.  Code that
makes use of :meth:`.ColumnElement.cast` on JSON indexed access,
e.g. ``col[someindex].cast(Integer)``, will need to be changed
to call :attr:`.postgresql.JSON.Comparator.astext` explicitly.  This is
part of the refactor in references #3503 for consistency in operator
use.

6 files changed, 535 insertions, 36 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 9fa5c9804..1489fe24c 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -9,7 +9,7 @@ from sqlalchemy import Sequence, Table, Column, Integer, update, String,\
     Text
 from sqlalchemy.dialects.postgresql import ExcludeConstraint, array
 from sqlalchemy import exc, schema
-from sqlalchemy.dialects.postgresql import base as postgresql
+from sqlalchemy.dialects import postgresql
 from sqlalchemy.dialects.postgresql import TSRANGE
 from sqlalchemy.orm import mapper, aliased, Session
 from sqlalchemy.sql import table, column, operators
@@ -21,7 +21,7 @@ class SequenceTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_format(self):
         seq = Sequence('my_seq_no_schema')
-        dialect = postgresql.PGDialect()
+        dialect = postgresql.dialect()
         assert dialect.identifier_preparer.format_sequence(seq) \
             == 'my_seq_no_schema'
         seq = Sequence('my_seq', schema='some_schema')
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index fac0f2df8..9e0e5bcc6 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -7,11 +7,11 @@ 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
+    Text, null, text, column
 from sqlalchemy.sql import operators
 from sqlalchemy import types
 import sqlalchemy as sa
-from sqlalchemy.dialects.postgresql import base as postgresql
+from sqlalchemy.dialects import postgresql
 from sqlalchemy.dialects.postgresql import HSTORE, hstore, array, \
     INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, TSTZRANGE, \
     JSON, JSONB
@@ -20,6 +20,8 @@ from sqlalchemy import util
 from sqlalchemy.testing.util import round_decimal
 from sqlalchemy import inspect
 from sqlalchemy import event
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import Session
 
 tztable = notztable = metadata = table = None
 
@@ -698,7 +700,127 @@ class TimePrecisionTest(fixtures.TestBase, AssertsCompiledSQL):
         eq_(t2.c.c6.type.timezone, True)
 
 
-class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
+class ArrayTest(AssertsCompiledSQL, fixtures.TestBase):
+    __dialect__ = 'postgresql'
+
+    def test_array_int_index(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col[3]]),
+            "SELECT x[%(x_1)s] AS anon_1",
+            checkparams={'x_1': 3}
+        )
+
+    def test_array_any(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.any(7, operator=operators.lt)]),
+            "SELECT %(param_1)s < ANY (x) AS anon_1",
+            checkparams={'param_1': 7}
+        )
+
+    def test_array_all(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.all(7, operator=operators.lt)]),
+            "SELECT %(param_1)s < ALL (x) AS anon_1",
+            checkparams={'param_1': 7}
+        )
+
+    def test_array_contains(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.contains(array([4, 5, 6]))]),
+            "SELECT x @> ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
+            "AS anon_1",
+            checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5}
+        )
+
+    def test_array_contained_by(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.contained_by(array([4, 5, 6]))]),
+            "SELECT x <@ ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
+            "AS anon_1",
+            checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5}
+        )
+
+    def test_array_overlap(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.overlap(array([4, 5, 6]))]),
+            "SELECT x && ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
+            "AS anon_1",
+            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(
+            select([col[5:10]]),
+            "SELECT x[%(x_1)s:%(x_2)s] AS anon_1",
+            checkparams={'x_2': 10, 'x_1': 5}
+        )
+
+    def test_array_dim_index(self):
+        col = column('x', postgresql.ARRAY(Integer, dimensions=2))
+        self.assert_compile(
+            select([col[3][5]]),
+            "SELECT x[%(x_1)s][%(param_1)s] AS anon_1",
+            checkparams={'x_1': 3, 'param_1': 5}
+        )
+
+    def test_array_concat(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        literal = array([4, 5])
+
+        self.assert_compile(
+            select([col + literal]),
+            "SELECT x || ARRAY[%(param_1)s, %(param_2)s] AS anon_1",
+            checkparams={'param_1': 4, 'param_2': 5}
+        )
+
+    def test_array_index_map_dimensions(self):
+        col = column('x', postgresql.ARRAY(Integer, dimensions=3))
+        is_(
+            col[5].type._type_affinity, postgresql.ARRAY
+        )
+        eq_(
+            col[5].type.dimensions, 2
+        )
+        is_(
+            col[5][6].type._type_affinity, postgresql.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', postgresql.ARRAY(Integer)),
+            Column('strarr', postgresql.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', 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)
+
+
+class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults):
 
     __only_on__ = 'postgresql'
     __backend__ = True
@@ -828,16 +950,6 @@ class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
                 ), True
         )
 
-    def test_array_getitem_single_type(self):
-        arrtable = self.tables.arrtable
-        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):
-        arrtable = self.tables.arrtable
-        is_(arrtable.c.intarr[1:3].type._type_affinity, postgresql.ARRAY)
-        is_(arrtable.c.strarr[1:3].type._type_affinity, postgresql.ARRAY)
-
     def test_array_getitem_single_exec(self):
         arrtable = self.tables.arrtable
         self._fixture_456(arrtable)
@@ -926,6 +1038,14 @@ class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
             lambda elem: (
                 x for x in elem))
 
+    def test_multi_dim_roundtrip(self):
+        arrtable = self.tables.arrtable
+        testing.db.execute(arrtable.insert(), dimarr=[[1, 2, 3], [4, 5, 6]])
+        eq_(
+            testing.db.scalar(select([arrtable.c.dimarr])),
+            [[-1, 0, 1], [2, 3, 4]]
+        )
+
     def test_array_contained_by_exec(self):
         arrtable = self.tables.arrtable
         with testing.db.connect() as conn:
@@ -1030,12 +1150,72 @@ class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
             set([('1', '2', '3'), ('4', '5', '6'), (('4', '5'), ('6', '7'))])
         )
 
-    def test_dimension(self):
-        arrtable = self.tables.arrtable
-        testing.db.execute(arrtable.insert(), dimarr=[[1, 2, 3], [4, 5, 6]])
+
+class HashableFlagORMTest(fixtures.TestBase):
+    """test the various 'collection' types that they flip the 'hashable' flag
+    appropriately.  [ticket:3499]"""
+
+    __only_on__ = 'postgresql'
+
+    def _test(self, type_, data):
+        Base = declarative_base(metadata=self.metadata)
+
+        class A(Base):
+            __tablename__ = 'a1'
+            id = Column(Integer, primary_key=True)
+            data = Column(type_)
+        Base.metadata.create_all(testing.db)
+        s = Session(testing.db)
+        s.add_all([
+            A(data=elem) for elem in data
+        ])
+        s.commit()
+
         eq_(
-            testing.db.scalar(select([arrtable.c.dimarr])),
-            [[-1, 0, 1], [2, 3, 4]]
+            [(obj.A.id, obj.data) for obj in
+             s.query(A, A.data).order_by(A.id)],
+            list(enumerate(data, 1))
+        )
+
+    @testing.provide_metadata
+    def test_array(self):
+        self._test(
+            postgresql.ARRAY(Text()),
+            [['a', 'b', 'c'], ['d', 'e', 'f']]
+        )
+
+    @testing.requires.hstore
+    @testing.provide_metadata
+    def test_hstore(self):
+        self._test(
+            postgresql.HSTORE(),
+            [
+                {'a': '1', 'b': '2', 'c': '3'},
+                {'d': '4', 'e': '5', 'f': '6'}
+            ]
+        )
+
+    @testing.provide_metadata
+    def test_json(self):
+        self._test(
+            postgresql.JSON(),
+            [
+                {'a': '1', 'b': '2', 'c': '3'},
+                {'d': '4', 'e': {'e1': '5', 'e2': '6'},
+                 'f': {'f1': [9, 10, 11]}}
+            ]
+        )
+
+    @testing.requires.postgresql_jsonb
+    @testing.provide_metadata
+    def test_jsonb(self):
+        self._test(
+            postgresql.JSONB(),
+            [
+                {'a': '1', 'b': '2', 'c': '3'},
+                {'d': '4', 'e': {'e1': '5', 'e2': '6'},
+                 'f': {'f1': [9, 10, 11]}}
+            ]
         )
 
 
@@ -1372,6 +1552,19 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
             {"key1": "value1", "key2": "value2"}
         )
 
+    def test_ret_type_text(self):
+        col = column('x', HSTORE())
+
+        is_(col['foo'].type.__class__, Text)
+
+    def test_ret_type_custom(self):
+        class MyType(types.UserDefinedType):
+            pass
+
+        col = column('x', HSTORE(text_type=MyType))
+
+        is_(col['foo'].type.__class__, MyType)
+
     def test_where_has_key(self):
         self._test_where(
             # hide from 2to3
@@ -2093,19 +2286,59 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase):
             "(test_table.test_column #> %(test_column_1)s) IS NULL"
         )
 
+    def test_path_typing(self):
+        col = column('x', JSON())
+        is_(
+            col['q'].type._type_affinity, JSON
+        )
+        is_(
+            col[('q', )].type._type_affinity, JSON
+        )
+        is_(
+            col['q']['p'].type._type_affinity, JSON
+        )
+        is_(
+            col[('q', 'p')].type._type_affinity, JSON
+        )
+
+    def test_custom_astext_type(self):
+        class MyType(types.UserDefinedType):
+            pass
+
+        col = column('x', JSON(astext_type=MyType))
+
+        is_(
+            col['q'].astext.type.__class__, MyType
+        )
+
+        is_(
+            col[('q', 'p')].astext.type.__class__, MyType
+        )
+
+        is_(
+            col['q']['p'].astext.type.__class__, MyType
+        )
+
     def test_where_getitem_as_text(self):
         self._test_where(
             self.jsoncol['bar'].astext == None,
             "(test_table.test_column ->> %(test_column_1)s) IS NULL"
         )
 
-    def test_where_getitem_as_cast(self):
+    def test_where_getitem_astext_cast(self):
         self._test_where(
-            self.jsoncol['bar'].cast(Integer) == 5,
+            self.jsoncol['bar'].astext.cast(Integer) == 5,
             "CAST(test_table.test_column ->> %(test_column_1)s AS INTEGER) "
             "= %(param_1)s"
         )
 
+    def test_where_getitem_json_cast(self):
+        self._test_where(
+            self.jsoncol['bar'].cast(Integer) == 5,
+            "CAST(test_table.test_column -> %(test_column_1)s AS INTEGER) "
+            "= %(param_1)s"
+        )
+
     def test_where_path_as_text(self):
         self._test_where(
             self.jsoncol[("foo", 1)].astext == None,
@@ -2144,6 +2377,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
             {'name': 'r3', 'data': {"k1": "r3v1", "k2": "r3v2"}},
             {'name': 'r4', 'data': {"k1": "r4v1", "k2": "r4v2"}},
             {'name': 'r5', 'data': {"k1": "r5v1", "k2": "r5v2", "k3": 5}},
+            {'name': 'r6', 'data': {"k1": {"r6v1": {'subr': [1, 2, 3]}}}},
         )
 
     def _assert_data(self, compare, column='data'):
@@ -2309,12 +2543,25 @@ class JSONRoundTripTest(fixtures.TablesTest):
         engine = testing.db
         self._fixture_data(engine)
         data_table = self.tables.data_table
+
         result = engine.execute(
-            select([data_table.c.data]).where(
-                data_table.c.data[('k1',)].astext == 'r3v1'
+            select([data_table.c.name]).where(
+                data_table.c.data[('k1', 'r6v1', 'subr')].astext == "[1, 2, 3]"
             )
-        ).first()
-        eq_(result, ({'k1': 'r3v1', 'k2': 'r3v2'},))
+        )
+        eq_(result.scalar(), 'r6')
+
+    def test_multi_index_query(self):
+        engine = testing.db
+        self._fixture_data(engine)
+        data_table = self.tables.data_table
+
+        result = engine.execute(
+            select([data_table.c.name]).where(
+                data_table.c.data['k1']['r6v1']['subr'].astext == "[1, 2, 3]"
+            )
+        )
+        eq_(result.scalar(), 'r6')
 
     def test_query_returned_as_text(self):
         engine = testing.db
@@ -2330,7 +2577,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
         self._fixture_data(engine)
         data_table = self.tables.data_table
         result = engine.execute(
-            select([data_table.c.data['k3'].cast(Integer)]).where(
+            select([data_table.c.data['k3'].astext.cast(Integer)]).where(
                 data_table.c.name == 'r5')
         ).first()
         assert isinstance(result[0], int)
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 55af023b1..3ed2e7d7a 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -1960,13 +1960,6 @@ class FilterTest(QueryTest, AssertsCompiledSQL):
             sess.query(User). \
             filter(User.addresses.any(email_address='fred@fred.com')).all()
 
-        # test that any() doesn't overcorrelate
-        assert [User(id=7), User(id=8)] == \
-            sess.query(User).join("addresses"). \
-            filter(
-                ~User.addresses.any(
-                    Address.email_address == 'fred@fred.com')).all()
-
         # test that the contents are not adapted by the aliased join
         assert [User(id=7), User(id=8)] == \
             sess.query(User).join("addresses", aliased=True). \
@@ -1978,6 +1971,18 @@ class FilterTest(QueryTest, AssertsCompiledSQL):
             sess.query(User).outerjoin("addresses", aliased=True). \
             filter(~User.addresses.any()).all()
 
+    def test_any_doesnt_overcorrelate(self):
+        User, Address = self.classes.User, self.classes.Address
+
+        sess = create_session()
+
+        # test that any() doesn't overcorrelate
+        assert [User(id=7), User(id=8)] == \
+            sess.query(User).join("addresses"). \
+            filter(
+                ~User.addresses.any(
+                    Address.email_address == 'fred@fred.com')).all()
+
     def test_has(self):
         Dingaling, User, Address = (
             self.classes.Dingaling, self.classes.User, self.classes.Address)
@@ -2190,6 +2195,42 @@ class FilterTest(QueryTest, AssertsCompiledSQL):
         )
 
 
+class HasMapperEntitiesTest(QueryTest):
+    def test_entity(self):
+        User = self.classes.User
+        s = Session()
+
+        q = s.query(User)
+
+        assert q._has_mapper_entities
+
+    def test_cols(self):
+        User = self.classes.User
+        s = Session()
+
+        q = s.query(User.id)
+
+        assert not q._has_mapper_entities
+
+    def test_cols_set_entities(self):
+        User = self.classes.User
+        s = Session()
+
+        q = s.query(User.id)
+
+        q._set_entities(User)
+        assert q._has_mapper_entities
+
+    def test_entity_set_entities(self):
+        User = self.classes.User
+        s = Session()
+
+        q = s.query(User)
+
+        q._set_entities(User.id)
+        assert not q._has_mapper_entities
+
+
 class SetOpsTest(QueryTest, AssertsCompiledSQL):
     __dialect__ = 'default'
 
diff --git a/test/orm/test_unitofworkv2.py b/test/orm/test_unitofworkv2.py
index 9e9f400be..4ba5d6fbf 100644
--- a/test/orm/test_unitofworkv2.py
+++ b/test/orm/test_unitofworkv2.py
@@ -1954,3 +1954,4 @@ class TypeWoBoolTest(fixtures.MappedTest, testing.AssertsExecutionResults):
         eq_(
             s.query(Thing.value).scalar().text, "foo"
         )
+
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index bb4cb1bf1..fbbdd7b62 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -12,8 +12,9 @@ from sqlalchemy import exc
 from sqlalchemy.engine import default
 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
+    Boolean, NullType, MatchType, Indexable
 from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \
     sqlite, mssql
 from sqlalchemy import util
@@ -21,7 +22,6 @@ import datetime
 import collections
 from sqlalchemy import text, literal_column
 from sqlalchemy import and_, not_, between, or_
-from sqlalchemy.sql import true, false, null
 
 
 class LoopOperate(operators.ColumnOperators):
@@ -577,6 +577,200 @@ class ExtensionOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         )
 
 
+class IndexableTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    def setUp(self):
+        class MyTypeCompiler(compiler.GenericTypeCompiler):
+            def visit_mytype(self, type, **kw):
+                return "MYTYPE"
+
+            def visit_myothertype(self, type, **kw):
+                return "MYOTHERTYPE"
+
+        class MyCompiler(compiler.SQLCompiler):
+            def visit_slice(self, element, **kw):
+                return "%s:%s" % (
+                    self.process(element.start, **kw),
+                    self.process(element.stop, **kw),
+                )
+
+            def visit_getitem_binary(self, binary, operator, **kw):
+                return "%s[%s]" % (
+                    self.process(binary.left, **kw),
+                    self.process(binary.right, **kw)
+                )
+
+        class MyDialect(default.DefaultDialect):
+            statement_compiler = MyCompiler
+            type_compiler = MyTypeCompiler
+
+        class MyType(Indexable, TypeEngine):
+            __visit_name__ = 'mytype'
+
+            def __init__(self, zero_indexes=False, dimensions=1):
+                if zero_indexes:
+                    self.zero_indexes = zero_indexes
+                self.dimensions = dimensions
+
+            class Comparator(Indexable.Comparator):
+                def _setup_getitem(self, index):
+                    if isinstance(index, slice):
+                        return_type = self.type
+                    elif self.type.dimensions is None or \
+                            self.type.dimensions == 1:
+                        return_type = Integer()
+                    else:
+                        adapt_kw = {'dimensions': self.type.dimensions - 1}
+                        # this is also testing the behavior of adapt()
+                        # that we can pass kw that override constructor kws.
+                        # required a small change to util.constructor_copy().
+                        return_type = self.type.adapt(
+                            self.type.__class__, **adapt_kw)
+
+                    return operators.getitem, index, return_type
+            comparator_factory = Comparator
+
+        self.MyType = MyType
+        self.__dialect__ = MyDialect()
+
+    def test_setup_getitem_w_dims(self):
+        """test the behavior of the _setup_getitem() method given a simple
+        'dimensions' scheme - this is identical to postgresql.ARRAY."""
+
+        col = Column('x', self.MyType(dimensions=3))
+
+        is_(
+            col[5].type._type_affinity, self.MyType
+        )
+        eq_(
+            col[5].type.dimensions, 2
+        )
+        is_(
+            col[5][6].type._type_affinity, self.MyType
+        )
+        eq_(
+            col[5][6].type.dimensions, 1
+        )
+        is_(
+            col[5][6][7].type._type_affinity, Integer
+        )
+
+    def test_getindex_literal(self):
+
+        col = Column('x', self.MyType())
+
+        self.assert_compile(
+            col[5],
+            "x[:x_1]",
+            checkparams={'x_1': 5}
+        )
+
+    def test_getindex_sqlexpr(self):
+
+        col = Column('x', self.MyType())
+        col2 = Column('y', Integer())
+
+        self.assert_compile(
+            col[col2],
+            "x[y]",
+            checkparams={}
+        )
+
+        self.assert_compile(
+            col[col2 + 8],
+            "x[(y + :y_1)]",
+            checkparams={'y_1': 8}
+        )
+
+    def test_getslice_literal(self):
+
+        col = Column('x', self.MyType())
+
+        self.assert_compile(
+            col[5:6],
+            "x[:x_1::x_2]",
+            checkparams={'x_1': 5, 'x_2': 6}
+        )
+
+    def test_getslice_sqlexpr(self):
+
+        col = Column('x', self.MyType())
+        col2 = Column('y', Integer())
+
+        self.assert_compile(
+            col[col2:col2 + 5],
+            "x[y:y + :y_1]",
+            checkparams={'y_1': 5}
+        )
+
+    def test_getindex_literal_zeroind(self):
+
+        col = Column('x', self.MyType(zero_indexes=True))
+
+        self.assert_compile(
+            col[5],
+            "x[:x_1]",
+            checkparams={'x_1': 6}
+        )
+
+    def test_getindex_sqlexpr_zeroind(self):
+
+        col = Column('x', self.MyType(zero_indexes=True))
+        col2 = Column('y', Integer())
+
+        self.assert_compile(
+            col[col2],
+            "x[(y + :y_1)]",
+            checkparams={'y_1': 1}
+        )
+
+        self.assert_compile(
+            col[col2 + 8],
+            "x[(y + :y_1 + :param_1)]",
+            checkparams={'y_1': 8, 'param_1': 1}
+        )
+
+    def test_getslice_literal_zeroind(self):
+
+        col = Column('x', self.MyType(zero_indexes=True))
+
+        self.assert_compile(
+            col[5:6],
+            "x[:x_1::x_2]",
+            checkparams={'x_1': 6, 'x_2': 7}
+        )
+
+    def test_getslice_sqlexpr_zeroind(self):
+
+        col = Column('x', self.MyType(zero_indexes=True))
+        col2 = Column('y', Integer())
+
+        self.assert_compile(
+            col[col2:col2 + 5],
+            "x[y + :y_1:y + :y_2 + :param_1]",
+            checkparams={'y_1': 1, 'y_2': 5, 'param_1': 1}
+        )
+
+    def test_override_operators(self):
+        special_index_op = operators.custom_op('->')
+
+        class MyOtherType(Indexable, TypeEngine):
+            __visit_name__ = 'myothertype'
+
+            class Comparator(TypeEngine.Comparator):
+
+                def _adapt_expression(self, op, other_comparator):
+                    return special_index_op, MyOtherType()
+
+            comparator_factory = Comparator
+
+        col = Column('x', MyOtherType())
+        self.assert_compile(
+            col[5],
+            "x -> :x_1",
+            checkparams={'x_1': 5}
+        )
+
+
 class BooleanEvalTest(fixtures.TestBase, testing.AssertsCompiledSQL):
 
     """test standalone booleans being wrapped in an AsBoolean, as well
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index 2545dec59..0ab8ef451 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -193,6 +193,22 @@ class AdaptTest(fixtures.TestBase):
                 t1 = typ()
             repr(t1)
 
+    def test_adapt_constructor_copy_override_kw(self):
+        """test that adapt() can accept kw args that override
+        the state of the original object.
+
+        This essentially is testing the behavior of util.constructor_copy().
+
+        """
+        t1 = String(length=50, convert_unicode=False)
+        t2 = t1.adapt(Text, convert_unicode=True)
+        eq_(
+            t2.length, 50
+        )
+        eq_(
+            t2.convert_unicode, True
+        )
+
 
 class TypeAffinityTest(fixtures.TestBase):