Re: Third thoughts about the DISTINCT MAX() problem

On Sat, Mar 29, 2008 at 3:21 AM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br
/><divclass="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt
0pt0pt 0.8ex; padding-left: 1ex;"> I just realized that the patch I applied here<br /><a
href="http://archives.postgresql.org/pgsql-committers/2008-03/msg00531.php"
target="_blank">http://archives.postgresql.org/pgsql-committers/2008-03/msg00531.php</a><br/> for Taiki Yamaguchi's bug
reporthere<br /><a href="http://archives.postgresql.org/pgsql-bugs/2008-03/msg00275.php"
target="_blank">http://archives.postgresql.org/pgsql-bugs/2008-03/msg00275.php</a><br/> really doesn't work.  It
assumesthat an ungrouped aggregate<br /> query can't return more than one row, which is true in straight<br /> SQL ...
butit's not true if you consider SRFs in the target list.<br /> CVS HEAD gives the wrong answer for this example in the
regression<br/> database:<br /><br /> regression=# select max(unique1), generate_series(1,3) as g from tenk1 order by g
desc;<br/>  max  | g<br /> ------+---<br />  9999 | 1<br />  9999 | 2<br />  9999 | 3<br /> (3 rows)<br /><br />
becauseit wrongly supposes it can discard the ORDER BY.<br /><br /> So, back to the drawing board.  I had thought of
twoapproaches to<br /> fixing the problem instead of just dodging it.  Plan A was to<br /> apply planagg.c's
Aggref->Paramsubstitution inside EquivalenceClasses,<br /> as in the draft patch here:<br /><a
href="http://archives.postgresql.org/pgsql-patches/2008-03/msg00388.php"
target="_blank">http://archives.postgresql.org/pgsql-patches/2008-03/msg00388.php</a><br/> which I didn't entirely like
forreasons mentioned in that post.<br /> Plan B was to try to revert to the way sort clause matching was<br /> done
pre-8.3,that is have make_sort_from_pathkeys check first<br /> for a matching ressortgroupref tag before it goes
lookingfor equal()<br /> expressions.  I had actually tried to do that first but got hung<br /> up on the problem of
identifyingthe correct sort operator ---<br /> just taking the exposed type of the targetlist entry doesn't always<br
/>work, because of binary-compatible cases (eg, tlist entry may say<br /> it yields varchar but we need to find the
textopclass).  Perhaps<br /> thinking a bit harder would yield a solution though.<br /><br /> Does anyone have comments
foror against either of these approaches,<br /> or perhaps a Plan C to consider?<br /><br />  <br
/></blockquote></div><br/>In the past I had seen suggestions (perhaps from you) that we should disallow SRFs in target
list...Although not for 8.3, but would this be a good time for 8.4 to deprecate the usage of SRFs in targetlist?<br
/><br/>Best regards,<br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br />singh.gurjeet@{ gmail | hotmail | indiatimes |
yahoo}.com<br /><br />EnterpriseDB <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br
/>Mailsent from my BlackLaptop device