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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
