Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Friendlier SQL with DuckDB

duckdb.org

Post reply on HN
Print view View original

111–120 of 134 posts

Re: Friendlier SQL with DuckDB

#111

Post by sagarm »

zasdffaa wrote: post #82
wruza wrote: post #79

Earlier quoted context omitted.

SQL is complex enough No, it is not. I mean it is, but not in parts where that could be seen as useful and/or convenient. [A]cyclic graph traversal/etc is one of the basic tests in a modern interview at any CRUD studio. How come it could do $%^& to any part of yours?

> How come it could do $%^& to any part of yours? Because just implementing the standard stuff nearly did my $^&% nut. Also I know about graphs & posets, and it's potentially a little more complex than it seems. The variables select x * x as y, 1 as x is meh, but what about select (select tbl.z from tbl where tbl.y = y) as subq, x * yy as y, xx + 1 as x, subq + yy as zzz from ( select xx, yy from ... ) I just don't f…

Note you can already reference select list items in GROUP, HAVING, and ORDER BY so it's not that big of an extension.

I've implemented the ability to reference select-list aliases before; it's not that hard to do if implemented basically like a macro expansion. The main problem is user confusion due to ambiguous references, e.g.

    select 2 as x, x as `which x?`
    from (select 1 as x) t;
we ended up adding a warning for the case where a select list alias shadowed a table column, suggesting using a fully-qualified table name if they actually wanted the table column (t.x in the above example).

IMO only allowing references to previous select list items is a perfectly reasonable restriction; loosening it isn't worth the implementation headache or user confusion. Though we did allow using aliases in the WHERE clause.

Re: Friendlier SQL with DuckDB

#112

Post by zasdffaa »

zasdffaa wrote: post #82
wruza wrote: post #79

Earlier quoted context omitted.

SQL is complex enough No, it is not. I mean it is, but not in parts where that could be seen as useful and/or convenient. [A]cyclic graph traversal/etc is one of the basic tests in a modern interview at any CRUD studio. How come it could do $%^& to any part of yours?

> How come it could do $%^& to any part of yours? Because just implementing the standard stuff nearly did my $^&% nut. Also I know about graphs & posets, and it's potentially a little more complex than it seems. The variables select x * x as y, 1 as x is meh, but what about select (select tbl.z from tbl where tbl.y = y) as subq, x * yy as y, xx + 1 as x, subq + yy as zzz from ( select xx, yy from ... ) I just don't f…

@wruza, @wenc: These are both very good answers, and you are of course both right. Check the symbol table, anything you can't find should be defined in the same context (in the select list, as a new expr). In which case, match each symbol use (eg. x in x * x as y) to the definition (eg. 1 as x) to establish a set of dependencies, then do a partial order sort, then spit out the results.

I can do that I just don't fancy it, and more to the point nobody is giving me an example of where it would be particularly helpful. So if anyone can, I'm interested.

(also, consider human factors; although an acyclic definition could be extracted from an unordered expression set, a consistent left to right (in the western world anyway, matching textual layout) with dependencies being introduced on the right and depending only on what came before on the left might actually be better for us meatsacks)

Re: Friendlier SQL with DuckDB

#113

Post by sagarm »

zasdffaa wrote: post #107
yread wrote: post #73

Earlier quoted context omitted.

for example select id, count(...something complicated) as complicated_count from .... order by complicated_count would help

SQL already supports "order by complicated_count". Did you mean group by? This isn't really the large, convincing example I was looking for btw.

Many dialects already support using aliases in GROUP BY and HAVING too, btw.

