WHERE a IN (SELECT non-existing column )
(1) By Roman (moskvich412) on 2020-10-14 17:57:37 [source]
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] [source]
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] [source]
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 [link] [source]
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] [source]
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