Repository: kayak/pypika
Pull request #10 (2016-08-23 15:31:12)

Moved between and isin functions from Field to Term


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 7b4a99a..2cf124c 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -73,6 +73,14 @@ class Term(object):
     def regex(self, pattern):
         return BasicCriterion(Matching.regex, self, self._wrap(pattern))
 
+    def between(self, lower, upper):
+        return BetweenCriterion(self, self._wrap(lower), self._wrap(upper))
+
+    def isin(self, arg):
+        if isinstance(arg, (list, tuple, set)):
+            return ContainsCriterion(self, ListField([self._wrap(value) for value in arg]))
+        return ContainsCriterion(self, arg)
+
     def bin_regex(self, pattern):
         return BasicCriterion(Matching.bin_regex, self, self._wrap(pattern))
 
@@ -132,6 +140,11 @@ class Term(object):
     def __le__(self, other):
         return BasicCriterion(Equality.lte, self, self._wrap(other))
 
+    def __getitem__(self, item):
+        if not isinstance(item, slice):
+            raise TypeError("Field' object is not subscriptable")
+        return self.between(item.start, item.stop)
+
     def __str__(self):
         return self.get_sql()
 
@@ -166,14 +179,6 @@ class Field(Term):
         self.name = name
         self.table = table
 
