3 ms·
I've had two real gripes with SQL. The rest of it has been, as you said, pretty good. Complaint 1: Not being able to use selected columns later in the same s
by jdmichal 3y ago
I've had two real gripes with SQL. The rest of it has been, as you said, pretty good.
Complaint 1: Not being able to use selected columns later in the same select.
SELECT
gnarly_calculation AS some_value,
some_value * 2 AS some_value_doubled
Instead:
SELECT
subquery.*,
some_value * 2 AS some_value_doubled
FROM (
gnarly_calculation AS some_value
) AS subquery
Complaint 2: Not being able to specify all columns except. This combines with the above, where I have to pull some intermediate calculations forward from a subquery, but I don't need them in the final output. So I have to then enumerate all the output columns that I actually want, instead of being able to say something like `* EXCEPT some_value`.
- snthpy 3y agoPRQL fixes this. (Disclaimer: I'm a PRQL contributor.)
- zX41ZdbW 3y agoNot only PQRL fixes this. It has been allowed in ClickHouse's SQL since its inception.
- snthpy 3y agoThat's awesome! ClickHouse is a great system by all accounts and I've been meaning to try out ClickHouse Local. I'm more familiar with DuckDB and they've also been doing some great innovation on the SQL front. I don't know offhand if they can also do the forward referencing thing but they allow putting the FROM first and having GROUP BY ALL etc.. It's great to see all this innovation happening in the SQL and Query Language space more generally at the moment.
- zX41ZdbW 3y agoIt is strange to hear about innovation in DuckDB - I see that they are gradually re-implementing the stuff already existing in ClickHouse. Sometimes they do a better job at promoting it.
- snthpy 3y agoThat's probably true but the big differentiator was that DuckDB can run in your python process so there's very low fiction to adopt it. My impression of ClickHouse was that it was more like postgresql in that regard, i.e. OLAP : OLTP as ClickHouse : Postgres as DuckDB : SQLite. clickhouse-local may have closed the gap on that though. Can you embed it in Python as a library?
- zX41ZdbW 3y ago> Can you embed it in Python as a library? https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb pip install chdb
- glogla 3y agoThey also do bunch of stuff that ClickHouse can't do, like correlated subqueries [1] or recursive CTEs. I kinda wish for a database with ClickHouse storage and DuckDB optimizer. At least my experience with ClickHouse is that MergeTree is incredibly good at what it does, but the optimizer hurts. [1]: https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.html https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h...
- zX41ZdbW 3y agoBoth complaints are resolved by ClickHouse. This video also covers many other advantages of ClickHouse's SQL dialect: https://www.youtube.com/watch?v=zhrOYQpgvkk https://www.youtube.com/watch?v=zhrOYQpgvkk Some may find your first complaint questionable... But I specifically designed ClickHouse SQL to allow aliases to be used and referenced in every part of SQL query.
- jdmichal 3y agoSo unsurprisingly based on my gripes, we are an OLAP shop. I've never heard of ClickHouse before, and I'll be looking into it. We're doing OK on PostgreSQL, but just OK. A lot of learning and unlearning.
- AtNightWeCode 3y agoDon't use *. It is a common source for all kinds of problems. Many query langs does not support it at all for that reason.
- jdmichal 3y agoI'm quite aware of your advice regarding using `` against table. However, I'm talking about using `` against subqueries and CTEs where the pieces of the table have already been extracted.
- jdmichal 3y agoThis is supposed to read: I'm quite aware of your advice regarding using `*` against tables. However, I'm talking about using `*` against subqueries and CTEs where the pieces of the table have already been extracted. I was unable to edit this earlier due to HN being unavailable after I realized the formatter mangled it.