[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-40) Filter and Positional Parameters Bound in Incorrect Order

Filter and Positional Parameters Bound in Incorrect Order --------------------------------------------------------- Key: HHH-40 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-40 Project: Hibernate3 Issue Type: Patch Affects Versions: 3.0 alpha Environment: Oracle 9i, 3.0 Alpha CVS as of Nov 17, 2004. Reporter: Shawn Clowater Assignee: Steve Ebersole Fix For: 3.0 beta 1 Attachments: patch.diff When combining a filter at a top level class with a criteria paramater (positional) it was possible that the parameters would be bound in the wrong order. i.e. the following filter at the class level <filter name="versionFilter" condition="((:effectiveDate BETWEEN CREATE_DATE AND CREATE_DATE OR :inactiveDate BETWEEN CREATE_DATE AND CREATE_DATE))"/> <filter-def name="versionFilter"> <filter-param name="effectiveDate" type="java.util.Date"/> <filter-param name="inactiveDate" type="java.util.Date"/> </filter-def> combined with a simple criteria parameter results in DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '2004-08-01 00:00:00' to parameter: 1 DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '2004-08-31 23:59:59' to parameter: 2 DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '4' to parameter: 3 DEBUG [AWT-EventQueue-0] (AbstractBatcher.java:260) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1) DEBUG [AWT-EventQueue-0] (AbstractBatcher.java:361) - closing statement DEBUG [AWT-EventQueue-0] (JDBCExceptionReporter.java:50) - could not execute query [select this_.AGMT_CNTRCT_LINE_ID as AGMT_CNT1_3_, this_.AGMT_CNTRCT_ID as AGMT_CNT2_18_3_, this_.NOTES_ID as NOTES_ID18_3_, this_.UPDATE_DATE as UPDATE_D4_18_3_, this_.CREATE_DATE as CREATE_D5_18_3_, this_.UPDATE_USER as UPDATE_U6_18_3_, this_.CREATE_USER as CREATE_U7_18_3_, agmtcontra_.AGMT_CNTRCT_ID as AGMT_CNT1_0_, agmtcontra_.AGMT_ID as AGMT_ID16_0_, agmtcontra_.NOTES_ID as NOTES_ID16_0_, agmtcontra_.UPDATE_DATE as UPDATE_D4_16_0_, agmtcontra_.CREATE_DATE as CREATE_D5_16_0_, agmtcontra_.UPDATE_USER as UPDATE_U6_16_0_, agmtcontra_.CREATE_USER as CREATE_U7_16_0_, acversionj_.AGMT_CNTRCT_V_ID as AGMT_CNT1_1_, acversionj_.AGMT_CNTRCT_ID as AGMT_CNT2_17_1_, acversionj_.CNTRCT_SEQ as CNTRCT_SEQ17_1_, acversionj_.CNTRCT_REVISION as CNTRCT_R4_17_1_, acversionj_.AGMT_CNTRCT_TYPE_ID as AGMT_CNT5_17_1_, acversionj_.CNTRCT_REF as CNTRCT_REF17_1_, acversionj_.DESCRIPTION as DESCRIPT7_17_1_, acversionj_.QTY_VOLUME_TYPE_CODE as QTY_VOLU8_17_1_, acversionj_.QTY_LIMIT_FLG as QTY_LIMI9_17_1_, acversionj_.EFFECTIVE_DATE as EFFECTI10_17_1_, acversionj_.INACTIVE_DATE as INACTIV11_17_1_, acversionj_.UPDATE_DATE as UPDATE_12_17_1_, acversionj_.CREATE_DATE as CREATE_13_17_1_, acversionj_.UPDATE_USER as UPDATE_14_17_1_, acversionj_.CREATE_USER as CREATE_15_17_1_, acversionj_.ACTIVE_FLG as ACTIVE_FLG17_1_, aclversion_.AGMT_CNTRCT_LINE_V_ID as AGMT_CNT1_2_, aclversion_.AGMT_CNTRCT_LINE_ID as AGMT_CNT2_23_2_, aclversion_.QTY_LIMIT_ENFORCED_FLG as QTY_LIMI3_23_2_, aclversion_.DESCRIPTION as DESCRIPT4_23_2_, aclversion_.BUYER_STKHLDR_ID as BUYER_ST5_23_2_, aclversion_.SELLER_STKHLDR_ID as SELLER_S6_23_2_, aclversion_.SHIPPER_STKHLDR_ID as SHIPPER_7_23_2_, aclversion_.SRC_ADDR_ID as SRC_ADDR8_23_2_, aclversion_.SRC_INVENTORY_ID as SRC_INVE9_23_2_, aclversion_.DEST_ADDR_ID as DEST_AD10_23_2_, aclversion_.DEST_INVENTORY_ID as DEST_IN11_23_2_, aclversion_.DELIVERY_POINT_ADDR_ID as DELIVER12_23_2_, aclversion_.TITLE_TRANSFER_ADDR_ID as TITLE_T13_23_2_, aclversion_.SHIP_MODE_CODE as SHIP_MO14_23_2_, aclversion_.SHIP_TERMS as SHIP_TERMS23_2_, aclversion_.PRIORITY as PRIORITY23_2_, aclversion_.END_USE_CODE as END_USE17_23_2_, aclversion_.QTY_CALC_METHOD as QTY_CAL18_23_2_, aclversion_.NOMINATION_TYPE as NOMINAT19_23_2_, aclversion_.BEFORE_AFTER_IND as BEFORE_20_23_2_, aclversion_.IN_TRANSIT_FLG as IN_TRAN21_23_2_, aclversion_.ROUTING_ID as ROUTING_ID23_2_, aclversion_.EXCHANGE_PROCESS_FLG as EXCHANG23_23_2_, aclversion_.BACKFLOW_FLG as BACKFLO24_23_2_, aclversion_.COSTING_SRC_FLG as COSTING25_23_2_, aclversion_.DEDUCTION_LIMIT as DEDUCTI26_23_2_, aclversion_.QTY as QTY23_2_, aclversion_.QTY_UOM_CODE as QTY_UOM28_23_2_, aclversion_.ENERGY as ENERGY23_2_, aclversion_.ENERGY_UOM_CODE as ENERGY_30_23_2_, aclversion_.QTY_GAIN_LOSS_INDEX_PRICE_ID as QTY_GAI31_23_2_, aclversion_.EFFECTIVE_DATE as EFFECTI32_23_2_, aclversion_.INACTIVE_DATE as INACTIV33_23_2_, aclversion_.UPDATE_DATE as UPDATE_34_23_2_, aclversion_.CREATE_DATE as CREATE_35_23_2_, aclversion_.UPDATE_USER as UPDATE_36_23_2_, aclversion_.CREATE_USER as CREATE_37_23_2_, aclversion_.ACTIVE_FLG as ACTIVE_FLG23_2_ from AGMT_CNTRCT_LINE this_ inner join AGMT_CNTRCT agmtcontra_ on this_.AGMT_CNTRCT_ID=agmtcontra_.AGMT_CNTRCT_ID inner join AGMT_CNTRCT_V acversionj_ on agmtcontra_.AGMT_CNTRCT_ID=acversionj_.AGMT_CNTRCT_ID inner join AGMT_CNTRCT_LINE_V aclversion_ on this_.AGMT_CNTRCT_LINE_ID=aclversion_.AGMT_CNTRCT_LINE_ID where acversionj_.AGMT_CNTRCT_TYPE_ID=? and ((:versionFilter.effectiveDate BETWEEN this_.CREATE_DATE AND this_.CREATE_DATE OR :versionFilter.inactiveDate BETWEEN this_.CREATE_DATE AND this_.CREATE_DATE))] java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:103) at org.hibernate.loader.Loader.getResultSet(Loader.java:1065) at org.hibernate.loader.Loader.doQuery(Loader.java:344) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:190) at org.hibernate.loader.Loader.doList(Loader.java:1271) at org.hibernate.loader.Loader.list(Loader.java:1254) at org.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:169) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:2100) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:274) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:340) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:315) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:281) at com.entero.enterovision.service.agmt.AgmtService.fetchAgmtContractLines(AgmtService.java:488) at com.entero.enterovision.components.agreementitemprice.AgreementController.find(AgreementController.java:71) at com.entero.enterovision.components.AbstractController.findByParameters(AbstractController.java:32) at com.entero.enterovision.components.ActiveWorkbench.populate(ActiveWorkbench.java:329) at com.entero.enterovision.components.AbstractPMAWorkbench.populate(AbstractPMAWorkbench.java:30) at com.entero.enterovision.components.AbstractSidebarSearchPanel.populateWorkbench(AbstractSidebarSearchPanel.java:241) at com.entero.enterovision.components.agreementitemprice.AgreementItemPriceSearchSidebar.find(AgreementItemPriceSearchSidebar.java:203) at com.entero.infrastructure.ui.sidebar.SidebarPanel.executeFunction(SidebarPanel.java:158) at com.entero.enterovision.components.AbstractSidebarSearchPanel.search(AbstractSidebarSearchPanel.java:183) at com.entero.enterovision.components.AbstractSidebarSearchPanel.access$000(AbstractSidebarSearchPanel.java:32) at com.entero.enterovision.components.AbstractSidebarSearchPanel$2.actionPerformed(AbstractSidebarSearchPanel.java:112) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786) at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245) at java.awt.Component.processMouseEvent(Component.java:5100) at java.awt.Component.processEvent(Component.java:4897) at java.awt.Container.processEvent(Container.java:1569) at java.awt.Component.dispatchEventImpl(Component.java:3615) at java.awt.Container.dispatchEventImpl(Container.java:1627) at java.awt.Component.dispatchEvent(Component.java:3477) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128) at java.awt.Container.dispatchEventImpl(Container.java:1613) at java.awt.Window.dispatchEventImpl(Window.java:1606) at java.awt.Component.dispatchEvent(Component.java:3477) at java.awt.EventQueue.dispatchEvent(EventQueue.java:456) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137) at java.awt.EventDispatchThread.run(EventDispatchThread.java:100) WARN [AWT-EventQueue-0] (JDBCExceptionReporter.java:58) - SQL Error: 932, SQLState: 42000 ERROR [AWT-EventQueue-0] (JDBCExceptionReporter.java:59) - ORA-00932: inconsistent datatypes: expected NUMBER got DATE ----- Note how the parameters were applied in the wrong order. This was due to the Loader class in the processFilterParameters() method ALWAYS putting the filter parameters at the front of the queue. It is even more pronounced if you have additional filters further on down the chain.