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...
