[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3866) Wrong generated SQL while doing MultiTableDelete/Update!

[Ioan LUPU (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3866?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33461#action_33461 ] 

Ioan LUPU commented on HHH-3866:
--------------------------------


H2 reports the following (tested with hibernate-3.3.2.GA)

app  --->  16:37:42,603 TRACE HQLQueryPlan:275 - executeUpdate: delete from model.m00.Bean04 a where a.id=:paramId
app  --->  16:37:42,603 TRACE QueryParameters:278 - named parameters: {paramId=1}
Hibernate: 
    /* insert-
        select
            for model.m00.Bean04 ids */ insert 
            into
                HT_T_Bean04
                select
                    bean04x0_.F_id as F_id 
                from
                    T_Bean04 bean04x0_ 
                inner join
                    T_Bean03 bean04x0_1_ 
                        on bean04x0_.F_id=bean04x0_1_.F_id 
                where
                    F_id=?
app  --->  16:37:42,613  WARN JDBCExceptionReporter:100 - SQL Error: 90059, SQLState: 90059
app  --->  16:37:42,613 ERROR JDBCExceptionReporter:101 - Ambiguous column name F_ID; SQL statement:
/* insert-select for model.m00.Bean04 ids */ insert into HT_T_Bean04 select bean04x0_.F_id as F_id from T_Bean04 bean04x0_ inner join T_Bean03 bean04x0_1_ on bean04x0_.F_id=bean04x0_1_.F_id where F_id=? [90059-111]
app  --->  16:37:42,613 ERROR HibernateUtil:63 - could not insert/select ids for bulk delete
org.hibernate.exception.GenericJDBCException: could not insert/select ids for bulk delete
  at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
  at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:125)
  at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:421)
  at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
  at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1169)
  at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
  ...

Caused by: org.h2.jdbc.JdbcSQLException: Ambiguous column name F_ID; SQL statement:
/* insert-select for model.m00.Bean04 ids */ insert into HT_T_Bean04 select bean04x0_.F_id as F_id from T_Bean04 bean04x0_ inner join T_Bean03 bean04x0_1_ on bean04x0_.F_id=bean04x0_1_.F_id where F_id=? [90059-111]
  at org.h2.message.Message.getSQLException(Message.java:107)
  at org.h2.message.Message.getSQLException(Message.java:118)
  at org.h2.message.Message.getSQLException(Message.java:77)
  at org.h2.expression.ExpressionColumn.mapColumn(ExpressionColumn.java:108)
  at org.h2.expression.ExpressionColumn.mapColumns(ExpressionColumn.java:85)
  at org.h2.expression.Comparison.mapColumns(Comparison.java:404)
  at org.h2.expression.ConditionAndOr.mapColumns(ConditionAndOr.java:216)
  at org.h2.command.dml.Select.init(Select.java:698)
  at org.h2.command.Parser.parseSelect(Parser.java:1336)
  at org.h2.command.Parser.parseInsert(Parser.java:913)
  at org.h2.command.Parser.parsePrepared(Parser.java:366)
  at org.h2.command.Parser.parse(Parser.java:288)
  at org.h2.command.Parser.parse(Parser.java:260)
  at org.h2.command.Parser.prepareCommand(Parser.java:232)
  at org.h2.engine.Session.prepareLocal(Session.java:409)
  at org.h2.engine.Session.prepareCommand(Session.java:370)
  at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1042)
  at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74)
  at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:227)
  at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
  at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116)
  at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
  at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:109)
  ... 8 more


> Wrong generated SQL while doing MultiTableDelete/Update!
> --------------------------------------------------------
>
>                 Key: HHH-3866
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3866
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.3.1
>         Environment: hibernate-distribution-3.3.1.GA
> HSQLDB is not affected, but the others are
>            Reporter: Ioan LUPU
>         Attachments: MultiTableProblem.zip
>
>
> The generated SQL:
> select
>     bean04x0_.F_id as F_id
> from
>     T_Bean04 bean04x0_
> inner join
>     T_Bean03 bean04x0_1_
>         on bean04x0_.F_id=bean04x0_1_.F_id
> where
>     F_id=?
> The problem: the field "F_id" can be found in both tables. HSQLDB doesn't complain about this, but other DB will.
> See the attached .zip file!

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira