Re: Third thoughts about the DISTINCT MAX() problem

On Sat, Mar 29, 2008 at 3:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I just realized that the patch I applied here
> http://archives.postgresql.org/pgsql-committers/2008-03/msg00531.php
> for Taiki Yamaguchi's bug report here
> http://archives.postgresql.org/pgsql-bugs/2008-03/msg00275.php
> really doesn't work. It assumes that an ungrouped aggregate
> query can't return more than one row, which is true in straight
> SQL ... but it's not true if you consider SRFs in the target list.
> CVS HEAD gives the wrong answer for this example in the regression
> database:
>
> regression=# select max(unique1), generate_series(1,3) as g from tenk1
> order by g desc;
> max | g
> ------+---
> 9999 | 1
> 9999 | 2
> 9999 | 3
> (3 rows)
>
> because it wrongly supposes it can discard the ORDER BY.
>
> So, back to the drawing board. I had thought of two approaches to
> fixing the problem instead of just dodging it. Plan A was to
> apply planagg.c's Aggref->Param substitution inside EquivalenceClasses,
> as in the draft patch here:
> http://archives.postgresql.org/pgsql-patches/2008-03/msg00388.php
> which I didn't entirely like for reasons mentioned in that post.
> Plan B was to try to revert to the way sort clause matching was
> done pre-8.3, that is have make_sort_from_pathkeys check first
> for a matching ressortgroupref tag before it goes looking for equal()
> expressions. I had actually tried to do that first but got hung
> up on the problem of identifying the correct sort operator ---
> just taking the exposed type of the targetlist entry doesn't always
> work, because of binary-compatible cases (eg, tlist entry may say
> it yields varchar but we need to find the text opclass). Perhaps
> thinking a bit harder would yield a solution though.
>
> Does anyone have comments for or against either of these approaches,
> or perhaps a Plan C to consider?
>
>
>

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?

Best regards,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device