MSSQL query with UNION and ORDER BY is mal-constructed

[derekm]

I have this rather large routine:

requestedOrdersForTemp := bunDB.NewSelect().
	ColumnExpr("o.orderid, o.customerid, eo.shiftnum").
	TableExpr("orders o").
	Join("LEFT JOIN tempRequestedShifts trs ON trs.orderID = o.orderid").
	Join("JOIN enrichedOrders eo ON o.orderID = eo.orderid").
	Where("(tempID = ? OR (o.filledby = ? AND o.status_ca = 'Filled (Unconfirmed)'))", params.TempID, params.TempID).
	Where("o.shiftendtime >= CAST(CAST(GETDATE() AS datetimeoffset) AT TIME ZONE 'US Eastern Standard Time' AS datetime)").
	Where("jobdatestart = ?", params.Date).
	Where("(isRejected = 0 OR (isRejected IS NULL AND o.status_ca = 'Filled (Unconfirmed)'))")
query := bunDB.NewSelect().
	With("requestedOrdersForTemp", requestedOrdersForTemp).
	ColumnExpr("o.orderid").
	ColumnExpr("o.customerid").
	TableExpr("orders o").
	Join("JOIN enrichedOrders eo ON o.orderid = eo.orderid").
	Where("jobdatestart = ?", params.Date).
	Where("o.shiftendtime >= CAST(CAST(GETDATE() AS datetimeoffset) AT TIME ZONE 'US Eastern Standard Time' AS datetime)").
	Where("o.status = 'open'").
	Where("eo.shiftnum NOT IN (SELECT shiftnum FROM requestedOrdersForTemp)").
	Union(bunDB.NewSelect().ColumnExpr("orderid, customerid").TableExpr("requestedOrdersForTemp"))
if params.TempID != 0 {
	addTempRelatedPredicates(params.TempID, query)
}
addFacilityRelatedPredicates(facilitiesByDistance, params.FacilityID, query)
addCommonPredicates(params.GetShiftsCommon, query)
query.OrderExpr("shiftstarttime")

Expected result is:

WITH
"requestedOrdersForTemp" AS (
    SELECT o.orderid, o.customerid, eo.shiftnum, o.shiftstarttime
    FROM orders o
    LEFT JOIN tempRequestedShifts trs ON trs.orderID = o.orderid 
    JOIN enrichedOrders eo ON o.orderID = eo.orderid 
    WHERE ((tempID = 4 OR (o.filledby = 4 AND o.status_ca = 'Filled (Unconfirmed)'))) 
    AND (o.shiftendtime >= CAST(CAST(GETDATE() AS datetimeoffset) AT TIME ZONE 'US Eastern Standard Time' AS datetime)) 
    AND (jobdatestart = N'2023-10-20') 
    AND ((isRejected = 0 OR (isRejected IS NULL AND o.status_ca = 'Filled (Unconfirmed)')))
)
SELECT o.orderid, o.customerid, eo.shiftnum, o.shiftstarttime
FROM orders o 
JOIN enrichedOrders eo ON o.orderid = eo.orderid 
WHERE (jobdatestart = N'2023-10-20') 
AND (o.shiftendtime >= CAST(CAST(GETDATE() AS datetimeoffset) AT TIME ZONE 'US Eastern Standard Time' AS datetime)) 
AND (o.status = 'open') 
AND (eo.shiftnum NOT IN (SELECT shiftnum FROM requestedOrdersForTemp)) 
AND (o.specialty IN (
    SELECT value FROM profile_temp CROSS APPLY STRING_SPLIT(Specialty, ',') WHERE (recordid = 4)
)) 
AND (o.nursetype IN (
    SELECT value FROM profile_temp CROSS APPLY STRING_SPLIT(Certification, ',') WHERE (recordid = 4)
))
AND (o.customerid NOT IN (
    SELECT FacilityID FROM facilities WHERE (FacilityDNR IS NOT NULL) AND (TempID = 4)
))
UNION
SELECT orderid, customerid, shiftnum, shiftstarttime FROM requestedOrdersForTemp
ORDER BY shiftstarttime

But what I get from Bun is invalid outer parens surrounding the two queries and incorrect placement of ORDER BY (should come after the two queries):

(
WITH "requestedOrdersForTemp" AS (
    SELECT o.orderid, o.customerid, eo.shiftnum 
    FROM orders o 
    LEFT JOIN tempRequestedShifts trs ON trs.orderID = o.orderid 
    JOIN enrichedOrders eo ON o.orderID = eo.orderid 
    WHERE ((tempID = 4 OR (o.filledby = 4 AND o.status_ca = 'Filled (Unconfirmed)'))) 
    AND (o.shiftendtime \u003e= CAST(CAST(GETDATE() AS datetimeoffset) AT TIME ZONE 'US Eastern Standard Time' AS datetime)) 
    AND (jobdatestart = N'2023-10-20') 
    AND ((isRejected = 0 OR (isRejected IS NULL AND o.status_ca = 'Filled (Unconfirmed)'))
)) 
SELECT o.orderid, o.customerid 
FROM orders o 
JOIN enrichedOrders eo ON o.orderid = eo.orderid 
WHERE (jobdatestart = N'2023-10-20') 
AND (o.shiftendtime \u003e= CAST(CAST(GETDATE() AS datetimeoffset) AT TIME ZONE 'US Eastern Standard Time' AS datetime)) 
AND (o.status = 'open') 
AND (eo.shiftnum NOT IN (
SELECT shiftnum FROM requestedOrdersForTemp)) 
AND (specialty IN (SELECT value FROM profile_temp CROSS APPLY STRING_SPLIT(Specialty, ',') WHERE (recordid = 4))) 
AND (nursetype IN (SELECT value FROM profile_temp CROSS APPLY STRING_SPLIT(Certification, ',') WHERE (recordid = 4))) 
AND (customerid NOT IN (SELECT FacilityID FROM facilities WHERE (FacilityDNR IS NOT NULL) 
AND (TempID = 4))) 
ORDER BY shiftstarttime
) 
UNION
(
SELECT orderid, customerid FROM requestedOrdersForTemp
)