SQLite Forum

WHERE a IN (SELECT non-existing column )
> WITH personList(concept_ID) AS (VALUES (1), (2), (3)) SELECT count() FROM PERSON WHERE person_ID IN (SELECT person_ID FROM personList) AND age >30;

 > As you see table personList() does not have column person_ID (because of a typo). The above simply counts people above 30 years old from the PERSON table, essentially discarding first condition entirely.

The select in the IN clause refers to a column in the outer (containing) query.  It is what is called a "correlated subquery".  It is perfectly valid.

 > WITH personList(concept_ID) AS (VALUES (1), (2), (3)) SELECT person_ID FROM personList;

The column "person_ID" does not exist so you get an error message stating that it does not exist.

So yes, this is in both cases entirely correct and expected behaviour.