9 ms·

I feel the problem. When coding (not only in SQL) you often have to add something to the end of a list, and it is annoying that the end of the list is always sp

I feel the problem. When coding (not only in SQL) you often have to add something to the end of a list, and it is annoying that the end of the list is always special. You can't just copy some line and move it there. Also when moving things around you always have to take extra care at the end. So, my solution for this was always SELECT a , b , c FROM ... instead of: SELECT a, b, c, -- people want to allow trailing comma here FROM ... Leading commas also are also very regular and visible. A missing trailing comma is a lot harder to see. Before people start to mess with the standard, I'd suggest to maybe just change your coding style - and make the start of the list special - instead of the end. I'd suggest going for a lot less trailing commas - instead of allowing more.

DonHopkins 2y ago
That's as bad as using regular expressions: now you have TWO problems. Why do you seem to think you've cleverly solved the problem, when you've just moved the problem somewhere else just as bad, by blithely messing with the standard formatting conventions universally used by most human written languages and programming languages in the world? Programming languages borrow commas from human written languages, and no human written languages have leading commas. And moving the problem to the beginning on the list because you sometimes add things to the end ignores the fact that that also causes problems with READING the code as well as writing it, and you READ code much more often than you WRITE it. That's not a solution at all, and there's nothing clever about it. You've just pushed the problem to the beginning of the list, and now your code is also butt-ugly with totally non-standard formatting, which sane people don't recognize and editors and IDEs and linters don't support. I agree with cnity that it's too clever by half, and I'm in the camp (along with Guido van Rossum and his point that "Language Design Is Not Just Solving Puzzles" [1] [2]) that's not impressed by showboating displays of pointlessly creative cleverness and Rube-Goldbergeesque hacks that makes things even worse. Of course it's not as bad as tezza's clever by a third suggestion to add a confusing throw-away verbose noisy arbitrarily named pad element at the beginning, that actually forces the SQL database to do more work and send more useless data. Now you have three or more problems. The last thing we need is MORE CODE and network traffic contributing to complexity and climate change. I would fire and forget any developer who tried to pull that stunt. [1] https://www.artima.com/weblogs/viewpost.jsp?thread=147358 https://www.artima.com/weblogs/viewpost.jsp?thread=147358 All Things Pythonic: Language Design Is Not Just Solving Puzzles. By Guido van van Rossum, February 10, 2006. Summary: An incident on python-dev today made me appreciate (again) that there's more to language design than puzzle-solving. A ramble on the nature of Pythonicity, culminating in a comparison of language design to user interface design. [...] The unspoken, right brain constraint here is that the complexity introduced by a solution to a design problem must be somehow proportional to the problem's importance. In my mind, the inability of lambda to contain a print statement or a while-loop etc. is only a minor flaw; after all instead of a lambda you can just use a named function nested in the current scope. [...] And there's the rub: there's no way to make a Rube Goldberg language feature appear simple. Features of a programming language, whether syntactic or semantic, are all part of the language's user interface. And a user interface can handle only so much complexity or it becomes unusable. [2] http://lambda-the-ultimate.org/node/1298 http://lambda-the-ultimate.org/node/1298 The discussion is about multi-statement lambdas, but I don't want to discuss this specific issue. What's more interesting is the discussion of language as a user interface (an interface to what, you might ask), the underlying assumption that languages have character (e.g., Pythonicity), and the integrated view of semantics and syntax of language constructs when thinking about language usability.
- _dain_ 2y ago
  >That's not a solution at all, and there's nothing clever about it. It is a solution, and I'm not motivated by trying to be "clever". It just makes writing and reading the query easier for me. >You've just pushed the problem to the beginning of the list The beginning of the list is modified less often than the end. The two cases aren't symmetric. >and now your code is also butt-ugly with totally non-standard formatting "Ugly" is subjective. Personally I like how the commas line up vertically, so I can tell at a glance that I didn't miss one out. SQL doesn't have a standard formatting in any case. It's whitespace insensitive and I've seen people write it in all kinds of weird ways. >which sane people don't recognize and editors and IDEs and linters don't support. A difference in code-formatting taste is not "insanity". And it does not interfere with tooling at all. >showboating displays of pointlessly creative cleverness Where are you getting all of this from? You seem to be imagining a "type of guy" in your head, so that you can be mad at him. I am reminded of Sayre's law: In any dispute the intensity of feeling is inversely proportional to the value of the issues at stake.
