diff -Nru php-sql-formatter-1.2.17+dct1.1.2/composer.json php-sql-formatter-1.2.17+dct1.1.3/composer.json --- php-sql-formatter-1.2.17+dct1.1.2/composer.json 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/composer.json 2022-05-23 21:33:49.000000000 +0000 @@ -15,7 +15,7 @@ { "name": "Jeremy Dorn", "email": "jeremy@jeremydorn.com", - "homepage": "http://jeremydorn.com/" + "homepage": "https://jeremydorn.com/" } ], "autoload": { @@ -29,6 +29,9 @@ } }, "config": { + "allow-plugins": { + "bamarni/composer-bin-plugin": true + }, "sort-packages": true, "platform": { "php": "7.1.0" diff -Nru php-sql-formatter-1.2.17+dct1.1.2/debian/changelog php-sql-formatter-1.2.17+dct1.1.3/debian/changelog --- php-sql-formatter-1.2.17+dct1.1.2/debian/changelog 2021-11-08 14:42:09.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/debian/changelog 2022-06-20 06:34:14.000000000 +0000 @@ -1,3 +1,16 @@ +php-sql-formatter (1.2.17+dct1.1.3-1) unstable; urgency=medium + + [ ndm2 ] + * Add support for common table expressions + * Add support for window functions + + [ David Prévot ] + * Update gbp import-orig advices + * Revert "Drop signature check" + * Update Standards-Version to 4.6.1 + + -- David Prévot Mon, 20 Jun 2022 08:34:14 +0200 + php-sql-formatter (1.2.17+dct1.1.2-1) unstable; urgency=medium * Rename main branch to debian/latest (DEP-14) diff -Nru php-sql-formatter-1.2.17+dct1.1.2/debian/control php-sql-formatter-1.2.17+dct1.1.3/debian/control --- php-sql-formatter-1.2.17+dct1.1.2/debian/control 2021-11-08 14:42:09.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/debian/control 2022-06-20 06:31:06.000000000 +0000 @@ -8,7 +8,7 @@ phpab, phpunit, pkg-php-tools (>= 1.41~) -Standards-Version: 4.6.0 +Standards-Version: 4.6.1 Homepage: https://github.com/doctrine/sql-formatter Vcs-Git: https://salsa.debian.org/php-team/pear/php-sql-formatter.git Vcs-Browser: https://salsa.debian.org/php-team/pear/php-sql-formatter @@ -20,7 +20,7 @@ Suggests: ${phpcomposer:Debian-suggest} Replaces: ${phpcomposer:Debian-replace} Breaks: ${phpcomposer:Debian-conflict}, ${phpcomposer:Debian-replace} -Provides: php-doctrine-sql-formatter (= 1.1.2), ${phpcomposer:Debian-provide} +Provides: php-doctrine-sql-formatter (= 1.1.3), ${phpcomposer:Debian-provide} Description: ${phpcomposer:description} SqlFormatter is a lightweight PHP class to format SQL statements. It can automatically indent and add line breaks in addition to syntax diff -Nru php-sql-formatter-1.2.17+dct1.1.2/debian/README.source php-sql-formatter-1.2.17+dct1.1.3/debian/README.source --- php-sql-formatter-1.2.17+dct1.1.2/debian/README.source 2021-11-08 14:24:05.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/debian/README.source 2022-06-20 06:28:34.000000000 +0000 @@ -1,5 +1,6 @@ -Since Doctrine takeover, we are merging the (sometimes) signed upstream -tags as upstream, instead of using the tarball that doesn’t ship tests. +Since Doctrine takeover, we are merging the signed upstream tags as +upstream, instead of using the tarball that doesn’t ship tests. -# Example to import upstream 1.1.2 version -gbp import-orig --uscan --upstream-vcs-tag=1.1.2 +# Example to import upstream 1.1.4 version +version=1.1.4 +gbp import-orig --uscan --upstream-vcs-tag=$version -u1.2.17+dct$version diff -Nru php-sql-formatter-1.2.17+dct1.1.2/debian/watch php-sql-formatter-1.2.17+dct1.1.3/debian/watch --- php-sql-formatter-1.2.17+dct1.1.2/debian/watch 2021-11-08 14:42:09.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/debian/watch 2022-06-20 06:28:34.000000000 +0000 @@ -1,6 +1,6 @@ version=4 options=uversionmangle=s/-?([^\d.])/~$1/i;tr/A-Z/a-z/,\ -mode=git,gitmode=full,gitexport=all,\ +mode=git,gitmode=full,gitexport=all,pgpmode=gittag,\ dversionmangle=s/^1.2.17\+dct// \ https://github.com/doctrine/sql-formatter \ refs/tags/(\d.+) diff -Nru php-sql-formatter-1.2.17+dct1.1.2/.github/workflows/release-on-milestone-closed.yml php-sql-formatter-1.2.17+dct1.1.3/.github/workflows/release-on-milestone-closed.yml --- php-sql-formatter-1.2.17+dct1.1.2/.github/workflows/release-on-milestone-closed.yml 1970-01-01 00:00:00.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/.github/workflows/release-on-milestone-closed.yml 2022-05-23 21:33:49.000000000 +0000 @@ -0,0 +1,16 @@ +name: "Automatic Releases" + +on: + milestone: + types: + - "closed" + +jobs: + release: + name: "Git tag, release & create merge-up PR" + uses: "doctrine/.github/.github/workflows/release-on-milestone-closed.yml@1.4.1" + secrets: + GIT_AUTHOR_EMAIL: ${{ secrets.GIT_AUTHOR_EMAIL }} + GIT_AUTHOR_NAME: ${{ secrets.GIT_AUTHOR_NAME }} + ORGANIZATION_ADMIN_TOKEN: ${{ secrets.ORGANIZATION_ADMIN_TOKEN }} + SIGNING_SECRET_KEY: ${{ secrets.SIGNING_SECRET_KEY }} diff -Nru php-sql-formatter-1.2.17+dct1.1.2/src/Tokenizer.php php-sql-formatter-1.2.17+dct1.1.3/src/Tokenizer.php --- php-sql-formatter-1.2.17+dct1.1.2/src/Tokenizer.php 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/src/Tokenizer.php 2022-05-23 21:33:49.000000000 +0000 @@ -70,6 +70,7 @@ 'CONVERT', 'CREATE', 'CROSS', + 'CURRENT ROW', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', @@ -108,11 +109,13 @@ 'FAST', 'FIELDS', 'FILE', + 'FILTER', 'FIRST', 'FIXED', 'FLUSH', 'FOR', 'FORCE', + 'FOLLOWING', 'FOREIGN', 'FULL', 'FULLTEXT', @@ -120,7 +123,8 @@ 'GLOBAL', 'GRANT', 'GRANTS', - 'GROUP_CONCAT', + 'GROUP', + 'GROUPS', 'HEAP', 'HIGH_PRIORITY', 'HOSTS', @@ -180,6 +184,7 @@ 'MYISAM', 'NAMES', 'NATURAL', + 'NO OTHERS', 'NOT', 'NOW()', 'NULL', @@ -192,12 +197,14 @@ 'ON UPDATE', 'ON DELETE', 'OUTFILE', + 'OVER', 'PACK_KEYS', 'PAGE', 'PARTIAL', 'PARTITION', 'PARTITIONS', 'PASSWORD', + 'PRECEDING', 'PRIMARY', 'PRIVILEGES', 'PROCEDURE', @@ -213,6 +220,7 @@ 'READ', 'READ_ONLY', 'READ_WRITE', + 'RECURSIVE', 'REFERENCES', 'REGEXP', 'RELOAD', @@ -277,6 +285,7 @@ 'TEMPORARY', 'TERMINATED', 'THEN', + 'TIES', 'TO', 'TRAILING', 'TRANSACTIONAL', @@ -284,6 +293,7 @@ 'TRUNCATE', 'TYPE', 'TYPES', + 'UNBOUNDED', 'UNCOMMITTED', 'UNIQUE', 'UNLOCK', @@ -307,6 +317,7 @@ * @var string[] */ private $reservedToplevel = [ + 'WITH', 'SELECT', 'FROM', 'WHERE', @@ -327,6 +338,11 @@ 'UNION', 'EXCEPT', 'INTERSECT', + 'PARTITION BY', + 'ROWS', + 'RANGE', + 'GROUPS', + 'WINDOW', ]; /** @var string[] */ @@ -341,6 +357,7 @@ 'XOR', 'OR', 'AND', + 'EXCLUDE', ]; /** @var string[] */ @@ -351,6 +368,7 @@ 'ADDTIME', 'AES_DECRYPT', 'AES_ENCRYPT', + 'APPROX_COUNT_DISTINCT', 'AREA', 'ASBINARY', 'ASCII', @@ -380,6 +398,7 @@ 'CHARACTER_LENGTH', 'CHARSET', 'CHAR_LENGTH', + 'CHECKSUM_AGG', 'COALESCE', 'COERCIBILITY', 'COLLATION', @@ -395,8 +414,10 @@ 'COS', 'COT', 'COUNT', + 'COUNT_BIG', 'CRC32', 'CROSSES', + 'CUME_DIST', 'CURDATE', 'CURRENT_DATE', 'CURRENT_TIME', @@ -418,6 +439,7 @@ 'DECODE', 'DEFAULT', 'DEGREES', + 'DENSE_RANK', 'DES_DECRYPT', 'DES_ENCRYPT', 'DIFFERENCE', @@ -437,6 +459,7 @@ 'EXTRACTVALUE', 'FIELD', 'FIND_IN_SET', + 'FIRST_VALUE', 'FLOOR', 'FORMAT', 'FOUND_ROWS', @@ -457,6 +480,8 @@ 'GET_LOCK', 'GLENGTH', 'GREATEST', + 'GROUPING', + 'GROUPING_ID', 'GROUP_CONCAT', 'GROUP_UNIQUE_USERS', 'HEX', @@ -478,9 +503,12 @@ 'ISSIMPLE', 'IS_FREE_LOCK', 'IS_USED_LOCK', + 'LAG', 'LAST_DAY', 'LAST_INSERT_ID', + 'LAST_VALUE', 'LCASE', + 'LEAD', 'LEAST', 'LEFT', 'LENGTH', @@ -489,6 +517,7 @@ 'LINESTRING', 'LINESTRINGFROMTEXT', 'LINESTRINGFROMWKB', + 'LISTAGG', 'LN', 'LOAD_FILE', 'LOCALTIME', @@ -536,6 +565,8 @@ 'MULTIPOLYGONFROMTEXT', 'MULTIPOLYGONFROMWKB', 'NAME_CONST', + 'NTH_VALUE', + 'NTILE', 'NULLIF', 'NUMGEOMETRIES', 'NUMINTERIORRINGS', @@ -546,6 +577,9 @@ 'ORD', 'OVERLAPS', 'PASSWORD', + 'PERCENT_RANK', + 'PERCENTILE_CONT', + 'PERCENTILE_DISC', 'PERIOD_ADD', 'PERIOD_DIFF', 'PI', @@ -566,6 +600,7 @@ 'QUOTE', 'RADIANS', 'RAND', + 'RANK', 'RELATED', 'RELEASE_LOCK', 'REPEAT', @@ -574,6 +609,7 @@ 'RIGHT', 'ROUND', 'ROW_COUNT', + 'ROW_NUMBER', 'RPAD', 'RTRIM', 'SCHEMA', @@ -591,9 +627,12 @@ 'SRID', 'STARTPOINT', 'STD', + 'STDEV', + 'STDEVP', 'STDDEV', 'STDDEV_POP', 'STDDEV_SAMP', + 'STRING_AGG', 'STRCMP', 'STR_TO_DATE', 'SUBDATE', @@ -630,7 +669,9 @@ 'UTC_TIME', 'UTC_TIMESTAMP', 'UUID', + 'VAR', 'VARIANCE', + 'VARP', 'VAR_POP', 'VAR_SAMP', 'VERSION', diff -Nru php-sql-formatter-1.2.17+dct1.1.2/tests/clihighlight.html php-sql-formatter-1.2.17+dct1.1.3/tests/clihighlight.html --- php-sql-formatter-1.2.17+dct1.1.2/tests/clihighlight.html 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/tests/clihighlight.html 2022-05-23 21:33:49.000000000 +0000 @@ -824,3 +824,154 @@ FROM b LEFT OUTER JOIN c on (d = f); +--- +WITH + cte AS ( + SELECT + a, + b + FROM + table + ), + RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( + SELECT + 1, + 0, + 1 + UNION ALL + SELECT + n + 1, + next_fib_n, + fib_n + next_fib_n + FROM + fibonacci + WHERE + n < 10 + ) +SELECT + * +FROM + fibonacci; +--- +WITH + cte1 AS ( + SELECT + a, + b + FROM + table1 + ), + cte2 AS ( + SELECT + c, + d + FROM + table2 + ) +SELECT + b, + d +FROM + cte1 + JOIN cte2 +WHERE + cte1.a = cte2.c; +--- +SELECT + a, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE NO OTHERS + ) AS no_others, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE CURRENT ROW + ) AS current_row, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE GROUP + ) AS grp, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE TIES + ) AS tie, + GROUP_CONCAT(b, '.') FILTER ( + WHERE + c != 'two' + ) OVER ( + ORDER BY + a + ) AS filtered, + CONVERT( + VARCHAR(20), + AVG(SalesYTD) OVER ( + PARTITION BY + TerritoryID + ORDER BY + DATEPART(yy, ModifiedDate) + ), + 1 + ) AS MovingAvg, + AVG(starting_salary) OVER w2 AVG, + MIN(starting_salary) OVER w2 MIN_STARTING_SALARY, + MAX(starting_salary) OVER ( + w1 + ORDER BY + hire_date + ), + LISTAGG(arg, ',') OVER ( + PARTITION BY + part + ORDER BY + ord + ROWS + BETWEEN 1 PRECEDING + AND 1 FOLLOWING + ) AS LISTAGG_ROWS, + LISTAGG(arg, ',') OVER ( + PARTITION BY + part + ORDER BY + ord + RANGE + BETWEEN 1 PRECEDING + AND 1 FOLLOWING + ) AS LISTAGG_RANGE, + MIN(Revenue) OVER ( + PARTITION BY + DepartmentID + ORDER BY + RevenueYear + ROWS + BETWEEN CURRENT ROW + AND UNBOUNDED FOLLOWING + ) AS MinRevenueBeyond +FROM + t1 +WINDOW + w1 AS ( + PARTITION BY + department, + division + ), + w2 AS ( + w1 + ORDER BY + hire_date + ); diff -Nru php-sql-formatter-1.2.17+dct1.1.2/tests/compress.html php-sql-formatter-1.2.17+dct1.1.3/tests/compress.html --- php-sql-formatter-1.2.17+dct1.1.2/tests/compress.html 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/tests/compress.html 2022-05-23 21:33:49.000000000 +0000 @@ -85,3 +85,9 @@ SELECT [sqlserver] FROM [escap[e]]d style]; --- SELECT a FROM b LEFT OUTER JOIN c on (d=f); +--- +WITH cte AS (SELECT a, b FROM table), RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( SELECT 1, 0, 1 UNION ALL SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10 ) SELECT * FROM fibonacci; +--- +WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c; +--- +SELECT a, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie, GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered, CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg, AVG(starting_salary) OVER w2 AVG, MIN(starting_salary) OVER w2 MIN_STARTING_SALARY, MAX(starting_salary) OVER (w1 ORDER BY hire_date), LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS, LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE, MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond FROM t1 WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date); diff -Nru php-sql-formatter-1.2.17+dct1.1.2/tests/format-highlight.html php-sql-formatter-1.2.17+dct1.1.3/tests/format-highlight.html --- php-sql-formatter-1.2.17+dct1.1.2/tests/format-highlight.html 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/tests/format-highlight.html 2022-05-23 21:33:49.000000000 +0000 @@ -824,3 +824,154 @@ FROM b LEFT OUTER JOIN c on (d = f); +--- +
WITH
+  cte AS (
+    SELECT
+      a,
+      b
+    FROM
+      table
+  ),
+  RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
+    SELECT
+      1,
+      0,
+      1
+    UNION ALL
+    SELECT
+      n + 1,
+      next_fib_n,
+      fib_n + next_fib_n
+    FROM
+      fibonacci
+    WHERE
+      n < 10
+  )
+SELECT
+  *
+FROM
+  fibonacci;
+--- +
WITH
+  cte1 AS (
+    SELECT
+      a,
+      b
+    FROM
+      table1
+  ),
+  cte2 AS (
+    SELECT
+      c,
+      d
+    FROM
+      table2
+  )
+SELECT
+  b,
+  d
+FROM
+  cte1
+  JOIN cte2
+WHERE
+  cte1.a = cte2.c;
+--- +
SELECT
+  a,
+  GROUP_CONCAT(b, '.') OVER (
+    ORDER BY
+      c
+    GROUPS
+      BETWEEN UNBOUNDED PRECEDING
+      AND CURRENT ROW
+      EXCLUDE NO OTHERS
+  ) AS no_others,
+  GROUP_CONCAT(b, '.') OVER (
+    ORDER BY
+      c
+    GROUPS
+      BETWEEN UNBOUNDED PRECEDING
+      AND CURRENT ROW
+      EXCLUDE CURRENT ROW
+  ) AS current_row,
+  GROUP_CONCAT(b, '.') OVER (
+    ORDER BY
+      c
+    GROUPS
+      BETWEEN UNBOUNDED PRECEDING
+      AND CURRENT ROW
+      EXCLUDE GROUP
+  ) AS grp,
+  GROUP_CONCAT(b, '.') OVER (
+    ORDER BY
+      c
+    GROUPS
+      BETWEEN UNBOUNDED PRECEDING
+      AND CURRENT ROW
+      EXCLUDE TIES
+  ) AS tie,
+  GROUP_CONCAT(b, '.') FILTER (
+    WHERE
+      c != 'two'
+  ) OVER (
+    ORDER BY
+      a
+  ) AS filtered,
+  CONVERT(
+    VARCHAR(20),
+    AVG(SalesYTD) OVER (
+      PARTITION BY
+        TerritoryID
+      ORDER BY
+        DATEPART(yy, ModifiedDate)
+    ),
+    1
+  ) AS MovingAvg,
+  AVG(starting_salary) OVER w2 AVG,
+  MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
+  MAX(starting_salary) OVER (
+    w1
+    ORDER BY
+      hire_date
+  ),
+  LISTAGG(arg, ',') OVER (
+    PARTITION BY
+      part
+    ORDER BY
+      ord
+    ROWS
+      BETWEEN 1 PRECEDING
+      AND 1 FOLLOWING
+  ) AS LISTAGG_ROWS,
+  LISTAGG(arg, ',') OVER (
+    PARTITION BY
+      part
+    ORDER BY
+      ord
+    RANGE
+      BETWEEN 1 PRECEDING
+      AND 1 FOLLOWING
+  ) AS LISTAGG_RANGE,
+  MIN(Revenue) OVER (
+    PARTITION BY
+      DepartmentID
+    ORDER BY
+      RevenueYear
+    ROWS
+      BETWEEN CURRENT ROW
+      AND UNBOUNDED FOLLOWING
+  ) AS MinRevenueBeyond
+FROM
+  t1
+WINDOW
+  w1 AS (
+    PARTITION BY
+      department,
+      division
+  ),
+  w2 AS (
+    w1
+    ORDER BY
+      hire_date
+  );
diff -Nru php-sql-formatter-1.2.17+dct1.1.2/tests/format.html php-sql-formatter-1.2.17+dct1.1.3/tests/format.html --- php-sql-formatter-1.2.17+dct1.1.2/tests/format.html 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/tests/format.html 2022-05-23 21:33:49.000000000 +0000 @@ -822,3 +822,154 @@ FROM b LEFT OUTER JOIN c on (d = f); +--- +WITH + cte AS ( + SELECT + a, + b + FROM + table + ), + RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( + SELECT + 1, + 0, + 1 + UNION ALL + SELECT + n + 1, + next_fib_n, + fib_n + next_fib_n + FROM + fibonacci + WHERE + n < 10 + ) +SELECT + * +FROM + fibonacci; +--- +WITH + cte1 AS ( + SELECT + a, + b + FROM + table1 + ), + cte2 AS ( + SELECT + c, + d + FROM + table2 + ) +SELECT + b, + d +FROM + cte1 + JOIN cte2 +WHERE + cte1.a = cte2.c; +--- +SELECT + a, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE NO OTHERS + ) AS no_others, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE CURRENT ROW + ) AS current_row, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE GROUP + ) AS grp, + GROUP_CONCAT(b, '.') OVER ( + ORDER BY + c + GROUPS + BETWEEN UNBOUNDED PRECEDING + AND CURRENT ROW + EXCLUDE TIES + ) AS tie, + GROUP_CONCAT(b, '.') FILTER ( + WHERE + c != 'two' + ) OVER ( + ORDER BY + a + ) AS filtered, + CONVERT( + VARCHAR(20), + AVG(SalesYTD) OVER ( + PARTITION BY + TerritoryID + ORDER BY + DATEPART(yy, ModifiedDate) + ), + 1 + ) AS MovingAvg, + AVG(starting_salary) OVER w2 AVG, + MIN(starting_salary) OVER w2 MIN_STARTING_SALARY, + MAX(starting_salary) OVER ( + w1 + ORDER BY + hire_date + ), + LISTAGG(arg, ',') OVER ( + PARTITION BY + part + ORDER BY + ord + ROWS + BETWEEN 1 PRECEDING + AND 1 FOLLOWING + ) AS LISTAGG_ROWS, + LISTAGG(arg, ',') OVER ( + PARTITION BY + part + ORDER BY + ord + RANGE + BETWEEN 1 PRECEDING + AND 1 FOLLOWING + ) AS LISTAGG_RANGE, + MIN(Revenue) OVER ( + PARTITION BY + DepartmentID + ORDER BY + RevenueYear + ROWS + BETWEEN CURRENT ROW + AND UNBOUNDED FOLLOWING + ) AS MinRevenueBeyond +FROM + t1 +WINDOW + w1 AS ( + PARTITION BY + department, + division + ), + w2 AS ( + w1 + ORDER BY + hire_date + ); diff -Nru php-sql-formatter-1.2.17+dct1.1.2/tests/highlight.html php-sql-formatter-1.2.17+dct1.1.3/tests/highlight.html --- php-sql-formatter-1.2.17+dct1.1.2/tests/highlight.html 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/tests/highlight.html 2022-05-23 21:33:49.000000000 +0000 @@ -272,3 +272,33 @@ 
SELECT a FROM b LEFT
 OUTER
 JOIN c on (d=f);
