Friendly SQL

[arhimondr]

DuckDB introduced a number of extensions making their SQL more user friendly: https://duckdb.org/2022/05/04/friendlier-sql.html

With the advent of AI and extremely wide tables it is not uncommon to encounter a thousands of lines SQL query. At first it might be extremely hard to understand what it does, but then you realize that the query has to apply a projection on a small subset of column while the remaining hundreds columns remain unmodified. DuckDB introduces an elegant solution for this query pattern:

SELECT 
    * REPLACE (movie_count+3 AS movie_count, show_count*1000 AS show_count)
FROM star_wars_owned_by_disney;

There are also extensions to solve extremely old problems.

For example how many times have you added one more dimension into the list of projections but forgot to edit the GROUP BY?

SELECT
    systems,
    planets,
    cities,
    cantinas,
    SUM(scum + villainy) AS total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL;

Or how annoying is that whenever you add a projection you need to add a CTE to filter by it?

SELECT
    only_imperial_storm_troopers_are_so_precise AS nope,
    turns_out_a_parsec_is_a_distance AS very_speedy,
    SUM(mistakes) AS total_oops
FROM oops
WHERE
    nope = 1
GROUP BY
    nope,
    very_speedy

Complex type literals also deserve a mention here. For example a MAP:

{name: 'Star Destroyer', common_misconceptions: 'Can''t in fact destroy a star'}

[arhimondr]

Although generally Presto direction was to stay to ANSI SQL as close as possible, Presto has always had some custom extensions.

I wonder whether for the sake of better usability and in the light of absence of other available interfaces (such as PyFrames) it would make sense to take one step forward in non ANSI SQL extension Presto allows?

[mbasmanova]

CC: @tdcmeehan @aditi-pandit @amitkdutta @kaikalur

[tdcmeehan]

I wonder whether for the sake of better usability and in the light of absence of other available interfaces (such as PyFrames) it would make sense to take one step forward in non ANSI SQL extension Presto allows?

Regarding the lack of a data frames API, we could try to see if we can add backend support for Ibis for Presto. There is already support for Trino.