IMO it's most useful (though somewhat more difficult to implement) to be able to use the aliases with window functions or large case/when statements, something like

   SELECT
     page,
     SUM(clicks) AS total_clicks,
     100. * total_clicks / (SUM(total_clicks) OVER ()) AS click_pct,
     100. * SUM(total_clicks) OVER (ORDER BY total_clicks DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (SUM(total_clicks) OVER ()) AS cumulative_click_pct
    FROM weblog
    GROUP BY page;

Re: Friendlier SQL with DuckDB

#114

Post by zasdffaa »

sagarm wrote: post #111
zasdffaa wrote: post #82

Earlier quoted context omitted.

> How come it could do $%^& to any part of yours? Because just implementing the standard stuff nearly did my $^&% nut. Also I know about graphs & posets, and it's potentially a little more complex than it seems. The variables select x * x as y, 1 as x is meh, but what about select (select tbl.z from tbl where tbl.y = y) as subq, x * yy as y, xx + 1 as x, subq + yy as zzz from ( select xx, yy from ... ) I just don't f…

Note you can already reference select list items in GROUP, HAVING, and ORDER BY so it's not that big of an extension. I've implemented the ability to reference select-list aliases before; it's not that hard to do if implemented basically like a macro expansion. The main problem is user confusion due to ambiguous references, e.g. select 2 as x, x as `which x?` from (select 1 as x) t; we ended up adding a warning for t…

> Note you can already reference select list items in GROUP, HAVING, and ORDER BY so it's not that big of an extension.

You're just looking for symbols in the symbol table, I think it's a big difference!

> IMO only allowing references to previous select list items is a perfectly reasonable...

agreed, see my other post where I say the same.

> Though we did allow using aliases in the WHERE clause

And the SQL standards people didn't go for this, and I'm sure they were very far from stupid. And nobody's asking why they didn't allow this, which really bothers me.

Re: Friendlier SQL with DuckDB

#115

Post by sagarm »

zasdffaa wrote: post #108
tomjakubowski wrote: post #85

Earlier quoted context omitted.

Wow, TIL. Great tip for those random one-off queries you have to bash out when investigating a problem.

Please never let this vile shortcut work its way into your production code.

I've seen quite a few production queries that use indexes in GROUP BY and ORDER BY; it's quite common. Probably partially because linters/code review/etc are lightweight to nonexistent amongst the analysts/data science types that I tend to work with.

Re: Friendlier SQL with DuckDB

#116

Post by sagarm »

throw_away wrote: post #106
roncohen wrote: post #2

Lots of great additions. I will just highlight two: Column selection : When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports: - APPLY: apply a function to a set of columns - COLUMN: select columns by matching a regular expression (!) Details here: https://clickhouse.com/docs/en/sql-reference/statement…

Are leading commas allowed? Because otherwise, you've just traded out the inability to comment out the last element for the inability to comment out the first. I never understood this convention.

I agree that it's ugly and don't use it myself, but I find that I modify the last item in a list far more frequently than the first. Probably because the grouping columns tend to go first by convention, and these change less.

Re: Friendlier SQL with DuckDB

#117

Post by sagarm »

heyda wrote: post #57
justin_oaks wrote: post #38

Earlier quoted context omitted.

That's a problem with select * in general, not a problem with using EXCLUDE with select *. So that still doesn't explain why it's not in SQL to begin with.

"A traditional SQL SELECT query requires that requested columns be explicitly specified, with one notable exception: the * wildcard. SELECT * allows SQL to return all relevant columns. This adds tremendous flexibility, especially when building queries on top of one another. However, we are often interested in almost all columns. In DuckDB, simply specify which columns to EXCLUDE:" It appears how this works is that is…

It can definitely be misused, but SELECT * is pretty handy for ad-hoc queries and to succinctly get all (or almost all) of the columns for a subquery or CTE.

Re: Friendlier SQL with DuckDB

#118

Post by sagarm »

TedDallas wrote: post #98

Does it support a syntax for recursive queries? In T-SQL we use recursive CTEs which are ugly as hell. This is very cool though. There are lot of features that would make my life easier. Group By All is noice.

What do you use recursive queries for?

Re: Friendlier SQL with DuckDB

#119

Post by sagarm »

zasdffaa wrote: post #114
sagarm wrote: post #111

Earlier quoted context omitted.

Note you can already reference select list items in GROUP, HAVING, and ORDER BY so it's not that big of an extension. I've implemented the ability to reference select-list aliases before; it's not that hard to do if implemented basically like a macro expansion. The main problem is user confusion due to ambiguous references, e.g. select 2 as x, x as `which x?` from (select 1 as x) t; we ended up adding a warning for t…

> Note you can already reference select list items in GROUP, HAVING, and ORDER BY so it's not that big of an extension. You're just looking for symbols in the symbol table, I think it's a big difference! > IMO only allowing references to previous select list items is a perfectly reasonable... agreed, see my other post where I say the same. > Though we did allow using aliases in the WHERE clause And the SQL standards…

Oh, was your objection specifically to allowing references to following (not just preceding) select list items? Then we're in violent agreement. That would be complicated to implement and confuse users. Definitely not worth it.

Re: Friendlier SQL with DuckDB

#120

Post by zasdffaa »

sagarm wrote: post #119
zasdffaa wrote: post #114

Earlier quoted context omitted.

> Note you can already reference select list items in GROUP, HAVING, and ORDER BY so it's not that big of an extension. You're just looking for symbols in the symbol table, I think it's a big difference! > IMO only allowing references to previous select list items is a perfectly reasonable... agreed, see my other post where I say the same. > Though we did allow using aliases in the WHERE clause And the SQL standards…

Oh, was your objection specifically to allowing references to following (not just preceding) select list items? Then we're in violent agreement. That would be complicated to implement and confuse users. Definitely not worth it.

I'm against doing anything without checking beforehand whether it's actually going to be worth the effort.

But yes, I'd be far happier doing left-to-right dependencies only, which I can believe (though I still need evidence) it would be of some value.

Post reply on HN