[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6920) Jboss7 with Insert into select query

[Steve Ebersole (JIRA)]

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

Steve Ebersole commented on HHH-6920:
-------------------------------------

The difficulty (given the current code) is that the optimizer being used in conjunction with {{SequenceStyleGenerator}} becomes important.  For example, if {{SequenceStyleGenerator}} is configured to use any of the hilo-based optimizers, it cannot support identifier generation as part of bulk insertion.  {{HqlSqlWalker.supportsIdGenWithBulkInsertion}} is given the actual {{IdentifierGenerator}} instance, so this is at least workable.

However, rather than a series of instanceof-type checks that could never be complete (think custom generators), I propose we instead add an optional contract for IdentifierGenerator impls to implement if they support id generation with bulk insertion.  We just need to identify the pieces that need to be part of the contract.

At the highest level, supporting id generation with bulk insertion comes down to requiring an "in-database" form of id value generation.  There are 2 broad forms of these:
* id column(s) is referenced in the insert-spec.  
** This mainly includes sequences.  
** This could also include calls to functions, etc
* id column(s) is not referenced in the insert-spec
** This mainly includes IDENTITY
** This could also include id values assigned through DEFAULT column specs, triggers, etc

So essentially, the information we need:
# Do the id columns need to be referenced?  If so we need to add the columns to the insert-spec, and:
# What is the "id generation" fragment we need to add to the select expression list?


Initial proposal on contract:
{code:title=BulkInsertionSupportingIdentifierGenerator.java|borderStyle=solid}
public interface BulkInsertionSupportingIdentifierGenerator extends IdentifierGenerator {
    /**
     * Given the configuration of this IdentifierGenerator, does it support identifier generation as part of
     * bulk insertion?
     * <p/>
     * IMPL NOTE : Mainly here to allow stuff like SequenceStyleGenerator which *can* support this based on 
     * configuration
     * ...
     */
    public boolean supportsBulkInsertionIdentifierGeneration();

    /**
     * Return the select expression fragment, if any, that generates the identifier values.  {@code null} indicates
     * that no fragment is needed.
     * ...
     */
    public String determineBulkInsertionIdentifierGenerationSelectFragment(Dialect dialect);
}
{code}

The code in HqlSqlWalker then becomes:
{code}
    protected void postProcessInsert(AST insert) throws SemanticException, QueryException {
        ...
        if ( !insertStatement.getIntoClause().isExplicitIdInsertion() ) {
            final IdentifierGenerator generator = persister.getIdentifierGenerator();
            if ( ! BulkInsertionSupportingIdentifierGenerator.class.isInstance( generator ) ) {
                throw new QueryException( ... );
            }
            final BulkInsertionSupportingIdentifierGenerator goodGenerator = BulkInsertionSupportingIdentifierGenerator.class.cast( generator );
            if ( ! goodGenerator.supportsBulkInsertionIdentifierGeneration() ) {
                throw new QueryException( ... );
            }
            final String fragment = goodGenerator.determineBulkInsertionIdentifierGenerationSelectFragment( sessionFactoryHelper.getFactory().getDialect() );

            if ( fragment != null ) {
                // we got a fragment from the generator, so alter the sql tree...
                //
                // first, wrap the fragment as a node
                AST fragmentNode = getASTFactory().create( HqlSqlTokenTypes.SQL_TOKEN, fragment );
                // next, rearrange the SQL tree to add the fragment node as the first select expression
                AST currentFirstSelectExprNode = selectClause.getFirstChild();
                selectClause.setFirstChild( idSelectExprNode );
                idSelectExprNode.setNextSibling( currentFirstSelectExprNode );
                // finally, prepend the id column name(s) to the insert-spec
                insertStatement.getIntoClause().prependIdColumnSpec();
            }
        }
    }
{code}

Thoughts?

> Jboss7 with Insert into select query
> ------------------------------------
>
>                 Key: HHH-6920
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6920
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 4.0.0.Final
>         Environment: I am using jboss-as-7.0.2.Final, its using hibernate 4.x. I am using oracle 10g as database.
>            Reporter: sabbirali kadiwala
>
> In my application we used "insert into select" query for improve performance which is running fine in jboss6.
> But
> with jboss7 it is giving error as below
>  
> ErrorMessage:
> javax.ejb.EJBTransactionRolledbackException: org.hibernate.QueryException: can only generate ids as part of bulk insert with either sequence or post-insert style generators [insert into ScheduleDetails(mstSchedule,articleCode,vendorCode,valuationClass,ruleClass) select  ts.mstSchedule,ma.id.articleCode,tg.vendorCode,tg.valuationClass,'O' from com.eks.ias.service.core.entity.TransGp tg, com.eks.ias.service.core.entity.MstArticle ma,com.eks.ias.service.schedule.entity.TransSchedule ts  where ts.id.scheduleReferenceId = :scheduleRefId and tg.id.siteCode = :siteCode and ma.isDeleted=false and tg.id.articleCode=ma.id.articleCode  and tg.id.siteCode = ts.mstSchedule.mstSite.siteCode and ts.mstSchedule.mstSite.sapVersion = ma.id.sapVersion and ma.mstMc.mcCode = ts.id.mcCode  ]
> and entity looks like
> @Entity
> @Table(name = "SCHEDULE_DETAILS")
> @SequenceGenerator(name = "SCHEDULE_DETAILS_ID_GENERATOR", sequenceName = "MST_SCHEDULE_DETAILS_ID_SEQ", initialValue = 1, allocationSize = 1)
> public class ScheduleDetails implements java.io.Serializable
> {
> private Long scheduleDetailsId;
> private MstSchedule mstSchedule;
> .....
> // we have some constructor also
> ...
>      @Id
>           @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SCHEDULE_DETAILS_ID_GENERATOR")
>           @Column(name = "SCHEDULE_DETAILS_ID", unique = true, nullable = false, precision = 38, scale = 0)
>           public Long getScheduleDetailsId() {
>                     return this.scheduleDetailsId;
>           }
>  
> From Jboss7 Community i got below reply
> @GeneratedValue(SEQUENCE) maps to org.hibernate.id.enhanced.SequenceStyleGenerator, which isn't checked for in org.hibernate.hql.internal.ast.HqlSqlWalker.supportsIdGenWithBulkInsertion().
>  
> As previously discussed here, hibernate.id.new_generator_mappings is set to true by default, and as a result, class SequenceStyleGenerator is used instead of class SequenceHiLoGenerator (which is checked for in the above supportsIdGenWithBulkInsertion). 
>  
> Could you create a Hibernate (against 4.0.0.Final) jira for this bug, the link is here. 
>  
> Please reply when it got resolved and in which version..

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira