Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?

Question

Is this valid ANSI SQL?:

SELECT 1 AS X
       ,2 * X AS Y
       ,3 * Y AS Z

Because Teradata (12) can do this, as well as this (yes, crazy isn't it):

SELECT 3 * Y AS Z
       ,2 * X AS Y
       ,1 AS X

But SQL Server 2005 requires something like this:

SELECT  X
       ,Y
       ,3 * Y AS Z
FROM    (
         SELECT X
               ,2 * X AS Y
         FROM   (
                 SELECT 1 AS X
                ) AS X
        ) AS Y

Answer 1

No, it's not valid ANSI. ANSI assumes that all SELECT clause items are evaluated at once.

And I'd've written it in SQL 2005 as:

SELECT *
FROM        (SELECT 1 AS X) X
CROSS APPLY (SELECT 2 * X AS Y) Y
CROSS APPLY (SELECT 3 * Y AS Z) Z
;

Answer 2

It doesn't need to be that ugly in SQL Server 2005+. That's why Microsoft introduced CTEs:

WITH T1 AS (SELECT 1 AS X),
     T2 AS (SELECT X, 2 * X AS Y FROM T1)
SELECT X, Y, 3 * Y AS Z FROM T2

Or you could use CROSS APPLY as Rob demonstrates - that may or may not work for you depending on the specifics of the query.

I admit that it's not as clean as Teradata's, but it's not nearly as bad as the subquery version, and the original Teradata example in your question is definitely not part of the SQL-92 standard.

I'd also add that in your original example, the X, Y and Z columns are not, technically, derived columns as you call them. At least as far as Microsoft and ANSI are concerned, they are just aliases, and an alias can't refer to another alias until it actually becomes a column (i.e. through a subquery or CTE).