nebius/SWE-rebench-V2-PRs huggingface dataset:
Repository: kayak/pypika
Pull request #577 (2021-03-17 08:39:28)

Change Case class to inherit from Criterion
* This allows CASE statements to be used in where clauses when the where clause contains a Case instance and subsequent instances derived from Criterion

coveralls: 
[![Coverage Status](https://coveralls.io/builds/38006465/badge)](https://coveralls.io/builds/38006465)

Coverage increased (+0.02%) to 98.323% when pulling **b0b577ebaf826320c3a05aa14d9da05e155c377a on mikeengland:fix-case** into **d8ed5442acb987cdaa9803b20ab335544ee10b5b on kayak:master**.


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index e3505b5..719421b 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1050,7 +1050,7 @@ class ArithmeticExpression(Term):
         return arithmetic_sql
 
 
-class Case(Term):
+class Case(Criterion):
     def __init__(self, alias: Optional[str] = None) -> None:
         super().__init__(alias=alias)
         self._cases = []


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 5d9916e..3d8ccc5 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -473,6 +473,26 @@ class WhereTests(unittest.TestCase):
 
         self.assertEqual('SELECT "foo" FROM "abc" FORCE INDEX ("egg") WHERE "foo"="bar"', str(q))
 
+    def test_where_with_multiple_wheres_using_and_case(self):
+        case_stmt = Case().when(self.t.foo == 'bar', 1).else_(0)
+        query = Query.from_(self.t).select(case_stmt).where(case_stmt & self.t.blah.isin(['test']))
+
+        self.assertEqual(
+            'SELECT CASE WHEN "foo"=\'bar\' THEN 1 ELSE 0 END FROM "abc" WHERE CASE WHEN "foo"=\'bar\' THEN 1 ELSE 0 '
+            'END AND "blah" IN (\'test\')',
+            str(query),
+        )
+
+    def test_where_with_multiple_wheres_using_or_case(self):
+        case_stmt = Case().when(self.t.foo == 'bar', 1).else_(0)
+        query = Query.from_(self.t).select(case_stmt).where(case_stmt | self.t.blah.isin(['test']))
+
+        self.assertEqual(
+            'SELECT CASE WHEN "foo"=\'bar\' THEN 1 ELSE 0 END FROM "abc" WHERE CASE WHEN "foo"=\'bar\' THEN 1 ELSE 0 '
+            'END OR "blah" IN (\'test\')',
+            str(query),
+        )
+
 
 class PreWhereTests(WhereTests):
     t = Table("abc")
Repository: kayak/pypika
Pull request #582 (2021-04-07 10:50:58)

add exists/not exists clause

coveralls: 
[![Coverage Status](https://coveralls.io/builds/38598541/badge)](https://coveralls.io/builds/38598541)

Coverage increased (+0.005%) to 98.319% when pulling **a55c87ac6e157c9c9bb10e30f451435b407cb443 on cpinamtz:feature/add_exist_clause** into **f9960d49714702fdc30e2a525f4a0ec0a9eb7e4d on kayak:master**.


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index b2f33cd..2237677 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -804,6 +804,23 @@ class ContainsCriterion(Criterion):
         self._is_negated = True
 
 
+class ExistsCriterion(Criterion):
+    def __init__(self, container, alias=None):
+        super(ExistsCriterion, self).__init__(alias)
+        self.container = container
+        self._is_negated = False
+
+    def get_sql(self, **kwargs):
+        # FIXME escape
+        return "{not_}EXISTS {container}".format(
+            container=self.container.get_sql(**kwargs), not_='NOT ' if self._is_negated else ''
+        )
+
+    def negate(self):
+        self._is_negated = True
+        return self
+
+
 class RangeCriterion(Criterion):
     def __init__(self, term: Term, start: Any, end: Any, alias: Optional[str] = None) -> str:
         super().__init__(alias)


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 223772b..d6fa20a 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -12,7 +12,7 @@ from pypika import (
     functions as fn,
 )
 from pypika.queries import QueryBuilder
-from pypika.terms import Mod
+from pypika.terms import ExistsCriterion, Mod
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -590,6 +590,27 @@ class LikeTests(unittest.TestCase):
         self.assertEqual('"like"."foo" GLOB \'a_b*\'', str(c2))
 
 
+class ExistsCriterionTests(unittest.TestCase):
+    t2 = Table("abc", alias="t2")
+    q2 = QueryBuilder().from_(t2).select(t2.field2)
+
+    def test_exists(self):
+        t1 = Table("def", alias="t1")
+        q1 = QueryBuilder().from_(t1).where(ExistsCriterion(self.q2)).select(t1.field1)
+
+        self.assertEqual(
+            'SELECT "t1"."field1" FROM "def" "t1" WHERE EXISTS (SELECT "t2"."field2" FROM "abc" "t2")', str(q1)
+        )
+
+    def test_not_exists(self):
+        t1 = Table("def", alias="t1")
+        q1 = QueryBuilder().from_(t1).where(ExistsCriterion(self.q2).negate()).select(t1.field1)
+
+        self.assertEqual(
+            'SELECT "t1"."field1" FROM "def" "t1" WHERE NOT EXISTS (SELECT "t2"."field2" FROM "abc" "t2")', str(q1)
+        )
+
+
 class ComplexCriterionTests(unittest.TestCase):
     table_abc, table_efg = Table("abc", alias="cx0"), Table("efg", alias="cx1")
Repository: kayak/pypika
Pull request #587 (2021-04-18 16:13:11)

Add IS NOT NULL operator
Hi! I'm unable to filter non-null values in CH with this syntax: `NOT "x" IS NULL`. It is invalid for CH. The correct expression is `"x" IS NOT NULL`. I added NotNullCriterion term to get things done. Thanks.


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 2237677..34109e4 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -127,6 +127,9 @@ class Term(Node):
     def notnull(self) -> "Not":
         return self.isnull().negate()
 
+    def isnotnull(self) -> 'NotNullCriterion':
+        return NotNullCriterion(self)
+
     def bitwiseand(self, value: int) -> "BitwiseAndCriterion":
         return BitwiseAndCriterion(self, self.wrap_constant(value))
 
@@ -937,6 +940,14 @@ class NullCriterion(Criterion):
         return format_alias_sql(sql, self.alias, **kwargs)
 
 
+class NotNullCriterion(NullCriterion):
+    def get_sql(self, with_alias: bool = False, **kwargs: Any) -> str:
+        sql = "{term} IS NOT NULL".format(
+            term=self.term.get_sql(**kwargs),
+        )
+        return format_alias_sql(sql, self.alias, **kwargs)
+
+
 class ComplexCriterion(BasicCriterion):
     def get_sql(self, subcriterion: bool = False, **kwargs: Any) -> str:
         sql = "{left} {comparator} {right}".format(


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index d6fa20a..da1803a 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -301,6 +301,13 @@ class NotTests(unittest.TestCase):
         self.assertEqual('NOT "foo" IS NULL', str(c1))
         self.assertEqual('NOT "cx0"."foo" IS NULL', str(c2))
 
+    def test_is_not_null(self):
+        c1 = Field("foo").isnotnull()
+        c2 = Field("foo", table=self.table_abc).isnotnull()
+
+        self.assertEqual('"foo" IS NOT NULL', str(c1))
+        self.assertEqual('"cx0"."foo" IS NOT NULL', str(c2))
+
     def test_not_null_with_alias(self):
         c1 = Field("foo").notnull().as_("something")
         c2 = Field("foo", table=self.table_abc).notnull().as_("something")
Repository: kayak/pypika
Pull request #589 (2021-04-23 13:39:38)

Test tabled fields dict key collision
`pypika.Field` inherits its `__hash__` from `Term` defined as 
```python
hash(self.get_sql(with_alias=True))
```
However for tabled `Fields` e.g.
```python
fld1 = Field("name", table=Table("tname1"))
fld2 = Field("name", table=Table("tname2"))
fld1.get_sql(with_alias=True) == fld2.get_sql(with_alias=True) == "name"
```
And thus the `__hash__` function would return the same result making Field not suited for being a key in a dictionary.



Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 34109e4..0917af1 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -264,7 +264,7 @@ class Term(Node):
         return self.get_sql(quote_char='"', secondary_quote_char="'")
 
     def __hash__(self) -> int:
-        return hash(self.get_sql(with_alias=True))
+        return hash(self.get_sql(with_alias=True, with_namespace=True))
 
     def get_sql(self, **kwargs: Any) -> str:
         raise NotImplementedError()


Test patch:
diff --git a/pypika/tests/test_terms.py b/pypika/tests/test_terms.py
index 7dea6b4..562e0d2 100644
--- a/pypika/tests/test_terms.py
+++ b/pypika/tests/test_terms.py
@@ -15,6 +15,30 @@ class FieldAliasTests(TestCase):
         self.assertEqual('bar', str(c1.alias))
 
 
+class FieldHashingTests(TestCase):
+    def test_tabled_eq_fields_equally_hashed(self):
+        client_name1 = Field(name="name", table=Table("clients"))
+        client_name2 = Field(name="name", table=Table("clients"))
+        self.assertTrue(hash(client_name1) == hash(client_name2))
+
+    def test_tabled_ne_fields_differently_hashed(self):
+        customer_name = Field(name="name", table=Table("customers"))
+        client_name = Field(name="name", table=Table("clients"))
+        self.assertTrue(hash(customer_name) != hash(client_name))
+
+    def test_non_tabled_aliased_eq_fields_equally_hashed(self):
+        self.assertTrue(hash(Field(name="A", alias="my_a")) == hash(Field(name="A", alias="my_a")))
+
+    def test_non_tabled_aliased_ne_fields_differently_hashed(self):
+        self.assertTrue(hash(Field(name="A", alias="my_a1")) != hash(Field(name="A", alias="my_a2")))
+
+    def test_non_tabled_eq_fields_equally_hashed(self):
+        self.assertTrue(hash(Field(name="A")) == hash(Field(name="A")))
+
+    def test_non_tabled_ne_fields_differently_hashed(self):
+        self.assertTrue(hash(Field(name="A")) != hash(Field(name="B")))
+
+
 class AtTimezoneTests(TestCase):
     def test_when_interval_not_specified(self):
         query = Query.from_("customers").select(AtTimezone("date", "US/Eastern"))
Repository: kayak/pypika
Pull request #614 (2021-07-09 02:06:05)

Accept EmptyCriterion for HAVING filtering
Right now, if we are writing a query similar at

```
Query.from_(Table('t')).select("*").having(EmptyCriterion())
```
Or, with a real world query

```
Query.from_(Table('t')).select("*").having(Criterion.all([
   # A generator who returns nothing
]))
```

It will raise this exception.

```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/guillaume/.virtualenvs/project/lib/python3.8/site-packages/pypika/queries.py", line 1188, in __repr__
    return self.__str__()
  File "/home/guillaume/.virtualenvs/project/lib/python3.8/site-packages/pypika/queries.py", line 1185, in __str__
    return self.get_sql(dialect=self.dialect)
  File "/home/guillaume/.virtualenvs/project/lib/python3.8/site-packages/pypika/queries.py", line 1322, in get_sql
    querystring += self._having_sql(**kwargs)
  File "/home/guillaume/.virtualenvs/project/lib/python3.8/site-packages/pypika/queries.py", line 1515, in _having_sql
    return " HAVING {having}".format(having=self._havings.get_sql(quote_char=quote_char, **kwargs))
TypeError: get_sql() got an unexpected keyword argument 'quote_char'
```

The following pull-request solve the issue, with identifying if we have an empty criterion or not, as we do for the where filtering


Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index e268992..6366f89 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -933,7 +933,10 @@ class QueryBuilder(Selectable, Term):
             self._wheres = criterion
 
     @builder
-    def having(self, criterion: Term) -> "QueryBuilder":
+    def having(self, criterion: Union[Term, EmptyCriterion]) -> "QueryBuilder":
+        if isinstance(criterion, EmptyCriterion):
+            return
+
         if self._havings:
             self._havings &= criterion
         else:


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index de4e77b..f8f5c08 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -468,11 +468,16 @@ class WhereTests(unittest.TestCase):
 
         self.assertEqual('SELECT * FROM "abc" WHERE "foo" RLIKE \'^b\'', str(q))
 
-    def test_ignore_empty_criterion(self):
+    def test_ignore_empty_criterion_where(self):
         q1 = Query.from_(self.t).select("*").where(EmptyCriterion())
 
         self.assertEqual('SELECT * FROM "abc"', str(q1))
 
+    def test_ignore_empty_criterion_having(self):
+        q1 = Query.from_(self.t).select("*").having(EmptyCriterion())
+
+        self.assertEqual('SELECT * FROM "abc"', str(q1))
+
     def test_select_with_force_index_and_where(self):
         q = Query.from_("abc").select("foo").where(self.t.foo == self.t.bar).force_index("egg")
Repository: kayak/pypika
Pull request #707 (2022-10-12 08:30:30)

Add support of SAMPLE ClickHouse clause

coveralls: 
[![Coverage Status](https://coveralls.io/builds/53200889/badge)](https://coveralls.io/builds/53200889)

Coverage increased (+0.005%) to 98.425% when pulling **f78214334659f1cf9518135a392ec71d837ec77d on Kadiska:gt/clickhouse_sample** into **30574f997c80851f7e940ad09a63e14a98871dd3 on kayak:master**.

AzisK: Could you pull from the master branch so that the newest CI test kick in?
YiuRULE: done, it has been rebase to master

Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 6e151d6..8894e56 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -781,6 +781,16 @@ class ClickHouseQuery(Query):
 class ClickHouseQueryBuilder(QueryBuilder):
     QUERY_CLS = ClickHouseQuery
 
+    def __init__(self, **kwargs) -> None:
+        super().__init__(**kwargs)
+        self._sample = None
+        self._sample_offset = None
+
+    @builder
+    def sample(self, sample: int, offset: Optional[int] = None) -> "ClickHouseQueryBuilder":
+        self._sample = sample
+        self._sample_offset = offset
+
     @staticmethod
     def _delete_sql(**kwargs: Any) -> str:
         return 'ALTER TABLE'
@@ -792,7 +802,12 @@ class ClickHouseQueryBuilder(QueryBuilder):
         selectable = ",".join(clause.get_sql(subquery=True, with_alias=True, **kwargs) for clause in self._from)
         if self._delete_from:
             return " {selectable} DELETE".format(selectable=selectable)
-        return " FROM {selectable}".format(selectable=selectable)
+        clauses = [selectable]
+        if self._sample is not None:
+            clauses.append(f"SAMPLE {self._sample}")
+        if self._sample_offset is not None:
+            clauses.append(f"OFFSET {self._sample_offset}")
+        return " FROM {clauses}".format(clauses=" ".join(clauses))
 
     def _set_sql(self, **kwargs: Any) -> str:
         return " UPDATE {set}".format(


Test patch:
diff --git a/pypika/tests/dialects/test_clickhouse.py b/pypika/tests/dialects/test_clickhouse.py
index 1a8b8b8..8da6270 100644
--- a/pypika/tests/dialects/test_clickhouse.py
+++ b/pypika/tests/dialects/test_clickhouse.py
@@ -13,6 +13,16 @@ class ClickHouseQueryTests(TestCase):
         query = ClickHouseQuery.from_(t).select(t.foo.as_('f1'), t.bar.as_('f2'))
         self.assertEqual(str(query), 'SELECT "foo" AS "f1","bar" AS "f2" FROM "abc"')
 
+    def test_use_SAMPLE_keyword(self):
+        t = Table('abc')
+        query = ClickHouseQuery.from_(t).select(t.foo).sample(10)
+        self.assertEqual(str(query), 'SELECT "foo" FROM "abc" SAMPLE 10')
+
+    def test_use_SAMPLE_with_offset_keyword(self):
+        t = Table('abc')
+        query = ClickHouseQuery.from_(t).select(t.foo).sample(10, 5)
+        self.assertEqual(str(query), 'SELECT "foo" FROM "abc" SAMPLE 10 OFFSET 5')
+
 
 class ClickHouseDeleteTests(TestCase):
     table_abc = Table("abc")
Repository: kayak/pypika
Pull request #34 (2017-03-29 14:39:05)

Added ToChar function
I added a `ToChar()` function. Amongst other uses, this is needed to extract strings from datetime objects.  


Patch:
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 6def655..e6b0c51 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -38,4 +38,4 @@ from .utils import JoinException, GroupingException, CaseException, UnionExcepti
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.2.2"
+__version__ = "0.2.3"
diff --git a/pypika/functions.py b/pypika/functions.py
index f815d20..acc7ab7 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -91,6 +91,18 @@ class Convert(Function):
         )
 
 
+class ToChar(Function):
+    def __init__(self, term, as_type, alias=None):
+        super(ToChar, self).__init__('TO_CHAR', term, as_type, alias=alias)
+
+    def get_sql(self, **kwargs):
+        return '{name}({field}, {type})'.format(
+            name=self.name,
+            field=self.params[0],
+            type=self.params[1],
+        )
+
+
 class Signed(Cast):
     def __init__(self, term, alias=None):
         super(Signed, self).__init__(term, SqlTypes.SIGNED, alias=alias)


Test patch:
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index 4a7651a..0b16d8b 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -391,6 +391,11 @@ class CastTests(unittest.TestCase):
         self.assertEqual("SELECT TIMESTAMP(\"foo\") FROM \"abc\"", str(q1))
         self.assertEqual("SELECT CAST(\"foo\" AS TIMESTAMP) FROM \"abc\"", str(q2))
 
+    def test__tochar__(self):
+        q = Q.from_(self.t).select(fn.ToChar(self.t.foo, "SomeFormat"))
+
+        self.assertEqual("SELECT TO_CHAR(\"foo\", 'SomeFormat') FROM \"abc\"", str(q))
+
 
 class DateFunctionsTests(unittest.TestCase):
     dt = F('dt')