WHERE a IN (SELECT non-existing column )
(1) By Roman (moskvich412) on 2020-10-14 17:57:37 [link]
Dear SQLiters, I made a typo in SQL statement, but instead of getting error it succeeded. Is that expected? 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. However, the internal select: WITH personList(concept_ID) AS (VALUES (1), (2), (3)) SELECT person_ID FROM personList; fails because column person_ID does not exist. Is that normal behavior? Thank you, Roman
(2) By Richard Hipp (drh) on 2020-10-14 18:07:29 in reply to 1 [link]
Equivalent to: ~~~~ WITH personList(concept_ID) AS (VALUES(1),(2),(3)) SELECT count(*) FROM person WHERE person_ID IN (SELECT person.person_ID FROM personList) AND age > 30; ~~~~ Also works with PostgreSQL
(3) By Keith Medcalf (kmedcalf) on 2020-10-14 18:16:08 in reply to 1 [link]
> 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.
(4) By L Carl (lcarlp) on 2020-10-15 15:11:00 in reply to 3
This is, however, a common case where standard SQL behavior can be very surprising and puzzling. I remember the first time this happened to me, and it took me a while to find my bug. (The problem was buried in a substantial amount of code.) Some people advocate always qualifying all column names for this reason. No, I don’t do that.
(5) By Roman (moskvich412) on 2020-10-16 03:37:53 in reply to 4 [link]
Thank you, Richard, Keith and Carl. I did not know about this SQL feature. I know and agree that fully qualified name is proper use. But it is long to type, more typos and more to edit when applying the same query to a different table/database. Roman