[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3202) Problem with different aliases in select and where clauses
Neil Hart (JIRA)
noreply at atlassian.com
Tue Mar 25 14:57:33 EDT 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3202?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29880 ]
Neil Hart commented on HHH-3202:
--------------------------------
To make it clearer, the SQL in the second query fails. The "this_" aliase used in the where clause does not match the "dailybbgra0_" alias in the From and Select clauses.
> Problem with different aliases in select and where clauses
> ----------------------------------------------------------
>
> Key: HHH-3202
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3202
> Project: Hibernate3
> Issue Type: Bug
> Components: build
> Affects Versions: 3.2.5, 3.2.6
> Environment: Oracle 10G, Java 1.5.0_11
> Reporter: Neil Hart
>
> Our code is usually building the following query:
> * criteria query */ select
> this_.ID as ID2_0_,
> this_.rateDate as rateDate2_0_,
> this_.ticker as ticker2_0_,
> this_.currency as currency2_0_,
> this_.rate as rate2_0_
> from
> BbgDailyRateData this_
> where
> this_.rateDate between to_date('2008-03-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
> and to_date('2008-03-24 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
> and this_.currency='USD'
> Every night we bounce our 4 servers and once or twice a week a server will end up trying to run the query as:
> /* criteria query */ select
> dailybbgra0_.ID as ID2_0_,
> dailybbgra0_.rateDate as rateDate2_0_,
> dailybbgra0_.ticker as ticker2_0_,
> dailybbgra0_.currency as currency2_0_,
> dailybbgra0_.rate as rate2_0_
> from
> BbgDailyRateData dailybbgra0_
> where
> this_.rateDate between to_date('2008-03-23 00:00:00',
> 'YYYY-MM-DD HH24:MI:SS') and to_date('2008-03-23 23:59:59', 'YYYY-MM-DD
> HH24:MI:SS')
> and this_.currency='USD'
> without any changes to code or configuration. We quickly notice the problem and restart the server and the problem goes away.
> The Java we are running is (using Spring):
> return getHibernateTemplate().executeFind(new HibernateCallback() {
> public Object doInHibernate(Session session) throws HibernateException, SQLException {
> Date from = DateUtils.getStartOfDay(date);
> Date to = DateUtils.getEndOfDay(date);
> Criteria criteria = session.createCriteria(DailyBbgRateData.class);
> criteria.add(Expression.between("date", from, to));
> criteria.add(Expression.eq("currency", currency));
> return criteria.list();
> }
> });
> The mapping is:
> <?xml version="1.0"?>
> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
> "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
> <hibernate-mapping>
> <class name="com.creditex.backoffice.defaultswap.BbgCdswData" table="TRADEBBGCDSW">
> <cache usage="nonstrict-read-write" />
> <id name="id">
> <column name="ID" precision="10" scale="0" />
> <generator class="assigned"/>
> </id>
> <property name="bbgTicker" column="bbgTicker" length="32" not-null="true" />
> <property name="settlementDate" column="settlementDate" />
> <property name="maturityDate" column="maturityDate" />
> <property name="effectiveDate" column="effectiveDate" />
> <property name="firstCouponDate" column="firstCouponDate" />
> <property name="nextToLastCouponDate" column="nextToLastCouponDate" />
> <property name="spread" column="spread" precision="8" scale="3" />
> <property name="curveDate" column="curveDate" />
> <property name="notional" column="notional" precision="12" scale="0" />
> <property name="currency" column="currency" length="3" not-null="true" />
> <property name="paymentFrequency" column="paymentFrequency" length="1" not-null="true" />
> <property name="recoveryRate" column="recoveryRate" precision="7" scale="6" />
> <property name="flatSpread" column="flatSpread" precision="8" scale="3" />
> <property name="cnvBpv" column="cnvBpv" precision="10" scale="2" />
> <property name="swapCurveBpv" column="swapCurveBpv" precision="10" scale="2" />
> <property name="marketValue" column="marketValue" precision="10" scale="2" />
> <property name="netAccruedInterest" column="netAccruedInterest" precision="10" scale="2" />
> <property name="daysAccrued" column="daysAccrued" precision="4" scale="0" />
> <property name="payCurveNumber" column="payCurveNumber" length="4" />
> <property name="payCurveMktSide" column="payCurveMktSide" length="20" not-null="true" />
> <property name="securityDescription" column="securityDescription" length="32" not-null="true" />
> <property name="replSpread" column="replSpread" precision="8" scale="3" />
> <property name="buySellFlag" column="buySellFlag" length="1" not-null="true">
> <type name="com.creditex.db.hibernate.StringLabeledEnumUserType">
> <param name="targetClass">com.creditex.trading.TradeDirection</param>
> </type>
> </property>
> <property name="busDays1" column="busDays1" length="3" />
> <property name="busDays2" column="busDays2" length="3" />
> <property name="busDays3" column="busDays3" length="3" />
> <property name="cashSettledOn" column="cashSettledOn" />
> <property name="quotedPrice" column="quotedPrice" precision="12" scale="8" />
> <property name="dealRecoveryRate" column="dealRecoveryRate" precision="7" scale="6" />
> <property name="priceToSpreadMode" column="priceToSpreadMode" precision="1" scale="0" />
> <property name="swapPremium" column="swapPremium" precision="12" scale="2" />
> <property name="dateGenMethod" column="dateGenMethod" length="20" not-null="true" />
> <property name="payAccrued" column="payAccrued" type="yes_no" length="1" />
> </class>
> </hibernate-mapping>
> This problem has been persistent from Hibernate 3.2.5.ga and 3.2.6.ga. The Java is 1.5.0_11 and the database is Oracle 10G.
> This is the first query that we run on startup that has the problem. Once we see this query failing, other queries for different classes fail with the same alias problem. Anyone have any ideas.
> I think this might be related to HHH-2847; which was also not repeatable but does repeats.
> Thanks
> Neil Hart
--
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
More information about the hibernate-issues
mailing list