The following SQL works fine in HSQLDB, but fails in PostgreSQL.
String hql = "insert into com.otto.profiles.group.dto.GroupPlayerApplicationDTO"
+ " (recipientGroup, player, created, approved)"
+ " select gm.group, gm.player, now(), gm.group.open"
+ " from com.otto.profiles.group.dto.GroupMembershipDTO gm where gm.group.id = :groupId";

Query query = getSession().createQuery(hql);
query.setLong("groupId", groupId);

query.executeUpdate();

In postgre it correctly maps the first part of the sql, but it also includes all the columns of the group and player entities in the values part of the insert into statement.

Any ideas why this happens and what can be done to remedy ???

regards rundis.





Hibernate version:hibernate-3.1rc2

Full stack trace of any exception that occurs:
2006-07-03 10:52:17,484 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute update query [insert into group_player_application ( id, group_id, player_id, created, approved ) select nextval ('group_player_application_id_seq'), group_id as col_0_0_, player_id as col_1_0_, now() as col_2_0_, is_open as col_3_0_, groupdto1_.id as id65_0_, playerdto2_.id as id77_1_, groupdto1_.created as created65_0_, groupdto1_.updated as updated65_0_, groupdto1_.forum_id as forum4_65_0_, groupdto1_.language as language65_0_, groupdto1_.level as level65_0_, groupdto1_.mcc_recipient_id as mcc7_65_0_, groupdto1_.monetary_goal as monetary8_65_0_, groupdto1_.name as name65_0_, groupdto1_.is_open as is10_65_0_, groupdto1_.status as status65_0_, groupdto1_.discontinued_date as discont12_65_0_, groupdto1_.deleted_date as deleted13_65_0_, groupdto1_.is_sponsored as is14_65_0_, groupdto1_.sponsored_from as sponsored15_65_0_, groupdto1_.sponsored_to as sponsored16_65_0_, groupdto1_.ticker as ticker65_0_, groupdto1_.percentage_group as percentage18_65_0_, groupdto1_.percentage_shared as percentage19_65_0_, groupdto1_.percentage_winner as percentage20_65_0_, groupdto1_.size as size65_0_, groupdto1_.founder_id as founder22_65_0_, groupdto1_.blog_id as blog23_65_0_, groupdto1_.region_id as region24_65_0_, playerdto2_.email_address as email2_77_1_, playerdto2_.mobile_number as mobile3_77_1_, playerdto2_.recruiter_id as recruiter4_77_1_, playerdto2_.created_date as created5_77_1_, playerdto2_.currency_code as currency6_77_1_, playerdto2_.language_code as language7_77_1_, playerdto2_.locale as locale77_1_, playerdto2_.time_zone as time9_77_1_, playerdto2_.luckiest_ticket as luckiest10_77_1_, playerdto2_.luckiest_ticket_amount as luckiest11_77_1_, playerdto2_1_.thumbnail as thumbnail78_1_, playerdto2_1_.birth_date as birth3_78_1_, playerdto2_1_.first_teamticket as first4_78_1_, playerdto2_1_.firstname as firstname78_1_, playerdto2_1_.gender as gender78_1_, playerdto2_1_.hint_answer as hint7_78_1_, playerdto2_1_.last_login as last8_78_1_, playerdto2_1_.lastname as lastname78_1_, playerdto2_1_.last_played as last10_78_1_, playerdto2_1_.last_updated as last11_78_1_, playerdto2_1_.nick_visibility as nick12_78_1_, playerdto2_1_.hintquestion_id as hintque13_78_1_, playerdto2_1_.password as password78_1_, playerdto2_1_.registered_date as registered15_78_1_, playerdto2_1_.status as status78_1_, playerdto2_1_.nickname as nickname78_1_, playerdto2_1_.accredited as accredited18_78_1_, playerdto2_1_.city as city78_1_, playerdto2_1_.country_code as country20_78_1_, playerdto2_1_.postal_code as postal21_78_1_, playerdto2_1_.province as province78_1_, playerdto2_1_.street as street78_1_, playerdto2_1_.street2 as street24_78_1_, playerdto2_1_.mlm_multiplier as mlm25_78_1_, playerdto2_1_.blog_id as blog26_78_1_, playerdto2_1_.activation_ticket as activation27_78_1_, playerdto2_1_.activation_start as activation28_78_1_, case when playerdto2_1_.id is not null then 1 when playerdto2_.id is not null then 0 end as clazz_1_ from group_membership groupmembe0_ inner join groups groupdto1_ on group_id=groupdto1_.id inner join player playerdto2_ on player_id=playerdto2_.id left outer join registered_player playerdto2_1_ on playerdto2_.id=playerdto2_1_.id where group_id=?]
org.postgresql.util.PSQLException: ERROR: INSERT has more expressions than target columns
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:300)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:227)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:71)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:324)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1047)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.otto.profiles.group.dao.hibernate.GroupHibernateDAO.createGroupPlayerApplications(GroupHibernateDAO.java:949)
at com.otto.profiles.group.service.GroupPrivilegedService.updateTeamSettings(GroupPrivilegedService.java:873)
at com.otto.profiles.group.ejb.GroupPrivilegedBean.updateTeamSettings(GroupPrivilegedBean.java:300)
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:585)
at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:154)
at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:54)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
at com.otto.interceptor.ExceptionInterceptor.invoke(ExceptionInterceptor.java:40)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
at org.jboss.ejb.Container.invoke(Container.java:873)
at sun.reflect.GeneratedMethodAccessor228.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.invocation.local.LocalInvoker$MBeanServerAction.invoke(LocalInvoker.java:155)
at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:104)
at org.jboss.invocation.InvokerInterceptor.invokeMarshalled(InvokerInterceptor.java:201)
at org.jboss.invocation.MarshallingInvokerInterceptor.invoke(MarshallingInvokerInterceptor.java:35)
at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:55)
at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInterceptor.java:97)
at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:86)
at $Proxy294.updateTeamSettings(Unknown Source)
at com.otto.profiles.group.ejb.GroupBeanTestCase.testUpdateTeamSettings(GroupBeanTestCase.java:442)
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:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
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.junitee.runner.TestRunner$1.run(TestRunner.java:72)
at org.junitee.runner.TestRunner.run(TestRunner.java:95)
at org.junitee.servlet.JUnitEEServlet.runTests(JUnitEEServlet.java:232)
at org.junitee.servlet.JUnitEEServlet.doGet(JUnitEEServlet.java:203)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)




Name and version of the database you are using:
PostGreSQL 8.1

The generated SQL (show_sql=true):
see above

Debug level Hibernate log excerpt: