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

81–90 of 134 posts

Re: Friendlier SQL with DuckDB

#81

Post by wruza »

heyda wrote: post #33
mattrighetti wrote: post #30

`EXCLUDE` Extremely useful, is there a reason why this is something not implemented in SQL in the first place? I often find myself writing very long queries just to select basically all columns except for two or three of them.

because columns can be added to tables in production databases, so any time you use select * you run the chance the number of columns changing and breaking anything you wrote.

This seems reasonable on its own, but then you can add a compound index and forget to join on a second part, or refactor a column in two and only collect one value into aggregation. This spotted babysitting is just stupid. If you’re anxious about query integrity, get some tooling and check your sqls/ddls against some higher-level schema.

Even if that turns out to be a constant source of trouble worth not having, then why SQL can’t provide columnsets at least, so that queries could include, group or join on these predefined sets of columns instead of repeating tens of columns and/or expressions and/or aggregations many times across a single query. You had employees.bio_set=(name, dob), now you add `edu` to it and it just works everywhere, because you think in sets rather than in specific columns. Even group by bio_set works. Heck, I bet most of ORMs partially exist only to generate SQL, because it’s sometimes unbearable as is.

Re: Friendlier SQL with DuckDB

#82

Post by zasdffaa »

wruza wrote: post #79
zasdffaa wrote: post #63

Earlier quoted context omitted.

(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it. But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta

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 fancy supporting that.

Re: Friendlier SQL with DuckDB

#83

Post by jsmith99 »

yread wrote: post #73
zasdffaa wrote: post #63

Earlier quoted context omitted.

(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it. But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta

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

'ORDER BY 2' would work here, but using the named column is a lot nicer.

Re: Friendlier SQL with DuckDB

#84

Post by wruza »

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…

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.

Re: Friendlier SQL with DuckDB

#85

Post by tomjakubowski »

jsmith99 wrote: post #83
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

'ORDER BY 2' would work here, but using the named column is a lot nicer.

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

Re: Friendlier SQL with DuckDB

#87

Post by 1egg0myegg0 »

IshKebab wrote: post #77
tosh wrote: post #3

How does DuckDB compare to SQLite (e.g. which workloads are a good fit for what? Would it be a good idea to use both?) I found https://duckdb.org/why_duckdb but I'm sure someone here can share some real world lessons learned?

I haven't used DuckDB yet but the biggest differences I've discovered if you aren't working on huge datasets where the column/row thing makes a difference (you're probably not) are: 1. SQLite has a great GUI and is really really widely supported. 2. DuckDB is properly statically typed with a much wider range of types than SQLite, which is dynamically typed and only just added support for any kind of type checking at…

If you'd like to work with DuckDB in a SQL IDE/GUI, we recommend DBeaver! It uses the DuckDB JDBC connector. A quick how to guide is here: https://duckdb.org/docs/guides/sql_editors/dbeaver

Re: Friendlier SQL with DuckDB

#88

Post by karmakaze »

sonthonax wrote: post #74
karmakaze wrote: post #48

Earlier quoted context omitted.

There's also no need to make it left to right usage, as long as it's acyclic: select y-2 as x, 3 as y, y/x as z;

Would this be compiled into a graph of subqueries and window statements?

I'm not following, the original could be written as

  select x + 2 as y, 1 as x, y/x as z;
with the same column values in a different order. Order of arguments shouldn't matter is all I was saying.

Re: Friendlier SQL with DuckDB

#89

Post by karmakaze »

zasdffaa wrote: post #63
snidane wrote: post #10

Earlier quoted context omitted.

Allow referencing columns defined previously in the same query would make duckdb competitive for data analytics. Without that one has to chain With statements for just the tiniest operations. select 1 as x, x + 2 as y, y/x as z;

(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it. But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta

The thing that makes SQL simple for me is that I can think in set operations devoid of proceduralness. Once we make things more and more sequential the more it is like programming than a formula.

Re: Friendlier SQL with DuckDB

#90

Post by wenc »

IshKebab wrote: post #77
tosh wrote: post #3

How does DuckDB compare to SQLite (e.g. which workloads are a good fit for what? Would it be a good idea to use both?) I found https://duckdb.org/why_duckdb but I'm sure someone here can share some real world lessons learned?

I haven't used DuckDB yet but the biggest differences I've discovered if you aren't working on huge datasets where the column/row thing makes a difference (you're probably not) are: 1. SQLite has a great GUI and is really really widely supported. 2. DuckDB is properly statically typed with a much wider range of types than SQLite, which is dynamically typed and only just added support for any kind of type checking at…

> huge datasets where the column/row thing makes a difference (you're probably not)

For programmers, it’s a tossup.

For most people working in data (databases, data engineering, ML etc) the column vs row thing makes a difference for datasets as small as a few hundred k records.

Post reply on HN