- tezza 2y ago
  > I would fire and forget any developer who tried to pull that stunt. A tad bit harsh there? it is a trade off. clarity and ease during design time versus slightly uglier but still consistent code as a work around. miniscule energy overhead
  - DonHopkins 2y ago
    Firing and forgetting was your suggestion! >tezza 4 hours ago | root | parent | next [–] >as mentioned elsewhere, i personally introduce a pad element to get fire and forget consistency Adding an extra pad entry is a cure much worse than the disease, and I'd expect it should be objectively obvious to anyone that you're introducing more complexity and noise than you're removing, so it's not just a matter of "style" when you're pointlessly increasing the amount of work, memory, and network traffic per row. But sadly some people are just blind to or careless about that kind of complexity and waste. You might at least have the courtesy of writing a comment explaining "Ignore this extra unused pad argument because I'm just adding it to make the following commas line up." But that would make it even more painfully obvious that your solution was much worse than the problem you're trying to solve. You seem to have forgotten that other people have to read your code. Maybe just don't leave dumpster fires burning in your code that you want to forget in the first place. As Guido so wisely puts it: "the complexity introduced by a solution to a design problem must be somehow proportional to the problem's importance".
emayljames 2y ago
SELECT a , b , c FROM ... is the same as: SELECT a, b, c FROM ...
- adornKey 2y ago
  I updated my comment... On the first try the code-formatter here played some tricks with me. The line before the Code has to be empty to get correct formatting.
starspangled 2y ago
You've moved the problem from the last to the first element though. Surely people would prefer to be able to do this SELECT a, b, c, FROM ... ?
- tezza 2y ago
  as mentioned elsewhere, i personally introduce a pad element to get fire and forget consistency SELECT 1 as pad , a , b
  - computerthings 2y ago
    That's something where "what it makes the computer do" overrides "how nice it looks in text form" to me.
- _dain_ 2y ago
  The first element is modified less often than the last element. Often it's just an "id" column or something. Comma-first is a net win.
cnity 2y ago
> it is annoying that the end of the list is always special. You can't just copy some line and move it there. Also when moving things around you always have to take extra care at the end. You have simply moved the "special" entry to the beginning rather than the end. Side remark: I've noticed that when it comes to code formatting and grammar it's almost like there are broadly two camps. There are some instances of code formatting that involve something "clever". Your example of leading commas above for example. Another example is code ligatures. It's as if there's a dividing line of taste here where one either feels delight at the clever twist, or the total opposite, rarely anything in between. I happen to dislike these kinds of things (and particularly loathe code ligatures) but it is often hard to justify exactly why beyond taste. Code ligature thing has something to do with just seeing the characters that are actually there rather than a smokescreen, which IMO impedes editability because I can't place the cursor half-way through a ligature and so on. But it's more than that -- you could fix those functional issues and I'll still dislike them.
- adornKey 2y ago
  Adding something to the end is the most common thing to do. And changing the start is extremely rare - it's anyway special because you usually put it in the line with the SELECT.
  - boxed 2y ago
    Hard disagree.
    - _dain_ 2y ago
      I'm curious why you think so? I agree totally with the parent comment; when I iterate on a SQL query the most common place to make changes is near the end of the SELECT block, adding and removing and refining column expressions. I do the exact same "comma first" trick to make my life easier.
      - boxed 2y ago
        I only write SQL for complex grouping operations, otherwise the Django ORM is superior. And in those situations, the ordering is really not very relevant, and thus changes can occur anywhere.
krembo 2y ago
IMHO this is one of the ugliest formattings. Whenever I see that i try to revert and avoid at all costs. I know it's a personal flavor, yet. I might be too opinionated..
- mewpmewp2 2y ago
  I agree, I'd say I usually don't care about aesthetics, but that somehow looks so wrong, I am bothered by it.
- datadrivenangel 2y ago
  SQL is also case insensitive for most clauses! `SeLeCt ... fRoM ... wHeRe ...` IS VALID! (And you should use a linter/formatter to avoid these categories of style war)
masklinn 2y ago
That is the haskell workaround, and it also sucks, because it still requires a special non-uniform first value. I do not want to write either of your snippets, I want to write SELECT a, b, c, FROM Because now selected values are uniform and I can move them around or add new ones with minimal changes no matter their position in the sequence. It’s also completely wonky in many contexts e.g. CREATE TABLE. Trailing commas always works. > I'd suggest to maybe just change your coding style - and make the start of the list special - instead of the end. Supporting trailing commas means neither is special.
- adornKey 2y ago
  I'd still want to have leading commas. If the items in the list are long IF(...), maybe uses several lines and maybe contain SELECTs it's hard to see a missing trailing comma. At the start they're all lined up well, and it's very hard to get them wrong.
  - masklinn 2y ago
    > I'd still want to have leading commas. Real happy for you. Trailing commas support don’t prevent you from doing that.
