Fails to format tsql code scenario

[stevewgr]

Search before asking

• I searched the issues and found no similar issues.

What Happened

When I run sqlfluff format file.sql, it fails to format it and I have to do it manually. I did spend enough time to understand where it fails, but I thought I should create a ticket so you have another scenario to look at. Here is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USERDATA](
	[strUserId] [char](21) NOT NULL,
	[Nation] [tinyint] NOT NULL,
	[Race] [tinyint] NOT NULL,
	[Class] [smallint] NOT NULL,
	[HairColor] [tinyint] NOT NULL,
	[Rank] [tinyint] NOT NULL,
	[Title] [tinyint] NOT NULL,
	[Level] [tinyint] NOT NULL,
	[Exp] [int] NOT NULL,
	[Loyalty] [int] NOT NULL,
	[Face] [tinyint] NOT NULL,
	[City] [tinyint] NOT NULL,
	[Knights] [smallint] NOT NULL,
	[Fame] [tinyint] NOT NULL,
	[Hp] [smallint] NOT NULL,
	[Mp] [smallint] NOT NULL,
	[Sp] [smallint] NOT NULL,
	[Strong] [tinyint] NOT NULL,
	[Sta] [tinyint] NOT NULL,
	[Dex] [tinyint] NOT NULL,
	[Intel] [tinyint] NOT NULL,
	[Cha] [tinyint] NOT NULL,
	[Authority] [tinyint] NOT NULL,
	[Points] [tinyint] NOT NULL,
	[Gold] [int] NOT NULL,
	[Zone] [tinyint] NOT NULL,
	[Bind] [smallint] NULL,
	[PX] [int] NOT NULL,
	[PZ] [int] NOT NULL,
	[PY] [int] NOT NULL,
	[dwTime] [int] NOT NULL,
	[strSkill] [varchar](10) NULL,
	[strItem] [binary](400) NULL,
	[strSerial] [binary](400) NULL,
 CONSTRAINT [PK_USERDATA] PRIMARY KEY CLUSTERED 
(
	[strUserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [PK_Knights] ON [dbo].[USERDATA]
(
	[Knights] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Nation]  DEFAULT ((0)) FOR [Nation]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Race]  DEFAULT ((1)) FOR [Race]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Class]  DEFAULT ((0)) FOR [Class]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Gender]  DEFAULT ((0)) FOR [HairColor]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Rank]  DEFAULT ((0)) FOR [Rank]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Title]  DEFAULT ((0)) FOR [Title]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Level]  DEFAULT ((1)) FOR [Level]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Exp]  DEFAULT ((0)) FOR [Exp]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Loyalty]  DEFAULT ((0)) FOR [Loyalty]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Religion]  DEFAULT ((0)) FOR [Face]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_City]  DEFAULT ((0)) FOR [City]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Knights]  DEFAULT ((0)) FOR [Knights]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Fame]  DEFAULT ((0)) FOR [Fame]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Hp]  DEFAULT ((100)) FOR [Hp]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Mp]  DEFAULT ((100)) FOR [Mp]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Sp]  DEFAULT ((100)) FOR [Sp]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Str]  DEFAULT ((0)) FOR [Strong]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Sta]  DEFAULT ((0)) FOR [Sta]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Dex]  DEFAULT ((0)) FOR [Dex]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Intel]  DEFAULT ((0)) FOR [Intel]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Cha]  DEFAULT ((0)) FOR [Cha]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Authority]  DEFAULT ((1)) FOR [Authority]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Points]  DEFAULT ((0)) FOR [Points]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Gold]  DEFAULT ((0)) FOR [Gold]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_Zone]  DEFAULT ((1)) FOR [Zone]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_PX]  DEFAULT ((268100)) FOR [PX]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_PZ]  DEFAULT ((131000)) FOR [PZ]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_PY]  DEFAULT ((0)) FOR [PY]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_dwTime]  DEFAULT ((0)) FOR [dwTime]
GO
ALTER TABLE [dbo].[USERDATA] ADD  CONSTRAINT [DF_USERDATA_strSkill]  DEFAULT (0x00) FOR [strSkill]
GO

Also this breaks:

-- Note that normally we would need to recreate the table if we want to perserve the order of columns,
-- as there is no way of sorting them. Worth noting that the sql engine doesn't care much about the order when
-- explictly querying columns from tables. However since the columns we modify are the last ones, we can
-- take a shortcut by droping and then adding them in the right order.

-- No longer need default value constraints for it, since it is fixed length binary column.
ALTER TABLE USERDATA DROP CONSTRAINT DF_USERDATA_strSkill;
GO

ALTER TABLE USERDATA ADD
  bySkill BINARY (10) NULL,
  byItem BINARY (400) NULL,
  bySerial BINARY (400) NULL;
GO

-- Implicit conversion from data type varchar to binary is not allowed.
UPDATE USERDATA SET bySkill = CONVERT(BINARY, strSkill);

-- Note that there is no need to convert these two below to binary, since they were already binary
-- and someone forgot or didn't bother to rename them.
UPDATE USERDATA SET byItem = strItem;
UPDATE USERDATA SET bySerial = strSerial;
GO

-- Done reworking these columns? let's get ride of them.
ALTER TABLE USERDATA DROP COLUMN strSkill, strItem, strSerial;
GO

Namely the part with dropping multiple columns:

ALTER TABLE USERDATA DROP COLUMN strSkill, strItem, strSerial;

Refer to this: https://stackoverflow.com/a/18564014

Expected Behaviour

It should format it without manual intervention.

Observed Behaviour

Had to format manually.

How to reproduce

Create a file out of the code above and run the format with default configs.

Dialect

tsql

Version

2.3.5 installed via pip

Configuration

default

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct