Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sqlfluff the SQL Linter for Humans

sqlfluff.com

Post reply on HN
Print view View original

61–70 of 95 posts

Re: Sqlfluff the SQL Linter for Humans

#61

Post by marcosdumay »

nmstoker wrote: post #44
darksaints wrote: post #33

Earlier quoted context omitted.

If SQL would just allow a trailing comma on the last column, none of this leading comma shit would be necessary. I still use trailing commas, because code readability is more important that writeability IMO, but the fact that I'm forced to sympathize with such an ugly shit sandwich is really frustrating. Can't sympathize with the clause alignment though. And I can't figure out why someone who does leading commas for…

Yes, despite knowing how to do it right those damn comma issues always come up at the worst time and to add insult to injury they're reported by most of the tools I have available like they're totally insoluble to the computer (even though far more taxing things are routinely fixed automatically).

The code you get with a trailing comma is valid SQL just like the one you get without, and both have different meanings.

The language is just too lenient with syntax, what is quite on the line for a "human legible" language from the late 70's and earlier 80's, but a really undesirable characteristic in practice. Unfortunately, nobody is doing a "better SQL", just radically different things that often not even support the same DB engines. And those have a real hard time getting popular, so we are stuck with the late 70's speculative innovations.

Re: Sqlfluff the SQL Linter for Humans

#62

Post by pwildenhain »

twoodfin wrote: post #49

Most of the rules seem reasonable, but what's the justification for this? Rule_L031: Avoid table aliases in from clauses and join conditions. I can't see what makes aliases clarifying in `select` or `where` but not in `join`. Also, inlined subqueries in a join must be given an alias, so there are cases where this rule can't be applied consistently.

I think it depends on if your table names are readable or not. If you table names are "sales" and "orders" then I would argue that makes the sql a lot more readable i.e

sales.amount orders.due_date

vs

s.amount ord.due_date

In the second example I have to flip back and forth between the join statement in order to know where the columns are coming from.

If you have awful table names though like qq123xc then I think aliasing would be appropriate

Re: Sqlfluff the SQL Linter for Humans

#63

Post by tunetheweb »

munro wrote: post #32
ErunamoJAZZ wrote: post #21

I wanted to use this linter, but they don't support many things that are used in real production code. By example, a simple postgres function like this one can't be parsed: CREATE OR REPLACE FUNCTION public.setof_test() RETURNS SETOF text LANGUAGE sql STABLE STRICT AS $function$ select unnest(array['hi', 'test']) $function$ ;

The code looks really great [1], very declarative, looks like it should be pretty easy to add better PostgreSQL support. I also wonder if how easy it would be to plug it into VS Code, and build in an auto formatter (♥ω♥ ). At a glance it looks like it's parsing AST, which is really hard to find for SQL, so auto formatter could be possible to build from this. I notice SQLite could use some improving too, seems like a…

There already is a VS Code extension for this: https://marketplace.visualstudio.com/items?itemName=dorzey.v...

https://github.com/sqlfluff/vscode-sqlfluff

Re: Sqlfluff the SQL Linter for Humans

#64

Post by marcosdumay »

Genmutant wrote: post #27
ThePadawan wrote: post #2

This is a linter which complains about spaces and newlines, but lets "WHERE col = NULL" pass (which can never be meaningful) just fine? Obviously this depends on the rules configuration, but just judging that first smell test, it seems very ill thought out.

In MS SQL Server, with ANSI_NULLS set to OFF, it will select all rows having a NULL value for col. AFAIK not used that often.

That is a serious gotcha of SQL Server. It (and Oracle) has some serious problems with unique constraints because of that kind of stuff.

Re: Sqlfluff the SQL Linter for Humans

#65

Post by cryptonector »

twoodfin wrote: post #49

Most of the rules seem reasonable, but what's the justification for this? Rule_L031: Avoid table aliases in from clauses and join conditions. I can't see what makes aliases clarifying in `select` or `where` but not in `join`. Also, inlined subqueries in a join must be given an alias, so there are cases where this rule can't be applied consistently.

This rule is no good.

Re: Sqlfluff the SQL Linter for Humans

#66

Post by cryptonector »

pwildenhain wrote: post #62
twoodfin wrote: post #49

Most of the rules seem reasonable, but what's the justification for this? Rule_L031: Avoid table aliases in from clauses and join conditions. I can't see what makes aliases clarifying in `select` or `where` but not in `join`. Also, inlined subqueries in a join must be given an alias, so there are cases where this rule can't be applied consistently.

