MSSQL: The ORDER BY clause is not allowed in a UNION statement.

[homestar9]

There may be a problem with a validation check in BaseGrammar.cfc when using MSSQL server. If you want to compose a query that utilizes a UNION and nested ORDER BY clauses, QB will throw the error "The ORDER BY clause is not allowed in a UNION statement.". I can't speak for other DB engines, but in MSSQL you can have ORDER BY clauses in a union.

For example, consider the following SQL:

SELECT TOP 5 *
FROM   (
        (
                SELECT TOP (5) 'Page' AS typeName, id, name, modifiedDate
                FROM   page
                ORDER  BY modifieddate DESC
        )
        UNION ALL
        (
                SELECT TOP (5) 'Document' AS typeName, id, name, modifiedDate
                FROM   document
                ORDER  BY modifieddate DESC
        )
) t
ORDER  BY modifiedDate DESC 

The above executes as expected in MSSQL server and returns the top 5 rows based on two subqueries ordered by the modifiedDate column.

If you attempt to run something similar in QB like this:

var query = qb.select( "*" )
    .fromSub( 't', function( q ) {

        // page
        q.select( "id, name, modifiedDate" )
            .selectRaw( "'Page' as TypeName" )
            .from( "page" )
            .orderBy( "modifiedDate", "DESC" )
            .limit( 5 )
        ;

        // document
        q.unionAll( function( q ) {

            q.select( "id, name, modifiedDate" )
                .selectRaw( "'Document' as TypeName" )
                .from( "document" )
                .orderBy( "modifiedDate", "DESC" )
                .limit( 5 )
            ;

        } );

    } )
    .limit( 5 )
    .orderBy( "modifiedDate", "DESC" );

You will get the following error triggered from BaseGrammar:

The ORDER BY clause is not allowed in a UNION statement.
A QueryBuilder instance used in a UNION statement is cannot have any ORDER BY clause, as this is not allowed by SQL. Only the outermost query is allowed to specify an ORDER BY clause which will be used on the unioned queries.

If this behavior isn't allowed in some SQL engines, perhaps we can fix this by overriding compileUnions() in the MSSQL Grammar file, or possibly adding a new setting like allowOrderByWithUnion which could be set to true/false.

I experimented with removing the validation check in BaseGrammar to see what would happen, and this is the resulting SQL that gets generated by QB for the above code:

SELECT TOP (5) * 
FROM    (
    SELECT TOP (5)[id], [name], [modifiedDate], 'Page' AS typename
    FROM   [page]
    // Missing ORDER BY
    UNION ALL
    SELECT TOP (5) [id], [name], [modifiedDate], 'Document' AS typename
    FROM     [document]
    ORDER BY [modifiedDate] DESC
    order BY [modifiedDate] DESC  // redundant ORDER BY
) AS [t]
ORDER BY [modifiedDate] DESC

The above is very close to working SQL but has a redundant order by statement and is missing the first ORDER BY statement in the top subquery. What do you think? Do you think this would be an easy fix if we made a few tweaks to SqlServerGrammar.cfc?