Repository: kayak/pypika
Pull request #782 (2023-12-21 00:01:46)

Make MySQL queries wrap set operations with parens
Add parens around set operations for MySQL. Fixes https://github.com/kayak/pypika/issues/773. 

Note that I removed the tests for checking no parens for MySQL, since adding back the parens brings the behavior in line with the default. I assume the tests are for checking that the behavior is not default. Let me know if the tests should be added back.

wd60622: I'm not fully versed on the test suite, but it might be best to test that the union now **has** parentheses instead of deleting the previous test. No harm in double checking
pandaxtc: Gotcha, just added the tests back.
wd60622: @AzisK Could you run the test suite?
coveralls: 
[![Coverage Status](https://coveralls.io/builds/65116512/badge)](https://coveralls.io/builds/65116512)

coverage: 98.39%. remained the same
when pulling **1e797c73daff68cb8b3f216c0b7c5a07689b41d3 on pandaxtc:pandaxtc/fix-mysql-set-wrap**
into **8841520e906970d76c5ed81c7dd5d154f0d5259d on kayak:master**.


Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 8894e56..dab72e0 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -1,4 +1,5 @@
 import itertools
+import warnings
 from copy import copy
 from typing import Any, Optional, Union, Tuple as TypedTuple
 
@@ -87,7 +88,7 @@ class MySQLQueryBuilder(QueryBuilder):
     QUERY_CLS = MySQLQuery
 
     def __init__(self, **kwargs: Any) -> None:
-        super().__init__(dialect=Dialects.MYSQL, wrap_set_operation_queries=False, **kwargs)
+        super().__init__(dialect=Dialects.MYSQL, **kwargs)
         self._duplicate_updates = []
         self._ignore_duplicates = False
         self._modifiers = []
@@ -347,6 +348,19 @@ class VerticaCopyQueryBuilder:
         return self.get_sql()
 
 
+class FetchNextAndOffsetRowsQueryBuilder(QueryBuilder):
+    def _limit_sql(self) -> str:
+        return " FETCH NEXT {limit} ROWS ONLY".format(limit=self._limit)
+
+    def _offset_sql(self) -> str:
+        return " OFFSET {offset} ROWS".format(offset=self._offset or 0)
+
+    @builder
+    def fetch_next(self, limit: int):
+        warnings.warn("`fetch_next` is deprecated - please use the `limit` method", DeprecationWarning)
+        self._limit = limit
+
+
 class OracleQuery(Query):
     """
     Defines a query class for use with Oracle.
@@ -357,7 +371,7 @@ class OracleQuery(Query):
         return OracleQueryBuilder(**kwargs)
 
 
-class OracleQueryBuilder(QueryBuilder):
+class OracleQueryBuilder(FetchNextAndOffsetRowsQueryBuilder):
     QUOTE_CHAR = None
     QUERY_CLS = OracleQuery
 
@@ -370,6 +384,16 @@ class OracleQueryBuilder(QueryBuilder):
         kwargs['groupby_alias'] = False
         return super().get_sql(*args, **kwargs)
 
+    def _apply_pagination(self, querystring: str) -> str:
+        # Note: Overridden as Oracle specifies offset before the fetch next limit
+        if self._offset:
+            querystring += self._offset_sql()
+
+        if self._limit is not None:
+            querystring += self._limit_sql()
+
+        return querystring
+
 
 class PostgreSQLQuery(Query):
     """
@@ -670,7 +694,7 @@ class MSSQLQuery(Query):
         return MSSQLQueryBuilder(**kwargs)
 
 
-class MSSQLQueryBuilder(QueryBuilder):
+class MSSQLQueryBuilder(FetchNextAndOffsetRowsQueryBuilder):
     QUERY_CLS = MSSQLQuery
 
     def __init__(self, **kwargs: Any) -> None:
@@ -695,17 +719,6 @@ class MSSQLQueryBuilder(QueryBuilder):
         self._top_percent: bool = percent
         self._top_with_ties: bool = with_ties
 
-    @builder
-    def fetch_next(self, limit: int) -> "MSSQLQueryBuilder":
-        # Overridden to provide a more domain-specific API for T-SQL users
-        self._limit = limit
-
-    def _offset_sql(self) -> str:
-        return " OFFSET {offset} ROWS".format(offset=self._offset or 0)
-
-    def _limit_sql(self) -> str:
-        return " FETCH NEXT {limit} ROWS ONLY".format(limit=self._limit)
-
     def _apply_pagination(self, querystring: str) -> str:
         # Note: Overridden as MSSQL specifies offset before the fetch next limit
         if self._limit is not None or self._offset:
diff --git a/pypika/terms.py b/pypika/terms.py
index b25af5c..ce7aed6 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -548,6 +548,10 @@ class Field(Criterion, JSON):
     ) -> None:
         super().__init__(alias=alias)
         self.name = name
+        if isinstance(table, str):
+            # avoid circular import at load time
+            from pypika.queries import Table
+            table = Table(table)
         self.table = table
 
     def nodes_(self) -> Iterator[NodeT]:


Test patch:
diff --git a/pypika/tests/dialects/test_mssql.py b/pypika/tests/dialects/test_mssql.py
index f8940b0..7cf53b2 100644
--- a/pypika/tests/dialects/test_mssql.py
+++ b/pypika/tests/dialects/test_mssql.py
@@ -53,18 +53,13 @@ class SelectTests(unittest.TestCase):
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
 
-    def test_fetch_next(self):
-        q = MSSQLQuery.from_("abc").select("def").orderby("def").fetch_next(10)
-
-        self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
-
     def test_offset(self):
         q = MSSQLQuery.from_("abc").select("def").orderby("def").offset(10)
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 10 ROWS', str(q))
 
-    def test_fetch_next_with_offset(self):
-        q = MSSQLQuery.from_("abc").select("def").orderby("def").fetch_next(10).offset(10)
+    def test_limit_with_offset(self):
+        q = MSSQLQuery.from_("abc").select("def").orderby("def").limit(10).offset(10)
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
 
diff --git a/pypika/tests/dialects/test_oracle.py b/pypika/tests/dialects/test_oracle.py
index c3a757a..21b54f1 100644
--- a/pypika/tests/dialects/test_oracle.py
+++ b/pypika/tests/dialects/test_oracle.py
@@ -19,3 +19,33 @@ class SelectTests(unittest.TestCase):
         q = OracleQuery.from_(subquery).select(col, Count('*')).groupby(col)
 
         self.assertEqual('SELECT sq0.abc a,COUNT(\'*\') FROM (SELECT abc FROM table1) sq0 GROUP BY sq0.abc', str(q))
+
+    def test_limit_query(self):
+        t = Table('table1')
+        limit = 5
+        q = OracleQuery.from_(t).select(t.test).limit(limit)
+
+        self.assertEqual(f'SELECT test FROM table1 FETCH NEXT {limit} ROWS ONLY', str(q))
+
+    def test_offset_query(self):
+        t = Table('table1')
+        offset = 5
+        q = OracleQuery.from_(t).select(t.test).offset(offset)
+
+        self.assertEqual(f'SELECT test FROM table1 OFFSET {offset} ROWS', str(q))
+
+    def test_limit_offset_query(self):
+        t = Table('table1')
+        limit = 5
+        offset = 5
+        q = OracleQuery.from_(t).select(t.test).limit(limit).offset(offset)
+
+        self.assertEqual(f'SELECT test FROM table1 OFFSET {offset} ROWS FETCH NEXT {limit} ROWS ONLY', str(q))
+
+    def test_fetch_next_method_deprecated(self):
+        with self.assertWarns(DeprecationWarning):
+            t = Table('table1')
+            limit = 5
+            q = OracleQuery.from_(t).select(t.test).fetch_next(limit)
+
+            self.assertEqual(f'SELECT test FROM table1 FETCH NEXT {limit} ROWS ONLY', str(q))
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index 6e54f88..03142d5 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -845,12 +845,12 @@ class UnionTests(unittest.TestCase):
         with self.assertRaises(SetOperationException):
             str(query1 + query2)
 
-    def test_mysql_query_does_not_wrap_unioned_queries_with_params(self):
+    def test_mysql_query_wraps_unioned_queries(self):
         query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
         query2 = Query.from_(self.table2).select(self.table2.bar)
 
         self.assertEqual(
-            "SELECT `foo` FROM `abc` UNION SELECT `bar` FROM `efg`",
+            "(SELECT `foo` FROM `abc`) UNION (SELECT `bar` FROM `efg`)",
             str(query1 + query2),
         )
 
@@ -968,12 +968,12 @@ class IntersectTests(unittest.TestCase):
         with self.assertRaises(SetOperationException):
             str(query1.intersect(query2))
 
-    def test_mysql_query_does_not_wrap_intersected_queries_with_params(self):
+    def test_mysql_query_wraps_intersected_queries(self):
         query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
         query2 = Query.from_(self.table2).select(self.table2.bar)
 
         self.assertEqual(
-            "SELECT `foo` FROM `abc` INTERSECT SELECT `bar` FROM `efg`",
+            "(SELECT `foo` FROM `abc`) INTERSECT (SELECT `bar` FROM `efg`)",
             str(query1.intersect(query2)),
         )
 
@@ -1064,12 +1064,12 @@ class MinusTests(unittest.TestCase):
         with self.assertRaises(SetOperationException):
             str(query1.minus(query2))
 
-    def test_mysql_query_does_not_wrap_minus_queries_with_params(self):
+    def test_mysql_query_wraps_minus_queries(self):
         query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
         query2 = Query.from_(self.table2).select(self.table2.bar)
 
         self.assertEqual(
-            "SELECT `foo` FROM `abc` MINUS SELECT `bar` FROM `efg`",
+            "(SELECT `foo` FROM `abc`) MINUS (SELECT `bar` FROM `efg`)",
             str(query1 - query2),
         )
 
diff --git a/pypika/tests/test_terms.py b/pypika/tests/test_terms.py
index 562e0d2..607c4c0 100644
--- a/pypika/tests/test_terms.py
+++ b/pypika/tests/test_terms.py
@@ -15,6 +15,13 @@ class FieldAliasTests(TestCase):
         self.assertEqual('bar', str(c1.alias))
 
 
+class FieldInitTests(TestCase):
+    def test_init_with_str_table(self):
+        test_table_name = "test_table"
+        field = Field(name="name", table=test_table_name)
+        self.assertEqual(field.table, Table(name=test_table_name))
+    
+
 class FieldHashingTests(TestCase):
     def test_tabled_eq_fields_equally_hashed(self):
         client_name1 = Field(name="name", table=Table("clients"))