Hi, Thanks for your mail! This is a bug in the optimization to group using an index. This feature is there since a long time, but was always disabled by default. In version 1.1 (which is still beta) it is enabled by default. You can disable this feature by setting the system property h2.optimizeGroupSorted to false. An alternative is to use version 1.0.79. In any case, this bug will be fixed in the next release.
Regards, Thomas On Fri, Oct 10, 2008 at 10:18 AM, Toshiaki Takashima <[EMAIL PROTECTED]> wrote: > > Hi, > > It seems that group by clause does not work at the latest version > 1.1.100 > you can reproduce the problem by executing following SQL : > > CREATE TABLE EAD(EMPLOYEE_ID NUMERIC(10,0),ADDRESS_ID > NUMERIC(10,0),DEPARTMENT_ID NUMERIC(10,0),EMPLOYEE_NAME > VARCHAR(255)NOT NULL,EMPLOYEE_JOB_TYPE NUMERIC(10,0)NOT > NULL,EMPLOYEE_SALARY NUMERIC(10,0),EMPLOYEE_VERSION NUMERIC(10,0)NOT > NULL,ADDRESS_NAME VARCHAR(255)NOT NULL,ADDRESS_VERSION > NUMERIC(10,0)NOT NULL,DEPARTMENT_NAME VARCHAR(255)NOT > NULL,DEPARTMENT_VERSION NUMERIC(10,0)NOT NULL,PRIMARY > KEY(EMPLOYEE_ID,DEPARTMENT_ID,ADDRESS_ID)); > INSERT INTO EAD VALUES(1,1,3,'ALLEN',1,1600,1,'STREET 1',1,'SALES0', > 1); > INSERT INTO EAD VALUES(2,2,3,'WARD',1,1250,1,'STREET 2',1,'SALES1',2); > INSERT INTO EAD VALUES(3,3,2,'JONES',2,2975,1,'STREET 3',1,'RESEARCH0', > 1); > INSERT INTO EAD VALUES(4,4,3,'MARTIN',1,1250,1,'STREET 4',1,'SALES2', > 1); > INSERT INTO EAD VALUES(5,5,3,'BLAKE',2,2850,1,'STREET 5',1,'SALES3', > 1); > INSERT INTO EAD VALUES(6,6,1,'CLARK',2,2450,1,'STREET 6', > 1,'ACCOUNTING0',1); > INSERT INTO EAD VALUES(7,7,2,'SCOTT',3,3000,1,'STREET 7',1,'RESEARCH1', > 1); > INSERT INTO EAD VALUES(8,8,1,'KING',4,5000,1,'STREET 8', > 1,'ACCOUNTING1',1); > INSERT INTO EAD VALUES(9,9,3,'TURNER',1,1500,1,'STREET 9',1,'SALES4', > 1); > INSERT INTO EAD VALUES(10,10,2,'ADAMS',0,1100,1,'STREET 10', > 1,'RESEARCH2',1); > INSERT INTO EAD VALUES(11,11,3,'JAMES',0,950,1,'STREET 11',1,'SALES4', > 2); > INSERT INTO EAD VALUES(12,12,2,'FORD',3,3000,1,'STREET 12', > 1,'RESEARCH3',2); > INSERT INTO EAD VALUES(13,13,1,'MILLER',0,1300,1,'STREET 13', > 1,'ACCOUNTING1',2); > INSERT INTO EAD VALUES(14,14,2,'SMITH',0,800,1,'STREET 14', > 1,'RESEARCH3',1); > SELECT DEPARTMENT_ID,MAX(DEPARTMENT_NAME),MAX(DEPARTMENT_VERSION)FROM > EAD GROUP BY DEPARTMENT_ID; > > > Sincerely, > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
