5 ms·

Can't see the top sql because it keeps flipping over, WTF. Where are these examples?

iLoveOncall 4y ago
I copy-pasted them to be able to read them better. SELECT first_name, last_name, ( SELECT MAX(order_total) FROM orders WHERE customer.id = orders.customer_id ) AS maximum_order FROM customers; There's this one to get the maximum order amount for each customer that struck me, and another similar one. Instead of doing a JOIN they do this weird subquery. You can clearly see that it's the work of an AI and how this AI works (putting together lego pieces which are subqueries), because nobody would be writing this query like that.
- datalopers 4y ago
  Plenty of people write SQL like that. It’s usually Python devs who are overly fascinated with shitty AI.
  - dragonwriter 4y ago
    I’ve seen it a lot by older SQL devs or in enterprise places with ossified standard patterns. Lots of RDBMS’s historically had quirks and a lot of workarounds became cargo cult practices that often got culturally transmitted to the communities of other DBMSs and/or survived beyond the problem they were meant to workaround.
    - zasdffaa 4y ago
      Am curious. How would you write it 'properly'? Other (good) alternative is with toporders as ( select max(order_total) as maxOT, customer_id from orders group by customer_id ) select last_name, maxOT from customers join toporders on ... What would you suggest?
      - datalopers 4y ago
        Correlated subqueries run N times, once for each outer row. Your solution (assuming the database supports predicate pushdown) is far better.
      - dragonwriter 4y ago
        SELECT customer.first_name, customer.last_name, max(order.order_total) as maximum_order FROM customers customer INNER JOIN orders order ON (customer.id = order.customer_id) GROUP BY customer.id
        zasdffaa 4y ago
        I don't think subqueries are so bad - at least they're clear, and if fast, that's surely problem solved. IME they are clearer and often faster, so overall better. I know purists don't like them but I do.
      - deleted 4y ago
        [deleted]
- zasdffaa 4y ago
  In fairness there's nothing weird about that, it being just a typical correlated subquery. I'd quite possibly do it that way.
  - iLoveOncall 4y ago
    Right, weird wasn't the right word. It's bad (here, not always).
    - zasdffaa 4y ago
      WHY bad? HOW am I supposed to learn anything from "It's bad"
- srhtftw 4y ago
  Well, this isn't that bad on a real database which will know to decorrelate[1] the subquery but a GPT based AI probably won't advise you to do an EXPLAIN and check the plan. 1- e.g as Jamie explains here https://www.scattered-thoughts.net/writing/materialize-decorrelation https://www.scattered-thoughts.net/writing/materialize-decor...
  - zasdffaa 4y ago
    Now this is a useful answer, and perhaps the best in the thread, thanks

I copy-pasted them to be able to read them better. SELECT first_name, last_name, ( SELECT MAX(order_total) FROM orders WHERE customer.id = orders.customer_id ) AS maximum_order FROM customers; There's this one to get the maximum order amount for each customer that struck me, and another similar one. Instead of doing a JOIN they do this weird subquery. You can clearly see that it's the work of an AI and how this AI works (putting together lego pieces which are subqueries), because nobody would be writing this query like that.

datalopers 4y ago
Plenty of people write SQL like that. It’s usually Python devs who are overly fascinated with shitty AI.
- dragonwriter 4y ago
  I’ve seen it a lot by older SQL devs or in enterprise places with ossified standard patterns. Lots of RDBMS’s historically had quirks and a lot of workarounds became cargo cult practices that often got culturally transmitted to the communities of other DBMSs and/or survived beyond the problem they were meant to workaround.
  - zasdffaa 4y ago
    Am curious. How would you write it 'properly'? Other (good) alternative is with toporders as ( select max(order_total) as maxOT, customer_id from orders group by customer_id ) select last_name, maxOT from customers join toporders on ... What would you suggest?
    - datalopers 4y ago
      Correlated subqueries run N times, once for each outer row. Your solution (assuming the database supports predicate pushdown) is far better.
    - dragonwriter 4y ago
      SELECT customer.first_name, customer.last_name, max(order.order_total) as maximum_order FROM customers customer INNER JOIN orders order ON (customer.id = order.customer_id) GROUP BY customer.id
      - zasdffaa 4y ago
        I don't think subqueries are so bad - at least they're clear, and if fast, that's surely problem solved. IME they are clearer and often faster, so overall better. I know purists don't like them but I do.
    - deleted 4y ago
      [deleted]
zasdffaa 4y ago
In fairness there's nothing weird about that, it being just a typical correlated subquery. I'd quite possibly do it that way.
- iLoveOncall 4y ago
  Right, weird wasn't the right word. It's bad (here, not always).
  - zasdffaa 4y ago
    WHY bad? HOW am I supposed to learn anything from "It's bad"
srhtftw 4y ago
Well, this isn't that bad on a real database which will know to decorrelate[1] the subquery but a GPT based AI probably won't advise you to do an EXPLAIN and check the plan. 1- e.g as Jamie explains here https://www.scattered-thoughts.net/writing/materialize-decorrelation https://www.scattered-thoughts.net/writing/materialize-decor...
- zasdffaa 4y ago
  Now this is a useful answer, and perhaps the best in the thread, thanks