The easy to catch case, I think, is when the targetlist of the IN or NOT IN subquery contains vars of the outer query level but no vars of the inner one and no volatile functions. This can be checked for with a handful of lines in the parser or a couple of dozen lines in a plugin module (though one would have to invent an error code, none of the existing WARNING sqlstates would do).
I would still like to warn on any outer vars show up in the target list (other than as function params), because it's still very likely to be a bug. But I agree that what you describe is even more certain to be one.
Maybe David Fetter's suggested module for catching missing WHERE clauses could be expanded into a more general SQL-'Lint' module?
Possibly, though I hadn't really considered treating this condition as an error.
Also, there's some other common gotchas that we could better warn users about, some of which involve DDL. One example is accidentally defining duplicate indexes. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
If we are contemplating a setting wherein we issue debug/notice/warning messages for potentially erroneous SQL, I would suggest a simple test would be any reference to a column without the a corresponding table/alias.
This is fine: SELECT a.x, b.y FROM table_that_has_x a JOIN table_that_has_y b ON a.id = b.foreign_id This gives the notice/warning: SELECT x, b.y FROM table_that_has_x a JOIN table_that_has_y b ON a.id = b.foreign_id
We'd have to suppress the warning in cases where no tables are mentioned (no table to alias, i.e. "SELECT 'a_constant' as config_var"), and I could see a reason for suppressing it where only one table is mentioned, though I often urge table aliasing and full references because it makes it easier when you modify the query to add another table.
