Comment by lkuty

I have a moderate amount of SQL experince, but I could not write that query at the top of my head. Maybe you misunderstand what the PRQL query is doing?

Here's the SQL it generates:

WITH table_0 AS ( SELECT , ROW_NUMBER() OVER ( PARTITION BY album_id ORDER BY milliseconds DESC ) AS _expr_0 FROM tracks ) SELECT FROM table_0 WHERE _expr_0 <= 1

If I understand PRQL correctly, it finds the longest song for each album? A simple concept, but not a simple MySQL query.

This "find the latest row for each <column>" query is kind of a poster-child for seemingly-simple but actually difficult to get right/performant sql.

E.g. see: https://stackoverflow.com/questions/1313120/retrieving-the-l...

I think the logic here is: SQL is hard and most people don't know it well. So PRQL is perhaps easier to learn.

The same logic is applied to TypeScript vs JavaScript. Or C vs assembly. Or Nano vs vim. Etc etc.

It's a quandary though. SQL is clearly difficult for most people to get their heads around. It does require a different way of thinking about data, and you can get by with a minimal SQL knowledge for a long time, especially nowadays with ORMs.

But like so many other things, making the investment is worthwhile and pays off in small and large ways, forever (so far).

I'm not a PRQL fun. But TBH I can't write this SQL from my head. I have thousand lines of written SQL.

You can apply this logic to choosing a high level language vs writing assembly code.

Yes the pipeline is more complex, there are more tools and more syntax to track, but the benefits are pretty clear (or we'd all be writing UI code in hand written asm)

While leaky abstractions are a huge problem, the thing about abstractions is that, if they are any good, the benefits and improvements to productivity outweigh the negatives. You just have to figure out if the gains PRQL could give you are worth the effort.

I rarely write SQL, so it's not worth it for me. But if PRQL were an actual query engine, not just a translation layer, and some database offered a native PRQL interface, I would immediately switch to it rather than to keep twisting my brain with SQL and it's inane syntax and rules.

(I was a full time DBA in one previous life, so I should be more comfortable with SQL than most.)

Eh, `DISTICT ON` is a custom PostgreSQL extension.

A standard* method would be:

    SELECT *
    FROM tracks
    QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1;

But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using:

    SELECT *
    FROM (
      SELECT *
        ,row_number() over (partition by album_id order by milliseconds desc) rn
      FROM tracks
      ) x
    WHERE x.rn = 1;

That said, I still don't think PRQL is particularly amazing. I can't tell if it's merely syntactic sugar for SQL, or if it's actually meant to control query execution. If it's the former, it's likely to frustrate developers because it's actually just another layer of abstraction. If it's the latter, then it requires the developer to not only understand the data model well enough to be able to write SQL queries, they need to be able to understand the RDBMS impementation details well enough to be able to write queries that best take advantage of the current database's indexes, statistics, and configuration. Even something as simple as sorting before filtering or projecting can be a significant performance issue. Nevermind the fact that relational algebra done in the wrong order can be non-deterministic or not equivalent transformations, so even if the query processor is smart enough to do rewrites whatever the developer enters might be logically different unintentionally.

Ultimately I think it's a tool that lets the developer thinking about the problem in the way they prefer, rather than thinking about the problem in the way that best suits the problem at hand. Like insisting on writing documentation in LaTeX instead of Word or Markdown.

*: I believed this was in SQL 2023, but double checking it looks like it did not have make the final standard. I would be surprised if it didn't make it in the future, however.

With the additional benefit that resources about SQL are everywhere and every question has been answered already multiple times. On top of that you are most likely to encounter SQL at a job than PRQL.

You'd also need a LIMIT or a TOP, and you might need a subquery for that depending on your SQL of choice.

QUALIFY is not part of the SQL standard.

This query would error: "Field `id` is neither in the GROUP BY nor in an aggregate function." Since `id` is a key in `tracks` and `album_id` is a foreign key and not unique in `tracks`, the RDBMS wouldn't be able to use any implicit GROUP BY determinism, either.

You could do this:

    select distinct
      album_id 
      ,first_value(id order by milliseconds desc) as longest_track_id
    from tracks

But this is unlikely to perform as well as the row_number() method because it will cause the RDBMS to generate a record for every track and then waste time sorting the intermediate results to find the unique records in the output.

your query will return just album_id and id, not other track fields

if they need ChatGPT to generate the query (costly and possibly error-prone), then it means that not everyone is familiar with the output...

Hmm, I would think that LLM helps adoption for the semantic layers such as PRQL, Malloy, and dbt since it's possible to generate/validate/iterate 5 lines of PRQL compared to 25 lines of SQL but considering none of them widely adopted yet, you might indeed be correct in a way that LLM makes it harder for the new tools to gain adoption by helping you to suffer less from the verboseness of SQL.

One issue with functions though is that they can change performance in unpredictable ways. For example a colleague of mine recently altered a function I had written that was used in multiple hot-path queries. The change he made accidentally caused the function to no longer be inline-able on PostgreSQL. Once the function couldn't be inlined then the PostgreSQL planner wasn't able to select the appropriate indices and the performance of several of the queries exploded by about 100x.

So while it's true it can be composed etc the current state of the art planners struggle except under very simple/constrained scenarios.

Do you have any links to a basic example on using CTEs and functions to keep SQL maintainable?

I've used CTEs, but I had not tried breaking up an SQL query into functions. Didn't know that was possible!

For whatever reason, I feel like I end up with a giant blob of SQL when writing SQL and it's incredibly frustrating.

> You can write queries with CTES to improve readability, and extract CTEs into functions that can be selected as queries get too large and unwieldly.

Personally I'd go for breaking them into views. IIRC as of around postgres 11-13 they're no longer a barrier for the query planner.

> There's very little reason to choose a DB other than postgres

Sure, if you are a startup, or write your own code. But for most people the choice of database(s) is a given, and they are not in a position to challenge that. At the end of the day, Oracle has to make a living, too...

I like PostgreSQL quite a bit and pushed for using it by default at my company, but SQL pops up in a surprising number of places. You can use it in PowerBI, Snowflake, and pandas just to name a few. You don't always control which DB you're talking to, and it would be nice if the SQL interface that gets presented in those scenarios got an upgrade. PRQL looks like a really promising option for that.

> There's very little reason to choose a DB other than postgres

Postgres is single server OLTP DB with complicated failover story, it is strong enough reason to consider some other contenders e.g. CocroachDB, SpannerDB for distributed OLTP or OLAP specialized ClickHouse, BigQuery, DuckDB.

I agree, sql is well documented and an industry standard, no need to make it more complex by adding preprocessors that do nothing but change the syntax.

Just bite in and learn proper sql.

That's why we stopped innovation of programming languages at C89? Why use sql at all if you can also do it in C?