2009/12/19 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi:
On 2009-12-15 23:10 +0200, Tom Lane wrote:
Andrew Gierthand...@tao11.riddles.org.uk writes:
Notice that there are cases where agg(distinct x order by x) is
nondeterministic while agg(distinct x order by x,y) is deterministic.
Well,
On 2009-12-15 23:10 +0200, Tom Lane wrote:
Andrew Gierthand...@tao11.riddles.org.uk writes:
Notice that there are cases where agg(distinct x order by x) is
nondeterministic while agg(distinct x order by x,y) is deterministic.
Well, I think what you're really describing is a case where you're
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes:
On 2009-12-15 23:10 +0200, Tom Lane wrote:
If we really wanted to take the above seriously, my opinion is that
we ought to introduce DISTINCT ON in aggregates.
FWIW, in my opinion the idea behind this patch is to not fall back on
hacks
Andrew Gierth and...@tao11.riddles.org.uk writes:
Updated version of the aggregate order by patch.
Applied with some editorialization. The main change I made was to get
rid of all the ad-hoc DISTINCT handling in parse_agg.c and use
transformDistinctClause() instead. This exposed what I believe
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom Andrew Gierth and...@tao11.riddles.org.uk writes:
Updated version of the aggregate order by patch.
Tom Applied with some editorialization. The main change I made was
Tom to get rid of all the ad-hoc DISTINCT handling in parse_agg.c
Tom and
Andrew Gierth and...@tao11.riddles.org.uk writes:
Query-level DISTINCT shouldn't allow columns in the order by that
aren't in the select list because those columns _do not exist_ at the
point that ordering logically takes place (even though in the
implementation, they might).
This isn't the
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Query-level DISTINCT shouldn't allow columns in the order by that
aren't in the select list because those columns _do not exist_ at
the point that ordering logically takes place (even though in the
implementation, they might).
This isn't the
Andrew Gierth and...@tao11.riddles.org.uk writes:
Notice that there are cases where agg(distinct x order by x) is
nondeterministic while agg(distinct x order by x,y) is deterministic.
Well, I think what you're really describing is a case where you're using
the wrong sort opclass. If the
Andrew Gierth and...@tao11.riddles.org.uk writes:
Updated version of the aggregate order by patch.
I'm starting to look at this now. I find it rather bizarre to merge
both the actual arguments of an aggregate and the optional ORDER BY
expressions into a single targetlist. It doesn't seem like
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Updated version of the aggregate order by patch.
Tom I'm starting to look at this now. I find it rather bizarre to
Tom merge both the actual arguments of an aggregate and the optional
Tom ORDER BY expressions into a single targetlist. It doesn't
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom and I would also expect there to be a nonzero performance hit
Tom from the extra TargetEntry expression nodes, even when the
Tom feature is not in use.
I tested for that and couldn't reliably
Hitoshi Harada escribió:
I found only trivial favors such like that a blank line is added
around line 595 in the patch, and proj in peraggstate sounds a
little weird to me because of surrounding evaldesc and evalslot
(evalproj seems better to me). Also catversion update doesn't mean
anything
2009/11/30 Andrew Gierth and...@tao11.riddles.org.uk:
Updated version of the aggregate order by patch.
Includes docs + regression tests all in the same patch.
Changes:
- removed SortGroupClause.implicit as per review comments,
replacing it with separate lists for Aggref.aggorder and
Updated version of the aggregate order by patch.
Includes docs + regression tests all in the same patch.
Changes:
- removed SortGroupClause.implicit as per review comments,
replacing it with separate lists for Aggref.aggorder and
Aggref.aggdistinct.
- Refactored in order to move
Here's my first review.
The patch was in context diff format and applied cleanly with a little
3 hunks in parse_expr.c. make succeeded without any warnings and make
check passed all 121 tests.
It implements as advertised, following SQL spec with extension of both
DISTINCT clause and ORDER BY
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:
Hitoshi Questions here:
Hitoshi - agglevelsup?
Hitoshi We have aggregate capability that all arguments from upper
Hitoshi level query in downer level aggregate makes aggregate call
Hitoshi itself to upper level call, as a constant
Andrew == Andrew Gierth and...@tao11.riddles.org.uk writes:
Andrew Performance.
Andrew tuplesort_getdatum etc. seems to be substantially faster than
Andrew tuplesort_gettupleslot especially for the case where you're
Andrew sorting a pass-by-value datum such as an integer (since the
Andrew
On Sun, Nov 15, 2009 at 11:23 PM, Andrew Gierth
and...@tao11.riddles.org.uk wrote:
Future performance enhancements (which I have no particular plans to
tackle) would involve having the planner consult the desired aggregate
orderings and estimating the cost of sorting as opposed to the cost of
2009/11/16 Andrew Gierth and...@tao11.riddles.org.uk:
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:
Hitoshi Questions here:
Hitoshi - agglevelsup?
What case exactly would you consider an error? When an order by
expression references a lower (more deeply nested) query level than
Hitoshi Harada umi.tan...@gmail.com writes:
2009/11/16 Andrew Gierth and...@tao11.riddles.org.uk:
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:
Hitoshi - SortGroupClause.implicit
Hitoshi implicit member was added in SortGroupClause. I didn't
Hitoshi find clear reason to add this.
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:
What case exactly would you consider an error? When an order by
expression references a lower (more deeply nested) query level
than any of the actual arguments?
Hitoshi It's only that I felt not intuitive. To me, arguments are
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom I agree with Hitoshi that this seems to be a hack to deal with the
[snip]
So copying the way that SELECT DISTINCT works would be the way to go?
i.e. keeping separate lists in the parse node for sorting and distinct?
What about error handling? If
Andrew Gierth and...@tao11.riddles.org.uk writes:
What about error handling? If the user specifies agg(distinct x) where
x is not sortable, do we leave it to the planner to detect that (which
means not reporting the error position?)
Well, at the moment there's only going to be a sort-based
2009/11/16 Andrew Gierth and...@tao11.riddles.org.uk:
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:
What case exactly would you consider an error? When an order by
expression references a lower (more deeply nested) query level
than any of the actual arguments?
Hitoshi It's
Tom == Tom Lane t...@sss.pgh.pa.us writes:
What about error handling? If the user specifies agg(distinct x)
where x is not sortable, do we leave it to the planner to detect
that (which means not reporting the error position?)
Tom Well, at the moment there's only going to be a sort-based
Andrew Gierth wrote:
Herewith a patch to implement agg(foo ORDER BY bar) with or without
DISTINCT, etc.
What does that mean? Aggregate functions are supposed to be commutative,
right?
No artificial restrictions are imposed on what
syntactical combinations are allowed. However, ORDER BY is
On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Andrew Gierth wrote:
Herewith a patch to implement agg(foo ORDER BY bar) with or without
DISTINCT, etc.
What does that mean? Aggregate functions are supposed to be commutative,
right?
We certainly
On fre, 2009-11-13 at 03:16 +, Andrew Gierth wrote:
Caveat: as discussed earlier, this patch changes the behaviour of
array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs
were unconditionally skipped; now, they are treated just like DISTINCT
or GROUP BY normally do.
The
Peter Eisentraut pete...@gmx.net writes:
On fre, 2009-11-13 at 03:16 +, Andrew Gierth wrote:
Caveat: as discussed earlier, this patch changes the behaviour of
array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs
were unconditionally skipped; now, they are treated just
Greg Stark gsst...@mit.edu writes:
On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Andrew Gierth wrote:
Herewith a patch to implement agg(foo ORDER BY bar) with or without
DISTINCT, etc.
What does that mean? Aggregate functions are supposed to
On fre, 2009-11-13 at 10:01 -0500, Tom Lane wrote:
Peter Eisentraut pete...@gmx.net writes:
On fre, 2009-11-13 at 03:16 +, Andrew Gierth wrote:
Caveat: as discussed earlier, this patch changes the behaviour of
array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs
were
On fre, 2009-11-13 at 10:35 -0500, Tom Lane wrote:
I'm not entirely convinced that adding ORDER BY here is a good idea,
partly because it goes so far beyond the spec
This is exactly the syntax that is in the spec AFAICT.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
On Fri, Nov 13, 2009 at 10:35 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Greg Stark gsst...@mit.edu writes:
On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Andrew Gierth wrote:
Herewith a patch to implement agg(foo ORDER BY bar) with or without
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com
writes:
Herewith a patch to implement agg(foo ORDER BY bar) with or
without DISTINCT, etc.
Heikki What does that mean? Aggregate functions are supposed to be
Heikki commutative, right?
The SQL spec defines two
Peter == Peter Eisentraut pete...@gmx.net writes:
I'm not entirely convinced that adding ORDER BY here is a good idea,
partly because it goes so far beyond the spec
Peter This is exactly the syntax that is in the spec AFAICT.
Right. The spec defines this syntax for array_agg and xmlagg
Andrew Gierth and...@tao11.riddles.org.uk writes:
Peter == Peter Eisentraut pete...@gmx.net writes:
Peter This is exactly the syntax that is in the spec AFAICT.
Right. The spec defines this syntax for array_agg and xmlagg (only).
Cool, I had forgotten that they added that in the latest
On Friday 13 November 2009 16:35:08 Tom Lane wrote:
Greg Stark gsst...@mit.edu writes:
On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Andrew Gierth wrote:
Herewith a patch to implement agg(foo ORDER BY bar) with or without
DISTINCT, etc.
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com
writes:
No artificial restrictions are imposed on what syntactical
combinations are allowed. However, ORDER BY is not allowed with
aggregates used as window functions (as per the existing
restriction on DISTINCT).
2009/11/14 Andrew Gierth and...@tao11.riddles.org.uk:
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com
writes:
No artificial restrictions are imposed on what syntactical
combinations are allowed. However, ORDER BY is not allowed with
aggregates used as window functions
2009/11/14 Tom Lane t...@sss.pgh.pa.us:
Andrew Gierth and...@tao11.riddles.org.uk writes:
Peter == Peter Eisentraut pete...@gmx.net writes:
Peter This is exactly the syntax that is in the spec AFAICT.
Right. The spec defines this syntax for array_agg and xmlagg (only).
Cool, I had
On Fri, Nov 13, 2009 at 5:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Quite. This is another instance of the thing I complained of before,
that the SQL committee likes to define the behavior of specific
aggregates instead of inducing a generic aggregate-behavior definition.
I think this makes
Herewith a patch to implement agg(foo ORDER BY bar) with or without
DISTINCT, etc. No artificial restrictions are imposed on what
syntactical combinations are allowed. However, ORDER BY is not allowed
with aggregates used as window functions (as per the existing
restriction on DISTINCT).
Included
42 matches
Mail list logo