You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Army <qo...@gmail.com> on 2006/10/27 00:25:49 UTC
Functions in GROUP BY expressions? (related to DERBY-883)
I was looking at DERBY-883 (expressions in GROUP BY) and saw that a patch has
been committed and one of the most recent comments says that the issue can be
closed.
When I do some quick testing, I can confirm that things appear to be working for
simple expressions. Ex:
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> insert into t1 values (1, 2), (2, 3), (4, 5), (3, 8);
4 rows inserted/updated/deleted
ij> select i + j from t1 group by i + j;
1
-----------
3
5
9
11
That said, though, the actual problem description for DERBY-883 uses *functions*
in the group by clause. And as it turns out, things look to be a bit
inconsistent when we introduce functions into the picture. Based on some quick
tests it seems like certain functions work and others don't; it also seems like
some functions work when I wouldn't expect them to. Maybe there's some kind of
logic behind it that I'm just missing...
These are some of the results I'm seeing:
<begin examples>
ij> create table dt (vc varchar(30));
0 rows inserted/updated/deleted
ij> insert into dt values ('1928-09-21'), ('1903-12-08');
2 rows inserted/updated/deleted
// Works.
ij> select year(vc) from dt group by year(vc);
1
-----------
1903
1928
ij> select year('1910-04-04') from dt group by year('1910-04-04');
1
-----------
1910
ij> select 'i' || 'j' from t1 group by 'i' || 'j';
1
--
ij
// Works but I don't think it should?? Perhaps a problem with
// constant expressions...?
ij> select 'i' || 'j' from t1 group by 'h' || 'h';
1
--
ij
ij> select year('1910-04-04') from dt group by year('1903-04-02');
1
-----------
1910
ij> select year('1910-04-04') from dt group by year(vc);
1
-----------
1910
// Doesn't work but seems like it could/should based on above queries...
ij> select sin(i) from t1 group by sin(i);
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
ij> select sin(.75) from t1 group by sin(.75);
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
// Doesn't work and shouldn't work.
ij> select year(vc) from dt group by year('1903-04-02');
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
ij> select sin(i) from t1 group by sin(2);
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
-- "boki" is a Java function (created with "CREATE FUNCTION")
ij> select boki(i, j) from t1 group by boki(i, j);
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
ij> select i, j from t1 group by boki(i, j);
ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
// Doesn't work but fails with NPE instead of correct error message.
// I'll file a Jira for this one.
ij> select substr(vc, 3) from dt group by substr(vc, 3);
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
<end examples>
How much of this behavior is expected? I see from the DERBY-883 comments and
tests that "extraction" functions like "year" are expected to work and Java
functions are not. But is there a reason why some functions (like "year",
"substr") work in the GROUP BY clause while others (like "sin") do not? And is
a GROUP BY with an expression that is is not equivalent to anything in the
select list supposed to work?
For the record: all of these queries fail with syntax errors in 10.1.3.
Apologies if I'm just missing something obvious...
Army
PS I just noticed that Øystein posted a comment to DERBY-883 today about
documentation in 10.2. Depending on how much of the above behavior is expected,
corresponding documentation seems like a good idea...
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Daniel John Debrunner <dj...@apache.org>.
Yip Ng wrote:
> I think what Dan meant was that all *user defined functions* defined via
> CREATE FUNCTION statement are assumed to be non-deterministic since
> users cannot declare a function as deterministic in the current
> implementation of Derby, so implicitly, they are non-deterministic.
Yes.
[snip]
> For SIN, COS, TAN functions, they are also considered deterministics.
SIN, COS etc. are implemented using the same mechanism as user defined
functions.
> However, they do not work currently in Derby with group by expression as
> they are mapped to JavaToSQLValueNode and such nodes always return
> false in isEquivalent() method, so I think this is an implementation
> restriction for those built-in functions.
Right, ideally these builtin functions should be able to declare
themselves as deterministic or not and the isEquivalent() method factor
that into its decision. I'd like to see this handled the same way for
the builtin Java SQL external functions (SIN, COS etc.) as for the user
defined functions, rather than special casing the builtin ones.
Dan.
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Manish Khettry <ma...@gmail.com>.
Actually, when I was implementing this functionality I thought that
comparing java nodes for equivalence was not possible or perhaps hard to do.
I could be wrong on this, so someone who knows this stuff better can comment
on this.
At any rate, I find it strange that one of these query works but not the
other. I think right now as things stand, both should fail. If there is
consensus that we should allow java value nodes in group by expressions,
then we should add isEquivalence methods to all the JavaValueNodes.
m
On 11/15/06, Oystein Grovlen - Sun Norway <Oy...@sun.com> wrote:
>
> Manish Khettry wrote:
> > Hmm strange. I am not sure why this
> >
> > select avg(i) from t group by sin(i)
> >
> > works but not this,
> >
> > select avg(i), sin(i) from test group by sin(i);
> >
> > I think its a bug-- both should be disallowed.
>
> Why should they be disallowed? I agree that in most cases grouping by
> non-deterministic functions do not make sense, but I am not sure that
> is a reason for disallowing it. Also, a function that is generally
> non-deterministic may be deterministic in a given context.
>
> Theoretically, I do not see why equivalence and non-deterministic
> functions are an issue for GROUP BY. I do not think an implementation
> need to execute the function twice if it appears both in the group-by
> clause and in the select list. The value returned when determining
> group could be used for the select list also.
>
> --
> Øystein
>
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
Manish Khettry wrote:
> Hmm strange. I am not sure why this
>
> select avg(i) from t group by sin(i)
>
> works but not this,
>
> select avg(i), sin(i) from test group by sin(i);
>
> I think its a bug-- both should be disallowed.
Why should they be disallowed? I agree that in most cases grouping by
non-deterministic functions do not make sense, but I am not sure that
is a reason for disallowing it. Also, a function that is generally
non-deterministic may be deterministic in a given context.
Theoretically, I do not see why equivalence and non-deterministic
functions are an issue for GROUP BY. I do not think an implementation
need to execute the function twice if it appears both in the group-by
clause and in the select list. The value returned when determining
group could be used for the select list also.
--
Øystein
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Manish Khettry <ma...@gmail.com>.
Hmm strange. I am not sure why this
select avg(i) from t group by sin(i)
works but not this,
select avg(i), sin(i) from test group by sin(i);
I think its a bug-- both should be disallowed.
m
On 11/14/06, Oystein Grovlen - Sun Norway <Oy...@sun.com> wrote:
>
> Yip Ng wrote:
>
> > For SIN, COS, TAN functions, they are also considered deterministics.
> > However, they do not work currently in Derby with group by expression as
> > they are mapped to JavaToSQLValueNode and such nodes always return
> > false in isEquivalent() method, so I think this is an implementation
> > restriction for those built-in functions.
>
> Just some late follow-up here. As far as I can tell, it is not strictly
> correct to say that non-deterministic functions does not work with group
> by expressions. What does not work is to use a non-deterministic
> function in the select list of a statement when grouping on the same
> function. However, the following query works:
>
> select avg(i) from t group by sin(i)
>
> I assume that it is the check for equivalence between the select list
> and the group by list that requires deterministic functions. (This may
> have been clear to others when this was discussed, but at least not to
> me.)
>
> --
> Øystein
>
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Yip Ng <yi...@gmail.com>.
On 11/15/06, Oystein Grovlen - Sun Norway <Oy...@sun.com> wrote:
>I thought we had established that SIN was non-deterministic. Are you
> saying that SIN is deterministic in some contexts, but not in others?
If we are talking about the current implementation of Derby, then all user
defined functions and built-ins are non-deterministic. As explained in
my previous comments, Derby does not have a way to declare user functions
deterministically, so they are all treated in non-deterministic manner
implicitly.
What I was trying to convey was most of the built-in functions in Derby are
*supposed*
to be deterministic (aggregates and SIN, COS, TAN, SUBSTR, etc.) since given
the
same argument(s), they return the same result. And so far, I have been
using
function determinisim to determine what is allowed in the group by
expression in
general and not limited to Derby. This is what I would like to describe in
the
group by expression documentation when Derby allows functions to be declared
determinisically in the future.
But for now, from a documentation perspective, the Derby ref manual should
mention
what is allowed in the group by expression in 10.2 due to implementation
restrictions.
Regards,
Yip Ng
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Oystein Grovlen - Sun Norway <Oy...@sun.com>.
Yip Ng wrote:
> On 11/14/06, *Oystein Grovlen - Sun Norway* <Oystein.Grovlen@sun.com
> <ma...@sun.com>> wrote:
>
>
> Just some late follow-up here. As far as I can tell, it is not
strictly
> correct to say that non-deterministic functions does not work
with group
> by expressions. What does not work is to use a non-deterministic
> function in the select list of a statement when grouping on the same
> function. However, the following query works:
>
> select avg(i) from t group by sin(i)
>
> I assume that it is the check for equivalence between the select list
> and the group by list that requires deterministic functions.
>
>
>
> In your example, AVG and (its other aggregate friends) and SIN are
> deterministic, so
I thought we had established that SIN was non-deterministic. Are you
saying that SIN is deterministic in some contexts, but not in others?
> select avg(i) from t group by sin(i) works expectedly. I was referring
> to Army's example
> where the sin(i) is in the select list
That was exactly my point. It is putting the non-deterministic
functions both in the select list and the group-by clause that is the
problem, not non-deterministic functions in the group-by clause, per
se.
> On a side note, I think it is awkward and perhaps wrong to group by with
> non-deterministic functions as the
> grouping will have unpredictable result, so they shouldn't be allow in
> the group by clause in my opinion.
If someone wants to group records in random ways, why should we
prevent them from doing so?
> For a quick reference, I created a non-deterministic function named f_nd
> in DB2 and attempt to group by
> with this function and as I expected, it throws an error. e.g.:
>
> db2 => select avg(i) from t1 group by f_nd(i)
> SQL0583N The use of routine "S1.F_ND" is invalid because it is not
> deterministic or has an external action. SQLSTATE=42845
The DB2 reference manual shows an example og how to get around this
restriction:
SELECT RANDID ,AVG(EDLEVEL ),AVG(SALARY )
FROM ( SELECT EDLEVEL,SALARY,INTEGER(RAND()*5) AS RANDID
FROM EMPLOYEE
) AS EMPRAND
GROUP BY RANDID
This made me try the following version of Army's original query on
Derby:
SELECT sinus
FROM ( SELECT sin(i) AS sinus
FROM t
) AS sinust
GROUP BY sinus;
This works. It is not clear to me why we should force people to write
people more complex queries than necessary to achieve what they want.
--
Øystein Grøvlen
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Yip Ng <yi...@gmail.com>.
On 11/14/06, Oystein Grovlen - Sun Norway <Oy...@sun.com> wrote:
>
>
> Just some late follow-up here. As far as I can tell, it is not strictly
> correct to say that non-deterministic functions does not work with group
> by expressions. What does not work is to use a non-deterministic
> function in the select list of a statement when grouping on the same
> function. However, the following query works:
>
> select avg(i) from t group by sin(i)
>
> I assume that it is the check for equivalence between the select list
> and the group by list that requires deterministic functions.
In your example, AVG and (its other aggregate friends) and SIN are
deterministic, so
select avg(i) from t group by sin(i) works expectedly. I was referring to
Army's example
where the sin(i) is in the select list
<snip>
ij> select sin(i) from t1 group by sin(i);
ERROR 42Y30: The SELECT list of a grouped query contains at least one
invalid
expression. If a SELECT list has a GROUP BY, the list may only contain valid
grouping expressions and valid aggregate expressions.
</snip>
and that the current implementation does not allow this due to
JavaToSQLValueNode's
behavior in isEquivalent() method. Hope I clear this up. =)
On a side note, I think it is awkward and perhaps wrong to group by with
non-deterministic functions as the
grouping will have unpredictable result, so they shouldn't be allow in the
group by clause in my opinion.
For a quick reference, I created a non-deterministic function named f_nd in
DB2 and attempt to group by
with this function and as I expected, it throws an error. e.g.:
db2 => select avg(i) from t1 group by f_nd(i)
SQL0583N The use of routine "S1.F_ND" is invalid because it is not
deterministic or has an external action. SQLSTATE=42845
Regards,
Yip Ng
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
Yip Ng wrote:
> For SIN, COS, TAN functions, they are also considered deterministics.
> However, they do not work currently in Derby with group by expression as
> they are mapped to JavaToSQLValueNode and such nodes always return
> false in isEquivalent() method, so I think this is an implementation
> restriction for those built-in functions.
Just some late follow-up here. As far as I can tell, it is not strictly
correct to say that non-deterministic functions does not work with group
by expressions. What does not work is to use a non-deterministic
function in the select list of a statement when grouping on the same
function. However, the following query works:
select avg(i) from t group by sin(i)
I assume that it is the check for equivalence between the select list
and the group by list that requires deterministic functions. (This may
have been clear to others when this was discussed, but at least not to me.)
--
Øystein
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Yip Ng <yi...@gmail.com>.
On 10/31/06, Army <qo...@gmail.com> wrote:
>
> Many thanks to all who took the time to provide clarifications in this
> thread.
> At this point I think the only question I have remaining is with respect
> to
> something Dan wrote in one of his replies, namely:
>
> > the grouping by functions does not work because at the moment Derby
> > assumes all functions are non-deterministic.
>
> Note the use of the word "all".
>
I think what Dan meant was that all *user defined functions* defined via
CREATE FUNCTION statement are assumed to be non-deterministic since users
cannot declare a function as deterministic in the current implementation of
Derby, so implicitly, they are non-deterministic.
For built-in functions such as SUBSTR, LTRIM and RTRIM, I believe they are
deterministic since they always return the same result from successive
invocations with identical input(s)
and they work as expected with group by expression.
For SIN, COS, TAN functions, they are also considered deterministics.
However, they do not work currently in Derby with group by expression as
they are mapped to JavaToSQLValueNode and such nodes always return false in
isEquivalent() method, so I think this is an implementation restriction for
those built-in functions.
Perhaps it is a good idea to have documentation that describes the function
determinism of all built-in functions in the reference manual.
Regards,
Yip Ng
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Army <qo...@gmail.com>.
Many thanks to all who took the time to provide clarifications in this thread.
At this point I think the only question I have remaining is with respect to
something Dan wrote in one of his replies, namely:
> the grouping by functions does not work because at the moment Derby
> assumes all functions are non-deterministic.
Note the use of the word "all".
My understanding is that both "substr" and "sin" are considered "functions" in
Derby--at least, they both show up in the "Built-in Functions" list for the
documentation. If that's the case, though, then I would expect that they *both*
be non-deterministic--but in actuality, "substr" seems to work.
I can see that "sin(i)" gets mapped to a JavaToSQLValueNode and such nodes
always return "false" for the "isEquivalent()" method, so that part makes sense
to me. And "substr()" gets mapped to a TernaryOperatorNode where isEquivalent()
can potentially return "true". So that explains why the two behave differently.
But is this difference accidental or deliberate? If it's deliberate, why is
"substr" allowed but "sin" rejected? Is there something about string functions
that makes them deterministic where a function like "sin" is not?
If it's true that we allow some functions in a GROUP BY clause but not others, I
think that's probably something worth including in whatever documentation is
written for DERBY-883. Assuming, of course, that I'm not just missing something
obvious.
Sorry if I'm being dense here...
Army
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Daniel John Debrunner wrote (2006-10-27 08:49:42):
> Army wrote:
> >Bernt M. Johnsen wrote:
> >>>>[...]
> >>>>// Doesn't work but seems like it could/should based on above
> >>>>queries...
> >>>
> >>>ij> select sin(i) from t1 group by sin(i);
> >>>ERROR 42Y30: The SELECT list of a grouped query contains at least one
> >>>invalid expression. If a SELECT list has a GROUP BY, the list may
> >>>only contain valid grouping expressions and valid aggregate expressions.
> >>>[...]
> >>
> >>To me it seems natural that this one doesn't work. To group by double
> >>or float values is not reasonable.
> >
> >Thank you for the reply, Bernt. Perhaps this is the detail I was
> >missing. When you say "is not reasonable", can you elaborate on why
> >that's the case (excuse me if that's a naive question)? Is it because
> >the types are imprecise?
> >
> >I did notice that following works:
> >
> >ij> create table td (d double);
> >0 rows inserted/updated/deleted
> >ij> insert into td values 2.08, 3.08;
> >2 rows inserted/updated/deleted
> >ij> select d from td group by d;
> >D
> >----------------------
> >2.08
> >3.08
> >
> >If it is not reasonable to group by a double, then should this query
> >work? Or should we be throwing the same error that we throw for "sin(i)"?
>
> No, you are correct Army, the grouping by functions does not work
> because at the moment Derby assumes all functions are non-deterministic.
> Grouping by DOUBLE should be allowed since they can be compared. It's
> probably not advisable, but Derby's SQL should allow it.
Dan is right. A double is just a finite set of bits (I was thinking
"too mathematical") so it should work, even if it is not the most
reasonable thing to do.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Manish Khettry <ma...@gmail.com>.
The compilation error with functions like sin are expected. Anytime Derby
sees a JavaValue node, it gives up trying to compare subexpressions for
equivalence.
Regarding constant expressions:
// Works but I don't think it should?? Perhaps a problem with
// constant expressions...?
ij> select 'i' || 'j' from t1 group by 'h' || 'h;
group by with a constant expression results in a single group and since the
expression in the select clause ('i' || 'j') contains no column refrences
this is a valid query. It works in mysql as well with the same results.
m
On 10/27/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Army wrote:
> > Bernt M. Johnsen wrote:
> >>>> [...]
> >>>> // Doesn't work but seems like it could/should based on above
> >>>> queries...
> >>>
> >>> ij> select sin(i) from t1 group by sin(i);
> >>> ERROR 42Y30: The SELECT list of a grouped query contains at least one
> >>> invalid expression. If a SELECT list has a GROUP BY, the list may
> >>> only contain valid grouping expressions and valid aggregate
> expressions.
> >>> [...]
> >>
> >> To me it seems natural that this one doesn't work. To group by double
> >> or float values is not reasonable.
> >
> > Thank you for the reply, Bernt. Perhaps this is the detail I was
> > missing. When you say "is not reasonable", can you elaborate on why
> > that's the case (excuse me if that's a naive question)? Is it because
> > the types are imprecise?
> >
> > I did notice that following works:
> >
> > ij> create table td (d double);
> > 0 rows inserted/updated/deleted
> > ij> insert into td values 2.08, 3.08;
> > 2 rows inserted/updated/deleted
> > ij> select d from td group by d;
> > D
> > ----------------------
> > 2.08
> > 3.08
> >
> > If it is not reasonable to group by a double, then should this query
> > work? Or should we be throwing the same error that we throw for
> "sin(i)"?
>
> No, you are correct Army, the grouping by functions does not work
> because at the moment Derby assumes all functions are non-deterministic.
> Grouping by DOUBLE should be allowed since they can be compared. It's
> probably not advisable, but Derby's SQL should allow it.
>
> Dan.
>
>
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Daniel John Debrunner <dj...@apache.org>.
Army wrote:
> Bernt M. Johnsen wrote:
>>>> [...]
>>>> // Doesn't work but seems like it could/should based on above
>>>> queries...
>>>
>>> ij> select sin(i) from t1 group by sin(i);
>>> ERROR 42Y30: The SELECT list of a grouped query contains at least one
>>> invalid expression. If a SELECT list has a GROUP BY, the list may
>>> only contain valid grouping expressions and valid aggregate expressions.
>>> [...]
>>
>> To me it seems natural that this one doesn't work. To group by double
>> or float values is not reasonable.
>
> Thank you for the reply, Bernt. Perhaps this is the detail I was
> missing. When you say "is not reasonable", can you elaborate on why
> that's the case (excuse me if that's a naive question)? Is it because
> the types are imprecise?
>
> I did notice that following works:
>
> ij> create table td (d double);
> 0 rows inserted/updated/deleted
> ij> insert into td values 2.08, 3.08;
> 2 rows inserted/updated/deleted
> ij> select d from td group by d;
> D
> ----------------------
> 2.08
> 3.08
>
> If it is not reasonable to group by a double, then should this query
> work? Or should we be throwing the same error that we throw for "sin(i)"?
No, you are correct Army, the grouping by functions does not work
because at the moment Derby assumes all functions are non-deterministic.
Grouping by DOUBLE should be allowed since they can be compared. It's
probably not advisable, but Derby's SQL should allow it.
Dan.
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Army <qo...@gmail.com>.
Bernt M. Johnsen wrote:
>>>[...]
>>>// Doesn't work but seems like it could/should based on above queries...
>>
>>ij> select sin(i) from t1 group by sin(i);
>>ERROR 42Y30: The SELECT list of a grouped query contains at least one
>>invalid expression. If a SELECT list has a GROUP BY, the list may only
>>contain valid grouping expressions and valid aggregate expressions.
>>[...]
>
> To me it seems natural that this one doesn't work. To group by double
> or float values is not reasonable.
Thank you for the reply, Bernt. Perhaps this is the detail I was missing. When
you say "is not reasonable", can you elaborate on why that's the case (excuse me
if that's a naive question)? Is it because the types are imprecise?
I did notice that following works:
ij> create table td (d double);
0 rows inserted/updated/deleted
ij> insert into td values 2.08, 3.08;
2 rows inserted/updated/deleted
ij> select d from td group by d;
D
----------------------
2.08
3.08
If it is not reasonable to group by a double, then should this query work? Or
should we be throwing the same error that we throw for "sin(i)"?
Army
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Army wrote (2006-10-26 15:25:49):
>> [...]
>> // Doesn't work but seems like it could/should based on above queries...
>
> ij> select sin(i) from t1 group by sin(i);
> ERROR 42Y30: The SELECT list of a grouped query contains at least one
> invalid expression. If a SELECT list has a GROUP BY, the list may only
> contain valid grouping expressions and valid aggregate expressions.
> [...]
To me it seems natural that this one doesn't work. To group by double
or float values is not reasonable.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Functions in GROUP BY expressions? (related to DERBY-883)
Posted by Yip Ng <yi...@gmail.com>.
For the Java function part, if the function characteristic is DETERMINISTIC,
then I think it should be reasonable to allow this in the group by clause.
I think this is mentioned in the DB2 manual but they refer to the term
VARIANT.
It appears that there is no way to explicitly specify the deterministic
characteristic of a function or procedure in their respective CREATE
statement currently in Derby. And according to the SQL:2003 standard, if it
is not specified, it defaults to NOT DETERMINISTIC implicitly. So for the
boki Java function, it fails expectedly.
Yip