Sql - The Two Standards of the Sql CTE

Sql really has two standards. There is the ANSI sql standard and then
there is the DOUBLE standard.

This example uses Sql Server 2005 and specifically the CTE (common table expression).

Sql Server sees nothing wrong with this query that duplicates a column name.
Something that no database should allow.

SELECT employeeid,customerid as employeeid
FROM orders

From Bol on the CTE column_name
'Duplicate names within a single CTE definition are not allowed.The list of
 column names is optional only if distinct names for all resulting  columns
 are supplied in the query definition.'

But this query produces an error.

WITH Emps  (Emp, Emp) AS
(
    SELECT employeeid,customerid as employeeid
    FROM orders
)
SELECT *
FROM Emps
Error: 'The column 'Emp' was specified multiple times for 'Emps'.

But try this. And it works!

WITH Emps  (Emp, Emp1) AS
(
    SELECT employeeid,customerid as employeeid
    FROM orders
)
SELECT *
FROM Emps

So what does Bol really mean in the column_name description. It means
that the stupid and error prone idea of creating duplicate column names
is perfectly ok in the query definition. It's only the outer reference
names (the  ( column_name [ ,...n ] ) that are going to be checked for
duplicates. So the MS statement:
'The list of column names is optional only if distinct names for all
 resulting columns are supplied in the query definition.'

is a recognition that its okay to screw up the query_definition as
as long as you come away with distinct names in the CTE. In other words,
give distinct names for the 'same' columns created in the query_definition
with the same name. If you don't duplicate names in the quey_definition
you can dispense with the CTE column name list totally. So not only is the
MS statement a statement of guilt (don't expect us to correct duplicate column
names when it first occurs, we'll catch it the 2nd time it comes around),
it's also a statement of a double standard. One standard for an independent
SELECT statement and another standard for a CTE! Double standards allow
the initial error to propagate. Not what you want in a database.

This is what a database should do when duplicate columns are declared.
This is what Dataphor will do when you try this.

select Orders {EmployeeID,CustomerID EmployeeID};
Error: Duplicate object name "EmployeeID".

Only if you 'love' sql could you tolerate its nonsense ☺ .

For more on the sql CTE see:
'Do you know what the Sql CTE is?'
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html