misleading alignment in correlated subquery lateral

[nraynaud]

I think those aligned keywords give the wrong impression when scanning the formatted query.

here is the text if you want to toy with it:

CREATE OR REPLACE VIEW "${VIEW_SCHEMA}"."network" AS
  SELECT "uuid", "VIFs_t"."VIFs"
  FROM
    "${TABLE_SCHEMA}"."network"
    LEFT JOIN LATERAL (
      SELECT
        COALESCE(
          array_agg(linked."VIFs" ORDER BY linked."VIFs"),
          '{}'
        ) AS "VIFs"
      FROM "${TABLE_SCHEMA}"."network_VIFs" AS linked
      WHERE linked."network" = uuid
    ) AS "VIFs_t"
      ON TRUE;

[nene]

I understand that you would like this SQL to be formatted differently. Please give an example of how you would expect this to get formatted.

[nraynaud]

good question, I just gave a cursory look at the tool, so I don't know exactly how to answer in the spirit of the formatter, do you have circumstances where you use a double indentation?

Maybe double identation inside the brackets?
Maybe close the bracket at the end of the previous line and put the AS at the beginning of the line?
Maybe line up the 'ON' with the 'LEFT', since they go together?

[nene]

Well, from my side I don't even really understand the problem. You say that something about this alignment is confusing, but confusing in what way? Like, what do you think this SQL looks like it's doing, and what does it actually do?

If you want more indentation than 2 spaces, then you can change it in prettier config, though that's not available in the online demo page. But I guess that's not what you're asking, and that'll probably make the situation even worse. Indenting part of the code by more than one indentation step at a time is something I would only consider as the very last resort - preferably never.

Anyway, for now it looks like more of a case with this specific snippet of SQL being confusing in some way. Personally I don't even really understand what this code is doing in the first place, so from my standpoint maybe just this code is kinda confusing in general. But my skills in writing SQL queries are pretty limited, so that might not mean much.

[nraynaud]

