I have added informations for this query.It is important :
It work with hsql, but doesn't work with oracle (8,9) and postgresql 8 beta
Both databases retunr error ' not GROUP by expression'
I make test with Christian's CaveatEmptor , too
This is test for CaveatEmptor :
Code:
/*
package org.hibernate.auction.test;
import java.util.List;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;
import net.sf.hibernate.Session;
import org.hibernate.auction.exceptions.InfrastructureException;
import org.hibernate.auction.persistence.HibernateUtil;
/**
* @author snpe
*
*/
public class TestHql extends TestCaseWithData {
public TestHql(String s) {
super(s);
}
public void testHql () throws Exception {
initData();
HibernateUtil.beginTransaction();
Session session = HibernateUtil.getSession();
List list = null;
try {
String queryString = "select bid.item.id,bid.item.name,min(bid.created) " +
"\nfrom Bid bid" +
"\ngroup by bid.item.id,bid.item.name";
Query query = session.createQuery(queryString);
list = query.list();
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
HibernateUtil.commitTransaction();
HibernateUtil.closeSession();
}
}
Log from test with postgresql :
17:41:12,698 INFO Environment: Hibernate 2.1.4
17:41:12,703 INFO Environment: hibernate.properties not found
17:41:12,716 INFO Environment: using CGLIB reflection optimizer
17:41:12,730 INFO Configuration: configuring from resource: /hibernate.cfg.xml
17:41:12,730 INFO Configuration: Configuration resource: /hibernate.cfg.xml
17:41:13,143 INFO Configuration: Mapping resource: org/hibernate/auction/model/User.hbm.xml
17:41:13,511 INFO Binder: Mapping class: org.hibernate.auction.model.User -> USERS
17:41:14,579 INFO Configuration: Mapping resource: org/hibernate/auction/model/Item.hbm.xml
17:41:14,721 INFO Binder: Mapping class: org.hibernate.auction.model.Item -> ITEM
17:41:14,764 INFO Binder: Mapping collection: org.hibernate.auction.model.Item.categories -> CATEGORY_ITEM
17:41:14,775 INFO Configuration: Mapping resource: org/hibernate/auction/model/Category.hbm.xml
17:41:14,875 INFO Binder: Mapping class: org.hibernate.auction.model.Category -> CATEGORY
17:41:14,885 INFO Binder: Mapping collection: org.hibernate.auction.model.Category.items -> CATEGORY_ITEM
17:41:14,886 INFO Configuration: Mapping resource: org/hibernate/auction/model/Bid.hbm.xml
17:41:15,041 INFO Binder: Mapping class: org.hibernate.auction.model.Bid -> BID
17:41:15,046 INFO Configuration: Mapping resource: org/hibernate/auction/model/BillingDetails.hbm.xml
17:41:15,090 INFO Binder: Mapping class: org.hibernate.auction.model.BillingDetails -> BILLING_DETAILS
17:41:15,263 INFO Binder: Mapping joined-subclass: org.hibernate.auction.model.CreditCard -> CREDIT_CARD
17:41:15,272 INFO Binder: Mapping joined-subclass: org.hibernate.auction.model.BankAccount -> BANK_ACCOUNT
17:41:15,273 INFO Configuration: Mapping resource: org/hibernate/auction/model/Comment.hbm.xml
17:41:15,353 INFO Binder: Mapping class: org.hibernate.auction.model.Comment -> COMMENTS
17:41:15,366 INFO Configuration: Mapping resource: org/hibernate/auction/persistence/audit/AuditLogRecord.hbm.xml
17:41:15,513 INFO Binder: Mapping class: org.hibernate.auction.persistence.audit.AuditLogRecord -> AUDIT_LOG
17:41:15,514 INFO Configuration: Configured SessionFactory: null
17:41:15,516 INFO Configuration: processing one-to-many association mappings
17:41:15,516 INFO Binder: Mapping collection: org.hibernate.auction.model.User.items -> ITEM
17:41:15,517 INFO Binder: Mapping collection: org.hibernate.auction.model.User.billingDetails -> BILLING_DETAILS
17:41:15,518 INFO Binder: Mapping collection: org.hibernate.auction.model.Item.bids -> BID
17:41:15,519 INFO Binder: Mapping collection: org.hibernate.auction.model.Category.childCategories -> CATEGORY
17:41:15,519 INFO Configuration: processing one-to-one association property references
17:41:15,520 INFO Configuration: processing foreign key constraints
17:41:15,661 INFO Dialect: Using dialect: net.sf.hibernate.dialect.PostgreSQLDialect
17:41:15,671 INFO SettingsFactory: Use outer join fetching: true
17:41:15,720 INFO DriverManagerConnectionProvider: Using Hibernate built-in connection pool (not for production use!)
17:41:15,720 INFO DriverManagerConnectionProvider: Hibernate connection pool size: 1
17:41:15,742 INFO DriverManagerConnectionProvider: using driver: org.postgresql.Driver at URL: jdbc:postgresql://localhost/snpe
17:41:15,742 INFO DriverManagerConnectionProvider: connection properties: {user=snpe, password=snjeza}
17:41:15,767 INFO TransactionManagerLookupFactory: No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
17:41:16,399 INFO SettingsFactory: Use scrollable result sets: true
17:41:16,400 INFO SettingsFactory: Use JDBC3 getGeneratedKeys(): false
17:41:16,400 INFO SettingsFactory: Optimize cache for minimal puts: false
17:41:16,400 INFO SettingsFactory: echoing all SQL to stdout
17:41:16,401 INFO SettingsFactory: Query language substitutions: {no='N', yes='Y'}
17:41:16,401 INFO SettingsFactory: cache provider: net.sf.ehcache.hibernate.Provider
17:41:16,414 INFO Configuration: instantiating and configuring caches
17:41:16,497 INFO SessionFactoryImpl: building session factory
17:41:19,028 INFO SessionFactoryObjectFactory: no JNDI name configured
17:41:19,029 INFO Configuration: processing one-to-many association mappings
17:41:19,029 INFO Configuration: processing one-to-one association property references
17:41:19,029 INFO Configuration: processing foreign key constraints
17:41:19,036 INFO Dialect: Using dialect: net.sf.hibernate.dialect.PostgreSQLDialect
17:41:19,037 INFO SettingsFactory: Use outer join fetching: true
17:41:19,038 INFO DriverManagerConnectionProvider: Using Hibernate built-in connection pool (not for production use!)
17:41:19,038 INFO DriverManagerConnectionProvider: Hibernate connection pool size: 1
17:41:19,038 INFO DriverManagerConnectionProvider: using driver: org.postgresql.Driver at URL: jdbc:postgresql://localhost/snpe
17:41:19,039 INFO DriverManagerConnectionProvider: connection properties: {user=snpe, password=snjeza}
17:41:19,039 INFO TransactionManagerLookupFactory: No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
17:41:19,050 INFO SettingsFactory: Use scrollable result sets: true
17:41:19,051 INFO SettingsFactory: Use JDBC3 getGeneratedKeys(): false
17:41:19,051 INFO SettingsFactory: Optimize cache for minimal puts: false
17:41:19,051 INFO SettingsFactory: echoing all SQL to stdout
17:41:19,052 INFO SettingsFactory: Query language substitutions: {no='N', yes='Y'}
17:41:19,052 INFO SettingsFactory: cache provider: net.sf.ehcache.hibernate.Provider
17:41:19,052 INFO Configuration: instantiating and configuring caches
17:41:19,052 INFO SessionFactoryImpl: building session factory
17:41:19,417 INFO SessionFactoryObjectFactory: no JNDI name configured
17:41:19,428 INFO Dialect: Using dialect: net.sf.hibernate.dialect.PostgreSQLDialect
17:41:19,429 INFO Configuration: processing one-to-many association mappings
17:41:19,430 INFO Configuration: processing one-to-one association property references
17:41:19,431 INFO Configuration: processing foreign key constraints
17:41:19,432 INFO Configuration: processing one-to-many association mappings
17:41:19,432 INFO Configuration: processing one-to-one association property references
17:41:19,432 INFO Configuration: processing foreign key constraints
17:41:19,480 INFO SchemaExport: Running hbm2ddl schema export
17:41:19,481 INFO SchemaExport: exporting generated schema to database
17:41:19,482 INFO DriverManagerConnectionProvider: Using Hibernate built-in connection pool (not for production use!)
17:41:19,482 INFO DriverManagerConnectionProvider: Hibernate connection pool size: 1
17:41:19,482 INFO DriverManagerConnectionProvider: using driver: org.postgresql.Driver at URL: jdbc:postgresql://localhost/snpe
17:41:19,483 INFO DriverManagerConnectionProvider: connection properties: {user=snpe, password=snjeza}
17:41:21,445 INFO SchemaExport: schema export complete
17:41:21,450 INFO DriverManagerConnectionProvider: cleaning up connection pool: jdbc:postgresql://localhost/snpe
Running test...
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
Hibernate: insert into CATEGORY (VERSION, NAME, CREATED, PARENT_CATEGORY_ID, CATEGORY_ID) values (?, ?, ?, ?, ?)
Hibernate: insert into USERS (VERSION, FIRSTNAME, LASTNAME, USERNAME, "PASSWORD", EMAIL, RANKING, IS_ADMIN, CREATED, DEFAULT_BILLING_DETAILS_ID, STREET, ZIPCODE, CITY, USER_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into ITEM (VERSION, NAME, DESCRIPTION, INITIAL_PRICE, INITIAL_PRICE_CURRENCY, RESERVE_PRICE, RESERVE_PRICE_CURRENCY, START_DATE, END_DATE, STATE, APPROVAL_DATETIME, CREATED, APPROVED_BY_USER_ID, SELLER_ID, SUCCESSFUL_BID_ID, ITEM_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into COMMENTS (VERSION, RATING, COMMENT_TEXT, ITEM_ID, FROM_USER_ID, COMMENT_ID) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into BILLING_DETAILS (VERSION, OWNER_NAME, CREATED, USER_ID, BILLING_DETAILS_ID) values (?, ?, ?, ?, ?)
Hibernate: insert into CREDIT_CARD (CC_TYPE, CC_NUMBER, EXP_MONTH, EXP_YEAR, CREDIT_CARD_ID) values (?, ?, ?, ?, ?)
Hibernate: insert into BILLING_DETAILS (VERSION, OWNER_NAME, CREATED, USER_ID, BILLING_DETAILS_ID) values (?, ?, ?, ?, ?)
Hibernate: insert into BANK_ACCOUNT (ACCOUNT_NUMBER, BANK_NAME, BANK_SWIFT, BANK_ACCOUNT_ID) values (?, ?, ?, ?)
Hibernate: insert into BID (AMOUNT, AMOUNT_CURRENCY, CREATED, ITEM_ID, BIDDER_ID, BID_ID) values (?, ?, ?, ?, ?, ?)
Hibernate: update USERS set VERSION=?, FIRSTNAME=?, LASTNAME=?, "PASSWORD"=?, EMAIL=?, RANKING=?, IS_ADMIN=?, DEFAULT_BILLING_DETAILS_ID=?, STREET=?, ZIPCODE=?, CITY=? where USER_ID=? and VERSION=?
Hibernate: update ITEM set VERSION=?, DESCRIPTION=?, INITIAL_PRICE=?, INITIAL_PRICE_CURRENCY=?, RESERVE_PRICE=?, RESERVE_PRICE_CURRENCY=?, STATE=?, APPROVAL_DATETIME=?, APPROVED_BY_USER_ID=?, SUCCESSFUL_BID_ID=? where ITEM_ID=? and VERSION=?
Hibernate: insert into CATEGORY_ITEM (CATEGORY_ID, ITEM_ID) values (?, ?)
Hibernate: update BILLING_DETAILS set USER_ID=? where BILLING_DETAILS_ID=?
Hibernate: select item1_.ITEM_ID as x0_0_, item1_.NAME as x1_0_, min(bid0_.CREATED) as x2_0_ from BID bid0_, ITEM item1_ where bid0_.ITEM_ID=item1_.ITEM_ID group by bid0_.ITEM_ID , item1_.NAME
17:41:36,981 WARN JDBCExceptionReporter: SQL Error: 0, SQLState: 42803
17:41:36,982 ERROR JDBCExceptionReporter: ERROR: column "item1_.item_id" must appear in the GROUP BY clause or be used in an aggregate function
17:41:36,999 WARN JDBCExceptionReporter: SQL Error: 0, SQLState: 42803
17:41:36,999 ERROR JDBCExceptionReporter: ERROR: column "item1_.item_id" must appear in the GROUP BY clause or be used in an aggregate function
17:41:37,019 ERROR JDBCExceptionReporter: Could not execute query
java.sql.SQLException: ERROR: column "item1_.item_id" must appear in the GROUP BY clause or be used in an aggregate function
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1130)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:933)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:139)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:291)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:206)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.hibernate.auction.test.TestHql.testHql(TestHql.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at org.hibernate.auction.test.TestCase.runTest(TestCase.java:17)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)