- catapart 2y ago
  Spot on. Also, to generalize a bit: if a human is expected to read it, the way humans write should be able to be parsed by it. That's subjective, to a point, but it's an easy rule-of-thumb to remember. Trailing commas are so common that people have built workarounds for them. Therefore, they can be understood as "the way humans write". If you're writing a language that you still want to be readable by humans, you really should account for that. And, no shade for it not already being done. I'm just reiterating that there should be NO pushback to allowing trailing commas. It's a completely "common-sense" proposal.
  - roenxi 2y ago
    > It's a completely "common-sense" proposal. The SQL standards committee is having none of it. I can tell you that just from this one sentence. And, more seriously, there isn't really such a thing as a common-sense proposal with SQL. The grammar is so warped after all these years that there isn't a path to consistency and the broken attempt at English syntax has rendered it nearly incomprehensible for both human and machine parsing. Any change to anything could have bizarre flow on effects. I'd love to see trailing commas added to SELECT though. Given the mess it isn't possible to make the situation worse and the end of the list being special can be infuriating.
tezza 2y ago
I do this as well. on top i often do a pad entry so that the elements are all on their own line SELECT 1 as pad , a , b then i can reorder lines trivially without stopping to re-comma the endpoints or maintain which special entry is on the line of the SELECT token what would be helpful is both LEADING and trailing commas so I am suggesting: SELECT , a , b would be permissible too. The parsing step is no longer the difficult portion. Developer ease leads to less mistakes is my conjecture.
- mewpmewp2 2y ago
  Why not something like SELECT a, b, c, 1 as pad FROM Then?
  - tezza 2y ago
    cool, that would work too. my preference is leading separators so the separators are all in a visual column. being in a visual column allows the eye to discount the separators easily. typically names are different lengths and the commas are hard/harder to spot Your suggestion: SELECT first_column, second_column_wider_a_lot, (third + fourth_combined_expression), 1 as pad FROM vs my current preference: SELECT 1 as pad , first_column , second_column_wider_a_lot , (third + fourth_combined_expression) FROM
tlb 2y ago
I've done this for arrays in JSON files, so that git merge will merge two changes that append to the list without a conflict. I think the right answer is to fix the merge algorithm to handle some common cases where an inserted line logically includes the delimiter at the end of the previous line.
- recursive 2y ago
  The problem with that is that it's invalid json. Some things might tolerate it though.
atombender 2y ago
I often do this with boolean WHERE filters when I'm doing interactive exploration on some data: SELECT ... WHERE foo = 1 AND bar = 2 I want to comment out a line (i.e. "--foo = 1"), but would break the syntax. The solution is to start with "WHERE true": SELECT ... WHERE true --AND foo = 1 AND bar = 2 Now you can comment/comment anything. (Putting the AND at end of each line has the same problem, of course, and requires putting a "true" at the bottom instead.)
- NoMoreNicksLeft 2y ago
  God, everyone's going to hate me for this. (I will have earned it, I think.) SELECT ... WHERE foo = 1 AND bar = 2 Each keyword gets a new line, the middle gutter between keyword and expressions stays in the same place, and things get really, really fugly if I need a subselect or whatever. Any given line can be commented out. (And no, none of that leading comma bullshit, somehow that looks nasty to me.) Downvote this into oblivion to protect the junior developers from being infected by whatever degeneracy has ahold of me.
  - wruza 2y ago
    Since we're already here, we could think about trailing AND, actually. Look: SELECT a, sum(b), WHERE foo = 1 AND GROUP BY a Sounds pretty SQL to me.
    - yellowapple 2y ago
      This is cursed, but also entirely consistent with the trailing comma proposal. It'd sure look funny if all keywords that connected clauses together were trailable, though. SELECT a, sum(b), FROM stuff WHERE foo = 1 OR GROUP BY a Or just as horrifying: SELECT a, b, c, FROM foo UNION ALL SELECT a, b, c, FROM bar UNION ALL
      - NoMoreNicksLeft 2y ago
        Those aren't horrifying to me, only problem is that I want the keywords to right-align with each other.
datadrivenangel 2y ago
Mode Analytics published some data years ago showing that SQL programmers who preferred leading commas had a lower rate of errors than programmers who used trailing commas. [0] 0 - https://mode.com/blog/should-sql-queries-use-trailing-or-leading-commas https://mode.com/blog/should-sql-queries-use-trailing-or-lea...
- skeeter2020 2y ago
  I do this but I'm skeptical of the causation. I think it might be a symptom of people who are generally more careful with syntax because the formatting means more to them, so they spend time reading the query and moving bits around, which is how they find little typo bugs.
