Shadowing a table name with a common table expression

If we create a table and then declare a common table expression of the
same name, `SELECT`s seem to refer to the table, while `INSERT` targets
refer to the CTE. For example:

```
CREATE TABLE foo (x);
INSERT INTO foo SELECT 1;
WITH foo (x) AS (SELECT 10) INSERT INTO foo SELECT x + 1 FROM foo;
SELECT x FROM foo;
```

On my SQLite (tested version 3.11.0 and also v3.33.1 from Fossil head),
this prints 1 and 11. If the `foo` in `SELECT x + 1 FROM foo` had
referred to the underlying table, it would have printed 1 and 2 instead.

Clearly this is a bit confusing, and I should probably avoid doing it.
But is the behavior defined either by SQL or SQLite? I didn't see
anything relevant in <https://sqlite.org/lang_select.html#fromclause>.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users