Re: can somebody execute this query on Oracle 11.2g and send result?

Hello,

I can't to install Oracle, and need to know result.

CREATE TABLE foo(a varchar(10), b varchar(10));

INSERT INTO foo VALUES('aaa',',');
INSERT INTO foo VALUES('bbb',';');
INSERT INTO foo VALUES('ccc','+');

SELECT listagg(a,b) FROM foo;

Thank you

Pavel Stehule

On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello,
>
> I can't to install Oracle, and need to know result.
>
> CREATE TABLE foo(a varchar(10), b varchar(10));
>
> INSERT INTO foo VALUES('aaa',',');
> INSERT INTO foo VALUES('bbb',';');
> INSERT INTO foo VALUES('ccc','+');
>
> SELECT listagg(a,b) FROM foo;
>

That's not how listagg works.

The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by
clause) [OVER partition clause]
If a delimiter is defined, it must be a constant.

Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa,bbb,ccc

Query: SELECT listagg(a, ';') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa;bbb;ccc

Query: SELECT listagg(a, '+') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa+bbb+ccc

--
Jonah H. Harris

2010/1/29 Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com>:
> On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> Hello,
>>
>> I can't to install Oracle, and need to know result.
>>
>> CREATE TABLE foo(a varchar(10), b varchar(10));
>>
>> INSERT INTO foo VALUES('aaa',',');
>> INSERT INTO foo VALUES('bbb',';');
>> INSERT INTO foo VALUES('ccc','+');
>>
>> SELECT listagg(a,b) FROM foo;
>
> That's not how listagg works.
>
> The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by
> clause) [OVER partition clause]
> If a delimiter is defined, it must be a constant.
>
> Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
> Result: aaa,bbb,ccc
>
> Query: SELECT listagg(a, ';') WITHIN GROUP (ORDER BY a) FROM foo;
> Result: aaa;bbb;ccc
>
> Query: SELECT listagg(a, '+') WITHIN GROUP (ORDER BY a) FROM foo;
> Result: aaa+bbb+ccc
>

Thank You very much

Pavel

> --
> Jonah H. Harris
>

Jonah H. Harris escribió:

> The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by
> clause) [OVER partition clause]
> If a delimiter is defined, it must be a constant.
>
> Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
> Result: aaa,bbb,ccc

So that's how Oracle supports ordered aggregates? Interesting -- we
just got that capability but using a different syntax. Hmm, the
SQL:200x draft also has <within group specification> which seems the
standard way to do the ORDER BY stuff for aggregates ... Should we
change the syntax?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

2010/1/29 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Jonah H. Harris escribió:
>
>> The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by
>> clause) [OVER partition clause]
>> If a delimiter is defined, it must be a constant.
>>
>> Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
>> Result: aaa,bbb,ccc
>
> So that's how Oracle supports ordered aggregates?  Interesting -- we
> just got that capability but using a different syntax.  Hmm, the
> SQL:200x draft also has <within group specification> which seems the
> standard way to do the ORDER BY stuff for aggregates ...  Should we
> change the syntax?

Oracle syntax is little bit longer, but it is safer. What is a standard?

Regards
Pavel Stehule

p.s. if it is only syntactic suger, then can't be a problem.

Pavel
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> So that's how Oracle supports ordered aggregates? Interesting -- we
> just got that capability but using a different syntax. Hmm, the
> SQL:200x draft also has <within group specification> which seems the
> standard way to do the ORDER BY stuff for aggregates ... Should we
> change the syntax?

No. The syntax we are using is also standard. As best I can tell,
WITHIN GROUP means something different --- the spec only defines it
for rank functions (RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST)
and it's basically a shorthand form of a window function call.
I find it doubtful that it's actually necessary in Oracle's version
of listagg ...

regards, tom lane

On Fri, Jan 29, 2010 at 11:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I find it doubtful that it's actually necessary in Oracle's version
> of listagg ...
>

Eh?

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm

Defines:

*LISTAGG* (measure_expr [, 'delimiter_expr'])
*WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause]

--
Jonah H. Harris

On Fri, Jan 29, 2010 at 12:09 PM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com>wrote:

> On Fri, Jan 29, 2010 at 11:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> I find it doubtful that it's actually necessary in Oracle's version
>> of listagg ...
>>
>
> Eh?
>
>
> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
>
> Defines:
>
> *LISTAGG* (measure_expr [, 'delimiter_expr'])
> *WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause]
>
>
>
SQL Server's listagg is similar to the PG implementation. It seems Oracle
thinks people would prefer to order the list and for that reason, made their
listagg a rank function type. Having done quite a bit of work generating
delimited lists/arrays based on ordering in PG, I generally agree that it's
what I would generally want.

--
Jonah H. Harris

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
>> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
>>
>> Defines:
>>
>> *LISTAGG* (measure_expr [, 'delimiter_expr'])
>> *WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause]

Hmph. I don't know what would possess them to model their function on
the rank-function syntax extension rather than ARRAY_AGG. The latter
seems a lot closer to the functionality that's actually needed. I'm
still trying to wrap my brain around what the spec says about the
rank-function syntax, but it's notable that the order-by clause is
tightly tied to the aggregate input value(s) --- the sort expressions
have to have the same number and types as the inputs. Which is
certainly not very sensible for listagg.

