Re: Oddity with NOT IN

On 8/6/16 12:57 PM, Andrew Gierth wrote:
> 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