Re: Oddity with NOT IN
От | Corey Huinker |
---|---|
Тема | Re: Oddity with NOT IN |
Дата | |
Msg-id | CADkLM=fKB=Qhw9buhW=Fs-MwO5X=KkKbJMuJAq_dj1qTRxLyyg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Oddity with NOT IN (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
Список | 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>
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Peter GeogheganДата:
Сообщение: Re: Parallel tuplesort (for parallel B-Tree index creation)