Repository: kayak/pypika
Pull request #176 (2018-11-07 09:34:26)

Null functions and Functions as args for Complex Criterions
- Added support for using functions as arguments in complex criterions
- Added null functions


Patch:
diff --git a/pypika/functions.py b/pypika/functions.py
index 1a2b9fd..34c86ba 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -82,11 +82,6 @@ class Sqrt(Function):
         super(Sqrt, self).__init__('SQRT', term, alias=alias)
 
 
-class Coalesce(Function):
-    def __init__(self, term, *default_values, **kwargs):
-        super(Coalesce, self).__init__('COALESCE', term, *default_values, **kwargs)
-
-
 # Type Functions
 class Cast(Function):
     def __init__(self, term, as_type, alias=None):
@@ -157,8 +152,8 @@ class Ascii(Function):
 
 
 class NullIf(Function):
-    def __init__(self, term, criterion, alias=None):
-        super(NullIf, self).__init__('NULLIF', term, criterion, alias=alias)
+    def __init__(self, criterion, alias=None):
+        super(NullIf, self).__init__('NULLIF', criterion, alias=alias)
 
 
 class Bin(Function):
@@ -252,3 +247,24 @@ class Extract(Function):
         return 'FROM {field}'.format(
             field=self.field,
         )
+
+
+# Null Functions
+class IsNull(Function):
+    def __init__(self, term, alias=None):
+        super(IsNull, self).__init__('ISNULL', term, alias=alias)
+
+
+class Coalesce(Function):
+    def __init__(self, term, *default_values, **kwargs):
+        super(Coalesce, self).__init__('COALESCE', term, *default_values, **kwargs)
+
+
+class IfNull(Function):
+    def __init__(self, condition, term, **kwargs):
+        super(IfNull, self).__init__('IFNULL', condition, term, **kwargs)
+
+
+class NVL(Function):
+    def __init__(self, condition, term, alias=None):
+        super(NVL, self).__init__('NVL', condition, term, alias=alias)
diff --git a/pypika/terms.py b/pypika/terms.py
index cf9d065..b1badb7 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -261,6 +261,7 @@ class ValueWrapper(Term):
             return 'null'
         return str(self.value)
 
+
 class Values(Term):
     def __init__(self, field,):
         super(Values, self).__init__(None)
@@ -281,7 +282,24 @@ class NullValue(Term):
         return alias_sql(sql, self.alias, quote_char)
 
 
-class Field(Term):
+class Criterion(Term):
+    def __and__(self, other):
+        return ComplexCriterion(Boolean.and_, self, other)
+
+    def __or__(self, other):
+        return ComplexCriterion(Boolean.or_, self, other)
+
+    def __xor__(self, other):
+        return ComplexCriterion(Boolean.xor_, self, other)
+
+    def fields(self):
+        raise NotImplementedError()
+
+    def get_sql(self):
+        raise NotImplementedError()
+
+
+class Field(Criterion):
     def __init__(self, name, alias=None, table=None):
         super(Field, self).__init__(alias)
         self.name = name
@@ -296,6 +314,9 @@ class Field(Term):
     def __xor__(self, other):
         return ComplexCriterion(Boolean.xor_, self, other)
 
+    def fields(self):
+        return [self]
+
     @property
     def tables_(self):
         return {self.table}
@@ -373,23 +394,6 @@ class Array(Tuple):
         )
 
 
-class Criterion(Term):
-    def __and__(self, other):
-        return ComplexCriterion(Boolean.and_, self, other)
-
-    def __or__(self, other):
-        return ComplexCriterion(Boolean.or_, self, other)
-
-    def __xor__(self, other):
-        return ComplexCriterion(Boolean.xor_, self, other)
-
-    def fields(self):
-        raise NotImplementedError()
-
-    def get_sql(self):
-        raise NotImplementedError()
-
-
 class BasicCriterion(Criterion):
     def __init__(self, comparator, left, right, alias=None):
         """
@@ -728,7 +732,7 @@ class Not(Criterion):
         return self.term.tables_
 
 
-class Function(Term):
+class Function(Criterion):
     def __init__(self, name, *args, **kwargs):
         super(Function, self).__init__(kwargs.get('alias'))
         self.name = name
@@ -741,6 +745,9 @@ class Function(Term):
                 for param in self.args
                 for table in param.tables_}
 
+    def fields(self):
+        return self.args
+
     @property
     def is_aggregate(self):
         """


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 8644a7b..1e1e4a3 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -523,27 +523,42 @@ class LikeTests(unittest.TestCase):
 class ComplexCriterionTests(unittest.TestCase):
     table_abc, table_efg = Table('abc', alias='cx0'), Table('efg', alias='cx1')
 
