Re: Oddity with NOT IN - Mailing list pgsql-hackers

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Sat, Aug 6, 2016 at 2:13 PM, Jim Nasby <span
dir="ltr"><<ahref="mailto:Jim.Nasby@bluetreble.com" target="_blank">Jim.Nasby@bluetreble.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><spanclass="">On 8/6/16 12:57 PM, Andrew Gierth wrote:<br /><blockquote
class="gmail_quote"style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> The easy
tocatch case, I think, is when the targetlist of the IN or NOT<br /> IN subquery contains vars of the outer query level
butno vars of the<br /> inner one and no volatile functions. This can be checked for with a<br /> handful of lines in
theparser or a couple of dozen lines in a plugin<br /> module (though one would have to invent an error code, none of
the<br/> existing WARNING sqlstates would do).<br /></blockquote><br /></span> I would still like to warn on any outer
varsshow up in the target list (other than as function params), because it's still very likely to be a bug. But I agree
thatwhat you describe is even more certain to be one.<span class=""><br /><br /><blockquote class="gmail_quote"
style="margin:0px0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> Maybe David Fetter's suggested
modulefor catching missing WHERE clauses<br /> could be expanded into a more general SQL-'Lint' module?<br
/></blockquote><br/></span> Possibly, though I hadn't really considered treating this condition as an error.<br /><br
/>Also, there's some other common gotchas that we could better warn users about, some of which involve DDL. One example
isaccidentally defining duplicate indexes.<span class="im"><br /> -- <br /> Jim Nasby, Data Architect, Blue Treble
Consulting,Austin TX<br /> Experts in Analytics, Data Architecture and PostgreSQL<br /> Data in Trouble? Get it in
Treble!<a href="http://BlueTreble.com" rel="noreferrer" target="_blank">http://BlueTreble.com</a><br /> 855-TREBLE2 <a
href="tel:%28855-873-2532"target="_blank" value="+18558732532">(855-873-2532</a>)   mobile: <a href="tel:512-569-9461"
target="_blank"value="+15125699461">512-569-9461</a><br /><br /><br /></span><div class=""><div class="h5"> -- <br />
Sentvia pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org"
target="_blank">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-hackers"rel="noreferrer"
target="_blank">http://www.postgresql.org/mail<wbr/>pref/pgsql-hackers</a><br /></div></div></blockquote></div><br
/></div><divclass="gmail_extra">If we are contemplating a setting wherein we issue debug/notice/warning messages for
potentiallyerroneous SQL, I would suggest a simple test would be any reference to a column without the a corresponding
table/alias.<br /><br />This is fine:<br />    SELECT a.x, b.y FROM table_that_has_x a JOIN table_that_has_y b ON <a
href="http://a.id">a.id</a>= b.foreign_id<br />This gives the notice/warning:<br />    SELECT x, b.y FROM
table_that_has_xa JOIN table_that_has_y b ON <a href="http://a.id">a.id</a> = b.foreign_id<br /><br />We'd have to
suppressthe 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
aliasingand full references because it makes it easier when you modify the query to add another table.<br /><br />Some
settingname suggestions:</div><div class="gmail_extra"><br /></div><blockquote style="margin:0 0 0
40px;border:none;padding:0px"><divclass="gmail_extra">notify_vague_column_reference = (on,off)</div><div
class="gmail_extra">pedantic_column_identifiers= (off,debug,notice,warn,error)</div></blockquote><div
class="gmail_extra"><br/></div></div>