6 ms·

If you've ever tried debugging a broken 100 line SQL statement created by some business analyst for some niche use then you might agree.

by xmprt 4y ago

Closi 4y ago
Have you ever tried debugging a broken 100 line python script created by a business analyst? It's not any better.
- masklinn 4y ago
  It’s better by orders of magnitude, the python script you can literally trace instruction by instruction, and process every which way you want.
  - actually_a_dog 4y ago
    But that's not a function of the language itself. If there were SQL debuggers you could step through a big query with, debugging SQL would be a lot easier. As it is, you often have to take the whole damn thing apart from the inside to see what's going on, then put it all back together again. That's... suboptimal, to say the least.
    - masklinn 4y ago
      > But that's not a function of the language itself. It is tho, SQL is a declarative language, so the execution model is largely opaque, by language design. But even if it were solely an implementation detail, Closi would still be wrong: they literally stated that “it’s not any better” to try debugging a 100 lines python script than a 100 lines query. > As it is, you often have to take the whole damn thing apart from the inside to see what's going on, then put it all back together again. That's... suboptimal, to say the least. Which is the point? Sounds like you agree that it is significantly easier to debug a 100 lines python script than a 100 lines SQL query.
      - ltbarcly3 4y ago
    - DemocracyFTW2 4y ago
      I often watch in horror what some people suggest is 'good SQL'. You even find plenty of those humongous multi-hundred-line beasts on the official PostgreSQL wiki, a single statement going over several pages if you were to print it out. Sure that's hard to debug. What I do in such cases is using many intermediate views, sometimes just named like `_foobar_010`, `_foobar_020` and so on, and a single `create (materialized) view foobar ... select from _foobar_120 ...`. That way, I can rely on the DB caring for basic structural soundness, giving error messages that are better localized than "something is wrong near ','", and I can visually (or, indeed, programmatically) debug all the intermediate steps.
      - ltbarcly3 4y ago
        This is just obviously selection bias, the stuff people put in the wiki is exactly the set of things that are so hard to do it's worth having a reference for other people. The stuff in the postgres wiki is in no way representative of sql in general.
oarabbus_ 4y ago
There was a time that doing exactly this (fixing, updating, or modifying existing queries) was a significant chunk of my day-to-day work. It certainly can be painful and tedious, but that's quite universal - certainly not specific to SQL. Unlike "true" programming languages (not to get into a debate about SQL's completeness, we all know it isn't practically a general-purpose language) this is usually best approached differently than debugging a program. Whenever I encountered a mess of a query, I'd ask myself two questions: 1. What is the purpose of the query/what is the desired output? 2. What are the source tables used in the query? At this point I'd typically rewrite the query, taking inspiration from the non-broken parts of the original if possible, and otherwise rewriting from scratch. In most cases this was faster (and the rewritten result often more efficient) than trying to debug a truly busted statement.
ltbarcly3 4y ago
That you don't know SQL very well isn't an argument against it? Give me a 100 line SQL statement and I'll have it figured out in 20 minutes, about the same as it would take to figure out bad C++ or Python or whatever else.
irrational 4y ago
I have done that. This sounds more like a developer who is not truly skilled in SQL than an SQL issue.

If you've ever tried debugging a broken 100 line SQL statement created by some business analyst for some niche use then you might agree.

by xmprt 4y ago

If you've ever tried debugging a broken 100 line SQL statement created by some business analyst for some niche use then you might agree.

Closi 4y ago

Have you ever tried debugging a broken 100 line python script created by a business analyst? It's not any better.

masklinn 4y ago
It’s better by orders of magnitude, the python script you can literally trace instruction by instruction, and process every which way you want.
- actually_a_dog 4y ago
  But that's not a function of the language itself. If there were SQL debuggers you could step through a big query with, debugging SQL would be a lot easier. As it is, you often have to take the whole damn thing apart from the inside to see what's going on, then put it all back together again. That's... suboptimal, to say the least.
  - masklinn 4y ago
    > But that's not a function of the language itself. It is tho, SQL is a declarative language, so the execution model is largely opaque, by language design. But even if it were solely an implementation detail, Closi would still be wrong: they literally stated that “it’s not any better” to try debugging a 100 lines python script than a 100 lines query. > As it is, you often have to take the whole damn thing apart from the inside to see what's going on, then put it all back together again. That's... suboptimal, to say the least. Which is the point? Sounds like you agree that it is significantly easier to debug a 100 lines python script than a 100 lines SQL query.
    - ltbarcly3 4y ago
  - DemocracyFTW2 4y ago
    I often watch in horror what some people suggest is 'good SQL'. You even find plenty of those humongous multi-hundred-line beasts on the official PostgreSQL wiki, a single statement going over several pages if you were to print it out. Sure that's hard to debug. What I do in such cases is using many intermediate views, sometimes just named like `_foobar_010`, `_foobar_020` and so on, and a single `create (materialized) view foobar ... select from _foobar_120 ...`. That way, I can rely on the DB caring for basic structural soundness, giving error messages that are better localized than "something is wrong near ','", and I can visually (or, indeed, programmatically) debug all the intermediate steps.
    - ltbarcly3 4y ago
      This is just obviously selection bias, the stuff people put in the wiki is exactly the set of things that are so hard to do it's worth having a reference for other people. The stuff in the postgres wiki is in no way representative of sql in general.

oarabbus_ 4y ago

There was a time that doing exactly this (fixing, updating, or modifying existing queries) was a significant chunk of my day-to-day work. It certainly can be painful and tedious, but that's quite universal - certainly not specific to SQL. Unlike "true" programming languages (not to get into a debate about SQL's completeness, we all know it isn't practically a general-purpose language) this is usually best approached differently than debugging a program. Whenever I encountered a mess of a query, I'd ask myself two questions: 1. What is the purpose of the query/what is the desired output? 2. What are the source tables used in the query? At this point I'd typically rewrite the query, taking inspiration from the non-broken parts of the original if possible, and otherwise rewriting from scratch. In most cases this was faster (and the rewritten result often more efficient) than trying to debug a truly busted statement.

ltbarcly3 4y ago

That you don't know SQL very well isn't an argument against it? Give me a 100 line SQL statement and I'll have it figured out in 20 minutes, about the same as it would take to figure out bad C++ or Python or whatever else.

irrational 4y ago

I have done that. This sounds more like a developer who is not truly skilled in SQL than an SQL issue.