nebius/SWE-rebench-V2-PRs huggingface dataset:
Repository: kayak/pypika
Pull request #376 (2020-02-19 14:13:19)

add filter clause for window functions
- add filter clause support for window functions 


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index e1ebb22..95f9ddb 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1194,10 +1194,17 @@ class AnalyticFunction(Function):
 
     def __init__(self, name, *args, **kwargs):
         super(AnalyticFunction, self).__init__(name, *args, **kwargs)
+        self._filters = []
         self._partition = []
         self._orderbys = []
+        self._include_filter = False
         self._include_over = False
 
+    @builder
+    def filter(self, *filters):
+        self._include_filter = True
+        self._filters += filters
+
     @builder
     def over(self, *terms):
         self._include_over = True
@@ -1216,6 +1223,12 @@ class AnalyticFunction(Function):
             field=field.get_sql(**kwargs), orient=orient.value,
         )
 
+    def get_filter_sql(self):
+        if self._include_filter:
+            return "WHERE {criterions}".format(
+                criterions=Criterion.all(self._filters).get_sql()
+            )
+
     def get_partition_sql(self, **kwargs):
         terms = []
         if self._partition:
@@ -1242,14 +1255,16 @@ class AnalyticFunction(Function):
 
     def get_function_sql(self, **kwargs):
         function_sql = super(AnalyticFunction, self).get_function_sql(**kwargs)
+        filter_sql = self.get_filter_sql()
         partition_sql = self.get_partition_sql(**kwargs)
 
-        if not self._include_over:
-            return function_sql
+        sql = function_sql
+        if self._include_filter:
+            sql += " FILTER({filter_sql})".format(filter_sql=filter_sql)
+        if self._include_over:
+            sql += " OVER({partition_sql})".format(partition_sql=partition_sql)
 
-        return "{function_sql} OVER({partition_sql})".format(
-            function_sql=function_sql, partition_sql=partition_sql
-        )
+        return sql
 
 
 class WindowFrameAnalyticFunction(AnalyticFunction):


Test patch:
diff --git a/pypika/tests/test_analytic_queries.py b/pypika/tests/test_analytic_queries.py
index d22caed..da77365 100644
--- a/pypika/tests/test_analytic_queries.py
+++ b/pypika/tests/test_analytic_queries.py
@@ -1,6 +1,7 @@
 import unittest
 
 from pypika import (
+    Criterion,
     JoinType,
     Order,
     Query,
@@ -220,6 +221,25 @@ class RankTests(unittest.TestCase):
             str(q),
         )
 
+    def test_filter(self):
+        expr = (
+            an.LastValue(self.table_abc.fizz)
+            .filter(Criterion.all([self.table_abc.bar == True]))
+            .over(self.table_abc.foo)
+            .orderby(self.table_abc.date)
+        )
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual(
+            "SELECT "
+            'LAST_VALUE("fizz") '
+            'FILTER(WHERE "bar"=true) '
+            'OVER(PARTITION BY "foo" ORDER BY "date") '
+            'FROM "abc"',
+            str(q),
+        )
+
     def test_orderby_asc(self):
         expr = (
             an.LastValue(self.table_abc.fizz)
Repository: kayak/pypika
Pull request #386 (2020-03-23 19:29:16)

Use self._wrapper_cls in QueryBuilder.set
This commit fixes a problem where the QueryBuilder.set method always
used the ValueWrapper class instead of the instance's _wrapper_cls.

Signed-off-by: Petter Nyström <jimorie@gmail.com>


Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index 593a88b..33b2156 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -901,7 +901,7 @@ class QueryBuilder(Selectable, Term):
     @builder
     def set(self, field, value):
         field = Field(field) if not isinstance(field, Field) else field
-        self._updates.append((field, ValueWrapper(value)))
+        self._updates.append((field, self._wrapper_cls(value)))
 
     def __add__(self, other):
         return self.union(other)


Test patch:
diff --git a/pypika/tests/test_updates.py b/pypika/tests/test_updates.py
index ebbceac..ece6aa1 100644
--- a/pypika/tests/test_updates.py
+++ b/pypika/tests/test_updates.py
@@ -1,6 +1,6 @@
 import unittest
 
-from pypika import Table, Query, PostgreSQLQuery, AliasedQuery
+from pypika import Table, Query, PostgreSQLQuery, AliasedQuery, SQLLiteQuery
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -132,3 +132,12 @@ class PostgresUpdateTests(unittest.TestCase):
         self.assertEqual(
             'UPDATE "abc" SET "lname"="bcd"."long_name" FROM "bcd" RETURNING "abc"."id","bcd"."fname"', str(q)
         )
+
+
+class SQLLiteUpdateTests(unittest.TestCase):
+    table_abc = Table("abc")
+
+    def test_update_with_bool(self):
+        q = SQLLiteQuery.update(self.table_abc).set(self.table_abc.foo, True)
+
+        self.assertEqual('UPDATE "abc" SET "foo"=1', str(q))
Repository: kayak/pypika
Pull request #391 (2020-03-30 08:44:14)

Set default unit with Interval query
Current `Interval` code can generate wrong query When all value is unset.
`(e.g Interval(days=0).get_sql())`

So just check that all value is unset, and set default with `DAY`. 
This is small fixing, you may don't like this solution. 
Maybe we need to consider as `exception` when all value is unset.
Please check it and review plz. Thank u :)

