I've hit on a very odd behavior with SQL Prompt. As nearly as I can tell, the wildcard expansion / All non-aggregated columns options are both confused by the presence of an aliased aggregation of a function result.
Consider the following code:
;WITH myCTE AS (SELECT 1 AS x, NULL AS y, '' AS z)
SELECT *
FROM
(
SELECT
myCTE.x
,MAX(LEFT(y,1)) AS Something
FROM myCTE
GROUP BY myCTE.x
) yak
Expanding the wildcard at the top adds the 'y' column that I used in a function aggregation:
SELECT yak.x
,yak.y -- !!!!!????!!!!
,yak.Something
FROM
(
SELECT
myCTE.x
,MAX(LEFT(y,1)) AS Something
FROM myCTE
GROUP BY
) yak
Recreating the inner GROUP BY using the "All non-aggregated columns"shortcut adds both the x and y fields, even though y is only used inside of the aggregation:
SELECT *
FROM
(
SELECT
myCTE.x
,MAX(LEFT(y,1)) AS Something
FROM myCTE
GROUP BY myCTE.x
,myCTE.y -- !!!!????!!!!
) yak
Note that if I remove the "Something" alias, and the outer query, the "non-aggregated columns" shortcut works just fine:
SELECT
myCTE.x
,MAX(LEFT(y,1))
FROM myCTE
GROUP BY myCTE.x
Other notes:
This appears to only happen when I'm using a GROUP BY
This appears to only happen when the aggregation has an alias
This happened every time I tried to aggregate the result of a function, including ISNULL(), COALESCE(), LEFT(), and UPPER()
This did not happen when I tried to aggregate the result of a direct math operation (e.g., "MAX(x+y) AS Something"
Consider the following code:
;WITH myCTE AS (SELECT 1 AS x, NULL AS y, '' AS z) SELECT * FROM ( SELECT myCTE.x ,MAX(LEFT(y,1)) AS Something FROM myCTE GROUP BY myCTE.x ) yakExpanding the wildcard at the top adds the 'y' column that I used in a function aggregation:
SELECT yak.x ,yak.y -- !!!!!????!!!! ,yak.Something FROM ( SELECT myCTE.x ,MAX(LEFT(y,1)) AS Something FROM myCTE GROUP BY ) yakRecreating the inner GROUP BY using the "All non-aggregated columns"shortcut adds both the x and y fields, even though y is only used inside of the aggregation:
SELECT * FROM ( SELECT myCTE.x ,MAX(LEFT(y,1)) AS Something FROM myCTE GROUP BY myCTE.x ,myCTE.y -- !!!!????!!!! ) yakNote that if I remove the "Something" alias, and the outer query, the "non-aggregated columns" shortcut works just fine:
SELECT myCTE.x ,MAX(LEFT(y,1)) FROM myCTE GROUP BY myCTE.xOther notes:
This appears to only happen when I'm using a GROUP BY
This appears to only happen when the aggregation has an alias
This happened every time I tried to aggregate the result of a function, including ISNULL(), COALESCE(), LEFT(), and UPPER()
This did not happen when I tried to aggregate the result of a direct math operation (e.g., "MAX(x+y) AS Something"