Repository: kayak/pypika
Pull request #666 (2022-01-31 18:33:48)

Fix: Add py.typed to root package
I don't believe I need to add this to the pypika.clickhouse package.  Works well in vscode and along with mypy.

whardier: Referencing #609 
Referencing #323
Referencing #410, #413, #414, #418

Patch:
diff --git a/.github/workflows/black.yml b/.github/workflows/black.yml
deleted file mode 100644
index c386fce..0000000
--- a/.github/workflows/black.yml
+++ /dev/null
@@ -1,12 +0,0 @@
----
-name: Lint
-
-on: [push, pull_request]
-
-jobs:
-  lint:
-    runs-on: ubuntu-latest
-    steps:
-      - uses: actions/checkout@v2
-      - uses: actions/setup-python@v2
-      - uses: psf/black@21.5b1
diff --git a/.github/workflows/pythonpackage.yml b/.github/workflows/ci.yml
similarity index 51%
rename from .github/workflows/pythonpackage.yml
rename to .github/workflows/ci.yml
index 8665b42..cce2084 100644
--- a/.github/workflows/pythonpackage.yml
+++ b/.github/workflows/ci.yml
@@ -1,32 +1,36 @@
-name: Unit Tests
+name: CI
 
 on:
   push:
-    branches: ["master"]
   pull_request:
-    branches: ["master"]
+  workflow_dispatch:
 
 jobs:
-  test:
+  lint:
+    name: Lint
+    runs-on: ubuntu-latest
+    steps:
+      - uses: actions/checkout@v4
+      - uses: actions/setup-python@v4
+      - uses: pre-commit/action@v3.0.0
 
+  test:
+    name: Unit Tests
     runs-on: ubuntu-latest
     strategy:
-      max-parallel: 4
+      max-parallel: 5
       matrix:
-        python-version: [3.6, 3.7, 3.8, 3.9]
+        python-version: ["3.8", "3.9", "3.10", "3.11"]
 
     steps:
-      - uses: actions/checkout@v1
+      - uses: actions/checkout@v4
       - name: Set up Python ${{ matrix.python-version }}
-        uses: actions/setup-python@v1
+        uses: actions/setup-python@v4
         with:
           python-version: ${{ matrix.python-version }}
 
       - name: Install dependencies
-        run: |
-          python -m pip install --upgrade pip
-          pip install -r requirements-dev.txt
-          pip install "coveralls<3.0.0"
+        run: python -m pip install -U pip -r requirements-dev.txt "coveralls<3.0.0"
 
       - name: Run test suite
         env:
diff --git a/.gitignore b/.gitignore
index f7aa02f..2ef1831 100644
--- a/.gitignore
+++ b/.gitignore
@@ -70,6 +70,7 @@ target/
 # virtualenv
 venv/
 ENV/
+.venv/
 
 # OS X
 .DS_Store
diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
new file mode 100644
index 0000000..3232a4d
--- /dev/null
+++ b/.pre-commit-config.yaml
@@ -0,0 +1,25 @@
+exclude: '.git'
+default_stages: [commit]
+fail_fast: false
+
+repos:
+  - repo: https://github.com/pre-commit/pre-commit-hooks
+    rev: v4.4.0
+    hooks:
+      - id: trailing-whitespace
+        files: "pypika.*"
+        exclude: ".*json$|.*txt$|.*csv|.*md|.*svg"
+      - id: check-yaml
+      - id: no-commit-to-branch
+        args: ['--branch', 'master']
+      - id: check-merge-conflict
+      - id: check-ast
+      - id: check-json
+      - id: check-toml
+      - id: check-yaml
+      - id: debug-statements
+
+  - repo: https://github.com/psf/black
+    rev: 23.9.1
+    hooks:
+      - id: black
diff --git a/CONTRIBUTING.rst b/CONTRIBUTING.rst
new file mode 100644
index 0000000..efbb472
--- /dev/null
+++ b/CONTRIBUTING.rst
@@ -0,0 +1,101 @@
+Guidelines for Contributing
+===========================
+
+PyPika welcomes contributions in all forms. These may be bugs, feature requests, documentation, or examples. Please feel free to:
+
+#. Submitting an issue
+#. Opening a pull request
+#. Helping with outstanding issues and pull requests 
+
+Open an issue
+-------------
+
+If you find a bug or have a feature request, please `open an issue <https://github.com/kayak/pypika/issues>`_ on GitHub. Please just check that the issue doesn't already exist before opening a new one.
+
+Local development steps
+-----------------------
+
+Create a forked branch of the repo
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+Do this once but keep it up to date
+
+#. `Fork the kayak/PyPika repo GitHub <https://github.com/kayak/pypika/fork>`_
+#. Clone forked repo and set upstream
+
+    .. code-block:: bash
+
+        git clone git@github.com:<your-username>/pypika.git
+        cd pypika
+        git remote add upstream git@github.com:kayak/pypika.git
+
+Setup local development environment
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+#. Setup up python virtual environment
+
+    Create and activate the environment. Here is an example using ``venv`` from the standard library:
+
+    .. code-block:: bash
+
+        python -m venv .venv
+        source .venv/bin/activate
+
+#. Install python dependencies for development
+
+    With the virtual environment activated, install the development requirements, pre-commit, and the package itself:
+
+    .. code-block:: bash
+
+        make install
+
+#. Run the tests
+
+    The unit tests are run with ``unittest`` via ``tox``. To run the tests locally:
+
+    .. code-block:: bash 
+
+        make test
+
+    These tests will also run on GitHub Actions when you open a pull request.
+
+#. Build the docs locally
+
+    Our docs are built with Sphinx. To build the docs locally:
+
+    .. code-block:: bash 
+
+        make docs.build
+
+    Open the docs in your browser. For instance, on macOS:
+
+    .. code-block:: bash
+
+        open docs/_build/index.html
+
+Pull Request checklist
+----------------------
+
+Please check that your pull request meets the following criteria:
+
+- Unit tests pass
+- pre-commit hooks pass
+- Docstring and examples and checking for correct render in the docs
+
+Documentation
+-------------
+
+Documentation is built with Sphinx and hosted on `Read the Docs <https://pypika.readthedocs.io/en/latest/>`_. The latest builds are displayed on their site `here <https://readthedocs.org/projects/pypika/builds/>`_.
+
+The code documentation is to be written in the docstrings of the code itself or in the various ``.rst`` files in project root or the ``docs/`` directory.
+
+The docstrings can be in either `Numpy <https://numpydoc.readthedocs.io/en/latest/format.html>`_ or `Sphinx <https://sphinx-rtd-tutorial.readthedocs.io/en/latest/docstrings.html>`_ format.
+
+Automations
+-----------
+
+We use `pre-commit <https://pre-commit.com/>`_ to automate format checks. Install the pre-commit hooks with the ``make install`` command described above.
+
+GitHub Actions runs both format checks and unit tests on every pull request.
+
+**NOTE:** The hosted documentation is built separately from the GitHub Actions workflow. To build the docs locally, use the ``make docs.build`` command described above.
\ No newline at end of file
diff --git a/Makefile b/Makefile
new file mode 100644
index 0000000..aec52ac
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,23 @@
+# You can set these variables from the command line.
+SPHINXBUILD   = sphinx-build
+SOURCEDIR     = docs
+BUILDDIR      = docs/_build
+
+
+help:            ## Show this help.
+	@fgrep -h "##" $(MAKEFILE_LIST) | fgrep -v fgrep | sed -e 's/\\$$//' | sed -e 's/##//'
+
+install:         ## Install development dependencies
+	pip install -r requirements-dev.txt pre-commit -e . 
+	pre-commit install
+
+test:            ## Run tests
+	tox
+
+docs.build:      ## Build the documentation
+	$(SPHINXBUILD) $(SOURCEDIR) $(BUILDDIR) -b html -E
+	@echo
+	@echo "Build finished. The HTML pages are in $(BUILDDIR)."
+
+docs.clean:      ## Remove the generated documentation
+	rm -rf $(BUILDDIR)
diff --git a/README.rst b/README.rst
index 3d90bf9..31bb94f 100644
--- a/README.rst
+++ b/README.rst
@@ -510,7 +510,7 @@ Example of a correlated subquery in the `SELECT`
 Unions
 """"""
 
-Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with "UNION`` so |Brand| does not
+Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonymous with "UNION`` so |Brand| does not
 provide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so
 trying to cast a unioned query to string will throw a ``SetOperationException`` if the column sizes are mismatched.
 
@@ -1274,12 +1274,166 @@ This produces:
 
         CREATE TABLE "names" AS (SELECT "last_name","first_name" FROM "person")
 
+Managing Table Indices
+^^^^^^^^^^^^^^^^^^^^^^
+
+Create Indices
+""""""""""""""""
+
+The entry point for creating indices is ``pypika.Query.create_index``.
+An index name (as ``str``) or a ``pypika.terms.Index`` a table (as ``str`` or ``pypika.Table``) and
+columns (as ``pypika.Column``) must be specified.
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    person = Table("person")
+    stmt = Query \
+        .create_index(my_index) \
+        .on(person) \
+        .columns(person.first_name, person.last_name)
+
+This produces:
+
+.. code-block:: sql
+
+    CREATE INDEX my_index
+    ON person (first_name, last_name)
+
+It is also possible to create a unique index
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    person = Table("person")
+    stmt = Query \
+        .create_index(my_index) \
+        .on(person) \
+        .columns(person.first_name, person.last_name) \
+        .unique()
+
+This produces:
+
+.. code-block:: sql
+
+        CREATE UNIQUE INDEX my_index
+        ON person (first_name, last_name)
+
+It is also possible to create an index if it does not exist
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    person = Table("person")
+    stmt = Query \
+        .create_index(my_index) \
+        .on(person) \
+        .columns(person.first_name, person.last_name) \
+        .if_not_exists()
+
+This produces:
+
+.. code-block:: sql
+
+        CREATE INDEX IF NOT EXISTS my_index
+        ON person (first_name, last_name)
+
+Drop Indices
+""""""""""""""""
+
+Then entry point for dropping indices is ``pypika.Query.drop_index``.
+It takes either ``str`` or ``pypika.terms.Index`` as an argument.
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    stmt = Query.drop_index(my_index)
+
+This produces:
+
+.. code-block:: sql
+
+    DROP INDEX my_index
+
+It is also possible to drop an index if it exists
+
+.. code-block:: python
+
+    my_index = Index("my_index")
+    stmt = Query.drop_index(my_index).if_exists()
+
+This produces:
+
+.. code-block:: sql
+
+    DROP INDEX IF EXISTS my_index
+
+
+Chaining Functions
+^^^^^^^^^^^^^^^^^^
+
+The ``QueryBuilder.pipe`` method gives a more readable alternative while chaining functions.
+
+.. code-block:: python 
+
+    # This 
+    (
+        query
+        .pipe(func1, *args)
+        .pipe(func2, **kwargs)
+        .pipe(func3)
+    )
+
+    # Is equivalent to this
+    func3(func2(func1(query, *args), **kwargs))
+
+Or for a more concrete example:
+
+.. code-block:: python 
+
+    from pypika import Field, Query, functions as fn
+    from pypika.queries import QueryBuilder
+
+    def filter_days(query: QueryBuilder, col, num_days: int) -> QueryBuilder: 
+        if isinstance(col, str): 
+            col = Field(col)
+
+        return query.where(col > fn.Now() - num_days)
+
+    def count_groups(query: QueryBuilder, *groups) -> QueryBuilder: 
+        return query.groupby(*groups).select(*groups, fn.Count("*").as_("n_rows"))
+
+    base_query = Query.from_("table")
+
+    query = (
+        base_query
+        .pipe(filter_days, "date", num_days=7)
+        .pipe(count_groups, "col1", "col2")
+    )
+
+This produces: 
+
+.. code-block:: sql
+
+    SELECT "col1","col2",COUNT(*) n_rows 
+    FROM "table" 
+    WHERE "date">NOW()-7 
+    GROUP BY "col1","col2"
+
 .. _tutorial_end:
 
+.. _contributing_start: 
 
-.. _license_start:
+Contributing
+------------
+
+We welcome community contributions to |Brand|. Please see the `contributing guide <6_contributing.html>`_ to more info.
+
+.. _contributing_end:
 
 
+.. _license_start:
+
 License
 -------
 
diff --git a/docs/3_advanced.rst b/docs/3_advanced.rst
index d6ae1e5..2dcf1e4 100644
--- a/docs/3_advanced.rst
+++ b/docs/3_advanced.rst
@@ -51,7 +51,8 @@ The pseudo-column can change from database to database, so here it's possible to
 
 .. code-block:: python
 
-    from pypika import Query, PseudoColumn
+    from pypika import Query
+    from pypika.terms import PseudoColumn
 
     CurrentDate = PseudoColumn('current_date')
 
diff --git a/docs/6_contributing.rst b/docs/6_contributing.rst
new file mode 100644
index 0000000..3bdd7dc
--- /dev/null
+++ b/docs/6_contributing.rst
@@ -0,0 +1,1 @@
+.. include:: ../CONTRIBUTING.rst
\ No newline at end of file
diff --git a/docs/api/pypika.analytics.rst b/docs/api/pypika.analytics.rst
new file mode 100644
index 0000000..36ae82e
--- /dev/null
+++ b/docs/api/pypika.analytics.rst
@@ -0,0 +1,7 @@
+pypika.analytics module
+=======================
+
+.. automodule:: pypika.analytics
+    :members:
+    :undoc-members:
+    :show-inheritance:
\ No newline at end of file
diff --git a/docs/api/pypika.dialects.rst b/docs/api/pypika.dialects.rst
new file mode 100644
index 0000000..0c10e70
--- /dev/null
+++ b/docs/api/pypika.dialects.rst
@@ -0,0 +1,7 @@
+pypika.dialects module
+======================
+
+.. automodule:: pypika.dialects
+    :members:
+    :undoc-members:
+    :show-inheritance:
\ No newline at end of file
diff --git a/docs/api/pypika.pseudocolumns.rst b/docs/api/pypika.pseudocolumns.rst
new file mode 100644
index 0000000..dc57833
--- /dev/null
+++ b/docs/api/pypika.pseudocolumns.rst
@@ -0,0 +1,7 @@
+pypika.pseudocolumns module
+===========================
+
+.. automodule:: pypika.pseudocolumns
+    :members:
+    :undoc-members:
+    :show-inheritance:
\ No newline at end of file
diff --git a/docs/api/pypika.rst b/docs/api/pypika.rst
index bff77c9..2628284 100644
--- a/docs/api/pypika.rst
+++ b/docs/api/pypika.rst
@@ -3,9 +3,11 @@ pypika package
 
 .. toctree::
 