skeeter2020 2y ago
I too prefer leading commas, especially useful when you're prototyping a query. I also picked up the ORM trick of starting your WHERE clause with 1=1 so that every meaningful filter can start with AND ... I'm not as consistent with this one, but it's handy too. I catch (friendly) flak for my zealot SQL formatting (capitalization, indenting) and know it doesn't impact the execution, but there's something about working in logic / set theory that matches with strict presentation; maybe helps you think with a more rigid mindset?
tiffanyh 2y ago
Another trick is, if you're programmatically building a SQL statement - adding "WHERE 1=1" makes things easier ... like so: SELECT * FROM table WHERE 1=1 That way, if you want to filter down the result, everything programmatically appended just needs an "AND ..." at the start, like: SELECT * FROM table WHERE 1=1 AND age > 21 AND xyz = 'abc' ... Because without "WHERE 1=1", you'd had to handle the first condition different than all subsequent conditions.
- dewey 2y ago
  You can also just do "where true", easier to type.
  - andy81 2y ago
    Not in e.g. MSSQL
- chasil 2y ago
  Similarly, you could select NULL as your leading column, and prepend commas by that means. That method does impact the result set, and using it for CTAS or bulk insert would require more care in column selection.
- wruza 2y ago
  I prefer “1=0 OR 1=1”, because when you delete all conditions you can keep 1=0 out of selection and it decays into a no-op rather than destroying a table: DELETE FROM table WHERE 1=0[ OR 1=1 AND age > 21 AND xyz = 'abc'] ; Brackets designate selection bounds before text deletion. The above just safely does nothing after you hit DEL. Without that you’d have to delete whole [WHERE…], which leaves a very dangerous statement in the code.
