From abb10856dcea07ca4d38d28df4e493d11d8fd345 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Thu, 3 Apr 2008 16:34:03 +0000
Subject: - case() interprets the "THEN" expressions as values by default,
 meaning case([(x==y, "foo")]) will interpret "foo" as a bound value, not a
 SQL expression. use text(expr) for literal SQL expressions in this case. For
 the criterion itself, these may be literal strings only if the "value"
 keyword is present, otherwise SA will force explicit usage of either text()
 or literal().

---
 test/sql/case_statement.py | 35 +++++++++++++++++++++++------------
 1 file changed, 23 insertions(+), 12 deletions(-)

(limited to 'test/sql')

diff --git a/test/sql/case_statement.py b/test/sql/case_statement.py
index 730517b21..257298c8e 100644
--- a/test/sql/case_statement.py
+++ b/test/sql/case_statement.py
@@ -2,10 +2,11 @@ import testenv; testenv.configure_for_tests()
 import sys
 from sqlalchemy import *
 from testlib import *
-from sqlalchemy import util
+from sqlalchemy import util, exceptions
+from sqlalchemy.sql import table, column
 
 
-class CaseTest(TestBase):
+class CaseTest(TestBase, AssertsCompiledSQL):
 
     def setUpAll(self):
         metadata = MetaData(testing.db)
@@ -30,9 +31,9 @@ class CaseTest(TestBase):
     def testcase(self):
         inner = select([case([
                 [info_table.c.pk < 3,
-                        literal('lessthan3', type_=String)],
+                        'lessthan3'],
         [and_(info_table.c.pk >= 3, info_table.c.pk < 7),
-                        literal('gt3', type_=String)]]).label('x'),
+                        'gt3']]).label('x'),
         info_table.c.pk, info_table.c.info],
                 from_obj=[info_table]).alias('q_inner')
 
@@ -69,9 +70,9 @@ class CaseTest(TestBase):
 
         w_else = select([case([
                 [info_table.c.pk < 3,
-                        literal(3, type_=Integer)],
+                        3],
         [and_(info_table.c.pk >= 3, info_table.c.pk < 6),
-                        literal(6, type_=Integer)]],
+                        6]],
                 else_ = 0).label('x'),
         info_table.c.pk, info_table.c.info],
                 from_obj=[info_table]).alias('q_inner')
@@ -87,12 +88,21 @@ class CaseTest(TestBase):
             (0, 6, 'pk_6_data')
         ]
 
+    def test_literal_interpretation(self):
+        t = table('test', column('col1'))
+        
+        self.assertRaises(exceptions.ArgumentError, case, [("x", "y")])
+        
+        self.assert_compile(case([("x", "y")], value=t.c.col1), "CASE test.col1 WHEN :param_1 THEN :param_2 END")
+        self.assert_compile(case([(t.c.col1==7, "y")], else_="z"), "CASE WHEN (test.col1 = :test_col1_1) THEN :param_1 ELSE :param_2 END")
+
+        
     @testing.fails_on('maxdb')
     def testcase_with_dict(self):
         query = select([case({
-                    info_table.c.pk < 3: literal('lessthan3'),
-                    info_table.c.pk >= 3: literal('gt3'),
-                }, else_=literal('other')),
+                    info_table.c.pk < 3: 'lessthan3',
+                    info_table.c.pk >= 3: 'gt3',
+                }, else_='other'),
                 info_table.c.pk, info_table.c.info
             ],
             from_obj=[info_table])
@@ -106,13 +116,14 @@ class CaseTest(TestBase):
         ]
 
         simple_query = select([case({
-                    1: literal('one'),
-                    2: literal('two'),
-                }, value=info_table.c.pk, else_=literal('other')),
+                    1: 'one',
+                    2: 'two',
+                }, value=info_table.c.pk, else_='other'),
                 info_table.c.pk
             ],
             whereclause=info_table.c.pk < 4,
             from_obj=[info_table])
+        
         assert simple_query.execute().fetchall() == [
             ('one', 1),
             ('two', 2),
-- 
cgit v1.2.1