Options

Bug in Build - Computed Column with DEFAULT Parameter

Matt ConnollyMatt Connolly Posts: 4 New member
edited March 11, 2015 12:20PM in SQL CI 2
Hi,

I think I've spotted a bug in the Build process: I have a table with a computed column referencing a scalar function using a DEFAULT parameter:

*****************************************

CREATE FUNCTION [dbo].[DefaultParamTest](@I INT, @X INT = NULL)
RETURNS INT
AS BEGIN
SET @X = ISNULL(@X,1);
RETURN @I + @X;
END

GO

-- Columns

CREATE TABLE [dbo].[DefaultParamTestTable]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[ParamTest] AS ([dbo].[DefaultParamTest]([Id],DEFAULT))
) ON [PRIMARY]
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[DefaultParamTestTable] ADD CONSTRAINT [PK_DefaultParamTestTable] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
GO

*****************************************

SQL Source Control has difficulty recognizing the DEFAULT keyword, but does correctly save the file to the repository (however it always flags it as a change, and shows the "latest source control version" as having the DEFAULT parameter missing entirely):

*****************************************

-- Columns

CREATE TABLE [dbo].[DefaultParamTestTable]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[ParamTest] AS ([dbo].[DefaultParamTest]([Id],))
) ON [PRIMARY]
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[DefaultParamTestTable] ADD CONSTRAINT [PK_DefaultParamTestTable] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
GO

*****************************************

In SQL CI, the build step fails with the following error:

failed: Incorrect syntax near ')'. Error executing the following SQL:
CREATE TABLE [dbo].[DefaultParamTestTable] ( [Id] [int] NOT NULL IDENTITY(1, 1),
[ParamTest] AS ([dbo].[DefaultParamTest]([Id],)) )

Note that the DEFAULT parameter is entirely missing, as in the "latest source control version" in SQL Source Control - but the file in the repository does in fact have the DEFAULT keyword in place.

If I change the computed column to [dbo].[DefaultParamTest]([Id],NULL), instead of [dbo].[DefaultParamTest]([Id],DEFAULT), everything works as expected, both in SQL Source Control and SQL CI.

Has anyone else seen this issue?

Thanks,

Matt

Comments

  • Options
    Hi Matt,

    Thanks for reporting this and for providing the scripts. I have been able to replicate the behaviour and I have filed a new bug report, with reference SC-7809. I will escalate this to the developers and let you know as soon as I hear any new information.

    Many thanks,

    Rob
    Software Engineer
    Redgate Software
Sign In or Register to comment.