From 6397a4ff4bce537487a3b30552622544868da9a0 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 12 Nov 2012 15:48:40 -0500 Subject: [PATCH] Fixed bug in type_coerce() whereby typing information could be lost if the statement were used as a subquery inside of another statement, as well as other similar situations. Among other things, would cause typing information to be lost when the Oracle/mssql dialects would apply limit/offset wrappings. [ticket:2603] --- doc/build/changelog/changelog_08.rst | 11 +++++++++++ lib/sqlalchemy/dialects/oracle/__init__.py | 2 +- lib/sqlalchemy/dialects/oracle/base.py | 4 ++-- lib/sqlalchemy/sql/expression.py | 2 ++ test/dialect/test_oracle.py | 11 ++++++++++- test/orm/test_query.py | 5 +++-- test/sql/test_selectable.py | 9 +++++++++ test/sql/test_types.py | 7 ++++++- 8 files changed, 44 insertions(+), 7 deletions(-) diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst index 0d552d5463..0571798ce7 100644 --- a/doc/build/changelog/changelog_08.rst +++ b/doc/build/changelog/changelog_08.rst @@ -6,6 +6,17 @@ .. changelog:: :version: 0.8.0b2 + .. change:: + :tags: sql, bug + :tickets: 2603 + + Fixed bug in type_coerce() whereby typing information + could be lost if the statement were used as a subquery + inside of another statement, as well as other similar + situations. Among other things, would cause + typing information to be lost when the Oracle/mssql dialects + would apply limit/offset wrappings. + .. change:: :tags: orm, bug :tickets: 2602 diff --git a/lib/sqlalchemy/dialects/oracle/__init__.py b/lib/sqlalchemy/dialects/oracle/__init__.py index 34192530a5..a4235f0235 100644 --- a/lib/sqlalchemy/dialects/oracle/__init__.py +++ b/lib/sqlalchemy/dialects/oracle/__init__.py @@ -12,7 +12,7 @@ from sqlalchemy.dialects.oracle.base import \ VARCHAR, NVARCHAR, CHAR, DATE, DATETIME, NUMBER,\ BLOB, BFILE, CLOB, NCLOB, TIMESTAMP, RAW,\ FLOAT, DOUBLE_PRECISION, LONG, dialect, INTERVAL,\ - VARCHAR2, NVARCHAR2, ROWID + VARCHAR2, NVARCHAR2, ROWID, dialect __all__ = ( diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 7b1470f636..7a4d835c9a 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -586,7 +586,7 @@ class OracleCompiler(compiler.SQLCompiler): limitselect._is_wrapper = True offsetselect = sql.select( - [c for c in limitselect.c if c.key!='ora_rn']) + [c for c in limitselect.c if c.key != 'ora_rn']) offsetselect._oracle_visit = True offsetselect._is_wrapper = True @@ -594,7 +594,7 @@ class OracleCompiler(compiler.SQLCompiler): if not self.dialect.use_binds_for_limits: offset_value = sql.literal_column("%d" % offset_value) offsetselect.append_whereclause( - sql.literal_column("ora_rn")>offset_value) + sql.literal_column("ora_rn") > offset_value) offsetselect.for_update = select.for_update select = offsetselect diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 91f10cf3c1..1d3be7de17 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -4256,6 +4256,8 @@ class Label(ColumnElement): e = self.element._make_proxy(selectable, name=name if name else self.name) e._proxies.append(self) + if self._type is not None: + e.type = self._type return e class ColumnClause(Immutable, ColumnElement): diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index edb7be8402..3e7ebf0121 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -206,9 +206,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ':ROWNUM_1) WHERE ora_rn > :ora_rn_1 FOR ' 'UPDATE') + def test_limit_preserves_typing_information(self): + class MyType(TypeDecorator): + impl = Integer + + stmt = select([type_coerce(column('x'), MyType).label('foo')]).limit(1) + dialect = oracle.dialect() + compiled = stmt.compile(dialect=dialect) + assert isinstance(compiled.result_map['foo'][-1], MyType) + def test_use_binds_for_limits_disabled(self): t = table('sometable', column('col1'), column('col2')) - dialect = oracle.OracleDialect(use_binds_for_limits = False) + dialect = oracle.OracleDialect(use_binds_for_limits=False) self.assert_compile(select([t]).limit(10), "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, " diff --git a/test/orm/test_query.py b/test/orm/test_query.py index 137fdcd95d..9bf728de22 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -133,9 +133,10 @@ class RowTupleTest(QueryTest): mapper(User, users) s = Session() - row = s.\ + q = s.\ query(User, type_coerce(users.c.id, MyType).label('foo')).\ - filter(User.id == 7).first() + filter(User.id == 7) + row = q.first() eq_( row, (User(id=7), [7]) ) diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index 53c9018cde..65dc654702 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -228,6 +228,15 @@ class SelectableTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiled "table1.col3, table1.colx FROM table1) AS anon_1" ) + def test_type_coerce_preserve_subq(self): + class MyType(TypeDecorator): + impl = Integer + + stmt = select([type_coerce(column('x'), MyType).label('foo')]) + stmt2 = stmt.select() + assert isinstance(stmt._raw_columns[0].type, MyType) + assert isinstance(stmt.c.foo.type, MyType) + assert isinstance(stmt2.c.foo.type, MyType) def test_select_on_table(self): sel = select([table1, table2], use_labels=True) diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 5f80e79471..b229694481 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -408,7 +408,7 @@ class UserDefinedTest(fixtures.TablesTest, AssertsCompiledSQL): t = Table('t', metadata, Column('data', String(50))) metadata.create_all() - t.insert().values(data=type_coerce('d1BIND_OUT',MyType)).execute() + t.insert().values(data=type_coerce('d1BIND_OUT', MyType)).execute() eq_( select([type_coerce(t.c.data, MyType)]).execute().fetchall(), @@ -420,6 +420,11 @@ class UserDefinedTest(fixtures.TablesTest, AssertsCompiledSQL): [('d1', 'd1BIND_OUT')] ) + eq_( + select([t.c.data, type_coerce(t.c.data, MyType)]).select().execute().fetchall(), + [('d1', 'd1BIND_OUT')] + ) + eq_( select([t.c.data, type_coerce(t.c.data, MyType)]).\ where(type_coerce(t.c.data, MyType) == 'd1BIND_OUT').\ -- 2.47.3