I run into a problem in translating HQL Queries having correlated subqueries to HQL in the context of a HQL-Bulk insert / DML-style operations.
Here ist the HQL select:
Code:
select base.p2 as p2,sum(base.p4) as p4,sum(base.p6) as p6,
(select sub5.p5 from MarketSample as sub5 where sub5.id = min(base.id)) as p5,
(select sub0.p0 from MarketSample as sub0 where sub0.id = max(base.id)) as p0,
(select sub1.p1 from MarketSample as sub1 where sub1.id = max(base.id)) as p1,
(select sub3.p3 from MarketSample as sub3 where sub3.id = max(base.id)) as p3,
(select sub7.p7 from MarketSample as sub7 where sub7.id = max(base.id)) as p7
from MarketSample as base group by base.p2
This translates correctly into the following SQL:
Code:
select marketsamp0_.`typeid` as col_0_0_, sum(marketsamp0_.`price`) as col_1_0_, sum(marketsamp0_.`volenter`) as col_2_0_, (select marketsamp1_.`volremain` from `MarketSample` marketsamp1_ where marketsamp1_.`#id`=min(marketsamp0_.`#id`)) as col_3_0_, (select marketsamp2_.`orderid` from `MarketSample` marketsamp2_ where marketsamp2_.`#id`=max(marketsamp0_.`#id`)) as col_4_0_, (select marketsamp3_.`stationid` from `MarketSample` marketsamp3_ where marketsamp3_.`#id`=max(marketsamp0_.`#id`)) as col_5_0_, (select marketsamp4_.`bid` from `MarketSample` marketsamp4_ where marketsamp4_.`#id`=max(marketsamp0_.`#id`)) as col_6_0_, (select marketsamp5_.`date` from `MarketSample` marketsamp5_ where marketsamp5_.`#id`=max(marketsamp0_.`#id`)) as col_7_0_ from `MarketSample` marketsamp0_ group by marketsamp0_.`typeid`
Then I try to apply this statement in the following HQL bulk insert:
Code:
insert into MarketSample(p0,p1,p2,p3,p4,p5,p6,p7) + hql_select
This results in the following error (tested on mysql 5.1.x and postgres 8.3.x):
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'MarketSample.#id' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:101)
And indeed the SQL of the HQL-instert statement is now corrupted, having a wrong alias in the aggregate functions of the correlated subqueries:
Code:
select marketsamp0_.`typeid` as col_0_0_, sum(marketsamp0_.`price`) as col_1_0_, sum(marketsamp0_.`volenter`) as col_2_0_, (select marketsamp1_.`volremain` from `MarketSample` marketsamp1_ where marketsamp1_.`#id`=min(`MarketSample`.`#id`)) as col_3_0_, (select marketsamp2_.`orderid` from `MarketSample` marketsamp2_ where marketsamp2_.`#id`=max(`MarketSample`.`#id`)) as col_4_0_, (select marketsamp3_.`stationid` from `MarketSample` marketsamp3_ where marketsamp3_.`#id`=max(`MarketSample`.`#id`)) as col_5_0_, (select marketsamp4_.`bid` from `MarketSample` marketsamp4_ where marketsamp4_.`#id`=max(`MarketSample`.`#id`)) as col_6_0_, (select marketsamp5_.`date` from `MarketSample` marketsamp5_ where marketsamp5_.`#id`=max(`MarketSample`.`#id`)) as col_7_0_ from `MarketSample` marketsamp0_ group by marketsamp0_.`typeid`
When trying to resolve the SQL directly from HQL via this code snippet, I get a null value as result, whereas it works for the original HQL-select statement:
Code:
final QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
final SessionFactoryImplementor factory = (SessionFactoryImplementor) sessionFactory;
final QueryTranslator translator = translatorFactory.
createQueryTranslator(
hqlQueryText,
hqlQueryText,
Collections.EMPTY_MAP, factory
);
translator.compile(Collections.EMPTY_MAP, false);
return translator.getSQLString();
Environment:hibernate 3.5-Beta3
java6, 64 Bit
hbm mapping file: Code:
<hibernate-mapping>
<class entity-name="MarketSample" table="`MarketSample`">
<id column="`#id`" name="id" type="long">
<generator class="native"/>
</id>
<property column="`orderid`" name="p0" type="integer"/>
<property column="`stationid`" name="p1" type="integer"/>
<property column="`typeid`" name="p2" type="integer"/>
<property column="`bid`" name="p3" type="integer"/>
<property column="`price`" name="p4" type="double"/>
<property column="`volremain`" name="p5" type="double"/>
<property column="`volenter`" name="p6" type="double"/>
<property column="`date`" name="p7" type="date"/>
</class>
</hibernate-mapping>
:
I use hibernate in dynamic map entity mode and a StatelessSession with a user provided JDBC Connection.
Connections, databases and tables, queries, etc. are known not bevor runtime. Data is part of a dynamic ETL-Process (Palo-ETLServer), where I try to use hibernate as backend for this dynamic data also.
For now I circumvent the problem by generating native SQL queries instead of HQL.
Hibernate 3.3.2:Using hibernate 3.3.2 I run into the same problem earlier already when creating the HQL insert statement above. This can be avoided there by moving the correlated subselects all to the end of the select part of the statement.
Does anybody experience related problems or do I just miss something important?
Login
FAQ
Search