On Sat, Aug 6, 2016 at 2:13 PM, Jim Nasby <jim.na...@bluetreble.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@postgresql.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)