Fixed : #389 


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 95f9ddb..9023255 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1425,6 +1425,10 @@ class Interval(Node):
                 else self.largest
             )
 
+            # Set default unit with DAY
+            if unit is None:
+                unit = "DAY"
+
         return self.templates.get(dialect, "INTERVAL '{expr} {unit}'").format(
             expr=expr, unit=unit
         )


Test patch:
diff --git a/pypika/tests/test_date_math.py b/pypika/tests/test_date_math.py
index 47b5514..73e09c7 100644
--- a/pypika/tests/test_date_math.py
+++ b/pypika/tests/test_date_math.py
@@ -50,6 +50,11 @@ class AddIntervalTests(unittest.TestCase):
 
         self.assertEqual("\"dt\"+INTERVAL '1 YEAR'", str(c))
 
+    def test_add_default(self):
+        c = dt + Interval(days=0)
+
+        self.assertEqual("\"dt\"+INTERVAL '0 DAY'", str(c))
+
 
 class AddIntervalMultipleUnitsTests(unittest.TestCase):
     def test_add_second_microsecond(self):
Repository: kayak/pypika
Pull request #392 (2020-04-01 11:30:01)

Allow alias with nested function
With #388, when using function nest. Inner alias is ignored.
I don't know that this behavior is intended.
Allowing alias with nested function (inner) can be useful
(Maybe custom function?.. umm.. i'm not sure 😕, Plz see #388)
And add test to test this feature.

Fixed: #388 


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 9023255..cbb05db 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1148,7 +1148,7 @@ class Function(Criterion):
         return "{name}({args}{special})".format(
             name=self.name,
             args=",".join(
-                p.get_sql(with_alias=False, **kwargs)
+                p.get_sql(with_alias=True, **kwargs)
                 if hasattr(p, "get_sql")
                 else str(p)
                 for p in self.args


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index a83ad8f..bdf2558 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -847,6 +847,16 @@ class AliasTests(unittest.TestCase):
 
         self.assertEqual('SELECT COUNT(*) "foo" FROM "abc"', str(q))
 
+    def test_function_using_as_nested(self):
+        q = Query.from_(self.t).select(fn.Sqrt(fn.Count("*").as_("foo")).as_("bar"))
+
+        self.assertEqual('SELECT SQRT(COUNT(*) "foo") "bar" FROM "abc"', str(q))
+
+    def test_functions_using_constructor_param_nested(self):
+        q = Query.from_(self.t).select(fn.Sqrt(fn.Count("*", alias="foo"), alias="bar"))
+
+        self.assertEqual('SELECT SQRT(COUNT(*) "foo") "bar" FROM "abc"', str(q))
+
     def test_ignored_in_where(self):
         q = Query.from_(self.t).select(self.t.foo).where(self.t.foo.as_("bar") == 1)
Repository: kayak/pypika
Pull request #421 (2020-04-24 09:47:54)

Fix tuple table replacement


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index cbed857..147dde9 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -539,7 +539,6 @@ class Star(Field):
         return "*"
 
 
-
 class Tuple(Criterion):
     def __init__(self, *values: Any) -> None:
         super().__init__()
@@ -571,8 +570,7 @@ class Tuple(Criterion):
             A copy of the field with the tables replaced.
         """
         self.values = [
-            [value.replace_table(current_table, new_table) for value in value_list]
-            for value_list in self.values
+            value.replace_table(current_table, new_table) for value in self.values
         ]
 
 


Test patch:
diff --git a/pypika/tests/test_query.py b/pypika/tests/test_query.py
index c1bf2dc..e90e1fd 100644
--- a/pypika/tests/test_query.py
+++ b/pypika/tests/test_query.py
@@ -1,11 +1,6 @@
 import unittest
 
-from pypika import (
-    Case,
-    Query,
-    Tables,
-    functions,
-)
+from pypika import Case, Query, Tables, functions, Tuple
 
 
 class QueryTablesTests(unittest.TestCase):
@@ -69,7 +64,7 @@ class QueryTablesTests(unittest.TestCase):
         query = query.replace_table(self.table_a, self.table_b)
 
         self.assertEqual(
-            'SELECT "name" ' 'FROM "b" ' "WHERE \"name\"='Mustermann'", str(query)
+            'SELECT "name" FROM "b" WHERE "name"=\'Mustermann\'', str(query)
         )
 
     def test_replace_having_table(self):
@@ -117,7 +112,22 @@ class QueryTablesTests(unittest.TestCase):
         query = query.replace_table(self.table_a, self.table_b)
 
         self.assertEqual(
-            'SELECT "customer" ' 'FROM "b" ' 'ORDER BY "customer"', str(query)
+            'SELECT "customer" FROM "b" ORDER BY "customer"', str(query)
+        )
+
+    def test_replace_tuple_table(self):
+        query = (
+            Query.from_(self.table_a)
+            .select(self.table_a.cost, self.table_a.revenue)
+            .where((self.table_a.cost, self.table_a.revenue) == Tuple(1, 2))
+        )
+
+        query = query.replace_table(self.table_a, self.table_b)
+
+        # Order is reversed due to lack of right equals method
+        self.assertEqual(
+            'SELECT "cost","revenue" FROM "b" WHERE (1,2)=("cost","revenue")',
+            str(query),
         )
 
     def test_is_joined(self):
Repository: kayak/pypika
Pull request #427 (2020-05-03 14:33:01)

Add table to Extract function
Add table name to `EXTRACT` function

Fixed : #426 


Patch:
diff --git a/pypika/functions.py b/pypika/functions.py
index 249f9be..05ba92c 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -303,7 +303,7 @@ class Extract(Function):
         self.field = field
 
     def get_special_params_sql(self, **kwargs):
-        return "FROM {field}".format(field=self.field,)
+        return "FROM {field}".format(field=self.field.get_sql(**kwargs))
 
 
 # Null Functions


Test patch:
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index cf1796c..816ce04 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -561,6 +561,7 @@ class CastTests(unittest.TestCase):
 class DateFunctionsTests(unittest.TestCase):
     dt = F("dt")
     t = T("abc")
+    t2 = T("efg")
 
     def _test_extract_datepart(self, date_part):
         q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo))
@@ -596,6 +597,20 @@ class DateFunctionsTests(unittest.TestCase):
     def test_extract_year(self):
         self._test_extract_datepart(DatePart.year)
 
+    def test_extract_join(self):
+        q = (
+            Q.from_(self.t)
+            .join(self.t2)
+            .on(self.t.id == self.t2.t_id)
+            .select(fn.Extract(DatePart.year, self.t.foo))
+        )
+
+        self.assertEqual(
+            'SELECT EXTRACT(YEAR FROM "abc"."foo") FROM "abc" '
+            'JOIN "efg" ON "abc"."id"="efg"."t_id"',
+            str(q)
+        )
+
     def test_timestampadd(self):
         a = fn.TimestampAdd("year", 1, "2017-10-01")
         self.assertEqual(str(a), "TIMESTAMPADD('year',1,'2017-10-01')")
Repository: kayak/pypika
Pull request #431 (2020-05-05 11:50:01)

Fix columns with empty args
Fix columns with empty arguments. I occurs by #373 that support iterable params to `columns`
Really sorry for the hasty PR :disappointed_relieved: ...

Fixed: #430 


Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index 2ab94ed..65dfd71 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -772,7 +772,7 @@ class QueryBuilder(Selectable, Term):
         if self._insert_table is None:
             raise AttributeError("'Query' object has no attribute '%s'" % "insert")
 
-        if isinstance(terms[0], (list, tuple)):
+        if terms and isinstance(terms[0], (list, tuple)):
             terms = terms[0]
 
         for term in terms:


Test patch:
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index 7a3ada7..3f888c6 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -113,6 +113,17 @@ class InsertIntoTests(unittest.TestCase):
             'INSERT INTO "abc" ("foo","bar","buz") VALUES (1,\'a\',true)', str(query)
         )
 
+    def test_insert_empty_columns(self):
+        query = (
+            Query.into(self.table_abc)
+            .columns()
+            .insert(1, "a", True)
+        )
+
+        self.assertEqual(
+            'INSERT INTO "abc" VALUES (1,\'a\',true)', str(query)
+        )
+
     def test_insert_selected_columns_type_list(self):
         query = (
             Query.into(self.table_abc)
@@ -124,6 +135,17 @@ class InsertIntoTests(unittest.TestCase):
             'INSERT INTO "abc" ("foo","bar","buz") VALUES (1,\'a\',true)', str(query)
         )
 
+    def test_insert_empty_columns_type_list(self):
+        query = (
+            Query.into(self.table_abc)
+            .columns([])
+            .insert(1, "a", True)
+        )
+
+        self.assertEqual(
+            'INSERT INTO "abc" VALUES (1,\'a\',true)', str(query)
+        )
+
     def test_insert_selected_columns_type_tuple(self):
         query = (
             Query.into(self.table_abc)
@@ -135,6 +157,17 @@ class InsertIntoTests(unittest.TestCase):
             'INSERT INTO "abc" ("foo","bar","buz") VALUES (1,\'a\',true)', str(query)
         )
 
+    def test_insert_empty_columns_type_tuple(self):
+        query = (
+            Query.into(self.table_abc)
+            .columns(())
+            .insert(1, "a", True)
+        )
+
+        self.assertEqual(
+            'INSERT INTO "abc" VALUES (1,\'a\',true)', str(query)
+        )
+
     def test_insert_none_skipped(self):
         query = Query.into(self.table_abc).insert()
Repository: kayak/pypika
Pull request #448 (2020-06-16 15:34:31)

Fix is_aggregate property for Case fields
So Case's criterions were not taken into account when evaluation a Case field's is_aggregate property, which imo is incorrect.


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index f16143e..95def41 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -974,9 +974,9 @@ class Case(Term):
 
     @property
     def is_aggregate(self) -> Optional[bool]:
-        # True if all cases are True or None. None all cases are None. Otherwise, False
+        # True if all criterions/cases are True or None. None all cases are None. Otherwise, False
         return resolve_is_aggregate(
-              [term.is_aggregate for _, term in self._cases]
+              [criterion.is_aggregate or term.is_aggregate for criterion, term in self._cases]
               + [self._else.is_aggregate if self._else else None]
         )
 


Test patch:
diff --git a/pypika/tests/test_aggregate.py b/pypika/tests/test_aggregate.py
index 44a7112..72b1769 100644
--- a/pypika/tests/test_aggregate.py
+++ b/pypika/tests/test_aggregate.py
@@ -53,6 +53,15 @@ class IsAggregateTests(unittest.TestCase):
         v = 1 / fn.Sum(Field("foo"))
         self.assertTrue(v.is_aggregate)
 
+    def test__agg_case_criterion_is_aggregate(self):
+        v = (
+            Case()
+            .when(fn.Sum(Field("foo")) > 666, 'More than 666')
+            .else_('Less than 666')
+        )
+
+        self.assertTrue(v.is_aggregate)
+
     def test__agg_case_is_aggregate(self):
         v = (
             Case()
@@ -97,7 +106,7 @@ class IsAggregateTests(unittest.TestCase):
 
         self.assertFalse(v.is_aggregate)
 
-    def test__case_with_single_aggregate_field_is_not_aggregate(self):
+    def test__case_with_single_aggregate_field_in_one_criterion_is_aggregate(self):
         v = (
             Case()
             .when(Field("foo") == 1, 1)
@@ -105,12 +114,7 @@ class IsAggregateTests(unittest.TestCase):
             .else_(3)
         )
 
-        self.assertFalse(v.is_aggregate)
-
-    def test__case_all_constants_is_aggregate_none(self):
-        v = Case().when(True, 1).when(False, 2).else_(3)
-
-        self.assertIsNone(v.is_aggregate)
+        self.assertTrue(v.is_aggregate)
 
     def test__non_aggregate_function_with_aggregated_arg(self):
         t = Table("abc")
Repository: kayak/pypika
Pull request #453 (2020-06-24 08:58:31)

Change Tuple class to use resolve_is_aggregate in is_aggregate


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index a5095c9..d5d850e 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -558,7 +558,7 @@ class Tuple(Criterion):
 
     @property
     def is_aggregate(self) -> bool:
-        return all([value.is_aggregate for value in self.values])
+        return resolve_is_aggregate([val.is_aggregate for val in self.values])
 
     @builder
     def replace_table(self, current_table: Optional["Table"], new_table: Optional["Table"]) -> "Tuple":


Test patch:
diff --git a/pypika/tests/test_tuples.py b/pypika/tests/test_tuples.py
index 374c03c..2fab67f 100644
--- a/pypika/tests/test_tuples.py
+++ b/pypika/tests/test_tuples.py
@@ -3,12 +3,12 @@ import unittest
 from pypika import (
     Array,
     Bracket,
-    PostgreSQLQuery,
     Query,
     Table,
     Tables,
     Tuple,
 )
+from pypika.terms import AggregateFunction, Function
 
 
 class TupleTests(unittest.TestCase):
@@ -124,6 +124,27 @@ class TupleTests(unittest.TestCase):
         q = Query.from_(tb).select(Tuple(tb.col).as_("different_name"))
         self.assertEqual(str(q), 'SELECT ("col") "different_name" FROM "tb"')
 
+    def test_tuple_is_aggregate_None_if_args_implement_is_aggregate_None(self):
+        with self.subTest('single non-aggregatable argument'):
+            self.assertEqual(None, Tuple(0).is_aggregate)
+
+        with self.subTest('multiple non-aggregatable arguments'):
+            self.assertEqual(None, Tuple(0, 'a').is_aggregate)
+
+    def test_tuple_is_aggregate_True_if_args_all_implement_is_aggregate_True(self):
+        with self.subTest('single aggregatable function'):
+            self.assertEqual(True, Tuple(AggregateFunction('col')).is_aggregate)
+
+        with self.subTest('multiple aggregatable functions'):
+            self.assertEqual(True, Tuple(AggregateFunction('col'), AggregateFunction('col2')).is_aggregate)
+
+    def test_tuple_is_aggregate_False_if_some_of_the_args_implement_is_aggregate_False(self):
+        with self.subTest('single non-aggregatable function'):
+            self.assertEqual(False, Tuple(Function('col')).is_aggregate)
+
+        with self.subTest('when a mix of non-aggregatable and aggregatable functions'):
+            self.assertEqual(False, Tuple(Function('col'), Function('col2', 0), AggregateFunction('col3')).is_aggregate)
+
 
 class ArrayTests(unittest.TestCase):
     table_abc, table_efg = Tables("abc", "efg")
Repository: kayak/pypika
Pull request #457 (2020-06-27 21:45:03)

Support JOINs with _SetOperation subqueries (union, itersect, except, minus)
Joining with a union subquery fails with: ValueError: Cannot join on type '<class 'pypika.queries._SetOperation'>' 

Example:
```
        subquery = (
            Query.from_(self.table_abc).select("*")
            .union(
                Query.from_(self.table_abc).select("*")
            ).as_("subq")
        )

        test_query = (
            Query.from_(self.table_abc)
            .join(subquery)
            .on(subquery.x == self.table_abc.id)
            .select(self.table_abc.foo)
        )
```





Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index 56db694..5495220 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -921,7 +921,9 @@ class QueryBuilder(Selectable, Term):
             self._orderbys.append((field, kwargs.get("order")))
 
     @builder
-    def join(self, item: Union[Table, "QueryBuilder", AliasedQuery], how: JoinType = JoinType.inner) -> "Joiner":
+    def join(
+        self, item: Union[Table, "QueryBuilder", AliasedQuery, Selectable], how: JoinType = JoinType.inner
+    ) -> "Joiner":
         if isinstance(item, Table):
             return Joiner(self, item, how, type_label="table")
 
@@ -933,6 +935,9 @@ class QueryBuilder(Selectable, Term):
         elif isinstance(item, AliasedQuery):
             return Joiner(self, item, how, type_label="table")
 
+        elif isinstance(item, Selectable):
+            return Joiner(self, item, how, type_label="subquery")
+
         raise ValueError("Cannot join on type '%s'" % type(item))
 
     def inner_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":


Test patch:
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index 5ceb305..408468a 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -728,6 +728,30 @@ class JoinBehaviorTests(unittest.TestCase):
             str(test_query),
         )
 
+    def test_join_query_with_setoperation(self):
+        subquery = (
+            Query.from_(self.table_abc).select("*")
+            .union(
+                Query.from_(self.table_abc).select("*")
+            ).as_("subq")
+        )
+
+        test_query = (
+            Query.from_(self.table_abc)
+            .join(subquery)
+            .on(subquery.x == self.table_abc.id)
+            .select(self.table_abc.foo)
+        )
+
+        self.assertEqual(
+            'SELECT "abc"."foo" FROM "abc" '
+            'JOIN '
+            '((SELECT * FROM "abc") '
+            'UNION '
+            '(SELECT * FROM "abc")) "subq" '
+            'ON "subq"."x"="abc"."id"',
+            str(test_query)
+        )
 
 class UnionTests(unittest.TestCase):
     table1, table2 = Tables("abc", "efg")