Repository: kayak/pypika (python)

Issue:
Interval query trims right zeros for integer values
--------------------------------------------------
[System environment]

OS     : CentOS 7.5 and Ubuntu 16.04 LTS
PyPika : 0.15.1

--------------------------------------------------
[Overview]

When numbers ending in zero (such as 10, 100, etc.) are specified in
Interval class (terms.py), all consective zeros are accidentally
trimmed.

https://github.com/kayak/pypika/blob/master/pypika/terms.py#L952

I guessed that a regular expression specified in 'self.trim_pattern'
might be inappropriate.

https://github.com/kayak/pypika/blob/master/pypika/terms.py#L910

--------------------------------------------------
[Steps to reproduce]

>>> from pypika import Interval
>>> Interval(seconds=100).get_sql()

--------------------------------------------------
[Expected behavior]

>>> Interval(seconds=100).get_sql()

"INTERVAL '100 SECOND'"

--------------------------------------------------
[Actual behavior]

>>> Interval(seconds=100).get_sql()

"INTERVAL '1 SECOND'"


Pull request:
Fixed intervals to support decimals ending in zeros
Fixes #152 Changed the regular expression used for removing unused parts of the interval string to not drop zero digits at the end of integer values

Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index de2bd65..2af61e2 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -938,10 +938,23 @@ class IgnoreNullsAnalyticFunction(AnalyticFunction):
 
 
 class Interval(object):
+    templates = {
+        # MySQL requires no single quotes around the expr and unit
+        Dialects.MYSQL: 'INTERVAL {expr} {unit}',
+
+        # PostgreSQL, Redshift and Vertica require quotes around the expr and unit e.g. INTERVAL '1 week'
+        Dialects.POSTGRESQL: 'INTERVAL \'{expr} {unit}\'',
+        Dialects.REDSHIFT: 'INTERVAL \'{expr} {unit}\'',
+        Dialects.VERTICA: 'INTERVAL \'{expr} {unit}\'',
+
+        # Oracle requires just single quotes around the expr
+        Dialects.ORACLE: 'INTERVAL \'{expr}\' {unit}'
+    }
+
     units = ['years', 'months', 'days', 'hours', 'minutes', 'seconds', 'microseconds']
     labels = ['YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MICROSECOND']
 
-    trim_pattern = re.compile(r'^[0\-\.: ]+|[0\-\.: ]+$')
+    trim_pattern = re.compile(r'(^0+\.)|(\.0+$)|(^[0\-.: ]+[\-: ])|([\-:. ][0\-.: ]+$)')
 
     def __init__(self, years=0, months=0, days=0, hours=0, minutes=0, seconds=0, microseconds=0, quarters=0, weeks=0,
                  dialect=None):
@@ -973,7 +986,11 @@ class Interval(object):
     def get_sql(self, **kwargs):
         dialect = self.dialect or kwargs.get('dialect')
 
-        if hasattr(self, 'quarters'):
+        if self.largest == 'MICROSECOND':
+            expr = getattr(self, 'microseconds')
+            unit = 'MICROSECOND'
+
+        elif hasattr(self, 'quarters'):
             expr = getattr(self, 'quarters')
             unit = 'QUARTER'
 
@@ -983,9 +1000,7 @@ class Interval(object):
 
         else:
             # Create the whole expression but trim out the unnecessary fields
