Snowflake - aggreagate function: SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*)

What may be the reason that the following syntax works:

SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*);

Output:

Data types:

DESCRIBE RESULT LAST_QUERY_ID();
/*
name	type	kind
SUM(*)	NUMBER(30,0)	COLUMN
AVG(*)	NUMBER(36,6)	COLUMN
MIN(*)	VARCHAR(0)	COLUMN
MAX(*)	VARCHAR(0)	COLUMN
ANY_VALUE(*)	VARCHAR(0)	COLUMN
*/

Based on the query profile it is resolved as:

EXPLAIN USING TABULAR
SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*);

When providing a table/subquery * is resolved as the first column:

SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*)
FROM (VALUES (1)) sub(c);
/*
SUM(*)	AVG(*)	MIN(*)	MAX(*)	ANY_VALUE(*)
1	1	1	1	1
*/

However, it does not work if more than one column exists:

SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*)
FROM (VALUES (1,2));

> Error: too many arguments for function [SUM(VALUES.COLUMN1, VALUES.COLUMN2)]

Also:

SELECT SUM(*)
FROM (VALUES(1))
HAVING SUM(*) > 1;

> Use of * as a function argument is only allowed in the SELECT clause.

The documentation of aggregate functions does not provide any clue for * in context of SUM, MIN/MAX, etc.

I am looking for a valid use case when it may be useful.

EDIT:

Aggregate functions that accept more than single input:

SELECT LISTAGG(*) FROM (SELECT 'a', 'b'); 
--error: argument 2 to function LISTAGG needs to be constant, found'"values"."'B'"'

but:

SELECT MIN_BY(*) 
FROM (VALUES('a', 'b'), ('aa', 'a')) AS sub(c1, c2);
/*
MIN_BY(*)
aa
*/

EDIT 2:

It works not only for aggregate functions but scalar functions too:

SELECT COALESCE(*)
FROM (VALUES (NULL, 'a', 'b'));
-- 'a'

my assumption is it is a "magic artifact" from count(*) which really is a odd expression.

Well it is and it is not. But given the parser will see <inbuilt_built_function(token)><paren><star><paren> and count is an inbuilt function, as are the other functions. The * will act as template of "put all the imputs here" I assume. The should be testable with a count(distinct *) giving the same results as count(distinct a,b,c) (for a three column table)

So then the expantions of * to "all the input" is just auto magic'ed in, and then sum(a,b) fails the parameter count check of sum, which is not a parse problem, or an AST problem, but a almost run-time problem.

Test:

select 
    count(*) as c1, 
    count(distinct *) as c2, 
    count(distinct column1, column2) as c3
from values 
    (1,2), 
    (1,2), 
    (null, null);

Well that didn't prove my idea, but it does show the tokenizer is generating a start token...

More out loud thinking:

select 
    count(*) as c1, 
    count(column1) as c2, 
    count(column2) as c3, 
    count(column1, column2) as c4, 
    
    --count(distinct *) as c2, 
    --count(distinct(column1, column2)) as c2, 
    count(distinct column1, column2) as c3
from values 
    (1,2), 
    (1,2), 
    (null, null);
    select 
    count(*) as c1, 
    count(column1) as c2, 
    count(column2) as c3, 
    count(column1, column2) as c4, 
    count(distinct column1, column2) as c3
from values 
    (1,2), 
    (1,2), 
    (null, null);

Ah, yes, the * means a row exists where-as the explicit names, trigger the not null values, so it's a expansions of the parameters. But a whole TYPE class of parameter, "the row" that count must understand, and SUM and MAX etc, can handle, but just auto magic into "must be only one column".

Super weak answer, because auto-magic is like a free lunch, "nicer".

Or it's "disgusting", but some big customer had lots of SQL that "could not be changed, and was needed to be supported to make big $$$ so hey why not?" I don't like these as they seem too simple and shutdown thinking. But they exist as options.

Magic Row option:

There is a really nice explanation of how PIVOT on Medium, and it has an implicit GROUPING BY ALL in it, for the parameters of the source (that are not named in the result column OR the output columns that is independent of selected columns), and thus there is already a notion of "just take all the stuff you have been given, and make sense of it" that is required to support this. Perhaps in it's like an ARGC, ARGV from C or params from C# where it becomes a bucket of stuff, that is dynamically processed, thus the LISTAGG error, which really does show the parameter idea.

This is so much fun!