Ok, let me try to explain the 2 issues (what's a LATERAL, and the grammatical makup of this expression).
It so happens that I wrote some help for co-workers who are not up to speed on modern SQL.
For context, network is a table, and it has a relationship to multiple VIF (virtual interface), the relationship is materialized in the table network_VIFs.

The created view network has the same number of lines as the network table, but it has a column VIFs containing an array of VIF primary keys.

• LATERAL allows us to run a query referencing the table in the FROM (the WHERE linked."network"=uuid would error otherwise)
• LEFT JOIN forces the lateral query to run for every row of the table network, including the ones whose VIFs is empty
• array_agg() transforms the SELECT rows into an array (GROUP BY is implicitly "all in one group")
• array_agg() returns NULL if the SELECT has no rows
• COALESCE(..., '{}') transforms NULL into an empty array
• LEFT JOIN ... ON TRUE because the subquery is returning only one row on the right per row on the left, no need to join anything.

As for the grammar of the thing, let's first pull the doc.
I will cut the first line (CREATE VIEW), and just consider the SELECT and call it select1.
The projection is usual.
then we have a FROM followed by from_item1:

  SELECT "uuid", "VIFs_t"."VIFs"
  FROM <from_item1>

• from_item1: is just a table name "${TABLE_SCHEMA}"."network"
• join_type1: LEFT JOIN
• from_item2: LEFT JOIN <from_item3> ON TRUE
• from_item3: LATERAL (<select2>) AS "VIFs_t"
• select2: SELECT [...] uuid

I don't think I need to descend into select2, there is nothing unusual about it.

I hope this helps the discussion.

I think the ON should probably be aligned with the LEFT JOIN, and from_item3 sould probably be indented together.

[nene]

OK. Thanks for the explanation. I think I sort of get it.

Currently the FROM clause get formatted roughly by this pattern:

FROM
  <from_item>
  JOIN <from_item> ON ...
  JOIN <from_item> ON ...

or when there lines get too long, the ON-part gets placed on a separate indented line:

FROM
  <from_item>
  JOIN <from_item>
    ON ...
  JOIN <from_item>
    ON ...

The same applies when the from_item is a LATERAL ( ) expression. But really the important bit is just that we're dealing with a sub-query:

FROM
  <from_item>
  JOIN LATERAL ( SELECT ... )
    ON ...
  JOIN ( SELECT ... )
    ON ...

Now, if that sub-query gets long it'll be broken to multiple lines, which currently leads to the following:

FROM
  <from_item>
  JOIN LATERAL (
    SELECT ...
  )
    ON ...
  JOIN (
    SELECT ...
  )
    ON ...

I agree that this feels somewhat awkward.

One alternative formatting could be to fully indent the whole subquery by one step:

FROM
  <from_item>
  JOIN
    LATERAL (
      SELECT ...
    )
    ON ...
  JOIN
    (
      SELECT ...
    )
    ON ...

I guess that would best represent the actual semantic structure of the code, but it's kinda verbose this way.

So we'd like to make it more compact. That could be done by introducing that double-indentation you mentioned:

FROM
  <from_item>
  JOIN LATERAL (
      SELECT ...
    )
    ON ...
  JOIN (
      SELECT ...
    )
    ON ...

Or moving the ON-parts to the same line as JOIN-s:

FROM
  <from_item>
  JOIN LATERAL (
    SELECT ...
  )
  ON ...
  JOIN (
    SELECT ...
  )
  ON ...

But we could also take another path and not try to move the ON-part to separate line, then we could get instead:

FROM
  <from_item>
  JOIN LATERAL (
    SELECT ...
  ) ON ...
  JOIN (
    SELECT ...
  ) ON ...

I don't really have one clear favorite. I admit that formatting of the FROM-clause in SQL is probably one of the trickiest parts of this whole formatter.

[nraynaud]

I don't axactly have a formed opinion on the subject at hand, but I generally like having the line starting by a keyword rather than a bracket, and have the code with an indentation that screams the structure.

My reason for avoiding brackets starting a line, is that I think we tend to scan code more than we really read it, and I think we scan the beginning of the line.

In this context, if the line only start with indentation and keywords or operators if we lose the plot in the scan, we just have to scan the beginning of the previous lines to orient ourselves, wheras to find an opening bracket we have to scan towards the ends of the lines, which is messy.

For the same reason, I don't like brackets alone on their line, they don't add anything the indentation doesn't already show, and they use lines and slow down scanning. So I would probably reject the closing bracket to the end of the line and if space is tight, I would wrap before the 'AS'.

here is how I think this piece of code looks OK:

CREATE OR REPLACE VIEW "${VIEW_SCHEMA}"."network" AS
  SELECT "uuid", "VIFs_t"."VIFs"
  FROM "${TABLE_SCHEMA}"."network"
  LEFT JOIN LATERAL (
      SELECT COALESCE(array_agg(linked."VIFs" ORDER BY linked."VIFs"), '{}') AS "VIFs"
      FROM "${TABLE_SCHEMA}"."network_VIFs" AS linked
      WHERE linked."network"=uuid ) AS "VIFs_t"
    ON TRUE;

[nene]

Thanks for the input.

I'll see what I will incorporate from this. There are definitely some changes I'd like to make to the overall indentation style produced by this tool. It's good to get some input regarding the indentation style. In large part the current style has been an evolution from the style produced by SQL Formatter, where however it was largely the result of the limitations of that tool itself. With this one, I now have much more capable framework for implementing the formatting.

I should note though that this awkward-looking indentation of the SELECT COALESCE( ... ) part is primarily the result of the line-length limit. It defaults to 80 characters limit and does its best to avoid lines longer than that.

[nene]

One thing I have been considering pretty much since the beginning is whether to indent the joins at the same level as FROM itself. I'm slowly warming up to that idea, as currently with the contents of FROM indented, it always ends up staying kinda alone:

FROM
  tbl1
  LEFT JOIN tbl2 ON ...
  LEFT JOIN tbl3 ON ...

but as the first from_item is almost always a plain table name, it ends up being quite a waste of space. But the following gets rid of that wasted space:

FROM tbl1
LEFT JOIN tbl2 ON ...
LEFT JOIN tbl3 ON ...