Hi, I think I've found some problems with queries using built in functions and aggregates. A few statements I tried generate errors when using hsqldb. PostgreSQL, MSSQL and Oracle have no problems with these statements. For example, when I have a simple table created like this:
create table test (sel int, name1 varchar(3), name2 varchar(3)) with some random data: insert into test (sel, name1, name2) values (0, 'foo', 'bar') insert into test (sel, name1, name2) values (1, 'baz', 'foo') insert into test (sel, name1, name2) values (1, 'foo', 'qux') then with these statements: select coalesce(a.name1, a.name2) as name,count(a.sel) as counter from test a group by coalesce(a.name1, a.name2) select case when a.sel=1 then a.name2 else a.name1 end as name,count(a.name1) as counter from test a group by case when a.sel=1 then a.name2 else a.name1 end I get an exception such as: Not in aggregate function or group by clause: COLUMN A.NAME1 in statement [select coalesce(a.name1, a.name2) as name,count(a.sel) as counter from test a group by coalesce(a.name1, a.name2)] When I add all the columns I use in the built-in function to the group by clause the statement works, but it does not give me the same results. I also tried the alias name: select coalesce(a.name1, a.name2) as name,count(a.sel) as counter from test a group by name but this also doesn't work. Like I said before other databases accept the statements and give a proper result so I guess this is a bug. cheers, Armin ------------------------------------------------------- This SF.Net email is sponsored by: NEC IT Guy Games. Get your fingers limbered up and give it your best shot. 4 great events, 4 opportunities to win big! Highest score wins.NEC IT Guy Games. Play to win an NEC 61 plasma display. Visit http://www.necitguy.com/?r=20 _______________________________________________ hsqldb-developers mailing list hsqldb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
