BUG: "Format SQL" introduces syntax errors

HugoKornelis

Hi all,

The code below, though ill-formatted, is correct and works without error in SQL Server 2022:

SELECT frsx.ProductKey,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM (CAST (frsx.ShipDateKey AS bigint)) OVER w1 ,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAX (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MIN (frsx.ShipDateKey) OVER w1 ,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT (*) OVER w1 &nbsp;<br>FROM&nbsp;&nbsp; dbo.FactResellerSales AS frsx<br>WINDOW w1 AS&nbsp; (ORDER BY frsx.DueDateKey<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);

When I want to use SQL Prompt to reformat the code (Ctrl-K, Ctrl-Y), it adds an extra AS keyword in some places where it should not exist, and then gives up with an error message:

The code now looks like this:

&nbsp;&nbsp;&nbsp; SELECT frsx.ProductKey,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; SUM (CAST (frsx.ShipDateKey AS bigint)) OVER w1 AS ,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; MAX (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; MIN (frsx.ShipDateKey) OVER w1 AS ,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; COUNT (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; COUNT (*) OVER w1 AS&nbsp; <br>&nbsp;&nbsp; &nbsp;FROM&nbsp;&nbsp; dbo.FactResellerSales AS frsx<br>&nbsp;&nbsp; &nbsp;WINDOW w1 AS&nbsp; (ORDER BY frsx.DueDateKey<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);

SQL Server will (rightly) throw an error when I try to execute this "reformatted" code.

Jon_Kirkwood

Hi @HugoKornelis

 

Thank you for reaching out on the Redgate forums regarding this potential bug in SQL Prompt.

 

I believe I have replicated the behaviour using SQL Server 2022 using the OVER clause. 

Initial code executes as anticipated, then when using the formatting it introduces an AS clause causing syntax issues.

 Before formatting:

 After formatting:

I've documented this and forwarded through to our development team. This forum post will be updated with any responses and/or fixes made available in a future release of SQL Prompt to target this concern.

 

Jon_Kirkwood

Hi there,

Our development team have advised a fix for this has been included in the recent release of SQL Prompt v10.14.21

Release notes:

10.14.21.9884 - Released on 14 June 2024
Features
Added support for Synapse Serverless SQL Pools.
Fixes

• Fixed Prompt+ in SSMS 18.
• Fixed few issues which caused data types to be missing/invalid for aliased columns.
• Fixed Apply Column Alias Style with WINDOW OVER clause.
• Fixed invalid data type of aliased columns when using arithmetic operators.

This version can be accessed directly here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.21.9884.exe

sympathizetrack

Jon_Kirkwood said:

Hi there,

Our development team have advised a fix for this has been included in the recent release of SQL Prompt v10.14.21

Release notes:

10.14.21.9884 - Released on 14 June 2024
Features
Added support for Synapse Serverless SQL Pools.
Fixes

• Fixed Prompt+ in SSMS 18.
• Fixed few issues which caused data types to be missing/invalid for aliased columns.
• Fixed Apply Column Alias Style with WINDOW OVER clause.
• Fixed invalid data type of aliased columns when using arithmetic operators.

This version can be accessed directly here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.21.9884.exe geometry dash lite

I have downloaded it successfully. Many thanks!

johnsamir

Legend Financial offers a wide range of services designed to meet diverse financial needs. With a strong commitment to client education and a robust online presence, Legend Financial has established itself as a trusted partner in the financial industry.