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

121–130 of 134 posts

Re: Friendlier SQL with DuckDB

#121

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…

> suggesting using a fully-qualified table name if they actually wanted the table column (t.x in the above example).

I just realised why this was bothering me. That means 't' and 't.x' are actually different variables. In standard SQL it's always the case (right?) that an unqualified variable ('t') is just an convenient shorthand for the fully qualified variable ('t.x', or more fully I suppose, '..t.x), and you just broke that.

Re: Friendlier SQL with DuckDB

#122

Post by zasdffaa »

sagarm wrote: post #113
zasdffaa wrote: post #107

Earlier quoted context omitted.

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 U…

Interesting, ta. My code rarely looks like that so thanks for the insight. Was exactrly what I was looking for.

Re: Friendlier SQL with DuckDB

#123

Post by zasdffaa »

sagarm wrote: post #115
zasdffaa wrote: post #108

Earlier quoted context omitted.

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.

Indexes are used all over for grouping an ordering, I was objecting only to the syntax of ORDER BY

Re: Friendlier SQL with DuckDB

#124

Post by kristianp »

mytherin wrote: post #100
kristianp wrote: post #95

I'm enjoying experimenting with Duckdb from python, it's a promising product and has a large list of data formats it can read, including pandas dataframes from in-memory with zero-copy. However its still quite the moving target, with a number of things not at maturity yet. e.g. the TimestampZ column type isn't implemented yet [1], although it is in the documentation. Edit: I came across it via the podcast: https://ww…

The correct type is TIMESTAMPTZ. Is the type TimestampZ mentioned anywhere in our documentation? If so, that looks like a typo. I fully agree that error message needs to improve, however. I will have a look at that.

You're right, I was using 'timestampz' when I should have been using 'timestamptz', (or 'timestamp with time zone') thanks for that.

Re: Friendlier SQL with DuckDB

#125

Post by zasdffaa »

wruza wrote: post #84
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…

what about That’s no different than the first snippet, if you aren’t parsing it with regexps, of course. The resulting AST identifiers would simply refer to not only column names, but also to other defined expressions. This is the case for both snippets. It’s either cyclic or not, and when not, it is easy to substitute/cse/etc as usual. The complexity of these expressions is irrelevant.

https://news.ycombinator.com/item?id=31364281

Re: Friendlier SQL with DuckDB

#126

Post by zasdffaa »

wenc wrote: post #91
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…

It’s not trivial but as someone who has implemented something similar (for an equation based modeling language) it’s not super complicated if you use the right abstractions. It’s basically traversing the AST and doing substitutions.

https://news.ycombinator.com/item?id=31364281

Re: Friendlier SQL with DuckDB

#127

Post by mytherin »

kristianp wrote: post #124
mytherin wrote: post #100

Earlier quoted context omitted.

The correct type is TIMESTAMPTZ. Is the type TimestampZ mentioned anywhere in our documentation? If so, that looks like a typo. I fully agree that error message needs to improve, however. I will have a look at that.

You're right, I was using 'timestampz' when I should have been using 'timestamptz', (or 'timestamp with time zone') thanks for that.

On the topic of Friendlier SQL, I have extended the similarity search to types in this PR [1], so the system will now offer you this correction as well :)

[1] https://github.com/duckdb/duckdb/pull/3633

Re: Friendlier SQL with DuckDB

#128

Post by 1egg0myegg0 »

ashes wrote: post #110

I've been experimenting with DuckDB using modified Mondrian OLAP engine and it looks very promising so far, performance wise. A questions I have to author, or anyone using: Is there a easy way to transfer whole Postgres DB into DuckDB so I can do some tests with actual client data? I could export each table by hand and reimport it, but that is kind of painful.

Interesting thought! I have not tried this yet so I only have a guess as an answer. Could you export the data as SQL statements and then run those statements on DuckDB? That may be easier to set up, but may take longer to run...

DuckDB also has the ability to read Postgres data directly, and there is a Postgres FDW that can read from DuckDB!

https://github.com/duckdblabs/postgresscanner

https://github.com/alitrack/duckdb_fdw

Re: Friendlier SQL with DuckDB

#129

Post by wruza »

zasdffaa wrote: post #112
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…

@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 fanc…

My examples are from boring enterprise, not from what we love to create at home. I’ve read and patched literally meters long queries in analytics, which could be reduced dramatically by being self-referential and by other approaches discussed itt. Of course these could be refactored into something “create view/temp/cte”, but that requires a full control of ddl, special access rights and code ownership. Most space was used by similar case-when-then constructs and permutations of values these produced. The original code was on official support, so we couldn’t just rewrite it, because migrating to the next update would cost a week instead of an hour.

I could reach to and post a lenghty example, but it’s nothing but boring reshuffles really, spiced with 3-level joins of “modelling db in db to allow user columns”.

I agree on the LTR idea, because reading a symbol not yet defined may lead to confusion.

Post reply on HN