I think it depends on if your table names are readable or not. If you table names are "sales" and "orders" then I would argue that makes the sql a lot more readable i.e sales.amount orders.due_date vs s.amount ord.due_date In the second example I have to flip back and forth between the join statement in order to know where the columns are coming from. If you have awful table names though like qq123xc then I think ali…

The moment you have the same table used more than once as a table source (in a JOIN) then this goes out the window.

Moreover, there's nothing wrong with saying `SELECT ... FROM sales sales ...`.

Re: Sqlfluff the SQL Linter for Humans

#67

Post by forgetfulness »

munk-a wrote: post #54
forgetfulness wrote: post #46

Earlier quoted context omitted.

Yes but realistically, save for the code that someone who's really inexperienced or sloppy would write, fixating over whether someone is writing select a, b, c from foo where bar = 'quux' rather than SELECT a, b, c FROM foo WHERE bar = 'quux' is petty and immaterial to what the code communicates. Worse if it's about details within the roughly the same style. Edit: but if there's a code formatter that can be seamlessl…

Code isn't meant to be read by computers - it's meant to be read by humans (including yourself in three years when you've forgotten everything). Code sugaring and style adherence are vital portions of retaining a maintainable and flexible codebase.

Yeah I know the spirit. I'm saying it amounts to absolutely nothing in readability. If there's any formatting at all, it's structure and not particular whitespace and comma placement conventions that make or break readability, the rest is code quality theater.

Re: Sqlfluff the SQL Linter for Humans

#68

Post by Orou »

cryptonector wrote: post #66
pwildenhain wrote: post #62

Earlier quoted context omitted.

I think it depends on if your table names are readable or not. If you table names are "sales" and "orders" then I would argue that makes the sql a lot more readable i.e sales.amount orders.due_date vs s.amount ord.due_date In the second example I have to flip back and forth between the join statement in order to know where the columns are coming from. If you have awful table names though like qq123xc then I think ali…

The moment you have the same table used more than once as a table source (in a JOIN) then this goes out the window. Moreover, there's nothing wrong with saying `SELECT ... FROM sales sales ...`.

Wholeheartedly agree with this. The problem isn't aliasing, it's lazy aliasing which is more I think what GP was referring to (e.g. emp.id instead of employee.id).

Re: Sqlfluff the SQL Linter for Humans

#69

Post by bntyhntr »

snidane wrote: post #55
fnord123 wrote: post #24

Earlier quoted context omitted.

alt styling SELECT a, b, COUNT(*) as count FROM table WHERE cond1 AND cond2 GROUP BY a, b ORDER BY count DESC

I've seen this one in the wild and have objections to it. - why capitalize KEYWORDS. We're no longer in 1970s. We use colors - why waste so much space. Why not just put table on the same line with from. With joins this just bloats up - for complicated conditions they have to be put on their own lines. Do I give each AND a separate line and waste ever more space?

My editors and code review tools do not format or highlight sql for me when it's embedded in other files - eg java. I find capitalized keywords to be very convenient. Of course at that point, I can't run sqfluff on it either but this thread has already devolved fairly quickly.

Re: Sqlfluff the SQL Linter for Humans

#70

Post by Orou »

extr wrote: post #22
snidane wrote: post #11

There surely is a need for a sql linter, but if it wants to be strict about syntax style, it should better familiarize itself with not-noob styles first. Eg. some people like to align the clauses vertically and use a leading, not trailing, comma. select a , b , count(*) as count from table where cond1 and cond2 group by a, b order by count desc

Yes! I write SQL this way. I grant the comma thing is more a matter of taste, but aligning clauses vertically and maximizing rivers is super useful to me and it forever mystifies me that it isn't standard. This is an extreme example, but I like to keep join clauses vertically aligned as well: select a , b , count(*) as count from table join table2 on table.key1 = table2.key1 and table.key2 = table2.key2 and DATEADD('…

> it forever mystifies me that it isn't standard

I can give a few thoughts on why here:

- it doesn't work for collaborative efforts unless everyone is using the same linter or IDE with the same configuration. Not friendly to edits outside of that ecosystem (e.g. open source). - there are other ways of writing SQL (e.g. dbt style guide) which are still very readable while also being much faster to write by hand without the need for an auto-formatter. - some of these patterns such as the from and join indentations have nothing to do with readability and are purely stylistic. Nothing wrong with that, but there's nothing objectively better about this pattern - it just comes down to how you're used to reading SQL.

Post reply on HN