Max Gabrielsson
ad2d8e9acc
# Motivation
Binding functions has two steps:
1. Select an overload from a `FunctionSet<T>` given some argument types,
get back a `optional_idx` referring to the best matching overload (or
none if none matched).
2. Extract the "definition" (`shared_ptr<const SimpleFunction>`) from
the set, and pass it to the binder with a set of argument _expressions_,
and get back a `unique_ptr<BoundScalarExpression>` (or the equivalent
for other function types)
Over time the`FunctionBinder` has grown a lot in complexity. We now have
to deal with template types, named arguments, optional arguments with
default values, `*args` and `**kwargs`, which makes the both the
overload __selection__ (which has to check/reorder argument types) and
the __binding__ (which has to do the same for expressions) duplicate a
lot of work.
In this PR we now front-load and cache a lot of this work in the
overload-selection stage. Instead of just returning a `optional_idx`, we
now return a `FunctionBinding`, which holds the definition of the
overload, the actual resolved types (after templated resolution, `ANY`
types are gone, default-value insertion), and the mapping from input
argument to signature parameter - which we now can re-use when we
actually bind the inputs to produce the final bound function expression.
Additionally, we also save information about the overloads which
_didn't_ match, which allows us to produce much more detailed error
messages, instead of just dumping all the other overloads in the set as
"candidates" in the error message.
# What Changes
## General cleanup in the `FunctionBinder`
Lots of templating and redundant overloads have been removed. Its also
much easier/robust to do things like `set.GetFunctionByArguments()` with
the new `FunctionCallArguments` helper struct to select/bind functions
internally in e.g. the optimizer.
## Template inference as part of overload selection
Template parameter deduction (for functions that have
`LogicalType::TEMPLATE` as parameter/return types) is now part of
overload resolution, instead of being a hard error enforced at
expression-binding time (after the overload has already been selected).
This makes it possible to create more advanced functions with multiple
template based overloads. E.g. if a templated overload doesn't match, we
still have the chance to pick another overload that may, or if there are
multiple template overloads with different costs, we'll pick the
cheapest.
## Better "candidate list" error messages when overload selection fails
This is the main user facing change is that "candidate list" in error
messages are more informative.
We now print the "reason" an overload was _not_ selected, as well as
group candidates by reason. This is the main reason why the diff in this
PR is so large - lots of formatting and polymorphic error juggling code.
I know the below examples maybe aren't the prettiest - Im definitely
planning on reworking how we format/display error messages in the future
- but the main point is that this information is now _there_. We
actually store the `QueryLocation` for where exactly an argument was
passed wrong, for each failed overload, which is pretty precise
information.
I've also attempted to keep the first line `Binder Error: No function
...` the same to not have to change _too_ many test files right now
(saving that for the future formatting change)
Here are some examples:
### Missing parameters are grouped across overloads
```
SELECT substring('abc');
----
Binder Error: No function matches the given name and argument types 'substring(STRING_LITERAL)'. You might need to add explicit type casts.
Candidates:
'"substring"(string VARCHAR, "start" BIGINT) -> VARCHAR'
'"substring"(string VARCHAR, "start" BIGINT, length BIGINT) -> VARCHAR'
Required parameter "start" is missing
```
### A typo in a named argument is reported as the reason
```
SELECT round(1.5, foo := 2);
----
Binder Error: No function matches the given name and argument types 'round(DECIMAL(2,1), "foo" := INTEGER_LITERAL)'. You might need to add explicit type casts.
Candidates:
'round(x TINYINT) -> TINYINT'
'round(x TINYINT, "precision" INTEGER) -> TINYINT'
'round(x SMALLINT) -> SMALLINT'
... and 13 more
Has no parameter named "foo"
```
### Template deduction failures show how the type was inferred
```
SELECT [1, DATE '2020-01-01'];
----
Binder Error: No function matches the given name and argument types 'list_value(INTEGER_LITERAL, DATE)'. You might need to add explicit type casts.
Candidates:
'list_value("value" T, /, *args T) -> T[]'
Cannot deduce template type 'T', it was inferred to be:
- 'INTEGER_LITERAL', from first occurrence
- 'DATE', which is incompatible with the previously inferred type
'list_value() -> "NULL"[]'
Takes no positional arguments
```
### Each overload gets its own reason, closest first
```
SELECT * FROM range(1, 10, 2, 4);
----
Binder Error: No function matches the given name and argument types 'range(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
Candidates:
'"range"("start" BIGINT, stop BIGINT, step BIGINT, /)'
'"range"("start" TIMESTAMP, stop TIMESTAMP, step INTERVAL, /)'
'"range"("start" TIMESTAMP WITH TIME ZONE, stop TIMESTAMP WITH TIME ZONE, step INTERVAL, /)'
Takes at most 3 positional arguments
'"range"("start" BIGINT, stop BIGINT, /)'
Takes at most 2 positional arguments
'"range"(stop BIGINT, /)'
Takes at most 1 positional argument
```
### Type constructors use the same machinery
This is one of the really cool parts IMO. Since type constructors now
have `FunctionSignature`s, they participate in overload resolution the
same way as functions do.
```
SELECT NULL::DECIMAL('ten');
----
Binder Error: Type "DECIMAL" does not accept type parameters (VARCHAR)
Candidates:
'DECIMAL(width UTINYINT, scale UTINYINT := 0)'
Argument 1 of type VARCHAR cannot be implicitly cast to UTINYINT
'DECIMAL()'
Takes no positional arguments
```
```
SELECT NULL::MAP;
----
Binder Error: Type "MAP" requires type parameters
Candidates:
'MAP(key TYPE, value TYPE)'
Required parameter "key" is missing
```
### Ambiguous calls list the tied candidates
```
SELECT date_part(NULL, NULL);
----
Binder Error: Could not choose a best candidate function for the function call "date_part("NULL", "NULL")". In order to select one, please add explicit type casts.
Candidates:
'date_part(part VARCHAR, ts DATE) -> DOUBLE'
'date_part(part VARCHAR, ts INTERVAL) -> DOUBLE'
'date_part(part VARCHAR, ts TIME) -> DOUBLE'
... and 2 more
All match the given arguments equally well
```
# Future work
The main reason I'm excited for this is that resolving types (and
templates) at overload-selection time unblocks work on a lot of cool
stuff. One of these things are typed lambda functions (like being able
to define `LogicalType::LAMBDA(INT, INT) -> INT`), but the main thing
is: _removing_ `LogicalTypes` from the `FunctionSignature`.
This would basically turn all parameters in `FunctionSignatures` into
"unbound types", which would be resolved at bind/overload selection time
into concrete logical types - kinda like how `MacroFunction`s work.
Having that work would allow defining functions with non-type template,
e.g. `array_concat<N, M>(a INT[N], b INT[M]) -> INT[N + M)`, as well as
move `MacroFunction`'s to also use the `FunctionSignature` enabling fun
stuff like variadic macros (taking `*args`/`**kwargs` and/or lambda
functions).
DuckDB
DuckDB is a high-performance analytical database system. It is designed to be fast, reliable, portable, and easy to use. DuckDB runs in-process inside your application, so there is no separate server to manage and no external dependencies. It can also read remote files, query other databases, and connect to other DuckDB instances when needed. It provides a rich SQL dialect with support far beyond basic SQL: arbitrary and nested correlated subqueries, window functions, collations, and complex types (arrays, structs, maps). Its friendly SQL extensions add syntax that makes queries shorter and easier to write.
DuckDB is available as a standalone CLI application and has clients for Python, R, Java, Rust, Go, Wasm, and many more, with deep integrations with packages such as pandas.
For more information on using DuckDB, please refer to the DuckDB documentation.
Reads and writes any data
DuckDB reads and writes a wide range of file formats, including CSV, Parquet, JSON, and Excel, directly from local disk or from remote endpoints such as S3, HTTP(S), Azure, and Google Cloud Storage. For many of these formats, including CSV, Parquet, and JSON, loading data is as simple as referencing the file in the FROM clause:
SELECT * FROM 'myfile.csv';
SELECT * FROM 'myfile.parquet';
DuckDB also works with open lakehouse formats. DuckLake stores table metadata in a SQL database rather than in metadata files, which removes the need for a separate catalog server while still providing ACID transactions, snapshots, and time travel. DuckDB also supports the Apache Iceberg and Delta Lake formats. Refer to our Data Import section for more information.
Connects to other databases
DuckDB can attach to other database systems and query them in place, so you can read, join, and move data across engines without a separate ETL step. It speaks the native protocols of PostgreSQL, MySQL, and SQLite.
DuckDB instances can also connect to each other over the quack protocol, which turns a DuckDB instance into a server that other DuckDB clients attach to over HTTP.
Extensible
DuckDB has a modular extension mechanism: core extensions are bundled with DuckDB, while community and third-party extensions can be installed and loaded at runtime. Extensions add file formats, database connectors, and new functions, types, and more.
Installation
If you want to install DuckDB, please see our installation page for instructions.
SQL Reference
The documentation contains a SQL introduction and reference.
Development
For development, DuckDB requires CMake, Python 3 and a C++17 compliant compiler. In the root directory, run make to compile the sources. For development, use make debug to build a non-optimized debug version. You should run make unit and make allunit to verify that your version works properly after making changes. To test performance, you can run BUILD_BENCHMARK=1 BUILD_TPCH=1 make and then perform several standard benchmarks from the root directory by executing ./build/release/benchmark/benchmark_runner. The details of benchmarks are in our Benchmark Guide.
Please also refer to our Build Guide and Contribution Guide.
Support
See the Support Options page and the dedicated endoflife.date page.