How to make SnowFlake to raise an error when I forget comma between two selected columns?

Question

Usually, when I try to select multiple columns in SnowFlake and I forget to use comma (instead of a,b,c I write a b c), it gives me an error.

Input:

select a b c from (
SELECT column1 as c, column2 as a, column3 as b 
FROM (VALUES ('Q','g','a'),('C','D','x'))
);

Output:

SQL compilation error: syntax error line 1 at position 11 unexpected 'c'.

But when I try to do this only between two columns, it does not raise any error, instead, it returns wrong result:

select a b from (
SELECT column1 as a, column2 as b, column3 as c 
FROM (VALUES ('Q','g','a'),('C','D','x'))
);

Output:

It just shows single column, with values from column a but says that it's column b. I tried it several times on different warehouses, in different windows, with different names of columns and values, and it still does the same.

What is reason of such behavior? Why does it not raise error message same way as it does in case of 3 columns? I find this behavior very confusing. Can I somehow tell SF to raise error instead (just as in case with 3 or more columns)?

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-08-26T14:03:58

The good practice is to use prefixes and AS keyword. It may seems superfluous at the beginning, but "explicit is better than implicit".

For instance, when columns are prefixed this kind of "typo" will error out immediately:

select s.a s.b from (
    SELECT column1 as c, column2 as a, column3 as b 
    FROM (VALUES ('Q','g','a'),('C','D','x'))
) AS s;
-- Syntax error: unexpected '.'. (line 1)

The ideal situation is to use AS keyword:

SELECT s.a, s.b, s.c 
FROM(
    SELECT column1 as c, column2 as a, column3 as b 
    FROM (VALUES ('Q','g','a'),('C','D','x'))
) AS s;

The entire query could be further simplified:

SELECT s.a, s.b, s.c
FROM (VALUES ('Q','g','a'),('C','D','x')) AS s(a,b,c);

Extras:

Query below could be treated as a code smell.

SELECT col1 col2
FROM tab;

There are tools that are able to detect such occurences. For instance: SQLFluff

Rules in SQLFluff are implemented as crawlers. These are entities which work their way through the parsed structure of a query to evaluate a particular rule or set of rules.

...

class Rule_L012(code, description, **kwargs)

Implicit aliasing of column not allowed. Use explicit AS clause.

NB: This rule inherits its functionality from obj:Rule_L011 but is separate so that they can be enabled and disabled separately.

How to make SnowFlake to raise an error when I forget comma between two selected columns?

2 Answers