+--- +
WITH cte AS (SELECT a, b FROM table),
+RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
+  SELECT 1, 0, 1
+  UNION ALL
+  SELECT n + 1, next_fib_n, fib_n + next_fib_n
+  FROM fibonacci WHERE n < 10
+)
+SELECT * FROM fibonacci;
+--- +
WITH cte1 AS (SELECT a, b FROM table1),
+cte2 AS (SELECT c, d FROM table2)
+SELECT b, d FROM cte1 JOIN cte2
+WHERE cte1.a = cte2.c;
+--- +
SELECT a,
+    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others,
+    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row,
+    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp,
+    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie,
+    GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered,
+    CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
+    AVG(starting_salary) OVER w2 AVG,
+    MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
+    MAX(starting_salary) OVER (w1 ORDER BY hire_date),
+    LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS,
+    LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE,
+    MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond
+FROM t1
+WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date);
diff -Nru php-sql-formatter-1.2.17+dct1.1.2/tests/sql.sql php-sql-formatter-1.2.17+dct1.1.3/tests/sql.sql --- php-sql-formatter-1.2.17+dct1.1.2/tests/sql.sql 2021-11-05 11:11:14.000000000 +0000 +++ php-sql-formatter-1.2.17+dct1.1.3/tests/sql.sql 2022-05-23 21:33:49.000000000 +0000 @@ -272,3 +272,33 @@ SELECT a FROM b LEFT OUTER JOIN c on (d=f); +--- +WITH cte AS (SELECT a, b FROM table), +RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( + SELECT 1, 0, 1 + UNION ALL + SELECT n + 1, next_fib_n, fib_n + next_fib_n + FROM fibonacci WHERE n < 10 +) +SELECT * FROM fibonacci; +--- +WITH cte1 AS (SELECT a, b FROM table1), +cte2 AS (SELECT c, d FROM table2) +SELECT b, d FROM cte1 JOIN cte2 +WHERE cte1.a = cte2.c; +--- +SELECT a, + GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others, + GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row, + GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp, + GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie, + GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered, + CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg, + AVG(starting_salary) OVER w2 AVG, + MIN(starting_salary) OVER w2 MIN_STARTING_SALARY, + MAX(starting_salary) OVER (w1 ORDER BY hire_date), + LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS, + LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE, + MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond +FROM t1 +WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date);