Valid for WITH clause to use existing table name?

Question

Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is intentional before I use it in an application.

Let me add some detail. Say I have a table Entry and another table Thing, and a query like this

SELECT Entry.k, Entry.v, Thing.v2
FROM Entry INNER JOIN Thing ON Entry.v = Thing.k

Is the following valid?

WITH Entry AS (SELECT 1 AS k, 1234 AS v)
SELECT Entry.k, Entry.v, Thing.v2
FROM Entry INNER JOIN Thing ON Entry.v = Thing.k

This seems to do what I want, but can I count on this behavior not being a bug or undefined behavior? Can't find anything in the documentation of either SQLite or MySQL explicitly allowing or forbidding it.

(I know in this particular example I can just SELECT ... FROM Thing WHERE .... That's not the point of this question - please trust me that I have a good reason for writing the query I'm writing :) )

Answer 1

You can rest assured that this is not undefined behaviour.

From the official MYSQL documentation

For resolving references to objects with the same names, derived tables hide CTEs; and CTEs hide base tables, TEMPORARY tables, and views. Name resolution occurs by searching for objects in the same query block, then proceeding to outer blocks in turn while no object with the name is found.

So in terms of name resolution priority:

derived tables > CTEs (table defined in a WITH block) > everything else