Uncaught error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Question

How do we configure our TFS gated build validation to catch parser validation errors?

For example, we had a stored procedure with an invalid statement that kept passing the gated TFS check-in XAML build validation and even passed SSDT publish to a SQL Server 2008R2 database for two whole years!

CREATE PROCEDURE This_Should_Fail
AS BEGIN
    DECLARE @TableVariable TABLE(
        ID INT IDENTITY(1, 1),
        Name VARCHAR(MAX)
    )
    SELECT DISTINCT TOP 1 Name
    FROM @TableVariable
    ORDER BY ID
END

When I run this statement I get the appropriate error, however when I run the full version in TFS there is no error!

Msg 145, Level 15, State 1, Procedure This_Should_Fail, Line 7 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

But the error was never encountered until recently when the definition changed to be like this, which doesn't even throw any errors when run!

CREATE PROCEDURE This_Should_Fail
AS BEGIN
    DECLARE @TableVariable TABLE(
        ID INT IDENTITY(1, 1),
        Name VARCHAR(MAX),
        ForeignKeyID INT
    )

    --create some sample data
    INSERT INTO @TableVariable(Name, ForeignKeyID)
    VALUES ('Obj1', 999), ('Obj2', 999), ('Obj3', 0), ('Obj4', 0)

    DECLARE @ForeignKeyID INT = 999--some lookup here

    SELECT DISTINCT TOP 1 Name
    FROM @TableVariable
    WHERE ForeignKeyID = @ForeignKeyID
END

And it took a whole month after that change was made before the change in the TFS branch failed to deploy anywhere. In both cases, there was no ordering by all selected columns.

This is confusing me even more because neither version in TFS (which I can't include because it's my company's IP) throws any error even when manually run in SSMS 2014!

Thanks.

When you build the database project locally, does it produce an error on that statement? — Jacob Maki, Jul 11 '17 at 14:54
It's XAML and it has been built and published locally for 2 whole years without issue! — Elaskanator, Jul 11 '17 at 16:47
The issue seems to be much more insidious since the flawed statement in the full stored procedure definition doesn't even cause any errors when declared and the new version that caused the error doesn't cause any errors even when run?!? — Elaskanator, Jul 11 '17 at 17:06

score 1 · Answer 1 · answered Jul 11 '17 at 10:24

Gated check-in is a form of continuous integration build. In TFS, it creates a shelveset containing the code that's being validated, then runs a build of that code. Only if that code builds successfully and all configured unit tests pass does the code actually get checked in.

In other words, the validation is controlled by your build/test directly not by TFS. If the error is not caught by your build or not test out by your test. Then it will effect after actually deployed to a server.

One way to do this is catching the log info of your build and then use Logging Commands and exit 1 to fail the build. More details you could refer this question: How to fail the build from a PowerShell task in TFS 2015 (for vNext Build)

Uncaught error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified

1 Answers1