infogulch 2y ago
Recently I've been formatting like this but with tabs so the first column is aligned with subsequent columns: SELECT a , b , c FROM ...
zX41ZdbW 2y ago
ClickHouse has support for trailing commas for several years. I recommend looking at ClickHouse (https://github.com/ClickHouse/ClickHouse/ https://github.com/ClickHouse/ClickHouse/) as an example of a modern SQL database that emphasizes developer experience, performance, and quality-of-life improvements. I'm the author of ClickHouse, and I'm happy to see that its innovation has been inspired and adopted in other database management systems.
tqwhite 2y ago
You are a monster. Being against trailing commas is like being against happiness or cute children or Cheetohs. You do not need to see the missing comma. That is what compilers are for. Also, literally the only reason anyone has a missing comma is because they reordered the terms and forgot to put the comma onto the one that was forced to not have one because of this monstrous failure. Some things are nothing but good. You are on the wrong side of history.
- fiddlerwoaroof 2y ago
  One thing I like about the nix language is it uses semicolons to separate elements of the map: while I use trailing commas, they always look dangling to me whereas semicolons look fine without another expression following.
specialist 2y ago
Alternately: SELECT a, b, c, NULL FROM ... SELECT a, b, c, true FROM ... SELECT a, b, c, 'ignore me' FROM ... FWIW, my SQL grammar ignores trailing commas. IIRC, H2 Database does too.
giancarlostoro 2y ago
This is how SQL Management Studio writes the queries it writes for you (like Select 1000 records) so when you comment out a line or lines, it doesn't cause any issues due to a misplaced comma.
marcosdumay 2y ago
> Before people start to mess with the standard No, it's still a really good reason to mess with the standard. While the standard doesn't evolve into something slightly modern, yes, that workaround is better than the way people usually write SQL. But its existence isn't a reason not to fix the fundamental problem.
andelink 2y ago
I have never been a fan of leading commas. How often are we hastily moving column expressions around? I also have a somewhat controversial style preference in regard to capitalization. Because SQL is case-insensitive, I will always type everything in lowercase and let syntax highlighting do its thing. I hate mixed capitalization. Not only does it feel like keywords are yelling at me, but also the moment someone else gets involved there will be inconsistent casing. Do you capitalize just the keywords or do you include functions? How about operators (e.g. “in” or “like”). More often than not I see individuals are inconsistent with their own queries. So I say to hell with it all and just keep it lowercased
afiori 2y ago
the simplest solution is to have commas be "separating whitespace" so that "," === ",,,,,,,,,,,," === " ,,,,, , , ,, , ," so your example can become SELECT , a , b , c FROM ... and SELECT a, b, c, FROM ... or SELECT a, b, c, FROM ... the only limitation is that sometimes more parenthesis are needed and that SELECT col_name new_col_name from table_name needs to be rewritten as SELECT col_name as new_col_name from table_name. good tradeoffs IMO
Jean-Papoulos 2y ago
This is way less readable to me, because now my brain has to parse "a comma, actual thing" instead of a list of things.

DonHopkins 2y ago

That's as bad as using regular expressions: now you have TWO problems. Why do you seem to think you've cleverly solved the problem, when you've just moved the problem somewhere else just as bad, by blithely messing with the standard formatting conventions universally used by most human written languages and programming languages in the world? Programming languages borrow commas from human written languages, and no human written languages have leading commas. And moving the problem to the beginning on the list because you sometimes add things to the end ignores the fact that that also causes problems with READING the code as well as writing it, and you READ code much more often than you WRITE it. That's not a solution at all, and there's nothing clever about it. You've just pushed the problem to the beginning of the list, and now your code is also butt-ugly with totally non-standard formatting, which sane people don't recognize and editors and IDEs and linters don't support. I agree with cnity that it's too clever by half, and I'm in the camp (along with Guido van Rossum and his point that "Language Design Is Not Just Solving Puzzles" [1] [2]) that's not impressed by showboating displays of pointlessly creative cleverness and Rube-Goldbergeesque hacks that makes things even worse. Of course it's not as bad as tezza's clever by a third suggestion to add a confusing throw-away verbose noisy arbitrarily named pad element at the beginning, that actually forces the SQL database to do more work and send more useless data. Now you have three or more problems. The last thing we need is MORE CODE and network traffic contributing to complexity and climate change. I would fire and forget any developer who tried to pull that stunt. [1] https://www.artima.com/weblogs/viewpost.jsp?thread=147358 https://www.artima.com/weblogs/viewpost.jsp?thread=147358 All Things Pythonic: Language Design Is Not Just Solving Puzzles. By Guido van van Rossum, February 10, 2006. Summary: An incident on python-dev today made me appreciate (again) that there's more to language design than puzzle-solving. A ramble on the nature of Pythonicity, culminating in a comparison of language design to user interface design. [...] The unspoken, right brain constraint here is that the complexity introduced by a solution to a design problem must be somehow proportional to the problem's importance. In my mind, the inability of lambda to contain a print statement or a while-loop etc. is only a minor flaw; after all instead of a lambda you can just use a named function nested in the current scope. [...] And there's the rub: there's no way to make a Rube Goldberg language feature appear simple. Features of a programming language, whether syntactic or semantic, are all part of the language's user interface. And a user interface can handle only so much complexity or it becomes unusable. [2] http://lambda-the-ultimate.org/node/1298 http://lambda-the-ultimate.org/node/1298 The discussion is about multi-statement lambdas, but I don't want to discuss this specific issue. What's more interesting is the discussion of language as a user interface (an interface to what, you might ask), the underlying assumption that languages have character (e.g., Pythonicity), and the integrated view of semantics and syntax of language constructs when thinking about language usability.

_dain_ 2y ago
>That's not a solution at all, and there's nothing clever about it. It is a solution, and I'm not motivated by trying to be "clever". It just makes writing and reading the query easier for me. >You've just pushed the problem to the beginning of the list The beginning of the list is modified less often than the end. The two cases aren't symmetric. >and now your code is also butt-ugly with totally non-standard formatting "Ugly" is subjective. Personally I like how the commas line up vertically, so I can tell at a glance that I didn't miss one out. SQL doesn't have a standard formatting in any case. It's whitespace insensitive and I've seen people write it in all kinds of weird ways. >which sane people don't recognize and editors and IDEs and linters don't support. A difference in code-formatting taste is not "insanity". And it does not interfere with tooling at all. >showboating displays of pointlessly creative cleverness Where are you getting all of this from? You seem to be imagining a "type of guy" in your head, so that you can be mad at him. I am reminded of Sayre's law: In any dispute the intensity of feeling is inversely proportional to the value of the issues at stake.
tezza 2y ago
> I would fire and forget any developer who tried to pull that stunt. A tad bit harsh there? it is a trade off. clarity and ease during design time versus slightly uglier but still consistent code as a work around. miniscule energy overhead
- DonHopkins 2y ago
  Firing and forgetting was your suggestion! >tezza 4 hours ago | root | parent | next [–] >as mentioned elsewhere, i personally introduce a pad element to get fire and forget consistency Adding an extra pad entry is a cure much worse than the disease, and I'd expect it should be objectively obvious to anyone that you're introducing more complexity and noise than you're removing, so it's not just a matter of "style" when you're pointlessly increasing the amount of work, memory, and network traffic per row. But sadly some people are just blind to or careless about that kind of complexity and waste. You might at least have the courtesy of writing a comment explaining "Ignore this extra unused pad argument because I'm just adding it to make the following commas line up." But that would make it even more painfully obvious that your solution was much worse than the problem you're trying to solve. You seem to have forgotten that other people have to read your code. Maybe just don't leave dumpster fires burning in your code that you want to forget in the first place. As Guido so wisely puts it: "the complexity introduced by a solution to a design problem must be somehow proportional to the problem's importance".

emayljames 2y ago

SELECT a , b , c FROM ... is the same as: SELECT a, b, c FROM ...

adornKey 2y ago
I updated my comment... On the first try the code-formatter here played some tricks with me. The line before the Code has to be empty to get correct formatting.

starspangled 2y ago

You've moved the problem from the last to the first element though. Surely people would prefer to be able to do this SELECT a, b, c, FROM ... ?

tezza 2y ago
as mentioned elsewhere, i personally introduce a pad element to get fire and forget consistency SELECT 1 as pad , a , b
- computerthings 2y ago
  That's something where "what it makes the computer do" overrides "how nice it looks in text form" to me.
_dain_ 2y ago
The first element is modified less often than the last element. Often it's just an "id" column or something. Comma-first is a net win.

cnity 2y ago

> it is annoying that the end of the list is always special. You can't just copy some line and move it there. Also when moving things around you always have to take extra care at the end. You have simply moved the "special" entry to the beginning rather than the end. Side remark: I've noticed that when it comes to code formatting and grammar it's almost like there are broadly two camps. There are some instances of code formatting that involve something "clever". Your example of leading commas above for example. Another example is code ligatures. It's as if there's a dividing line of taste here where one either feels delight at the clever twist, or the total opposite, rarely anything in between. I happen to dislike these kinds of things (and particularly loathe code ligatures) but it is often hard to justify exactly why beyond taste. Code ligature thing has something to do with just seeing the characters that are actually there rather than a smokescreen, which IMO impedes editability because I can't place the cursor half-way through a ligature and so on. But it's more than that -- you could fix those functional issues and I'll still dislike them.

adornKey 2y ago
Adding something to the end is the most common thing to do. And changing the start is extremely rare - it's anyway special because you usually put it in the line with the SELECT.
- boxed 2y ago
  Hard disagree.
  - _dain_ 2y ago
    I'm curious why you think so? I agree totally with the parent comment; when I iterate on a SQL query the most common place to make changes is near the end of the SELECT block, adding and removing and refining column expressions. I do the exact same "comma first" trick to make my life easier.
    - boxed 2y ago
      I only write SQL for complex grouping operations, otherwise the Django ORM is superior. And in those situations, the ordering is really not very relevant, and thus changes can occur anywhere.

krembo 2y ago

IMHO this is one of the ugliest formattings. Whenever I see that i try to revert and avoid at all costs. I know it's a personal flavor, yet. I might be too opinionated..

mewpmewp2 2y ago
I agree, I'd say I usually don't care about aesthetics, but that somehow looks so wrong, I am bothered by it.
datadrivenangel 2y ago
SQL is also case insensitive for most clauses! `SeLeCt ... fRoM ... wHeRe ...` IS VALID! (And you should use a linter/formatter to avoid these categories of style war)

masklinn 2y ago

That is the haskell workaround, and it also sucks, because it still requires a special non-uniform first value. I do not want to write either of your snippets, I want to write SELECT a, b, c, FROM Because now selected values are uniform and I can move them around or add new ones with minimal changes no matter their position in the sequence. It’s also completely wonky in many contexts e.g. CREATE TABLE. Trailing commas always works. > I'd suggest to maybe just change your coding style - and make the start of the list special - instead of the end. Supporting trailing commas means neither is special.

adornKey 2y ago
I'd still want to have leading commas. If the items in the list are long IF(...), maybe uses several lines and maybe contain SELECTs it's hard to see a missing trailing comma. At the start they're all lined up well, and it's very hard to get them wrong.
- masklinn 2y ago
  > I'd still want to have leading commas. Real happy for you. Trailing commas support don’t prevent you from doing that.
catapart 2y ago
Spot on. Also, to generalize a bit: if a human is expected to read it, the way humans write should be able to be parsed by it. That's subjective, to a point, but it's an easy rule-of-thumb to remember. Trailing commas are so common that people have built workarounds for them. Therefore, they can be understood as "the way humans write". If you're writing a language that you still want to be readable by humans, you really should account for that. And, no shade for it not already being done. I'm just reiterating that there should be NO pushback to allowing trailing commas. It's a completely "common-sense" proposal.
- roenxi 2y ago
  > It's a completely "common-sense" proposal. The SQL standards committee is having none of it. I can tell you that just from this one sentence. And, more seriously, there isn't really such a thing as a common-sense proposal with SQL. The grammar is so warped after all these years that there isn't a path to consistency and the broken attempt at English syntax has rendered it nearly incomprehensible for both human and machine parsing. Any change to anything could have bizarre flow on effects. I'd love to see trailing commas added to SELECT though. Given the mess it isn't possible to make the situation worse and the end of the list being special can be infuriating.

tezza 2y ago

I do this as well. on top i often do a pad entry so that the elements are all on their own line SELECT 1 as pad , a , b then i can reorder lines trivially without stopping to re-comma the endpoints or maintain which special entry is on the line of the SELECT token what would be helpful is both LEADING and trailing commas so I am suggesting: SELECT , a , b would be permissible too. The parsing step is no longer the difficult portion. Developer ease leads to less mistakes is my conjecture.

mewpmewp2 2y ago
Why not something like SELECT a, b, c, 1 as pad FROM Then?
- tezza 2y ago
  cool, that would work too. my preference is leading separators so the separators are all in a visual column. being in a visual column allows the eye to discount the separators easily. typically names are different lengths and the commas are hard/harder to spot Your suggestion: SELECT first_column, second_column_wider_a_lot, (third + fourth_combined_expression), 1 as pad FROM vs my current preference: SELECT 1 as pad , first_column , second_column_wider_a_lot , (third + fourth_combined_expression) FROM

tlb 2y ago

I've done this for arrays in JSON files, so that git merge will merge two changes that append to the list without a conflict. I think the right answer is to fix the merge algorithm to handle some common cases where an inserted line logically includes the delimiter at the end of the previous line.

recursive 2y ago
The problem with that is that it's invalid json. Some things might tolerate it though.

atombender 2y ago

I often do this with boolean WHERE filters when I'm doing interactive exploration on some data: SELECT ... WHERE foo = 1 AND bar = 2 I want to comment out a line (i.e. "--foo = 1"), but would break the syntax. The solution is to start with "WHERE true": SELECT ... WHERE true --AND foo = 1 AND bar = 2 Now you can comment/comment anything. (Putting the AND at end of each line has the same problem, of course, and requires putting a "true" at the bottom instead.)

NoMoreNicksLeft 2y ago
God, everyone's going to hate me for this. (I will have earned it, I think.) SELECT ... WHERE foo = 1 AND bar = 2 Each keyword gets a new line, the middle gutter between keyword and expressions stays in the same place, and things get really, really fugly if I need a subselect or whatever. Any given line can be commented out. (And no, none of that leading comma bullshit, somehow that looks nasty to me.) Downvote this into oblivion to protect the junior developers from being infected by whatever degeneracy has ahold of me.
- wruza 2y ago
  Since we're already here, we could think about trailing AND, actually. Look: SELECT a, sum(b), WHERE foo = 1 AND GROUP BY a Sounds pretty SQL to me.
  - yellowapple 2y ago
    This is cursed, but also entirely consistent with the trailing comma proposal. It'd sure look funny if all keywords that connected clauses together were trailable, though. SELECT a, sum(b), FROM stuff WHERE foo = 1 OR GROUP BY a Or just as horrifying: SELECT a, b, c, FROM foo UNION ALL SELECT a, b, c, FROM bar UNION ALL
    - NoMoreNicksLeft 2y ago
      Those aren't horrifying to me, only problem is that I want the keywords to right-align with each other.

datadrivenangel 2y ago

Mode Analytics published some data years ago showing that SQL programmers who preferred leading commas had a lower rate of errors than programmers who used trailing commas. [0] 0 - https://mode.com/blog/should-sql-queries-use-trailing-or-leading-commas https://mode.com/blog/should-sql-queries-use-trailing-or-lea...

skeeter2020 2y ago
I do this but I'm skeptical of the causation. I think it might be a symptom of people who are generally more careful with syntax because the formatting means more to them, so they spend time reading the query and moving bits around, which is how they find little typo bugs.

skeeter2020 2y ago

I too prefer leading commas, especially useful when you're prototyping a query. I also picked up the ORM trick of starting your WHERE clause with 1=1 so that every meaningful filter can start with AND ... I'm not as consistent with this one, but it's handy too. I catch (friendly) flak for my zealot SQL formatting (capitalization, indenting) and know it doesn't impact the execution, but there's something about working in logic / set theory that matches with strict presentation; maybe helps you think with a more rigid mindset?

tiffanyh 2y ago

Another trick is, if you're programmatically building a SQL statement - adding "WHERE 1=1" makes things easier ... like so: SELECT * FROM table WHERE 1=1 That way, if you want to filter down the result, everything programmatically appended just needs an "AND ..." at the start, like: SELECT * FROM table WHERE 1=1 AND age > 21 AND xyz = 'abc' ... Because without "WHERE 1=1", you'd had to handle the first condition different than all subsequent conditions.

dewey 2y ago
You can also just do "where true", easier to type.
- andy81 2y ago
  Not in e.g. MSSQL
chasil 2y ago
Similarly, you could select NULL as your leading column, and prepend commas by that means. That method does impact the result set, and using it for CTAS or bulk insert would require more care in column selection.
wruza 2y ago
I prefer “1=0 OR 1=1”, because when you delete all conditions you can keep 1=0 out of selection and it decays into a no-op rather than destroying a table: DELETE FROM table WHERE 1=0[ OR 1=1 AND age > 21 AND xyz = 'abc'] ; Brackets designate selection bounds before text deletion. The above just safely does nothing after you hit DEL. Without that you’d have to delete whole [WHERE…], which leaves a very dangerous statement in the code.

infogulch 2y ago

Recently I've been formatting like this but with tabs so the first column is aligned with subsequent columns: SELECT a , b , c FROM ...

zX41ZdbW 2y ago

ClickHouse has support for trailing commas for several years. I recommend looking at ClickHouse (https://github.com/ClickHouse/ClickHouse/ https://github.com/ClickHouse/ClickHouse/) as an example of a modern SQL database that emphasizes developer experience, performance, and quality-of-life improvements. I'm the author of ClickHouse, and I'm happy to see that its innovation has been inspired and adopted in other database management systems.

tqwhite 2y ago

You are a monster. Being against trailing commas is like being against happiness or cute children or Cheetohs. You do not need to see the missing comma. That is what compilers are for. Also, literally the only reason anyone has a missing comma is because they reordered the terms and forgot to put the comma onto the one that was forced to not have one because of this monstrous failure. Some things are nothing but good. You are on the wrong side of history.

fiddlerwoaroof 2y ago
One thing I like about the nix language is it uses semicolons to separate elements of the map: while I use trailing commas, they always look dangling to me whereas semicolons look fine without another expression following.

specialist 2y ago

Alternately: SELECT a, b, c, NULL FROM ... SELECT a, b, c, true FROM ... SELECT a, b, c, 'ignore me' FROM ... FWIW, my SQL grammar ignores trailing commas. IIRC, H2 Database does too.

giancarlostoro 2y ago

This is how SQL Management Studio writes the queries it writes for you (like Select 1000 records) so when you comment out a line or lines, it doesn't cause any issues due to a misplaced comma.

marcosdumay 2y ago

> Before people start to mess with the standard No, it's still a really good reason to mess with the standard. While the standard doesn't evolve into something slightly modern, yes, that workaround is better than the way people usually write SQL. But its existence isn't a reason not to fix the fundamental problem.

andelink 2y ago

I have never been a fan of leading commas. How often are we hastily moving column expressions around? I also have a somewhat controversial style preference in regard to capitalization. Because SQL is case-insensitive, I will always type everything in lowercase and let syntax highlighting do its thing. I hate mixed capitalization. Not only does it feel like keywords are yelling at me, but also the moment someone else gets involved there will be inconsistent casing. Do you capitalize just the keywords or do you include functions? How about operators (e.g. “in” or “like”). More often than not I see individuals are inconsistent with their own queries. So I say to hell with it all and just keep it lowercased

afiori 2y ago

the simplest solution is to have commas be "separating whitespace" so that "," === ",,,,,,,,,,,," === " ,,,,, , , ,, , ," so your example can become SELECT , a , b , c FROM ... and SELECT a, b, c, FROM ... or SELECT a, b, c, FROM ... the only limitation is that sometimes more parenthesis are needed and that SELECT col_name new_col_name from table_name needs to be rewritten as SELECT col_name as new_col_name from table_name. good tradeoffs IMO

Jean-Papoulos 2y ago

This is way less readable to me, because now my brain has to parse "a comma, actual thing" instead of a list of things.