Can anyone figure out exactly what SQL:2008 10.9 rule 6 is actually saying?
The references to VE1..VEk in the scalar subquery seem to me to be
semantically invalid. They would be sensible if this were a window
function, but it's an aggregate, so I don't understand what row they'd
be evaluated with respect to.

regards, tom lane

2010/1/30 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
>>> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
>>>
>>> Defines:
>>>
>>> *LISTAGG* (measure_expr [, 'delimiter_expr'])
>>> *WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause]
>
> Hmph.  I don't know what would possess them to model their function on
> the rank-function syntax extension rather than ARRAY_AGG.  The latter
> seems a lot closer to the functionality that's actually needed.  I'm
> still trying to wrap my brain around what the spec says about the
> rank-function syntax, but it's notable that the order-by clause is
> tightly tied to the aggregate input value(s) --- the sort expressions
> have to have the same number and types as the inputs.  Which is
> certainly not very sensible for listagg.
>
> Can anyone figure out exactly what SQL:2008 10.9 rule 6 is actually saying?
> The references to VE1..VEk in the scalar subquery seem to me to be
> semantically invalid.  They would be sensible if this were a window
> function, but it's an aggregate, so I don't understand what row they'd
> be evaluated with respect to.

As far as I know <hypothetical set function> is used to do "what-if"
analysis. rank(val1) within group (order by sk1) chooses the rank
value so that val1 is equivalent to or just greater than sk1 when you
calculate rank() over (partition by group order by sk1) within the
group. So this is actually an aggregate and in 10.9 rule 6 it extracts
only one row from all results of rank() (WHERE MARKER = 1) which is
calculated with all rows within the group + argument value list.
Again, the argument of this kind of functions should be constant
during aggregate (at least it looks like so to me).

SELECT salary FROM emp;
salary
--------
300
500
700

SELECT rank(530) WITHIN GROUP(ORDER BY salary),
rank(200) WITHIN GROUP(ORDER BY salary) FROM emp;
rank | rank
------+------
3 | 1

Googling web, there's been the syntax in Oracle for some time. So I'd
bet Oracle crews hated to invent new syntax for listagg() because
ordered aggregate can be represented by *existing* WITHIN GROUP syntax
although the spec distinguish them. I don't think we should change
ordered aggregate syntax we have just introduced, but one of choices
is to support both of them. In other words, the queries can be the
same:

SELECT array_agg(val ORDER BY sk) FROM ...
SELECT array_agg(val) WITHIN GROUP (ORDER BY sk) FROM ...

P.S. I don't have Oracle to try with so I misunderstood something.

Regards,

--
Hitoshi Harada

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
> As far as I know <hypothetical set function> is used to do "what-if"
> analysis. rank(val1) within group (order by sk1) chooses the rank
> value so that val1 is equivalent to or just greater than sk1 when you
> calculate rank() over (partition by group order by sk1) within the
> group.

Hmm. I found this in SQL:2008 4.15:

The hypothetical set functions are related to the window functions RANK,
DENSE_RANK, PERCENT_RANK, and CUME_DIST, and use the same names, though
with a different syntax. These functions take an argument A and an
ordering of a value expression VE. VE is evaluated for all rows of the
group. This collection of values is augmented with A; the resulting
collection is treated as a window partition of the corresponding window
function whose window ordering is the ordering of the value expression.
The result of the hypothetical set function is the value of the
eponymous window function for the hypothetical "row" that contributes A
to the collection.

It appears that the syntax is meant to be

hypothetical_function(A) WITHIN GROUP (VE)

However this really ought to imply that A contains no variables of the
current query, and I don't see such a restriction mentioned anywhere ---
maybe an oversight in the spec? If A does contain a variable then there
is no unique value to append as the single additional row.

I still say that Oracle are completely wrong to have adopted this syntax
for listagg, because per spec it does something different than what
listagg needs to do. In particular it should mean that the listagg
argument can't contain variables --- which is what they want for the
delimiter, perhaps, but not for the expression to be concatenated.

> In other words, the queries can be the same:

> SELECT array_agg(val ORDER BY sk) FROM ...
> SELECT array_agg(val) WITHIN GROUP (ORDER BY sk) FROM ...

One more time: THOSE DON'T MEAN THE SAME THING. If we ever get
around to implementing the hypothetical set functions, we would
be very unhappy to have introduced such a bogus equivalence.

regards, tom lane

2010/2/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
>> In other words, the queries can be the same:
>
>> SELECT array_agg(val ORDER BY sk) FROM ...
>> SELECT array_agg(val) WITHIN GROUP (ORDER BY sk) FROM ...
>
> One more time: THOSE DON'T MEAN THE SAME THING.  If we ever get
> around to implementing the hypothetical set functions, we would
> be very unhappy to have introduced such a bogus equivalence.

I completely agree. Although Oracle's syntax can express ordered
aggregate, by introducing such syntax now it will be quite complicated
to implement hypothetical functions for those syntactic restrictions
and design in the future.

Regards,

--
Hitoshi Harada