SQLite Forum

WHERE a IN (SELECT non-existing column )
Login

WHERE a IN (SELECT non-existing column )

(1) By Roman (moskvich412) on 2020-10-14 17:57:37 [link] [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 [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