-
+   pypika.analytics
+   pypika.dialects
    pypika.enums
    pypika.functions
+   pypika.pseudocolumns
    pypika.queries
    pypika.terms
    pypika.utils
diff --git a/docs/conf.py b/docs/conf.py
index 9f49b4f..57f8d39 100644
--- a/docs/conf.py
+++ b/docs/conf.py
@@ -39,6 +39,7 @@ extensions = [
     'sphinx.ext.coverage',
     'sphinx.ext.imgmath',
     'sphinx.ext.viewcode',
+    'numpydoc',  
 ]
 
 # Add any paths that contain templates here, relative to this directory.
diff --git a/docs/index.rst b/docs/index.rst
index 9d7a3c4..72e39ea 100644
--- a/docs/index.rst
+++ b/docs/index.rst
@@ -9,6 +9,10 @@ PyPika - Python Query Builder
    :start-after: _appendix_start:
    :end-before:  _appendix_end:
 
+.. include:: ../README.rst
+   :start-after: _contributing_start:
+   :end-before:  _contributing_end:
+
 Contents
 --------
 
@@ -20,6 +24,7 @@ Contents
     3_advanced
     4_extending
     5_reference
+    6_contributing
 
 Indices and tables
 ------------------
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 445dac7..a875ae0 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -1,35 +1,49 @@
 """
 PyPika is divided into a couple of modules, primarily the ``queries`` and ``terms`` modules.
 
-pypika.queries
---------------
+pypika.analytics
+----------------
 
-This is where the ``Query`` class can be found which is the core class in PyPika.  Also, other top level classes such
-as ``Table`` can be found here.  ``Query`` is a container that holds all of the ``Term`` types together and also
-serializes the builder to a string.
+Wrappers for SQL analytic functions
 
-pypika.terms
+pypika.dialects
+---------------
+
+This contains all of the dialect specific implementations of the ``Query`` class.
+
+pypika.enums
 ------------
 
-This module contains the classes which represent individual parts of queries that extend the ``Term`` base class.
+Enumerated values are kept in this package which are used as options for Queries and Terms.
 
 pypika.functions
 ----------------
 
 Wrappers for common SQL functions are stored in this package.
 
-pypika.enums
-------------
+pypika.pseudocolumns
+--------------------
 
-Enumerated values are kept in this package which are used as options for Queries and Terms.
+Wrappers for common SQL pseudocolumns are stored in this package.
+
+pypika.queries
+--------------
+
+This is where the ``Query`` class can be found which is the core class in PyPika.  Also, other top level classes such
+as ``Table`` can be found here.  ``Query`` is a container that holds all of the ``Term`` types together and also
+serializes the builder to a string.
 
+pypika.terms
+------------
+
+This module contains the classes which represent individual parts of queries that extend the ``Term`` base class.
 
 pypika.utils
 ------------
 
 This contains all of the utility classes such as exceptions and decorators.
-
 """
