Add support for function chaining and the dot syntax for function calls

[pingsutw]

Is your feature request related to a problem or challenge?

Similar to https://github.com/duckdb/duckdb/discussions/6717. It would be awesome if datafusion could support function chaining as well.

Instead of

SELECT list_aggregate( list_filter( numbers,  x -> x > 40 ) , 'count') as greater_than_40s 
FROM relation

function chaining

SELECT numbers.filter( x -> x > 40).aggregate('count') as greater_than_40s 
FROM relation

Describe the solution you'd like

No response

Describe alternatives you've considered

use current syntax

Additional context

https://github.com/duckdb/duckdb/pull/6725

[doupache]

+1

VLDB 2024 has an interesting paper from Google SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL.

One of the key pain points the paper addresses is making the "reading flow match the logical execution flow."

The proposed function chaining is a lightweight mechanism, essentially a syntactic sugar to avoid deeply nested function calls in SELECT items .

It’s somewhat similar to how Promise in JavaScript help solve callback hell.

example from DuckDB blog

without function chaining

SELECT 
     concat(
          list_aggr(
               string_split(
                    upper('Make it stop'),
               ' '),
          'string_agg','.'),
     '.') AS oof;

function chaining

SELECT 
     ('Make it so')
          .upper()
          .string_split(' ')
          .list_aggr('string_agg','.')
          .concat('.') AS im_not_messing_around_number_one;

If we reach a consensus on this, I'd like to work on it. I have some experience with DuckDB's function chaining.

[dharanad]

+1 I was just looking at some UDF in DuckDB which uses dot sytax and was think how can do we the same for DF

[jayzhan211]

Maybe we could start from https://duckdb.org/docs/sql/functions/lambda 🤔

[gstvg]

I filled #14205 to track support for lambda functions

[gstvg]

Hi @doupache, are you working on it? Otherwise, I would like to propose an implementation