Why isn't a CTE evaluated one time?-CodePudding

If we take the following:

WITH tbl AS (
    SELECT RAND() AS a
) SELECT * FROM tbl AS tbl1, tbl AS tbl2

Why does this return two values instead of one? I thought a CTE is basically evaluated one time at the start and then it is used wherever it is needed? Or is my understanding of persistency of a CTE incorrect? Or does the implementation from db to db differ?

Compared with MySQL where it returns a single persisted-value:

CodePudding user response：

According to the docs

BigQuery only materializes the results of recursive CTEs, but does not materialize the results of non-recursive CTEs inside the WITH clause. If a non-recursive CTE is referenced in multiple places in a query, then the CTE is executed once for each reference.

So what you see is expected at least for BigQuery.