I found this link on Hacker News, and it seems to be a transpiler to generate SQL from a new language.

It’s been a long time since I have written SQL, but I’m sure this could be interesting since SQL can be infuriating for most developers I’ve worked with.

  • qwertyasdef@programming.dev
    link
    fedilink
    English
    arrow-up
    1
    ·
    1 year ago

    What are the implications of WHERE vs HAVING? I thought the only primary difference was that one happens before the aggregation and the other happens after, and all the other implications stem from that fact. PRQL’s simplification, rather than obscuring, seems like a more clear and reasonable way to express that distinction.

    I don’t know if PRQL supports all SQL features, but I think it could while being less complex than SQL by removing arbitrary SQL complications like different keywords for WHERE vs HAVING, only being able to use column aliases in certain places, needing to recompute a transformation to use it in multiple clauses, not forcing queries to be in SELECT… FROM… WHERE… order, etc.

    • Thinker@lemmy.world
      link
      fedilink
      English
      arrow-up
      1
      ·
      1 year ago

      “the only primary difference was that one happens before the aggregation and the other happens after, and all the other implications stem from that fact.”

      This is correct. The biggest implication of that difference is that, when you filter rows via a HAVING clause, the query will first select all the rows and aggregate them, and only then begin to filter them. That can be a massive performance hit if you thought that the filter would prevent filtered rows from ever being selected. Of course this makes perfect sense, there’s no logical way to filter an aggregate without first aggregating, but it’s not obvious.

      “PRQL’s simplification, rather than obscuring, seems like a more clear and reasonable way to express that distinction.”

      My main point is that PRQL makes no distinction. If you didn’t inspect that SQL output and already know about the difference between WHERE and HAVING, you would have no idea, because in PRQL they’re both just “filter”. Hence, PRQL is not simplifying the complexity (you still need to learn the full SQL syntax and the specifics of how it works), but it does obscure (you have no hints that one of your filter statements will behave completely differently from the other).

      As far as removing arbitrary SQL features, I agree that that is it’s main advantage. However, I think either the developers or else the users of PRQL will discover that far fewer of SQL’s complexities are arbitrary than you might first assume.

      • qwertyasdef@programming.dev
        link
        fedilink
        English
        arrow-up
        2
        ·
        1 year ago

        My main point is that PRQL makes no distinction. If you didn’t inspect that SQL output and already know about the difference between WHERE and HAVING, you would have no idea, because in PRQL they’re both just “filter”.

        Hmm, I have to disagree here. PRQL has no distinction in keyword, but it does have a distinction in where the filter goes relative to the aggregation. Given that the literal distinction being made is whether the filter happens before or after the aggregation, PRQL’s position-based distinction seems a lot clearer than SQL’s keyword-based distinction. Instead seeing two different keywords, remembering that one happens before the aggregation and the other after, then deducing the performance impacts from that, you just immediately see that one comes before the aggregation and the other after then deduce the performance impacts.

        As far as removing arbitrary SQL features, I agree that that is it’s main advantage. However, I think either the developers or else the users of PRQL will discover that far fewer of SQL’s complexities are arbitrary than you might first assume.

        That’s fair, I was just thinking of things that frustrate me with SQL, but I admittedly haven’t thought too hard about why things are that way.