-    def test__and(self):
+    def test_and(self):
         c1 = (Field('foo') == 1) & (Field('bar') == 2)
         c2 = (Field('foo', table=self.table_abc) == 1) & (Field('bar', table=self.table_efg) == 2)
 
         self.assertEqual('"foo"=1 AND "bar"=2', str(c1))
         self.assertEqual('"cx0"."foo"=1 AND "cx1"."bar"=2', str(c2))
 
-    def test__or(self):
+    def test_or(self):
         c1 = (Field('foo') == 1) | (Field('bar') == 2)
         c2 = (Field('foo', table=self.table_abc) == 1) | (Field('bar', table=self.table_efg) == 2)
 
         self.assertEqual('"foo"=1 OR "bar"=2', str(c1))
         self.assertEqual('"cx0"."foo"=1 OR "cx1"."bar"=2', str(c2))
 
-    def test__xor(self):
+    def test_xor(self):
         c1 = (Field('foo') == 1) ^ (Field('bar') == 2)
         c2 = (Field('foo', table=self.table_abc) == 1) ^ (Field('bar', table=self.table_efg) == 2)
 
         self.assertEqual('"foo"=1 XOR "bar"=2', str(c1))
         self.assertEqual('"cx0"."foo"=1 XOR "cx1"."bar"=2', str(c2))
 
+    def test_function_and(self):
+        c1 = fn.IsNull(Field('foo')) & (Field('bar') == 2)
+
+        self.assertEqual('ISNULL("foo") AND "bar"=2', str(c1))
+
+    def test_function_or(self):
+        c1 = fn.IsNull(Field('foo')) | (Field('bar') == 2)
+
+        self.assertEqual('ISNULL("foo") OR "bar"=2', str(c1))
+
+    def test_function_xor(self):
+        c1 = fn.IsNull(Field('foo')) ^ (Field('bar') == 2)
+
+        self.assertEqual('ISNULL("foo") XOR "bar"=2', str(c1))
+
     def test__nested__and(self):
         c = (Field('foo') == 1) & (Field('bar') == 2) & (Field('buz') == 3)
 
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index e272d6c..a71a0c6 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -249,11 +249,6 @@ class AggregationTests(unittest.TestCase):
 
         self.assertEqual('SELECT STDDEV(\"foo\") FROM \"abc\"', str(q))
 
-    def test__coalesce(self):
-        q = Q.from_('abc').select(fn.Coalesce(F('foo'), 0))
-
-        self.assertEqual('SELECT COALESCE(\"foo\",0) FROM \"abc\"', str(q))
-
 
 class ConditionTests(unittest.TestCase):
     def test__case__raw(self):
@@ -698,3 +693,25 @@ class DateFunctionsTests(unittest.TestCase):
         query = Query.select(fn.CurTime())
 
         self.assertEqual("SELECT CURRENT_TIME()", str(query))
+
+
+class NullFunctionsTests(unittest.TestCase):
+    def test_isnull(self):
+        q = Q.from_('abc').select(fn.IsNull(F('foo')))
+
+        self.assertEqual('SELECT ISNULL(\"foo\") FROM \"abc\"', str(q))
+
+    def test_coalesce(self):
+        q = Q.from_('abc').select(fn.Coalesce(F('foo'), 0))
+
+        self.assertEqual('SELECT COALESCE(\"foo\",0) FROM \"abc\"', str(q))
+
+    def test_nullif(self):
+        q = Q.from_('abc').select(fn.NullIf(F('foo') == 0))
+
+        self.assertEqual('SELECT NULLIF(\"foo\"=0) FROM \"abc\"', str(q))
+
+    def test_nvl(self):
+        q = Q.from_('abc').select(fn.NVL(F('foo'), 0))
+
+        self.assertEqual('SELECT NVL(\"foo\",0) FROM \"abc\"', str(q))