[FORMATTING] Window functions and expressionWidth

[Brandon-Schur]

Setting an arbitrarily large expression width doesn't lead to window functions staying on a single line

Goal is similar to #168 which I believe is now "expressionWidth"

Input data

Which SQL and options did you provide as input?

-- Simplified example
SELECT
  PERCENTILE_CONT(1) within GROUP (ORDER BY blocks_read) AS blocks_read_p100,
  PERCENTILE_CONT(0.99) within GROUP (ORDER BY blocks_read) AS blocks_read_p99,
  PERCENTILE_CONT(0.95) within GROUP (ORDER BY blocks_read) AS blocks_read_p95,
FROM
  query_metrics

Expected Output

SELECT
  PERCENTILE_CONT(1) within GROUP (ORDER BY blocks_read) AS blocks_read_p100,
  PERCENTILE_CONT(0.99) within GROUP (ORDER BY blocks_read) AS blocks_read_p99,
  PERCENTILE_CONT(0.95) within GROUP (ORDER BY blocks_read) AS blocks_read_p95,
FROM
  query_metrics

Actual Output

SELECT
  PERCENTILE_CONT(1) within GROUP (
    ORDER BY
      blocks_read
  ) AS blocks_read_p100,
  PERCENTILE_CONT(0.99) within GROUP (
    ORDER BY
      blocks_read
  ) AS blocks_read_p99,
  PERCENTILE_CONT(0.95) within GROUP (
    ORDER BY
      blocks_read
  ) AS blocks_read_p95,
FROM
  query_metrics

My settings from vscode

    "SQL-Formatter-VSCode.dialect": "redshift",
    "SQL-Formatter-VSCode.tabSizeOverride": 1,
    "SQL-Formatter-VSCode.keywordCase": "upper",
    "SQL-Formatter-VSCode.expressionWidth": 400,
    "SQL-Formatter-VSCode.dataTypeCase": "upper",
    "SQL-Formatter-VSCode.denseOperators": true,
    "SQL-Formatter-VSCode.functionCase": "upper",
    "SQL-Formatter-VSCode.paramTypes": {

    }

Usage

• How are you calling / using the library? Vscode
• What SQL language(s) does this apply to? Redshift/PostgreSQL
• Which SQL Formatter version are you using? 4.1.7

[nene]

This is sort of "by design".

The expressionWidth only applies to parenthesized blocks that don't include code that triggers newlines. Like when you parenthesize (SELECT 1) it gets always broken to multiple lines because of SELECT being in it. Similarly with your example ORDER BY clause causes this to happen. That in turn happens because sql-formatter is unable to distinguish between ORDER BY in SELECT-clause and in window function call.

I should point out that version 4.1.7 is extremely old. The latest is 15.4.11. You're probably using some unmaintained VSCode plugin. See the FAQ.

Normally I would recommend trying to switch to prettier-plugin-sql-cst, which doesn't have this problem. But it doesn't support Redshift :(

[Brandon-Schur]

Thanks! I'll give that plugin a shot. I'm open to trying other SQL dialects