-            expr = self.trim_pattern.sub(
-                '',
-                "{years}-{months}-{days} {hours}:{minutes}:{seconds}.{microseconds}".format(
+            expr = "{years}-{months}-{days} {hours}:{minutes}:{seconds}.{microseconds}".format(
                     years=getattr(self, 'years', 0),
                     months=getattr(self, 'months', 0),
                     days=getattr(self, 'days', 0),
@@ -994,26 +1009,15 @@ class Interval(object):
                     seconds=getattr(self, 'seconds', 0),
                     microseconds=getattr(self, 'microseconds', 0),
                 )
-            )
+            expr = self.trim_pattern.sub('', expr)
+
             unit = '{largest}_{smallest}'.format(
                 largest=self.largest,
                 smallest=self.smallest,
             ) if self.largest != self.smallest else self.largest
 
-        interval_templates = {
-            # MySQL requires no single quotes around the expr and unit
-            Dialects.MYSQL: 'INTERVAL {expr} {unit}',
-
-            # PostgreSQL, Redshift and Vertica require quotes around the expr and unit e.g. INTERVAL '1 week'
-            Dialects.POSTGRESQL: 'INTERVAL \'{expr} {unit}\'',
-            Dialects.REDSHIFT: 'INTERVAL \'{expr} {unit}\'',
-            Dialects.VERTICA: 'INTERVAL \'{expr} {unit}\'',
-
-            # Oracle requires just single quotes around the expr
-            Dialects.ORACLE: 'INTERVAL \'{expr}\' {unit}'
-        }
-
-        return interval_templates.get(dialect, 'INTERVAL \'{expr} {unit}\'').format(expr=expr, unit=unit)
+        return self.templates.get(dialect, 'INTERVAL \'{expr} {unit}\'') \
+            .format(expr=expr, unit=unit)
 
 
 class Pow(Function):


Test patch:
diff --git a/pypika/tests/test_date_math.py b/pypika/tests/test_date_math.py
new file mode 100644
index 0000000..d6aa692
--- /dev/null
+++ b/pypika/tests/test_date_math.py
@@ -0,0 +1,152 @@
+import unittest
+
+from pypika import (
+    Field as F,
+    Interval,
+)
+from pypika.enums import Dialects
+
+dt = F('dt')
+
+
+class AddIntervalTests(unittest.TestCase):
+    def test_add_microsecond(self):
+        c = dt + Interval(microseconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 MICROSECOND\'", str(c))
+
+    def test_add_second(self):
+        c = dt + Interval(seconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 SECOND\'", str(c))
+
+    def test_add_minute(self):
+        c = dt + Interval(minutes=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 MINUTE\'", str(c))
+
+    def test_add_day(self):
+        c = dt + Interval(days=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 DAY\'", str(c))
+
+    def test_add_week(self):
+        c = dt + Interval(weeks=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 WEEK\'", str(c))
+
+    def test_add_month(self):
+        c = dt + Interval(months=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 MONTH\'", str(c))
+
+    def test_add_quarter(self):
+        c = dt + Interval(quarters=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'", str(c))
+
+    def test_add_year(self):
+        c = dt + Interval(years=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 YEAR\'", str(c))
+
+
+class AddIntervalMultipleUnitsTests(unittest.TestCase):
+    def test_add_second_microsecond(self):
+        c = dt + Interval(seconds=1, microseconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1.1 SECOND_MICROSECOND\'", str(c))
+
+    def test_add_minute_microsecond(self):
+        c = dt + Interval(minutes=1, microseconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1:0.1 MINUTE_MICROSECOND\'", str(c))
+
+    def test_add_minute_second(self):
+        c = dt + Interval(minutes=1, seconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1:1 MINUTE_SECOND\'", str(c))
+
+    def test_add_hour_microsecond(self):
+        c = dt + Interval(hours=1, microseconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1:0:0.1 HOUR_MICROSECOND\'", str(c))
+
+    def test_add_hour_second(self):
+        c = dt + Interval(hours=1, seconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1:0:1 HOUR_SECOND\'", str(c))
+
+    def test_add_hour_minute(self):
+        c = dt + Interval(hours=1, minutes=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1:1 HOUR_MINUTE\'", str(c))
+
+    def test_add_day_microsecond(self):
+        c = dt + Interval(days=1, microseconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 0:0:0.1 DAY_MICROSECOND\'", str(c))
+
+    def test_add_day_second(self):
+        c = dt + Interval(days=1, seconds=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 0:0:1 DAY_SECOND\'", str(c))
+
+    def test_add_day_minute(self):
+        c = dt + Interval(days=1, minutes=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 0:1 DAY_MINUTE\'", str(c))
+
+    def test_add_day_hour(self):
+        c = dt + Interval(days=1, hours=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 1 DAY_HOUR\'", str(c))
+
+    def test_add_year_month(self):
+        c = dt + Interval(years=1, months=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1-1 YEAR_MONTH\'", str(c))
+
+    def test_add_value_right(self):
+        c = Interval(microseconds=1) - dt
+
+        self.assertEqual("INTERVAL \'1 MICROSECOND\'-\"dt\"", str(c))
+
+    def test_add_value_complex_expressions(self):
+        c = dt + Interval(quarters=1) + Interval(weeks=1)
+
+        self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'+INTERVAL \'1 WEEK\'", str(c))
+
+
+class DialectIntervalTests(unittest.TestCase):
+    def test_mysql_dialect_does_not_use_quotes_around_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.MYSQL)
+        self.assertEqual("INTERVAL 1 DAY", c)
+
+    def test_oracle_dialect_uses_single_quotes_around_expression_in_an_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.ORACLE)
+        self.assertEqual("INTERVAL '1' DAY", c)
+
+    def test_vertica_dialect_uses_single_quotes_around_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.VERTICA)
+        self.assertEqual("INTERVAL '1 DAY'", c)
+
+    def test_redshift_dialect_uses_single_quotes_around_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.REDSHIFT)
+        self.assertEqual("INTERVAL '1 DAY'", c)
+
+    def test_postgresql_dialect_uses_single_quotes_around_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.POSTGRESQL)
+        self.assertEqual("INTERVAL '1 DAY'", c)
+
+
+class TruncateTrailingZerosTests(unittest.TestCase):
+    def test_do_not_truncate_integer_values(self):
+        i = Interval(seconds=10)
+
+        self.assertEqual("INTERVAL \'10 SECOND\'", str(i))
+
+    def test_do_not_truncate_months_between_years_and_datys(self):
+        i = Interval(years=10, days=10)
+
+        self.assertEqual("INTERVAL \'10-0-10 YEAR_DAY\'", str(i))
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index a71a0c6..91f2419 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -1,4 +1,3 @@
-# coding: utf-8
 import unittest
 
 from pypika import (
@@ -6,7 +5,6 @@ from pypika import (
     CaseException,
     DatePart,
     Field as F,
-    Interval,
     Query,
     Query as Q,
     Table as T,
@@ -14,7 +12,6 @@ from pypika import (
     functions as fn,
 )
 from pypika.enums import (
-    Dialects,
     SqlTypes,
 )
 
@@ -509,131 +506,6 @@ class DateFunctionsTests(unittest.TestCase):
     dt = F('dt')
     t = T('abc')
 
-    def test_add_microsecond(self):
-        c = self.dt + Interval(microseconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 MICROSECOND\'", str(c))
-
-    def test_add_second(self):
-        c = self.dt + Interval(seconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 SECOND\'", str(c))
-
-    def test_add_minute(self):
-        c = self.dt + Interval(minutes=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 MINUTE\'", str(c))
-
-    def test_add_day(self):
-        c = self.dt + Interval(days=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 DAY\'", str(c))
-
-    def test_add_week(self):
-        c = self.dt + Interval(weeks=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 WEEK\'", str(c))
-
-    def test_add_month(self):
-        c = self.dt + Interval(months=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 MONTH\'", str(c))
-
-    def test_add_quarter(self):
-        c = self.dt + Interval(quarters=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'", str(c))
-
-    def test_add_year(self):
-        c = self.dt + Interval(years=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 YEAR\'", str(c))
-
-    def test_add_second_microsecond(self):
-        c = self.dt + Interval(seconds=1, microseconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1.1 SECOND_MICROSECOND\'", str(c))
-
-    def test_add_minute_microsecond(self):
-        c = self.dt + Interval(minutes=1, microseconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1:0.1 MINUTE_MICROSECOND\'", str(c))
-
-    def test_add_minute_second(self):
-        c = self.dt + Interval(minutes=1, seconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1:1 MINUTE_SECOND\'", str(c))
-
-    def test_add_hour_microsecond(self):
-        c = self.dt + Interval(hours=1, microseconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1:0:0.1 HOUR_MICROSECOND\'", str(c))
-
-    def test_add_hour_second(self):
-        c = self.dt + Interval(hours=1, seconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1:0:1 HOUR_SECOND\'", str(c))
-
-    def test_add_hour_minute(self):
-        c = self.dt + Interval(hours=1, minutes=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1:1 HOUR_MINUTE\'", str(c))
-
-    def test_add_day_microsecond(self):
-        c = self.dt + Interval(days=1, microseconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 0:0:0.1 DAY_MICROSECOND\'", str(c))
-
-    def test_add_day_second(self):
-        c = self.dt + Interval(days=1, seconds=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 0:0:1 DAY_SECOND\'", str(c))
-
-    def test_add_day_minute(self):
-        c = self.dt + Interval(days=1, minutes=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 0:1 DAY_MINUTE\'", str(c))
-
-    def test_add_day_hour(self):
-        c = self.dt + Interval(days=1, hours=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 1 DAY_HOUR\'", str(c))
-
-    def test_add_year_month(self):
-        c = self.dt + Interval(years=1, months=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1-1 YEAR_MONTH\'", str(c))
-
-    def test_add_value_right(self):
-        c = Interval(microseconds=1) - self.dt
-
-        self.assertEqual("INTERVAL \'1 MICROSECOND\'-\"dt\"", str(c))
-
-    def test_add_value_complex_expressions(self):
-        c = self.dt + Interval(quarters=1) + Interval(weeks=1)
-
-        self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'+INTERVAL \'1 WEEK\'", str(c))
-
-    def test_mysql_dialect_does_not_use_quotes_around_interval(self):
-        c = Interval(days=1).get_sql(dialect=Dialects.MYSQL)
-        self.assertEqual("INTERVAL 1 DAY", str(c))
-
-    def test_oracle_dialect_uses_single_quotes_around_expression_in_an_interval(self):
-        c = Interval(days=1).get_sql(dialect=Dialects.ORACLE)
-        self.assertEqual("INTERVAL '1' DAY", str(c))
-
-    def test_vertica_dialect_uses_single_quotes_around_interval(self):
-        c = Interval(days=1).get_sql(dialect=Dialects.VERTICA)
-        self.assertEqual("INTERVAL '1 DAY'", str(c))
-
-    def test_redshift_dialect_uses_single_quotes_around_interval(self):
-        c = Interval(days=1).get_sql(dialect=Dialects.REDSHIFT)
-        self.assertEqual("INTERVAL '1 DAY'", str(c))
-
-    def test_postgresql_dialect_uses_single_quotes_around_interval(self):
-        c = Interval(days=1).get_sql(dialect=Dialects.POSTGRESQL)
-        self.assertEqual("INTERVAL '1 DAY'", str(c))
-
     def _test_extract_datepart(self, date_part):
         q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo))