What are the challenges you face when working across database platforms? Take the survey
Options

Aliased function result breaks wildcard expansion

a.higginsa.higgins Posts: 90 Bronze 2
edited September 30, 2016 7:14AM in SQL Prompt
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"

Comments

Sign In or Register to comment.