+
 # noinspection PyUnresolvedReferences
 from pypika.dialects import (
     ClickHouseQuery,
@@ -100,7 +114,7 @@ from pypika.utils import (
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.48.8"
+__version__ = "0.48.9"
 
 NULL = NullValue()
 SYSTEM_TIME = SystemTimeValue()
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 6e151d6..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:
@@ -781,6 +794,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 +815,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(
diff --git a/pypika/enums.py b/pypika/enums.py
index 751889c..82febc3 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -139,7 +139,7 @@ class Dialects(Enum):
     ORACLE = "oracle"
     MSSQL = "mssql"
     MYSQL = "mysql"
-    POSTGRESQL = "postgressql"
+    POSTGRESQL = "postgresql"
     REDSHIFT = "redshift"
     SQLLITE = "sqllite"
     SNOWFLAKE = "snowflake"
diff --git a/pypika/functions.py b/pypika/functions.py
index 8358429..c820040 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -249,6 +249,11 @@ class RegexpLike(Function):
         super(RegexpLike, self).__init__("REGEXP_LIKE", term, pattern, modifiers, alias=alias)
 
 
+class Replace(Function):
+    def __init__(self, term, find_string, replace_with, alias=None):
+        super(Replace, self).__init__("REPLACE", term, find_string, replace_with, alias=alias)
+
+
 # Date/Time Functions
 class Now(Function):
     def __init__(self, alias=None):
diff --git a/pypika/py.typed b/pypika/py.typed
new file mode 100644
index 0000000..8b13789
--- /dev/null
+++ b/pypika/py.typed
@@ -0,0 +1,1 @@
+
diff --git a/pypika/queries.py b/pypika/queries.py
index 2adc1b1..c51c6b2 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1,11 +1,10 @@
 from copy import copy
 from functools import reduce
-from typing import Any, List, Optional, Sequence, Tuple as TypedTuple, Type, Union, Set
+from typing import Any, List, Optional, Sequence, Tuple as TypedTuple, Type, Union
 
 from pypika.enums import Dialects, JoinType, ReferenceOption, SetOperation
 from pypika.terms import (
     ArithmeticExpression,
-    Criterion,
     EmptyCriterion,
     Field,
     Function,
@@ -351,6 +350,15 @@ class Query:
     pattern.
 
     This class is immutable.
+
+    Examples
+    --------
+    Simple query
+
+    .. code-block:: python
+
+        from pypika import Query, Field
+        q = Query.from_('customers').select('*').where(Field("id") == 1)
     """
 
     @classmethod
@@ -368,7 +376,7 @@ class Query:
 
             An instance of a Table object or a string table name.
 
-        :returns QueryBuilder
+        :return: QueryBuilder
         """
         return cls._builder(**kwargs).from_(table)
 
@@ -384,6 +392,14 @@ class Query:
         """
         return CreateQueryBuilder().create_table(table)
 
+    @classmethod
+    def create_index(cls, index: Union[str, Index]) -> "CreateIndexBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the index name to be created. When using this
+        function, the query becomes a CREATE statement.
+        """
+        return CreateIndexBuilder().create_index(index)
+
     @classmethod
     def drop_database(cls, database: Union[Database, Table]) -> "DropQueryBuilder":
         """
@@ -432,6 +448,14 @@ class Query:
         """
         return DropQueryBuilder().drop_view(view)
 
+    @classmethod
+    def drop_index(cls, index: Union[str, Index]) -> "DropQueryBuilder":
+        """
+        Query builder entry point. Initializes query building and sets the index name to be dropped. When using this
+        function, the query becomes a DROP statement.
+        """
+        return DropQueryBuilder().drop_index(index)
+
     @classmethod
     def into(cls, table: Union[Table, str], **kwargs: Any) -> "QueryBuilder":
         """
@@ -443,7 +467,7 @@ class Query:
 
             An instance of a Table object or a string table name.
 
-        :returns QueryBuilder
+        :return QueryBuilder
         """
         return cls._builder(**kwargs).into(table)
 
@@ -463,7 +487,7 @@ class Query:
             A list of terms to select.  These can be any type of int, float, str, bool, or Term.  They cannot be a Field
             unless the function ``Query.from_`` is called first.
 
-        :returns QueryBuilder
+        :return: QueryBuilder
         """
         return cls._builder(**kwargs).select(*terms)
 
@@ -478,7 +502,7 @@ class Query:
 
             An instance of a Table object or a string table name.
 
-        :returns QueryBuilder
+        :return: QueryBuilder
         """
         return cls._builder(**kwargs).update(table)
 
@@ -492,7 +516,7 @@ class Query:
 
             A string table name.
 
-        :returns Table
+        :return: Table
         """
         kwargs["query_cls"] = cls
         return Table(table_name, **kwargs)
@@ -508,7 +532,7 @@ class Query:
 
             A list of string table names, or name and alias tuples.
 
-        :returns Table
+        :return: Table
         """
         kwargs["query_cls"] = cls
         return make_tables(*names, **kwargs)
@@ -1536,6 +1560,54 @@ class QueryBuilder(Selectable, Term):
             )
         )
 
+    def pipe(self, func, *args, **kwargs):
+        """Call a function on the current object and return the result.
+
+        Example usage:
+
+        .. code-block:: python
+
+            from pypika import Query, functions as fn
+            from pypika.queries import QueryBuilder
+
+            def rows_by_group(query: QueryBuilder, *groups) -> QueryBuilder:
+                return (
+                    query
+                    .select(*groups, fn.Count("*").as_("n_rows"))
+                    .groupby(*groups)
+                )
+
+            base_query = Query.from_("table")
+
+            col1_agg = base_query.pipe(rows_by_group, "col1")
+            col2_agg = base_query.pipe(rows_by_group, "col2")
+            col1_col2_agg = base_query.pipe(rows_by_group, "col1", "col2")
+
+        Makes chaining functions together easier, especially when the functions are
+        defined elsewhere. For example, you could define a function that filters
+        rows by a date range and then group by a set of columns:
+
+
+        .. code-block:: python
+
+            from datetime import datetime, timedelta
+
+            from pypika import Field
+
+            def days_since(query: QueryBuilder, n_days: int) -> QueryBuilder:
+                return (
+                    query
+                    .where("date" > fn.Date(datetime.now().date() - timedelta(days=n_days)))
+                )
+
+            (
+                base_query
+                .pipe(days_since, n_days=7)
+                .pipe(rows_by_group, "col1", "col2")
+            )
+        """
+        return func(self, *args, **kwargs)
+
 
 class Joiner:
     def __init__(
@@ -1564,8 +1636,8 @@ class Joiner:
 
         criterion = None
         for field in fields:
-            consituent = Field(field, table=self.query._from[0]) == Field(field, table=self.item)
-            criterion = consituent if criterion is None else criterion & consituent
+            constituent = Field(field, table=self.query._from[0]) == Field(field, table=self.item)
+            criterion = constituent if criterion is None else criterion & constituent
 
         self.query.do_join(JoinOn(self.item, self.how, criterion))
         return self.query
@@ -2042,6 +2114,70 @@ class CreateQueryBuilder:
         return self.__str__()
 
 
+class CreateIndexBuilder:
+    def __init__(self) -> None:
+        self._index = None
+        self._columns = []
+        self._table = None
+        self._wheres = None
+        self._is_unique = False
+        self._if_not_exists = False
+
+    @builder
+    def create_index(self, index: Union[str, Index]) -> "CreateIndexBuilder":
+        self._index = index
+
+    @builder
+    def columns(self, *columns: Union[str, TypedTuple[str, str], Column]) -> "CreateIndexBuilder":
+        for column in columns:
+            if isinstance(column, str):
+                column = Column(column)
+            elif isinstance(column, tuple):
+                column = Column(column_name=column[0], column_type=column[1])
+            self._columns.append(column)
+
+    @builder
+    def on(self, table: Union[Table, str]) -> "CreateIndexBuilder":
+        self._table = table
+
+    @builder
+    def where(self, criterion: Union[Term, EmptyCriterion]) -> "CreateIndexBuilder":
+        """
+        Partial index where clause.
+        """
+        if self._wheres:
+            self._wheres &= criterion
+        else:
+            self._wheres = criterion
+
+    @builder
+    def unique(self) -> "CreateIndexBuilder":
+        self._is_unique = True
+
+    @builder
+    def if_not_exists(self) -> "CreateIndexBuilder":
+        self._if_not_exists = True
+
+    def get_sql(self) -> str:
+        if not self._columns or len(self._columns) == 0:
+            raise AttributeError("Cannot create index without columns")
+        if not self._table:
+            raise AttributeError("Cannot create index without table")
+        columns_str = ", ".join([c.name for c in self._columns])
+        unique_str = "UNIQUE" if self._is_unique else ""
+        if_not_exists_str = "IF NOT EXISTS" if self._if_not_exists else ""
+        base_sql = f"CREATE {unique_str} INDEX {if_not_exists_str} {self._index} ON {self._table}({columns_str})"
+        if self._wheres:
+            base_sql += f" WHERE {self._wheres}"
+        return base_sql.replace("  ", " ")
+
+    def __str__(self) -> str:
+        return self.get_sql()
+
+    def __repr__(self) -> str:
+        return self.__str__()
+
+
 class DropQueryBuilder:
     """
     Query builder used to build DROP queries.
@@ -2081,6 +2217,10 @@ class DropQueryBuilder:
     def drop_view(self, view: str) -> "DropQueryBuilder":
         self._set_target('VIEW', view)
 
+    @builder
+    def drop_index(self, index: str) -> "DropQueryBuilder":
+        self._set_target('INDEX', index)
+
     @builder
     def if_exists(self) -> "DropQueryBuilder":
         self._if_exists = True
diff --git a/pypika/terms.py b/pypika/terms.py
index c522550..a277e1a 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -3,7 +3,21 @@ import re
 import uuid
 from datetime import date
 from enum import Enum
-from typing import TYPE_CHECKING, Any, Iterable, Iterator, List, Optional, Sequence, Set, Type, TypeVar, Union
+from typing import (
+    TYPE_CHECKING,
+    Any,
+    Callable,
+    Iterable,
+    Iterator,
+    List,
+    Optional,
+    Sequence,
+    Set,
+    Tuple,
+    Type,
+    TypeVar,
+    Union,
+)
 
 from pypika.enums import Arithmetic, Boolean, Comparator, Dialects, Equality, JSONOperators, Matching, Order
 from pypika.utils import (
@@ -184,12 +198,12 @@ class Term(Node):
     def all_(self) -> "All":
         return All(self)
 
-    def isin(self, arg: Union[list, tuple, set, "Term"]) -> "ContainsCriterion":
-        if isinstance(arg, (list, tuple, set)):
+    def isin(self, arg: Union[list, tuple, set, frozenset, "Term"]) -> "ContainsCriterion":
+        if isinstance(arg, (list, tuple, set, frozenset)):
             return ContainsCriterion(self, Tuple(*[self.wrap_constant(value) for value in arg]))
         return ContainsCriterion(self, arg)
 
-    def notin(self, arg: Union[list, tuple, set, "Term"]) -> "ContainsCriterion":
+    def notin(self, arg: Union[list, tuple, set, frozenset, "Term"]) -> "ContainsCriterion":
         return self.isin(arg).negate()
 
     def bin_regex(self, pattern: str) -> "BasicCriterion":
@@ -288,57 +302,111 @@ class Term(Node):
         raise NotImplementedError()
 
 
+def idx_placeholder_gen(idx: int) -> str:
+    return str(idx + 1)
+
+
+def named_placeholder_gen(idx: int) -> str:
+    return f'param{idx + 1}'
+
+
 class Parameter(Term):
     is_aggregate = None
 
     def __init__(self, placeholder: Union[str, int]) -> None:
         super().__init__()
-        self.placeholder = placeholder
+        self._placeholder = placeholder
+
+    @property
+    def placeholder(self):
+        return self._placeholder
 
     def get_sql(self, **kwargs: Any) -> str:
         return str(self.placeholder)
 
+    def update_parameters(self, param_key: Any, param_value: Any, **kwargs):
+        pass
 
-class QmarkParameter(Parameter):
-    """Question mark style, e.g. ...WHERE name=?"""
+    def get_param_key(self, placeholder: Any, **kwargs):
+        return placeholder
 
-    def __init__(self) -> None:
-        pass
 
-    def get_sql(self, **kwargs: Any) -> str:
-        return "?"
+class ListParameter(Parameter):
+    def __init__(self, placeholder: Union[str, int, Callable[[int], str]] = idx_placeholder_gen) -> None:
+        super().__init__(placeholder=placeholder)
+        self._parameters = list()
 
+    @property
+    def placeholder(self) -> str:
+        if callable(self._placeholder):
+            return self._placeholder(len(self._parameters))
 
-class NumericParameter(Parameter):
-    """Numeric, positional style, e.g. ...WHERE name=:1"""
+        return str(self._placeholder)
 
-    def get_sql(self, **kwargs: Any) -> str:
-        return ":{placeholder}".format(placeholder=self.placeholder)
+    def get_parameters(self, **kwargs):
+        return self._parameters
 
+    def update_parameters(self, value: Any, **kwargs):
+        self._parameters.append(value)
+
+
+class DictParameter(Parameter):
+    def __init__(self, placeholder: Union[str, int, Callable[[int], str]] = named_placeholder_gen) -> None:
+        super().__init__(placeholder=placeholder)
+        self._parameters = dict()
+
+    @property
+    def placeholder(self) -> str:
+        if callable(self._placeholder):
+            return self._placeholder(len(self._parameters))
+
+        return str(self._placeholder)
+
+    def get_parameters(self, **kwargs):
+        return self._parameters
+
+    def get_param_key(self, placeholder: Any, **kwargs):
+        return placeholder[1:]
+
+    def update_parameters(self, param_key: Any, value: Any, **kwargs):
+        self._parameters[param_key] = value
+
+
+class QmarkParameter(ListParameter):
+    def get_sql(self, **kwargs):
+        return '?'
 
-class NamedParameter(Parameter):
-    """Named style, e.g. ...WHERE name=:name"""
+
+class NumericParameter(ListParameter):
+    """Numeric, positional style, e.g. ...WHERE name=:1"""
 
     def get_sql(self, **kwargs: Any) -> str:
         return ":{placeholder}".format(placeholder=self.placeholder)
 
 
-class FormatParameter(Parameter):
+class FormatParameter(ListParameter):
     """ANSI C printf format codes, e.g. ...WHERE name=%s"""
 
-    def __init__(self) -> None:
-        pass
-
     def get_sql(self, **kwargs: Any) -> str:
         return "%s"
 
 
-class PyformatParameter(Parameter):
+class NamedParameter(DictParameter):
+    """Named style, e.g. ...WHERE name=:name"""
+
+    def get_sql(self, **kwargs: Any) -> str:
+        return ":{placeholder}".format(placeholder=self.placeholder)
+
+
+class PyformatParameter(DictParameter):
     """Python extended format codes, e.g. ...WHERE name=%(name)s"""
 
     def get_sql(self, **kwargs: Any) -> str:
         return "%({placeholder})s".format(placeholder=self.placeholder)
 
+    def get_param_key(self, placeholder: Any, **kwargs):
+        return placeholder[2:-2]
+
 
 class Negative(Term):
     def __init__(self, term: Term) -> None:
@@ -385,9 +453,44 @@ class ValueWrapper(Term):
             return "null"
         return str(value)
 
-    def get_sql(self, quote_char: Optional[str] = None, secondary_quote_char: str = "'", **kwargs: Any) -> str:
-        sql = self.get_value_sql(quote_char=quote_char, secondary_quote_char=secondary_quote_char, **kwargs)
-        return format_alias_sql(sql, self.alias, quote_char=quote_char, **kwargs)
+    def _get_param_data(self, parameter: Parameter, **kwargs) -> Tuple[str, str]:
+        param_sql = parameter.get_sql(**kwargs)
+        param_key = parameter.get_param_key(placeholder=param_sql)
+
+        return param_sql, param_key
+
+    def get_sql(
+        self,
+        quote_char: Optional[str] = None,
+        secondary_quote_char: str = "'",
+        parameter: Parameter = None,
+        **kwargs: Any,
+    ) -> str:
+        if parameter is None:
+            sql = self.get_value_sql(quote_char=quote_char, secondary_quote_char=secondary_quote_char, **kwargs)
+            return format_alias_sql(sql, self.alias, quote_char=quote_char, **kwargs)
+
+        # Don't stringify numbers when using a parameter
+        if isinstance(self.value, (int, float)):
+            value_sql = self.value
+        else:
+            value_sql = self.get_value_sql(quote_char=quote_char, **kwargs)
+        param_sql, param_key = self._get_param_data(parameter, **kwargs)
+        parameter.update_parameters(param_key=param_key, value=value_sql, **kwargs)
+
+        return format_alias_sql(param_sql, self.alias, quote_char=quote_char, **kwargs)
+
+
+class ParameterValueWrapper(ValueWrapper):
+    def __init__(self, parameter: Parameter, value: Any, alias: Optional[str] = None) -> None:
+        super().__init__(value, alias)
+        self._parameter = parameter
+
+    def _get_param_data(self, parameter: Parameter, **kwargs) -> Tuple[str, str]:
+        param_sql = self._parameter.get_sql(**kwargs)
+        param_key = self._parameter.get_param_key(placeholder=param_sql)
+
+        return param_sql, param_key
 
 
 class JSON(Term):
@@ -486,12 +589,18 @@ class SystemTimeValue(LiteralValue):
 
 class Criterion(Term):
     def __and__(self, other: Any) -> "ComplexCriterion":
+        if isinstance(other, EmptyCriterion):
+            return self
         return ComplexCriterion(Boolean.and_, self, other)
 
     def __or__(self, other: Any) -> "ComplexCriterion":
+        if isinstance(other, EmptyCriterion):
+            return self
         return ComplexCriterion(Boolean.or_, self, other)
 
     def __xor__(self, other: Any) -> "ComplexCriterion":
+        if isinstance(other, EmptyCriterion):
+            return self
         return ComplexCriterion(Boolean.xor_, self, other)
 
     @staticmethod
@@ -532,6 +641,9 @@ class EmptyCriterion(Criterion):
     def __xor__(self, other: Any) -> Any:
         return other
 
+    def __invert__(self) -> Any:
+        return self
+
 
 class Field(Criterion, JSON):
     def __init__(
@@ -539,6 +651,11 @@ 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]:
diff --git a/pypika/utils.py b/pypika/utils.py
index 1506704..ca3e9c4 100644
--- a/pypika/utils.py
+++ b/pypika/utils.py
@@ -1,4 +1,4 @@
-from typing import Any, Callable, List, Optional, Type
+from typing import Any, Callable, List, Optional, Type, TypeVar
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -36,7 +36,10 @@ class FunctionException(Exception):
     pass
 
 
-def builder(func: Callable) -> Callable:
+C = TypeVar("C")
+
+
+def builder(func: C) -> C:
     """
     Decorator for wrapper "builder" functions.  These are functions on the Query class or other classes used for
     building queries which mutate the query and return self.  To make the build functions immutable, this decorator is
diff --git a/pyproject.toml b/pyproject.toml
index 5f182ce..dcc56ba 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -1,8 +1,8 @@
 [tool.black]
 line-length = 120
 skip-string-normalization = true
-target-version = ['py36', 'py37', 'py38']
-exclude = '''
+target-version = ['py38', 'py39', 'py310', 'py311']
+force-exclude = '''
 (
   /(
       \.eggs         # exclude a few common directories in the
diff --git a/requirements-dev.txt b/requirements-dev.txt
index def44f9..e8f8d7f 100644
--- a/requirements-dev.txt
+++ b/requirements-dev.txt
@@ -2,6 +2,8 @@
 Sphinx==1.6.5
 sphinx-rtd-theme==0.2.4
 bumpversion==0.5.3
+jinja2==3.0.3
+numpydoc==1.1.0
 
 # Testing
 parameterized==0.7.0
@@ -9,6 +11,3 @@ tox==3.14.3
 tox-venv==0.4.0
 tox-gh-actions==0.3.0
 coverage==5.1
-
-# Formatting
-black==21.5b1
diff --git a/setup.cfg b/setup.cfg
index 029fb40..46084aa 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -1,5 +1,5 @@
 [bumpversion]
-current_version = 0.48.8
+current_version = 0.48.9
 commit = True
 tag = True
 
@@ -13,4 +13,4 @@ universal = 1
 [flake8]
 exclude = docs, .git, __pycache__
 max-line-length = 120
-
+per-file-ignores = __init__.py:F401
diff --git a/setup.py b/setup.py
index fb376d4..41cce7d 100644
--- a/setup.py
+++ b/setup.py
@@ -48,6 +48,7 @@ setup(
       packages=["pypika", "pypika.clickhouse"],
 
       # Include additional files into the package
+      package_data={"pypika": ["py.typed"]},
       include_package_data=True,
 
       install_requires=[],
diff --git a/tox.ini b/tox.ini
index cd8eb1d..2f0e689 100644
--- a/tox.ini
+++ b/tox.ini
@@ -1,13 +1,16 @@
 [tox]
-envlist = py36,py37,py38,py39,pypy3
+envlist = py38,py39,py310,py311,pypy3
+
 [testenv]
+description = testing against {basepython}
 deps = -r requirements-dev.txt
 commands =
     coverage run
     coverage xml
+
 [gh-actions]
 python =
-    3.6: py36
-    3.7: py37
     3.8: py38
     3.9: py39
+    3.10: py310
+    3.11: py311


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")
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_create.py b/pypika/tests/test_create.py
index e081a44..3250765 100644
--- a/pypika/tests/test_create.py
+++ b/pypika/tests/test_create.py
@@ -1,7 +1,7 @@
 import unittest
 
-from pypika import Column, Columns, Query, Tables
-from pypika.terms import ValueWrapper
+from pypika import Column, Columns, Query, Tables, Table
+from pypika.terms import ValueWrapper, Index
 from pypika.enums import ReferenceOption
 
 
@@ -165,3 +165,46 @@ class CreateTableTests(unittest.TestCase):
     def test_create_table_as_select_not_query_raises_error(self):
         with self.assertRaises(TypeError):
             Query.create_table(self.new_table).as_select("abc")
+
+
+class CreateIndexTests(unittest.TestCase):
+    new_index = Index("abc")
+    new_table = Table("my_table")
+    columns = (new_table.a, new_table.b)
+    where = new_table.a > 1
+
+    def test_create_index_simple(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns)
+        self.assertEqual(
+            f'CREATE INDEX "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])})',
+            q.get_sql(),
+        )
+
+    def test_create_index_where(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns).where(self.where)
+        self.assertEqual(
+            f'CREATE INDEX "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])}) WHERE {self.where.get_sql()}',
+            q.get_sql(),
+        )
+
+    def test_create_index_unique(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns).unique()
+        self.assertEqual(
+            f'CREATE UNIQUE INDEX "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])})',
+            q.get_sql(),
+        )
+
+    def test_create_index_if_not_exists(self) -> None:
+        q = Query.create_index(self.new_index).on(self.new_table).columns(*self.columns).if_not_exists()
+        self.assertEqual(
+            f'CREATE INDEX IF NOT EXISTS "{self.new_index.name}" ON "{self.new_table.get_table_name()}"({", ".join([c.name for c in self.columns])})',
+            q.get_sql(),
+        )
+
+    def test_create_index_without_columns_raises_error(self) -> None:
+        with self.assertRaises(AttributeError):
+            Query.create_index(self.new_index).on(self.new_table).get_sql()
+
+    def test_create_index_without_table_raises_error(self) -> None:
+        with self.assertRaises(AttributeError):
+            Query.create_index(self.new_index).columns(*self.columns).get_sql()
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 2ecaa13..fca2cee 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -438,6 +438,12 @@ class IsInTests(unittest.TestCase):
         self.assertEqual('COALESCE("foo",0) IN (0,1)', str(c1))
         self.assertEqual('COALESCE("isin"."foo",0) IN (0,1)', str(c2))
 
+        for t in (list, tuple, set, frozenset):
+            c_type = fn.Coalesce(Field("foo"), 0).isin(t([0, 1]))
+            self.assertEqual('COALESCE("foo",0) IN (0,1)', str(c_type))
+
+        self.assertRaises(AttributeError, lambda: str(fn.Coalesce(Field("foo"), 0).isin('SHOULD_FAIL')))
+
     def test__in_unicode(self):
         c1 = Field("foo").isin([u"a", u"b"])
         c2 = Field("foo", table=self.t).isin([u"a", u"b"])
@@ -791,6 +797,62 @@ class EmptyCriterionTests(unittest.TestCase):
 
         self.assertEqual(len(empty_criterion.fields_()), 0)
 
+    def test_empty_criterion_on_the_left(self):
+        t = Table("test1")
+        q = EmptyCriterion() & t.f1 == "v1"
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
+    def test_empty_criterion_on_the_right(self):
+        t = Table("test1")
+        q = (t.f1 == "v1") & EmptyCriterion()
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
+    def test_invertion_of_the_empty_criterion(self):
+        t = Table("test1")
+        q = (t.f1 == "v1") & ~EmptyCriterion()
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
+    def test_different_operations_with_empty_criterion(self):
+        t = Table("test1")
+        q1 = (t.f1 == "v1") & EmptyCriterion()
+        q2 = (t.f1 == "v1") | EmptyCriterion()
+        q3 = (t.f1 == "v1") ^ EmptyCriterion()
+
+        expected_sql = '"test1"."f1"=\'v1\''
+        self.assertEqual(
+            expected_sql,
+            q1.get_sql(with_namespace=True),
+        )
+        self.assertEqual(
+            expected_sql,
+            q2.get_sql(with_namespace=True),
+        )
+        self.assertEqual(
+            expected_sql,
+            q3.get_sql(with_namespace=True),
+        )
+
+    def test_more_than_one_empty_criterions(self):
+        t = Table("test1")
+        q = EmptyCriterion() & ~(EmptyCriterion() | EmptyCriterion()) & (t.f1 == "v1") & ~EmptyCriterion()
+
+        self.assertEqual(
+            '"test1"."f1"=\'v1\'',
+            q.get_sql(with_namespace=True),
+        )
+
 
 class AllTests(unittest.TestCase):
     def test_zero_args_returns_empty_criterion(self):
diff --git a/pypika/tests/test_drop.py b/pypika/tests/test_drop.py
index ef5b132..265ae73 100644
--- a/pypika/tests/test_drop.py
+++ b/pypika/tests/test_drop.py
@@ -40,3 +40,10 @@ class DropTableTests(unittest.TestCase):
 
         self.assertEqual('DROP VIEW "myview"', str(q1))
         self.assertEqual('DROP VIEW IF EXISTS "myview"', str(q2))
+
+    def test_drop_index(self):
+        q1 = Query.drop_index("myindex")
+        q2 = Query.drop_index("myindex").if_exists()
+
+        self.assertEqual('DROP INDEX "myindex"', str(q1))
+        self.assertEqual('DROP INDEX IF EXISTS "myindex"', str(q2))
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index f00d032..f526966 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -274,11 +274,11 @@ class ArithmeticTests(unittest.TestCase):
 
     def test__complex_op_exponent_parentheses(self):
         q1 = Q.from_("abc").select(F("a") / (F("b") ** 2))
-        q2 = Q.from_(self.t).select(self.t.a / (self.t.b ** 2))
+        q2 = Q.from_(self.t).select(self.t.a / (self.t.b**2))
         q3 = Q.from_("abc").select(F("a") ** (F("b") / 2))
         q4 = Q.from_(self.t).select(self.t.a ** (self.t.b / 2))
         q5 = Q.from_("abc").select((F("a") ** F("b")) ** 2)
-        q6 = Q.from_(self.t).select((self.t.a ** self.t.b) ** 2)
+        q6 = Q.from_(self.t).select((self.t.a**self.t.b) ** 2)
 
         self.assertEqual('SELECT "a"/POW("b",2) FROM "abc"', str(q1))
         self.assertEqual('SELECT "a"/POW("b",2) FROM "abc"', str(q2))
@@ -289,9 +289,9 @@ class ArithmeticTests(unittest.TestCase):
 
     def test__complex_op_exponent_no_parentheses(self):
         q1 = Q.from_("abc").select(F("a") ** F("b") ** 2)
-        q2 = Q.from_(self.t).select(self.t.a ** self.t.b ** 2)
+        q2 = Q.from_(self.t).select(self.t.a**self.t.b**2)
         q3 = Q.from_("abc").select(F("a") / F("b") ** 2)
-        q4 = Q.from_(self.t).select(self.t.a / self.t.b ** 2)
+        q4 = Q.from_(self.t).select(self.t.a / self.t.b**2)
 
         self.assertEqual('SELECT POW("a",POW("b",2)) FROM "abc"', str(q1))
         self.assertEqual('SELECT POW("a",POW("b",2)) FROM "abc"', str(q2))
@@ -369,14 +369,14 @@ class ArithmeticTests(unittest.TestCase):
 
     def test__exponent__number(self):
         q1 = Q.from_("abc").select(F("a") ** 2)
-        q2 = Q.from_(self.t).select(self.t.a ** 2)
+        q2 = Q.from_(self.t).select(self.t.a**2)
 
         self.assertEqual('SELECT POW("a",2) FROM "abc"', str(q1))
         self.assertEqual('SELECT POW("a",2) FROM "abc"', str(q2))
 
     def test__exponent__decimal(self):
         q1 = Q.from_("abc").select(F("a") ** 0.5)
-        q2 = Q.from_(self.t).select(self.t.a ** 0.5)
+        q2 = Q.from_(self.t).select(self.t.a**0.5)
 
         self.assertEqual('SELECT POW("a",0.5) FROM "abc"', str(q1))
         self.assertEqual('SELECT POW("a",0.5) FROM "abc"', str(q2))
@@ -570,6 +570,14 @@ class StringTests(unittest.TestCase):
 
         self.assertEqual('SELECT SUBSTRING("foo",2,6) FROM "abc"', str(q))
 
+    def test__replace__str(self):
+        q = Q.select(fn.Replace("ABC_DEF", "_", " "))
+        self.assertEqual("SELECT REPLACE('ABC_DEF','_',' ')", str(q))
+
+    def test__replace__field(self):
+        q = Q.from_(self.t).select(fn.Replace(self.t.foobar, self.t.foo, self.t.bar))
+        self.assertEqual('SELECT REPLACE("foobar","foo","bar") FROM "abc"', str(q))
+
 
 class SplitPartFunctionTests(unittest.TestCase):
     t = T("abc")
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_parameter.py b/pypika/tests/test_parameter.py
index e19666a..c11e9af 100644
--- a/pypika/tests/test_parameter.py
+++ b/pypika/tests/test_parameter.py
@@ -1,4 +1,5 @@
 import unittest
+from datetime import date
 
 from pypika import (
     FormatParameter,
@@ -10,6 +11,7 @@ from pypika import (
     Query,
     Tables,
 )
+from pypika.terms import ListParameter, ParameterValueWrapper
 
 
 class ParametrizedTests(unittest.TestCase):
@@ -92,3 +94,113 @@ class ParametrizedTests(unittest.TestCase):
 
     def test_pyformat_parameter(self):
         self.assertEqual('%(buz)s', PyformatParameter('buz').get_sql())
+
+
+class ParametrizedTestsWithValues(unittest.TestCase):
+    table_abc, table_efg = Tables("abc", "efg")
+
+    def test_param_insert(self):
+        q = Query.into(self.table_abc).columns("a", "b", "c").insert(1, 2.2, 'foo')
+
+        parameter = QmarkParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual('INSERT INTO "abc" ("a","b","c") VALUES (?,?,?)', sql)
+        self.assertEqual([1, 2.2, 'foo'], parameter.get_parameters())
+
+    def test_param_select_join(self):
+        q = (
+            Query.from_(self.table_abc)
+            .select("*")
+            .where(self.table_abc.category == 'foobar')
+            .join(self.table_efg)
+            .on(self.table_abc.id == self.table_efg.abc_id)
+            .where(self.table_efg.date >= date(2024, 2, 22))
+            .limit(10)
+        )
+
+        parameter = FormatParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT * FROM "abc" JOIN "efg" ON "abc"."id"="efg"."abc_id" WHERE "abc"."category"=%s AND "efg"."date">=%s LIMIT 10',
+            sql,
+        )
+        self.assertEqual(['foobar', '2024-02-22'], parameter.get_parameters())
+
+    def test_param_select_subquery(self):
+        q = (
+            Query.from_(self.table_abc)
+            .select("*")
+            .where(self.table_abc.category == 'foobar')
+            .where(
+                self.table_abc.id.isin(
+                    Query.from_(self.table_efg)
+                    .select(self.table_efg.abc_id)
+                    .where(self.table_efg.date >= date(2024, 2, 22))
+                )
+            )
+            .limit(10)
+        )
+
+        parameter = ListParameter(placeholder=lambda idx: f'&{idx+1}')
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT * FROM "abc" WHERE "category"=&1 AND "id" IN (SELECT "abc_id" FROM "efg" WHERE "date">=&2) LIMIT 10',
+            sql,
+        )
+        self.assertEqual(['foobar', '2024-02-22'], parameter.get_parameters())
+
+    def test_join(self):
+        subquery = (
+            Query.from_(self.table_efg)
+            .select(self.table_efg.fiz, self.table_efg.buz)
+            .where(self.table_efg.buz == 'buz')
+        )
+
+        q = (
+            Query.from_(self.table_abc)
+            .join(subquery)
+            .on(self.table_abc.bar == subquery.buz)
+            .select(self.table_abc.foo, subquery.fiz)
+            .where(self.table_abc.bar == 'bar')
+        )
+
+        parameter = NamedParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT "abc"."foo","sq0"."fiz" FROM "abc" JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=:param1)'
+            ' "sq0" ON "abc"."bar"="sq0"."buz" WHERE "abc"."bar"=:param2',
+            sql,
+        )
+        self.assertEqual({'param1': 'buz', 'param2': 'bar'}, parameter.get_parameters())
+
+    def test_join_with_parameter_value_wrapper(self):
+        subquery = (
+            Query.from_(self.table_efg)
+            .select(self.table_efg.fiz, self.table_efg.buz)
+            .where(self.table_efg.buz == ParameterValueWrapper(Parameter(':buz'), 'buz'))
+        )
+
+        q = (
+            Query.from_(self.table_abc)
+            .join(subquery)
+            .on(self.table_abc.bar == subquery.buz)
+            .select(self.table_abc.foo, subquery.fiz)
+            .where(self.table_abc.bar == ParameterValueWrapper(NamedParameter('bar'), 'bar'))
+        )
+
+        parameter = NamedParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual(
+            'SELECT "abc"."foo","sq0"."fiz" FROM "abc" JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=:buz)'
+            ' "sq0" ON "abc"."bar"="sq0"."buz" WHERE "abc"."bar"=:bar',
+            sql,
+        )
+        self.assertEqual({':buz': 'buz', 'bar': 'bar'}, parameter.get_parameters())
+
+    def test_pyformat_parameter(self):
+        q = Query.into(self.table_abc).columns("a", "b", "c").insert(1, 2.2, 'foo')
+
+        parameter = PyformatParameter()
+        sql = q.get_sql(parameter=parameter)
+        self.assertEqual('INSERT INTO "abc" ("a","b","c") VALUES (%(param1)s,%(param2)s,%(param3)s)', sql)
+        self.assertEqual({"param1": 1, "param2": 2.2, "param3": "foo"}, parameter.get_parameters())
diff --git a/pypika/tests/test_query.py b/pypika/tests/test_query.py
index 460b7d7..ae8dc01 100644
--- a/pypika/tests/test_query.py
+++ b/pypika/tests/test_query.py
@@ -1,6 +1,6 @@
 import unittest
 
-from pypika import Case, Query, Tables, Tuple, functions
+from pypika import Case, Query, Tables, Tuple, functions, Field
 from pypika.dialects import (
     ClickHouseQuery,
     ClickHouseQueryBuilder,
@@ -204,3 +204,39 @@ class QueryBuilderTests(unittest.TestCase):
 
         with self.subTest('OracleQueryBuilder'):
             self.assertEqual(OracleQuery, OracleQueryBuilder.QUERY_CLS)
+
+    def test_pipe(self) -> None:
+        base_query = Query.from_("test")
+
+        def select(query: QueryBuilder) -> QueryBuilder:
+            return query.select("test1", "test2")
+
+        def count_group(query: QueryBuilder, *groups) -> QueryBuilder:
+            return query.groupby(*groups).select(*groups, functions.Count("*"))
+
+        for func, args, kwargs, expected_str in [
+            (select, [], {}, 'SELECT "test1","test2" FROM "test"'),
+            (
+                count_group,
+                ["test1", "test2"],
+                {},
+                'SELECT "test1","test2",COUNT(*) FROM "test" GROUP BY "test1","test2"',
+            ),
+            (count_group, ["test1"], {}, 'SELECT "test1",COUNT(*) FROM "test" GROUP BY "test1"'),
+        ]:
+            result_str = str(base_query.pipe(func, *args, **kwargs))
+            self.assertEqual(result_str, str(func(base_query, *args, **kwargs)))
+            self.assertEqual(result_str, expected_str)
+
+        def where_clause(query: QueryBuilder, num_days: int) -> QueryBuilder:
+            return query.where(Field("date") > functions.Now() - num_days)
+
+        result_str = str(base_query.pipe(select).pipe(where_clause, num_days=1))
+        self.assertEqual(
+            result_str,
+            str(select(where_clause(base_query, num_days=1))),
+        )
+        self.assertEqual(
+            result_str,
+            'SELECT "test1","test2" FROM "test" WHERE "date">NOW()-1',
+        )
diff --git a/pypika/tests/test_terms.py b/pypika/tests/test_terms.py
index 562e0d2..4c7590d 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"))