Aliased function result breaks wildcard expansion

a.higgins

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"

a.higgins

Forgot to mention, this is on 7.3.0.564

David Priddle

Hi a.higgins,

Thanks for the great reproduction steps!

We can reproduce this so I'll take a look into this.

Best regards,

David