- The type/expression system now does a more complete job

of determining the return type from an expression
as well as the adaptation of the Python operator into
a SQL operator, based on the full left/right/operator
of the given expression.  In particular
the date/time/interval system created for Postgresql
EXTRACT in [ticket:1647] has now been generalized into
the type system.   The previous behavior which often
occured of an expression "column + literal" forcing
the type of "literal" to be the same as that of "column"
will now usually not occur - the type of
"literal" is first derived from the Python type of the
literal, assuming standard native Python types + date
types, before falling back to that of the known type
on the other side of the expression.  Also part
of [ticket:1683].

2 files changed, 60 insertions, 5 deletions

diff --git a/test/sql/test_select.py b/test/sql/test_select.py
index 766ce8e9b..657509d65 100644
--- a/test/sql/test_select.py
+++ b/test/sql/test_select.py
@@ -645,7 +645,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
             (table1.c.myid.match('somstr'), "CONTAINS (mytable.myid, :myid_1)", oracle.dialect()),            
         ]:
             self.assert_compile(expr, check, dialect=dialect)
-        
+
     def test_composed_string_comparators(self):
         self.assert_compile(
             table1.c.name.contains('jo'), "mytable.name LIKE '%%' || :name_1 || '%%'" , checkparams = {'name_1': u'jo'},
@@ -1377,7 +1377,7 @@ EXISTS (select yay from foo where boo = lar)",
         
         assert [str(c) for c in s.c] == ["id", "hoho"]
 
-        
+    
     @testing.emits_warning('.*empty sequence.*')
     def test_in(self):
         self.assert_compile(table1.c.myid.in_(['a']),
@@ -1547,7 +1547,7 @@ EXISTS (select yay from foo where boo = lar)",
             "SELECT dt.date FROM dt WHERE dt.date BETWEEN :date_1 AND :date_2", checkparams={'date_1':datetime.date(2006,6,1), 'date_2':datetime.date(2006,6,5)})
 
         self.assert_compile(table.select(sql.between(table.c.date, datetime.date(2006,6,1), datetime.date(2006,6,5))),
-            "SELECT dt.date FROM dt WHERE dt.date BETWEEN :param_1 AND :param_2", checkparams={'param_1':datetime.date(2006,6,1), 'param_2':datetime.date(2006,6,5)})
+            "SELECT dt.date FROM dt WHERE dt.date BETWEEN :date_1 AND :date_2", checkparams={'date_1':datetime.date(2006,6,1), 'date_2':datetime.date(2006,6,5)})
 
     def test_operator_precedence(self):
         table = Table('op', metadata,
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index 0dcf4470f..fd957c6e5 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -57,6 +57,14 @@ class AdaptTest(TestBase):
             
 class TypeAffinityTest(TestBase):
     def test_type_affinity(self):
+        for type_, affin in [
+            (String(), String),
+            (VARCHAR(), String),
+            (Date(), Date),
+            (LargeBinary(), types._Binary)
+        ]:
+            eq_(type_._type_affinity, affin)
+            
         for t1, t2, comp in [
             (Integer(), SmallInteger(), True),
             (Integer(), String(), False),
@@ -536,7 +544,7 @@ class BinaryTest(TestBase, AssertsExecutionResults):
 class ExpressionTest(TestBase, AssertsExecutionResults):
     @classmethod
     def setup_class(cls):
-        global test_table, meta
+        global test_table, meta, MyCustomType
 
         class MyCustomType(types.UserDefinedType):
             def get_col_spec(self):
@@ -570,7 +578,10 @@ class ExpressionTest(TestBase, AssertsExecutionResults):
     def test_control(self):
         assert testing.db.execute("select avalue from test").scalar() == 250
 
-        assert test_table.select().execute().fetchall() == [(1, 'somedata', datetime.date(2007, 10, 15), 25)]
+        eq_(
+            test_table.select().execute().fetchall(),
+            [(1, 'somedata', datetime.date(2007, 10, 15), 25)]
+        )
 
     def test_bind_adapt(self):
         expr = test_table.c.atimestamp == bindparam("thedate")
@@ -597,6 +608,12 @@ class ExpressionTest(TestBase, AssertsExecutionResults):
         expr = test_table.c.avalue + 40
         assert expr.type.__class__ is test_table.c.avalue.type.__class__
 
+        # value here is calculated as (250 - 40) / 10 = 21
+        # because "40" is an integer, not an "avalue"
+        assert testing.db.execute(select([expr.label('foo')])).scalar() == 21
+
+        expr = test_table.c.avalue + literal(40, type_=MyCustomType)
+        
         # + operator converted to -
         # value is calculated as: (250 - (40 * 10)) / 10 == -15
         assert testing.db.execute(select([expr.label('foo')])).scalar() == -15
@@ -604,6 +621,44 @@ class ExpressionTest(TestBase, AssertsExecutionResults):
         # this one relies upon anonymous labeling to assemble result
         # processing rules on the column.
         assert testing.db.execute(select([expr])).scalar() == -15
+    
+    def test_bind_typing(self):
+        from sqlalchemy.sql import column
+        
+        class MyFoobarType(types.UserDefinedType):
+            pass
+        
+        class Foo(object):
+            pass
+        
+        # unknown type + integer, right hand bind
+        # is an Integer
+        expr = column("foo", MyFoobarType) + 5
+        assert expr.right.type._type_affinity is types.Integer
+
+        # unknown type + unknown, right hand bind
+        # coerces to the left
+        expr = column("foo", MyFoobarType) + Foo()
+        assert expr.right.type._type_affinity is MyFoobarType
+        
+        # including for non-commutative ops
+        expr = column("foo", MyFoobarType) - Foo()
+        assert expr.right.type._type_affinity is MyFoobarType
+
+        expr = column("foo", MyFoobarType) - datetime.date(2010, 8, 25)
+        assert expr.right.type._type_affinity is types.Date
+        
+    def test_date_coercion(self):
+        from sqlalchemy.sql import column
+        
+        expr = column('bar', types.NULLTYPE) - column('foo', types.TIMESTAMP)
+        eq_(expr.type._type_affinity, types.NullType)
+        
+        expr = func.sysdate() - column('foo', types.TIMESTAMP)
+        eq_(expr.type._type_affinity, types.Interval)
+
+        expr = func.current_date() - column('foo', types.TIMESTAMP)
+        eq_(expr.type._type_affinity, types.Interval)
         
     def test_distinct(self):
         s = select([distinct(test_table.c.avalue)])