-    def between(self, lower, upper):
-        return BetweenCriterion(self, self._wrap(lower), self._wrap(upper))
-
-    def isin(self, arg):
-        if isinstance(arg, (list, tuple, set)):
-            return ContainsCriterion(self, ListField([self._wrap(value) for value in arg]))
-        return ContainsCriterion(self, arg)
-
     @builder
     def for_(self, table):
         """
@@ -187,11 +192,6 @@ class Field(Term):
         self.table = table
         return self
 
-    def __getitem__(self, item):
-        if not isinstance(item, slice):
-            raise TypeError("Field' object is not subscriptable")
-        return self.between(item.start, item.stop)
-
     def get_sql(self, with_alias=False, with_quotes=True, **kwargs):
         quote_char = '\"' if with_quotes else ''
 


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 22e24af..89c7c5d 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -18,16 +18,16 @@ class CriterionTests(unittest.TestCase):
         c2 = Field('foo', table=self.t).eq(0)
         c3 = Field('foo', table=self.t) == -1
 
-        self.assertEqual('\"foo\"=1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=0', str(c2))
-        self.assertEqual('\"t0\".\"foo\"=-1', str(c3))
+        self.assertEqual('"foo"=1', str(c1))
+        self.assertEqual('"t0"."foo"=0', str(c2))
+        self.assertEqual('"t0"."foo"=-1', str(c3))
 
     def test__criterion_eq_decimal(self):
         c1 = Field('foo') == 1.0
         c2 = Field('foo', table=self.t).eq(0.5)
 
-        self.assertEqual('\"foo\"=1.0', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=0.5', str(c2))
+        self.assertEqual('"foo"=1.0', str(c1))
+        self.assertEqual('"t0"."foo"=0.5', str(c2))
 
     def test__criterion_eq_bool(self):
         c1 = Field('foo') == True
@@ -35,209 +35,209 @@ class CriterionTests(unittest.TestCase):
         c3 = Field('foo') == False
         c4 = Field('foo', table=self.t).eq(False)
 
-        self.assertEqual('\"foo\"=true', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=true', str(c2))
-        self.assertEqual('\"foo\"=false', str(c3))
-        self.assertEqual('\"t0\".\"foo\"=false', str(c4))
+        self.assertEqual('"foo"=true', str(c1))
+        self.assertEqual('"t0"."foo"=true', str(c2))
+        self.assertEqual('"foo"=false', str(c3))
+        self.assertEqual('"t0"."foo"=false', str(c4))
 
     def test__criterion_eq_str(self):
         c1 = Field('foo') == 'abc'
         c2 = Field('foo', table=self.t).eq('abc')
 
-        self.assertEqual("\"foo\"='abc'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"='abc'", str(c2))
+        self.assertEqual('"foo"=\'abc\'', str(c1))
+        self.assertEqual('"t0"."foo"=\'abc\'', str(c2))
 
     def test__criterion_eq_date(self):
         c1 = Field('foo') == date(2000, 1, 1)
         c2 = Field('foo', table=self.t).eq(date(2000, 1, 1))
 
-        self.assertEqual("\"foo\"='2000-01-01'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"='2000-01-01'", str(c2))
+        self.assertEqual('"foo"=\'2000-01-01\'', str(c1))
+        self.assertEqual('"t0"."foo"=\'2000-01-01\'', str(c2))
 
     def test__criterion_eq_datetime(self):
         c1 = Field('foo') == datetime(2000, 1, 1, 12, 30, 55)
         c2 = Field('foo', table=self.t).eq(datetime(2000, 1, 1, 12, 30, 55))
 
-        self.assertEqual("\"foo\"='2000-01-01T12:30:55'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"='2000-01-01T12:30:55'", str(c2))
+        self.assertEqual('"foo"=\'2000-01-01T12:30:55\'', str(c1))
+        self.assertEqual('"t0"."foo"=\'2000-01-01T12:30:55\'', str(c2))
 
     def test__criterion_eq_right(self):
         c1 = 1 == Field('foo')
         c2 = -1 == Field('foo', table=self.t)
 
-        self.assertEqual('\"foo\"=1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=-1', str(c2))
+        self.assertEqual('"foo"=1', str(c1))
+        self.assertEqual('"t0"."foo"=-1', str(c2))
 
     def test__criterion_is_null(self):
         c1 = Field('foo').isnull()
         c2 = Field('foo', table=self.t).isnull()
 
-        self.assertEqual('\"foo\" IS NULL', str(c1))
-        self.assertEqual('\"t0\".\"foo\" IS NULL', str(c2))
+        self.assertEqual('"foo" IS NULL', str(c1))
+        self.assertEqual('"t0"."foo" IS NULL', str(c2))
 
     def test__criterion_is_not_null(self):
         c1 = Field('foo').notnull()
         c2 = Field('foo', table=self.t).notnull()
 
-        self.assertEqual('\"foo\" IS NOT NULL', str(c1))
-        self.assertEqual('\"t0\".\"foo\" IS NOT NULL', str(c2))
+        self.assertEqual('"foo" IS NOT NULL', str(c1))
+        self.assertEqual('"t0"."foo" IS NOT NULL', str(c2))
 
     def test__criterion_ne_number(self):
         c1 = Field('foo') != 1
         c2 = Field('foo', table=self.t).ne(0)
         c3 = Field('foo') != -1
 
-        self.assertEqual('\"foo\"<>1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"<>0', str(c2))
-        self.assertEqual('\"foo\"<>-1', str(c3))
+        self.assertEqual('"foo"<>1', str(c1))
+        self.assertEqual('"t0"."foo"<>0', str(c2))
+        self.assertEqual('"foo"<>-1', str(c3))
 
     def test__criterion_ne_str(self):
         c1 = Field('foo') != 'abc'
         c2 = Field('foo', table=self.t).ne('abc')
 
-        self.assertEqual("\"foo\"<>'abc'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<>'abc'", str(c2))
+        self.assertEqual('"foo"<>\'abc\'', str(c1))
+        self.assertEqual('"t0"."foo"<>\'abc\'', str(c2))
 
     def test__criterion_ne_date(self):
         c1 = Field('foo') != date(2000, 1, 1)
         c2 = Field('foo', table=self.t).ne(date(2000, 1, 1))
 
-        self.assertEqual("\"foo\"<>'2000-01-01'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<>'2000-01-01'", str(c2))
+        self.assertEqual('"foo"<>\'2000-01-01\'', str(c1))
+        self.assertEqual('"t0"."foo"<>\'2000-01-01\'', str(c2))
 
     def test__criterion_ne_datetime(self):
         c1 = Field('foo') != datetime(2000, 1, 1, 12, 30, 55)
         c2 = Field('foo', table=self.t).ne(datetime(2000, 1, 1, 12, 30, 55))
 
-        self.assertEqual("\"foo\"<>'2000-01-01T12:30:55'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<>'2000-01-01T12:30:55'", str(c2))
+        self.assertEqual('"foo"<>\'2000-01-01T12:30:55\'', str(c1))
+        self.assertEqual('"t0"."foo"<>\'2000-01-01T12:30:55\'', str(c2))
 
     def test__criterion_ne_right(self):
         c1 = 1 != Field('foo')
         c2 = -1 != Field('foo', table=self.t)
 
-        self.assertEqual('\"foo\"<>1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"<>-1', str(c2))
+        self.assertEqual('"foo"<>1', str(c1))
+        self.assertEqual('"t0"."foo"<>-1', str(c2))
 
     def test__criterion_lt_number(self):
         c1 = Field('foo') < 1
         c2 = Field('foo', table=self.t).lt(0)
         c3 = Field('foo') < -1
 
-        self.assertEqual('\"foo\"<1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"<0', str(c2))
-        self.assertEqual('\"foo\"<-1', str(c3))
+        self.assertEqual('"foo"<1', str(c1))
+        self.assertEqual('"t0"."foo"<0', str(c2))
+        self.assertEqual('"foo"<-1', str(c3))
 
     def test__criterion_lt_date(self):
         c1 = Field('foo') < date(2000, 1, 1)
         c2 = Field('foo', table=self.t).lt(date(2000, 1, 1))
 
-        self.assertEqual("\"foo\"<'2000-01-01'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<'2000-01-01'", str(c2))
+        self.assertEqual('"foo"<\'2000-01-01\'', str(c1))
+        self.assertEqual('"t0"."foo"<\'2000-01-01\'', str(c2))
 
     def test__criterion_lt_datetime(self):
         c1 = Field('foo') < datetime(2000, 1, 1, 12, 30, 55)
         c2 = Field('foo', table=self.t).lt(datetime(2000, 1, 1, 12, 30, 55))
 
-        self.assertEqual("\"foo\"<'2000-01-01T12:30:55'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<'2000-01-01T12:30:55'", str(c2))
+        self.assertEqual('"foo"<\'2000-01-01T12:30:55\'', str(c1))
+        self.assertEqual('"t0"."foo"<\'2000-01-01T12:30:55\'', str(c2))
 
     def test__criterion_lt_right(self):
         c1 = 1 > Field('foo')
         c2 = -1 > Field('foo', table=self.t)
 
-        self.assertEqual('\"foo\"<1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"<-1', str(c2))
+        self.assertEqual('"foo"<1', str(c1))
+        self.assertEqual('"t0"."foo"<-1', str(c2))
 
     def test__criterion_gt_number(self):
         c1 = Field('foo') > 1
         c2 = Field('foo', table=self.t).gt(0)
         c3 = Field('foo') > -1
 
-        self.assertEqual('\"foo\">1', str(c1))
-        self.assertEqual('\"t0\".\"foo\">0', str(c2))
-        self.assertEqual('\"foo\">-1', str(c3))
+        self.assertEqual('"foo">1', str(c1))
+        self.assertEqual('"t0"."foo">0', str(c2))
+        self.assertEqual('"foo">-1', str(c3))
 
     def test__criterion_gt_date(self):
         c1 = Field('foo') > date(2000, 1, 1)
         c2 = Field('foo', table=self.t).gt(date(2000, 1, 1))
 
-        self.assertEqual("\"foo\">'2000-01-01'", str(c1))
-        self.assertEqual("\"t0\".\"foo\">'2000-01-01'", str(c2))
+        self.assertEqual('"foo">\'2000-01-01\'', str(c1))
+        self.assertEqual('"t0"."foo">\'2000-01-01\'', str(c2))
 
     def test__criterion_gt_datetime(self):
         c1 = Field('foo') > datetime(2000, 1, 1, 12, 30, 55)
         c2 = Field('foo', table=self.t).gt(datetime(2000, 1, 1, 12, 30, 55))
 
-        self.assertEqual("\"foo\">'2000-01-01T12:30:55'", str(c1))
-        self.assertEqual("\"t0\".\"foo\">'2000-01-01T12:30:55'", str(c2))
+        self.assertEqual('"foo">\'2000-01-01T12:30:55\'', str(c1))
+        self.assertEqual('"t0"."foo">\'2000-01-01T12:30:55\'', str(c2))
 
     def test__criterion_gt_right(self):
         c1 = 1 < Field('foo')
         c2 = -1 < Field('foo', table=self.t)
 
-        self.assertEqual('\"foo\">1', str(c1))
-        self.assertEqual('\"t0\".\"foo\">-1', str(c2))
+        self.assertEqual('"foo">1', str(c1))
+        self.assertEqual('"t0"."foo">-1', str(c2))
 
     def test__criterion_lte_number(self):
         c1 = Field('foo') <= 1
         c2 = Field('foo', table=self.t).lte(0)
         c3 = Field('foo') <= -1
 
-        self.assertEqual('\"foo\"<=1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"<=0', str(c2))
-        self.assertEqual('\"foo\"<=-1', str(c3))
+        self.assertEqual('"foo"<=1', str(c1))
+        self.assertEqual('"t0"."foo"<=0', str(c2))
+        self.assertEqual('"foo"<=-1', str(c3))
 
     def test__criterion_lte_date(self):
         c1 = Field('foo') <= date(2000, 1, 1)
         c2 = Field('foo', table=self.t).lte(date(2000, 1, 1))
 
-        self.assertEqual("\"foo\"<='2000-01-01'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<='2000-01-01'", str(c2))
+        self.assertEqual('"foo"<=\'2000-01-01\'', str(c1))
+        self.assertEqual('"t0"."foo"<=\'2000-01-01\'', str(c2))
 
     def test__criterion_lte_datetime(self):
         c1 = Field('foo') <= datetime(2000, 1, 1, 12, 30, 55)
         c2 = Field('foo', table=self.t).lte(datetime(2000, 1, 1, 12, 30, 55))
 
-        self.assertEqual("\"foo\"<='2000-01-01T12:30:55'", str(c1))
-        self.assertEqual("\"t0\".\"foo\"<='2000-01-01T12:30:55'", str(c2))
+        self.assertEqual('"foo"<=\'2000-01-01T12:30:55\'', str(c1))
+        self.assertEqual('"t0"."foo"<=\'2000-01-01T12:30:55\'', str(c2))
 
     def test__criterion_lte_right(self):
         c1 = 1 >= Field('foo')
         c2 = -1 >= Field('foo', table=self.t)
 
-        self.assertEqual('\"foo\"<=1', str(c1))
-        self.assertEqual('\"t0\".\"foo\"<=-1', str(c2))
+        self.assertEqual('"foo"<=1', str(c1))
+        self.assertEqual('"t0"."foo"<=-1', str(c2))
 
     def test__criterion_gte_number(self):
         c1 = Field('foo') >= 1
         c2 = Field('foo', table=self.t).gte(0)
         c3 = Field('foo') >= -1
 
-        self.assertEqual('\"foo\">=1', str(c1))
-        self.assertEqual('\"t0\".\"foo\">=0', str(c2))
-        self.assertEqual('\"foo\">=-1', str(c3))
+        self.assertEqual('"foo">=1', str(c1))
+        self.assertEqual('"t0"."foo">=0', str(c2))
+        self.assertEqual('"foo">=-1', str(c3))
 
     def test__criterion_gte_date(self):
         c1 = Field('foo') >= date(2000, 1, 1)
         c2 = Field('foo', table=self.t).gte(date(2000, 1, 1))
 
-        self.assertEqual("\"foo\">='2000-01-01'", str(c1))
-        self.assertEqual("\"t0\".\"foo\">='2000-01-01'", str(c2))
+        self.assertEqual('"foo">=\'2000-01-01\'', str(c1))
+        self.assertEqual('"t0"."foo">=\'2000-01-01\'', str(c2))
 
     def test__criterion_gte_datetime(self):
         c1 = Field('foo') >= datetime(2000, 1, 1, 12, 30, 55)
         c2 = Field('foo', table=self.t).gte(datetime(2000, 1, 1, 12, 30, 55))
 
-        self.assertEqual("\"foo\">='2000-01-01T12:30:55'", str(c1))
-        self.assertEqual("\"t0\".\"foo\">='2000-01-01T12:30:55'", str(c2))
+        self.assertEqual('"foo">=\'2000-01-01T12:30:55\'', str(c1))
+        self.assertEqual('"t0"."foo">=\'2000-01-01T12:30:55\'', str(c2))
 
     def test__criterion_gte_right(self):
         c1 = 1 <= Field('foo')
         c2 = -1 <= Field('foo', table=self.t)
 
-        self.assertEqual('\"foo\">=1', str(c1))
-        self.assertEqual('\"t0\".\"foo\">=-1', str(c2))
+        self.assertEqual('"foo">=1', str(c1))
+        self.assertEqual('"t0"."foo">=-1', str(c2))
 
 
 class BetweenTests(unittest.TestCase):
@@ -249,67 +249,85 @@ class BetweenTests(unittest.TestCase):
         c2 = Field('foo', table=self.t).between(0, 1)
         c3 = Field('foo')[0:1]
 
-        self.assertEqual('\"foo\" BETWEEN 0 AND 1', str(c1))
-        self.assertEqual('\"t0\".\"foo\" BETWEEN 0 AND 1', str(c2))
-        self.assertEqual('\"foo\" BETWEEN 0 AND 1', str(c3))
+        self.assertEqual('"foo" BETWEEN 0 AND 1', str(c1))
+        self.assertEqual('"t0"."foo" BETWEEN 0 AND 1', str(c2))
+        self.assertEqual('"foo" BETWEEN 0 AND 1', str(c3))
 
     def test__between_date(self):
         c1 = Field('foo').between(date(2000, 1, 1), date(2000, 12, 31))
         c2 = Field('foo', table=self.t).between(date(2000, 1, 1), date(2000, 12, 31))
         c3 = Field('foo')[date(2000, 1, 1):date(2000, 12, 31)]
 
-        self.assertEqual("\"foo\" BETWEEN '2000-01-01' AND '2000-12-31'", str(c1))
-        self.assertEqual("\"t0\".\"foo\" BETWEEN '2000-01-01' AND '2000-12-31'", str(c2))
-        self.assertEqual("\"foo\" BETWEEN '2000-01-01' AND '2000-12-31'", str(c3))
+        self.assertEqual('"foo" BETWEEN \'2000-01-01\' AND \'2000-12-31\'', str(c1))
+        self.assertEqual('"t0"."foo" BETWEEN \'2000-01-01\' AND \'2000-12-31\'', str(c2))
+        self.assertEqual('"foo" BETWEEN \'2000-01-01\' AND \'2000-12-31\'', str(c3))
 
     def test__between_datetime(self):
         c1 = Field('foo').between(datetime(2000, 1, 1, 0, 0, 0), datetime(2000, 12, 31, 23, 59, 59))
         c2 = Field('foo', table=self.t).between(datetime(2000, 1, 1, 0, 0, 0), datetime(2000, 12, 31, 23, 59, 59))
         c3 = Field('foo')[datetime(2000, 1, 1, 0, 0, 0):datetime(2000, 12, 31, 23, 59, 59)]
 
-        self.assertEqual("\"foo\" BETWEEN '2000-01-01T00:00:00' AND '2000-12-31T23:59:59'", str(c1))
-        self.assertEqual("\"t0\".\"foo\" BETWEEN '2000-01-01T00:00:00' AND '2000-12-31T23:59:59'", str(c2))
-        self.assertEqual("\"foo\" BETWEEN '2000-01-01T00:00:00' AND '2000-12-31T23:59:59'", str(c3))
+        self.assertEqual('"foo" BETWEEN \'2000-01-01T00:00:00\' AND \'2000-12-31T23:59:59\'', str(c1))
+        self.assertEqual('"t0"."foo" BETWEEN \'2000-01-01T00:00:00\' AND \'2000-12-31T23:59:59\'', str(c2))
+        self.assertEqual('"foo" BETWEEN \'2000-01-01T00:00:00\' AND \'2000-12-31T23:59:59\'', str(c3))
 
-    def test_get_item_only_works_with_slice(self):
-        f = None
+    def test__function_between(self):
+        c1 = fn.Coalesce(Field('foo'), 0)[0:1]
+        c2 = fn.Coalesce(Field('foo', table=self.t), 0)[0:1]
+        c3 = fn.Coalesce(Field('foo', table=self.t), 0).between(0, 1)
 
-        with self.assertRaises(TypeError):
-            f = Field('foo')[0]
+        self.assertEqual('COALESCE("foo",0) BETWEEN 0 AND 1', str(c1))
+        self.assertEqual('COALESCE("t0"."foo",0) BETWEEN 0 AND 1', str(c2))
 
+    def test_get_item_only_works_with_slice(self):
         with self.assertRaises(TypeError):
-            f = Field('foo')[date(2000, 1, 1)]
+            Field('foo')[0]
 
         with self.assertRaises(TypeError):
-            f = Field('foo')[datetime(2000, 1, 1, 0, 0, 0)]
+            Field('foo')[date(2000, 1, 1)]
 
-        self.assertIsNone(f)
+        with self.assertRaises(TypeError):
+            Field('foo')[datetime(2000, 1, 1, 0, 0, 0)]
 
 
 class IsInTests(unittest.TestCase):
     t = Table('abc')
     t.alias = 't0'
 
-    def test__between_number(self):
+    def test__in_number(self):
         c1 = Field('foo').isin([0, 1])
         c2 = Field('foo', table=self.t).isin([0, 1])
 
-        self.assertEqual('\"foo\" IN (0,1)', str(c1))
-        self.assertEqual('\"t0\".\"foo\" IN (0,1)', str(c2))
+        self.assertEqual('"foo" IN (0,1)', str(c1))
+        self.assertEqual('"t0"."foo" IN (0,1)', str(c2))
 
-    def test__between_date(self):
+    def test__in_character(self):
+        c1 = Field('foo').isin(['a', 'b'])
+        c2 = Field('foo', table=self.t).isin(['a', 'b'])
+
+        self.assertEqual('"foo" IN (\'a\',\'b\')', str(c1))
+        self.assertEqual('"t0"."foo" IN (\'a\',\'b\')', str(c2))
+
+    def test__in_date(self):
         c1 = Field('foo').isin([date(2000, 1, 1), date(2000, 12, 31)])
         c2 = Field('foo', table=self.t).isin([date(2000, 1, 1), date(2000, 12, 31)])
 
-        self.assertEqual("\"foo\" IN ('2000-01-01','2000-12-31')", str(c1))
-        self.assertEqual("\"t0\".\"foo\" IN ('2000-01-01','2000-12-31')", str(c2))
+        self.assertEqual('"foo" IN (\'2000-01-01\',\'2000-12-31\')', str(c1))
+        self.assertEqual('"t0"."foo" IN (\'2000-01-01\',\'2000-12-31\')', str(c2))
 
-    def test__between_datetime(self):
+    def test__in_datetime(self):
         c1 = Field('foo').isin([datetime(2000, 1, 1, 0, 0, 0), datetime(2000, 12, 31, 23, 59, 59)])
         c2 = Field('foo', table=self.t).isin([datetime(2000, 1, 1, 0, 0, 0), datetime(2000, 12, 31, 23, 59, 59)])
 
-        self.assertEqual("\"foo\" IN ('2000-01-01T00:00:00','2000-12-31T23:59:59')", str(c1))
-        self.assertEqual("\"t0\".\"foo\" IN ('2000-01-01T00:00:00','2000-12-31T23:59:59')", str(c2))
+        self.assertEqual('"foo" IN (\'2000-01-01T00:00:00\',\'2000-12-31T23:59:59\')', str(c1))
+        self.assertEqual('"t0"."foo" IN (\'2000-01-01T00:00:00\',\'2000-12-31T23:59:59\')', str(c2))
+
+    def test__function_isin(self):
+        c1 = fn.Coalesce(Field('foo'), 0).isin([0, 1])
+        c2 = fn.Coalesce(Field('foo', table=self.t), 0).isin([0, 1])
+
+        self.assertEqual('COALESCE("foo",0) IN (0,1)', str(c1))
+        self.assertEqual('COALESCE("t0"."foo",0) IN (0,1)', str(c2))
 
 
 class ComplexCriterionTests(unittest.TestCase):
@@ -320,42 +338,42 @@ class ComplexCriterionTests(unittest.TestCase):
         c1 = (Field('foo') == 1) & (Field('bar') == 2)
         c2 = (Field('foo', table=self.t0) == 1) & (Field('bar', table=self.t1) == 2)
 
-        self.assertEqual('\"foo\"=1 AND \"bar\"=2', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=1 AND \"t1\".\"bar\"=2', str(c2))
+        self.assertEqual('"foo"=1 AND "bar"=2', str(c1))
+        self.assertEqual('"t0"."foo"=1 AND "t1"."bar"=2', str(c2))
 
     def test__or(self):
         c1 = (Field('foo') == 1) | (Field('bar') == 2)
         c2 = (Field('foo', table=self.t0) == 1) | (Field('bar', table=self.t1) == 2)
 
-        self.assertEqual('\"foo\"=1 OR \"bar\"=2', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=1 OR \"t1\".\"bar\"=2', str(c2))
+        self.assertEqual('"foo"=1 OR "bar"=2', str(c1))
+        self.assertEqual('"t0"."foo"=1 OR "t1"."bar"=2', str(c2))
 
     def test__xor(self):
         c1 = (Field('foo') == 1) ^ (Field('bar') == 2)
         c2 = (Field('foo', table=self.t0) == 1) ^ (Field('bar', table=self.t1) == 2)
 
-        self.assertEqual('\"foo\"=1 XOR \"bar\"=2', str(c1))
-        self.assertEqual('\"t0\".\"foo\"=1 XOR \"t1\".\"bar\"=2', str(c2))
+        self.assertEqual('"foo"=1 XOR "bar"=2', str(c1))
+        self.assertEqual('"t0"."foo"=1 XOR "t1"."bar"=2', str(c2))
 
     def test__nested__and(self):
         c = (Field('foo') == 1) & (Field('bar') == 2) & (Field('buz') == 3)
 
-        self.assertEqual('\"foo\"=1 AND \"bar\"=2 AND \"buz\"=3', str(c))
+        self.assertEqual('"foo"=1 AND "bar"=2 AND "buz"=3', str(c))
 
     def test__nested__or(self):
         c = (Field('foo') == 1) | (Field('bar') == 2) | (Field('buz') == 3)
 
-        self.assertEqual('\"foo\"=1 OR \"bar\"=2 OR \"buz\"=3', str(c))
+        self.assertEqual('"foo"=1 OR "bar"=2 OR "buz"=3', str(c))
 
     def test__nested__mixed(self):
         c = ((Field('foo') == 1) & (Field('bar') == 2)) | (Field('buz') == 3)
 
-        self.assertEqual('(\"foo\"=1 AND \"bar\"=2) OR \"buz\"=3', str(c))
+        self.assertEqual('("foo"=1 AND "bar"=2) OR "buz"=3', str(c))
 
     def test__between_and_isin(self):
         c = Field('foo').isin([1, 2, 3]) & Field('bar').between(0, 1)
 
-        self.assertEqual('\"foo\" IN (1,2,3) AND \"bar\" BETWEEN 0 AND 1', str(c))
+        self.assertEqual('"foo" IN (1,2,3) AND "bar" BETWEEN 0 AND 1', str(c))
 
 
 class CriterionOperationsTests(unittest.TestCase):
@@ -365,45 +383,45 @@ class CriterionOperationsTests(unittest.TestCase):
     def test_field_for_table(self):
         f = self.t0.foo.for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\"', str(f))
+        self.assertEqual('"t1"."foo"', str(f))
 
     def test_arithmeticfunction_for_table(self):
         f = (self.t0.foo + self.t0.bar).for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\"+\"t1\".\"bar\"', str(f))
+        self.assertEqual('"t1"."foo"+"t1"."bar"', str(f))
 
     def test_criterion_for_table(self):
         f = (self.t0.foo < self.t0.bar).for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\"<\"t1\".\"bar\"', str(f))
+        self.assertEqual('"t1"."foo"<"t1"."bar"', str(f))
 
     def test_complexcriterion_for_table(self):
         f = ((self.t0.foo < self.t0.bar) & (self.t0.fiz > self.t0.buz)).for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\"<\"t1\".\"bar\" AND \"t1\".\"fiz\">\"t1\".\"buz\"', str(f))
+        self.assertEqual('"t1"."foo"<"t1"."bar" AND "t1"."fiz">"t1"."buz"', str(f))
 
     def test_function_with_only_fields_for_table(self):
         f = fn.Sum(self.
                    t0.foo).for_(self.t1)
 
-        self.assertEqual('SUM(\"t1\".\"foo\")', str(f))
+        self.assertEqual('SUM("t1"."foo")', str(f))
 
     def test_function_with_values_and_fields_for_table(self):
         f = Mod(self.t0.foo, 2).for_(self.t1)
 
-        self.assertEqual('MOD(\"t1\".\"foo\",2)', str(f))
+        self.assertEqual('MOD("t1"."foo",2)', str(f))
 
     def test_betweencriterion_for_table(self):
         f = self.t0.foo[0:1].for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\" BETWEEN 0 AND 1', str(f))
+        self.assertEqual('"t1"."foo" BETWEEN 0 AND 1', str(f))
 
     def test_nullcriterion_for_table(self):
         f = self.t0.foo.isnull().for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\" IS NULL', str(f))
+        self.assertEqual('"t1"."foo" IS NULL', str(f))
 
     def test_notnullcriterion_for_table(self):
         f = self.t0.foo.notnull().for_(self.t1)
 
-        self.assertEqual('\"t1\".\"foo\" IS NOT NULL', str(f))
+        self.assertEqual('"t1"."foo" IS NOT NULL', str(f))