db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Functions in GROUP BY expressions? (related to DERBY-883)
Date Thu, 26 Oct 2006 22:25:49 GMT
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...


Mime
View raw message