The query below seems simple and straightforward, yet it produces unexpected results.
CREATE TABLE #NUMBERS
(
N BIGINT
);
INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;
WITH
A AS
(
-- CHOOSE A ROW AT RANDOM
SELECT TOP 1 *
FROM #NUMBERS
ORDER BY NewID()
),
B AS
(
SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
UNION ALL
SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
UNION ALL
SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
UNION ALL
SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A
UNION ALL
SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
)
SELECT *
FROM B
ORDER BY B.QUANTITY
;
I would expect it to execute CTE A once, and then carry those results into CTE B to produce results something like this:
QUANTITY | UNIT |
---|---|
4 | METERS |
400 | CENTIMETERS |
4000 | MILLIMETERS |
4000000 | MICRONS |
4000000000 | NANOMETERS |
However, it produces results like this:
QUANTITY | UNIT |
---|---|
8 | METERS |
700 | CENTIMETERS |
1000 | MILLIMETERS |
6000000 | MICRONS |
3000000000 | NANOMETERS |
It means it is going back and executing CTE A five times, once for every mention of A in CTE B. Not only is this unwanted and unintuitive, but it also seems unnecessarily inefficient.
What is going on, and how would a CTE genius rewrite it to produce the desired results?
BTW, the Microsoft documentation pages on CTEs contain this cryptic statement which might or might not be related:
If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.
Finally, rewriting the query to eliminate CTE B didn't help:
WITH
A AS
(
-- CHOOSE A ROW AT RANDOM
SELECT TOP 1 *
FROM #NUMBERS
ORDER BY NewID()
)
SELECT *
FROM (
SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
UNION ALL
SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
UNION ALL
SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
UNION ALL
SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A
UNION ALL
SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
) AS B
ORDER BY B.QUANTITY
;