Re: Oddity with NOT IN

On Sat, Aug 6, 2016 at 2:13 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> 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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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.

Some setting name suggestions:

notify_vague_column_reference = (on,off)
pedantic_column_identifiers = (off,debug,notice,warn,error)