Integer as SQL Alias doesn’t error but gives incorrect result

Question

I was working on some SQL code today where the originator had mistaken the Alias of a table, which was the letter l, but they had typed with the number 1.

Even though this error had been made the code still ran.

Here is an example of the code

SELECT l.[Name]
       ,l.Address
       ,1.Postcode
FROM List l

with the mistake being the code 1.Postcode instead of l.Postcode

The results came out with the Postcode column all 1’s, as below

If the mistake was to use t.Postcode then it would give an error

The multi-part identifier " t.Postcode " could not be bound.

But instead of erroring 1.Postcode is being treated as 1 AS Postcode

Correcting the line to l.Postcode gives the correct data, as below

So my question is why doesn’t the line 1.Postcode error?

Answer 1

Your syntax 1.Postcode is treated by sql-server as +1.0 AS Postcode

There is a misunderstanding, it throw no errors because there is no error to throw, and it give the right result... for this syntax..

The main problem here is that the space between column value and column alias is not mandatory, and in some circumnstances, can be omitted.

SELECT From Microsoft docs:

SELECT [ ALL | DISTINCT ]  
[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]   
<select_list>   
<select_list> ::=   
    {   
      *   
      | { table_name | view_name | table_alias }.*   
      | {  
          [ { table_name | view_name | table_alias }. ]  
               { column_name | $IDENTITY | $ROWGUID }   
          | udt_column_name [ { . | :: } { { property_name | field_name }   
            | method_name ( argument [ ,...n] ) } ]  
          | expression  
          [ [ AS ] column_alias ]   
         }  
      | column_alias = expression   
    } [ ,...n ]   

As you can see, considering the dot between 1 and Postcode as a separator, 1 is not a table_name, view_name, table_alias, nor a udt_column_name, so it remains only method_name and expression.
But 1.Postcode cannot be a method_name (cannot start with a number), so your case is expression [ [ AS ] column_alias ]

where:

expression
Is a constant, function, any combination of column names, constants, and functions connected by an operator or operators, or a subquery.

Well.. again, 1 cannot be a function nor a colum name (cannot start with a number), so it MUST be a constant.

the 1st character 1 is a number different from 0.. so it cannot be nothing else than a bit, integer, decimal or float constant, but the 2nd character . restricts only to decimal and float constant.

After the . we have P, it is not a number.. and it is not E or e (which denotes the float constants) so we have found a decimal constant.

The following is not AS (please note the blank after AS) so it could be the column alias, and Postcode is a valid column alias.. so we have our new column, its value is 1 (precisely 1.0) and its name is Postcode

take a look at these examples and enjoy:

select 
    1.Postcode_decimal, -- 1.0 AS Postcode_decimal
    1.ePostcode_float, -- 1.0E0 AS Postcode_float
    1.asPostcode_decimal, -- 1.0 AS asPostcode_decimal
    1.as PostcodeAS_decimal, -- 1.0 AS PostcodeAS_decimal
    1Postcode_int, -- 1 AS Postcode_int
    1.+1.Postcode_expression, -- (1.0 + 1.0) AS Postcode_expression
    1.%1.+1+0.-.0/.1e-1Postcode_more_complex_expr, -- (1.0 % 1.0) + 1 + (0.0 / 0.1E-1) AS Postcode_more_complex_expr
    0xPostcode_varbin, -- 0x00 as Postcode_varbin
    3.5[3.5], -- 3.5 AS [3.5]
    'Hello'Postcode_varchar, 
    '1.0'[1.0], -- value is varchar, name is '1.0'  
    -- you can single quote an alias 
    22'Postcode_int2', 
    22'2.2',
    -- but beware of two single quotes are treated as one literal single quote.. 
    'Hello''Postcode_varchar'_  -- yes, the underscore can be an identifier..
into 
    #test_alias

SELECT * FROM #test_alias

select column_ordinal, name, is_nullable, system_type_name, max_length, precision, source_column
from sys.dm_exec_describe_first_result_set(N'SELECT * FROM #test_alias',null,1) 

drop table #test_alias

Outputs:

Postcode_decimal    Postcode_float  asPostcode_decimal  PostcodeAS_decimal  Postcode_int    Postcode_expression Postcode_more_complex_expr  Postcode_varbin Postcode_varchar    1.0 Postcode_int2   2.2 _                       3.5
1                   1               1                   1                   1               2                   1                           0x              Hello               1.0 22              22  Hello'Postcode_varchar  3.5

and

column_ordinal  name                        is_nullable system_type_name    max_length  precision   
1               Postcode_decimal            0           numeric(1,0)        5           1           
2               Postcode_float              0           float               8           53          
3               asPostcode_decimal          0           numeric(1,0)        5           1           
4               PostcodeAS_decimal          0           numeric(1,0)        5           1           
5               Postcode_int                0           int                 4           10          
6               Postcode_expression         1           numeric(2,0)        5           2           
7               Postcode_more_complex_expr  1           float               8           53          
8               Postcode_varbin             0           varbinary(1)        1           0           
9               Postcode_varchar            0           varchar(5)          5           0           
10              1.0                         0           varchar(3)          3           0           
11              Postcode_int2               0           int                 4           10          
12              2.2                         0           int                 4           10          
13              _                           0           varchar(22)         22          0           
14              3.5                         0           numeric(2,1)        5           2