-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
topping
 Post subject: Subselect not compiling into SQL
PostPosted: Fri Jun 16, 2006 5:53 am 
Newbie

Joined: Mon Sep 01, 2003 9:48 pm
Posts: 17
Greetings,

I'm working on a Dialect for PostGIS and am running into a problem. So far, I've been relatively successful at getting a Dialect that SchemaUpdate can generate the correct database from, but now am having problems with a subselect in the query. The subselect is completely eliminated in the compiled SQL.

Working SQL for what I am trying to do would be
Code:
select id, astext(position) from users where id<>1 and distance(transform((select position from users where id=1),2227), transform(position,2227)) < 3000


My HQL:

Code:
from User where id<>:src and distance(transform((from User where id=:src),:srid), transform(position,:srid)) < :dist


'transform' and 'distance' are functions that are defined in PostGIS and are defined in the Dialect, below. Transform() takes a geometry (point, in this case) and a target transformation and gives you back a geometry. Consider them a no-op for purposes of discussion. distance() takes two geometry arguments, and returns an integer cartesian distance between them.

The problem

When the HQL is compiled, the HQL AST appears to be correct, but the SQL AST does not contain the subselect. Both the HQL and SQL AST dumps are further down in this post. You'll see in the generated SQL that the subselect is completely missing.

Here is my Dialect:

Code:
public class PostGISDialect extends PostgreSQLDialect {
    public static final NullableType GEOMETRY = new GeometryType();
    public PostGISDialect() {
        super();
        registerColumnType( Types.OTHER, "geometry" );
        registerHibernateType( Types.OTHER, GEOMETRY.getName() );
        registerFunction( "GeomFromText", new VarArgsSQLFunction("GeomFromText(",",",")") );
        registerFunction( "distance", new VarArgsSQLFunction(GEOMETRY, "distance(",",",")") );
        registerFunction( "transform", new VarArgsSQLFunction(GEOMETRY, "transform(",",",")") );
    }
}



Hibernate version: 3.1.3

Mapping documents:
Code:
<hibernate-mapping>
    <class name="net.voxme.portal.model.User" table="users">
        <id name="id" column="id" unsaved-value="null" >
            <generator class="native"/>
        </id>
        <property name="login"/>
        <property name="password"/>
        <property name="position" type="net.voxme.portal.hibernate.GeometryType"/>
    </class>
</hibernate-mapping>


Full stack trace of any exception that occurs:
Code:
org.postgresql.util.PSQLException: ERROR: function transform() does not exist
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:250)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
   at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
   at net.voxme.portal.dao.hibernate.UserDAOHibernate$3.doInHibernate(UserDAOHibernate.java:61)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:334)
   at net.voxme.portal.dao.hibernate.UserDAOHibernate.getUsers(UserDAOHibernate.java:64)

Name and version of the database you are using:
PostGIS 8

The generated SQL (show_sql=true):
Code:
select user0_.id as id0_, user0_.login as login0_, user0_.password as password0_, user0_.position as position0_ from users user0_ where user0_.id<>? and distance(transform(),transform(user0_.position,?))<?


Debug level Hibernate log excerpt:
Code:
(QueryTranslatorImpl.java:236) DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - parse() - HQL: from net.voxme.portal.model.User where id<>:src and distance(transform((from net.voxme.portal.model.User where id=:src),:srid), transform(position,:srid)) < :dist
(QueryTranslatorImpl.java:252) DEBUG org.hibernate.hql.ast.AST  - --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'from'
    |     \-[RANGE] 'RANGE'
    |        \-[DOT] '.'
    |           +-[DOT] '.'
    |           |  +-[DOT] '.'
    |           |  |  +-[DOT] '.'
    |           |  |  |  +-[IDENT] 'net'
    |           |  |  |  \-[IDENT] 'voxme'
    |           |  |  \-[IDENT] 'portal'
    |           |  \-[IDENT] 'model'
    |           \-[IDENT] 'User'
    \-[WHERE] 'where'
       \-[AND] 'and'
          +-[NE] '<>'
          |  +-[IDENT] 'id'
          |  \-[COLON] ':'
          |     \-[IDENT] 'src'
          \-[LT] '<'
             +-[METHOD_CALL] '('
             |  +-[IDENT] 'distance'
             |  \-[EXPR_LIST] 'exprList'
             |     +-[METHOD_CALL] '('
             |     |  +-[IDENT] 'transform'
             |     |  \-[EXPR_LIST] 'exprList'
             |     |     +-[QUERY] 'query'
             |     |     |  +-[SELECT_FROM] 'SELECT_FROM'
             |     |     |  |  \-[FROM] 'from'
             |     |     |  |     \-[RANGE] 'RANGE'
             |     |     |  |        \-[DOT] '.'
             |     |     |  |           +-[DOT] '.'
             |     |     |  |           |  +-[DOT] '.'
             |     |     |  |           |  |  +-[DOT] '.'
             |     |     |  |           |  |  |  +-[IDENT] 'net'
             |     |     |  |           |  |  |  \-[IDENT] 'voxme'
             |     |     |  |           |  |  \-[IDENT] 'portal'
             |     |     |  |           |  \-[IDENT] 'model'
             |     |     |  |           \-[IDENT] 'User'
             |     |     |  \-[WHERE] 'where'
             |     |     |     \-[EQ] '='
             |     |     |        +-[IDENT] 'id'
             |     |     |        \-[COLON] ':'
             |     |     |           \-[IDENT] 'src'
             |     |     \-[COLON] ':'
             |     |        \-[IDENT] 'srid'
             |     \-[METHOD_CALL] '('
             |        +-[IDENT] 'transform'
             |        \-[EXPR_LIST] 'exprList'
             |           +-[IDENT] 'position'
             |           \-[COLON] ':'
             |              \-[IDENT] 'srid'
             \-[COLON] ':'
                \-[IDENT] 'dist'

(ErrorCounter.java:68) DEBUG org.hibernate.hql.ast.ErrorCounter  - throwQueryException() : no errors
(HqlSqlBaseWalker.java:111) DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker  - select << begin [level=1, statement=select]
(FromElement.java:104) DEBUG org.hibernate.hql.ast.tree.FromElement  - FromClause{level=1} :  net.voxme.portal.model.User (no alias) -> user0_
(HqlSqlWalker.java:477) DEBUG org.hibernate.hql.ast.HqlSqlWalker  - attempting to resolve property [id] as a non-qualified ref
(FromReferenceNode.java:51) DEBUG org.hibernate.hql.ast.tree.FromReferenceNode  - Resolved :  {synthetic-alias} -> {synthetic-alias}
(DotNode.java:541) DEBUG org.hibernate.hql.ast.tree.DotNode  - getDataType() : id -> org.hibernate.type.LongType@3d57fa
(FromReferenceNode.java:51) DEBUG org.hibernate.hql.ast.tree.FromReferenceNode  - Resolved :  {synthetic-alias}.id -> user0_.id
(HqlSqlWalker.java:477) DEBUG org.hibernate.hql.ast.HqlSqlWalker  - attempting to resolve property [position] as a non-qualified ref
(FromReferenceNode.java:51) DEBUG org.hibernate.hql.ast.tree.FromReferenceNode  - Resolved :  {synthetic-alias} -> {synthetic-alias}
(DotNode.java:541) DEBUG org.hibernate.hql.ast.tree.DotNode  - getDataType() : position -> net.voxme.portal.hibernate.GeometryType@6d7e89
(FromReferenceNode.java:51) DEBUG org.hibernate.hql.ast.tree.FromReferenceNode  - Resolved :  {synthetic-alias}.position -> user0_.position
(HqlSqlBaseWalker.java:117) DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker  - select : finishing up [level=1, statement=select]
(HqlSqlWalker.java:511) DEBUG org.hibernate.hql.ast.HqlSqlWalker  - processQuery() :  ( SELECT ( FromClause{level=1} users user0_ ) ( where ( and ( <> ( user0_.id {synthetic-alias} id ) ? ) ( < ( ( distance ( exprList ( ( transform exprList ) ( ( transform ( exprList ( user0_.position {synthetic-alias} position ) ? ) ) ) ) ? ) ) ) )
(HqlSqlWalker.java:713) DEBUG org.hibernate.hql.ast.HqlSqlWalker  - Derived SELECT clause created.
(JoinProcessor.java:128) DEBUG org.hibernate.hql.ast.util.JoinProcessor  - Using FROM fragment [users user0_]
(HqlSqlBaseWalker.java:123) DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker  - select >> end [level=1, statement=select]
(QueryTranslatorImpl.java:222) DEBUG org.hibernate.hql.ast.AST  - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (users)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'user0_.id as id0_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=users,tableAlias=user0_,origin=null,colums={,className=net.voxme.portal.model.User}}}
    |  \-[SQL_TOKEN] SqlFragment: 'user0_.login as login0_, user0_.password as password0_, user0_.position as position0_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[], fromElementByTableAlias=[user0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'users user0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=users,tableAlias=user0_,origin=null,colums={,className=net.voxme.portal.model.User}}
    \-[WHERE] SqlNode: 'where'
       \-[AND] SqlNode: 'and'
          +-[NE] BinaryLogicOperatorNode: '<>'
          |  +-[DOT] DotNode: 'user0_.id' {propertyName=id,dereferenceType=4,propertyPath=id,path={synthetic-alias}.id,tableAlias=user0_,className=net.voxme.portal.model.User,classAlias=null}
          |  |  +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
          |  |  \-[IDENT] IdentNode: 'id' {originalText=id}
          |  \-[NAMED_PARAM] ParameterNode: '?' {name=src, expectedType=org.hibernate.type.LongType@3d57fa}
          \-[LT] BinaryLogicOperatorNode: '<'
             +-[METHOD_CALL] MethodNode: '('
             |  +-[METHOD_NAME] IdentNode: 'distance' {originalText=distance}
             |  \-[EXPR_LIST] SqlNode: 'exprList'
             |     +-[METHOD_CALL] MethodNode: '('
             |     |  +-[METHOD_NAME] IdentNode: 'transform' {originalText=transform}
             |     |  \-[EXPR_LIST] SqlNode: 'exprList'
             |     \-[METHOD_CALL] MethodNode: '('
             |        +-[METHOD_NAME] IdentNode: 'transform' {originalText=transform}
             |        \-[EXPR_LIST] SqlNode: 'exprList'
             |           +-[DOT] DotNode: 'user0_.position' {propertyName=position,dereferenceType=4,propertyPath=position,path={synthetic-alias}.position,tableAlias=user0_,className=net.voxme.portal.model.User,classAlias=null}
             |           |  +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
             |           |  \-[IDENT] IdentNode: 'position' {originalText=position}
             |           \-[NAMED_PARAM] ParameterNode: '?' {name=srid, expectedType=null}
             \-[NAMED_PARAM] ParameterNode: '?' {name=dist, expectedType=net.voxme.portal.hibernate.GeometryType@21e761}

(ErrorCounter.java:68) DEBUG org.hibernate.hql.ast.ErrorCounter  - throwQueryException() : no errors
(QueryTranslatorImpl.java:206) DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - HQL: from net.voxme.portal.model.User where id<>:src and distance(transform((from net.voxme.portal.model.User where id=:src),:srid), transform(position,:srid)) < :dist
(QueryTranslatorImpl.java:207) DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - SQL: select user0_.id as id0_, user0_.login as login0_, user0_.password as password0_, user0_.position as position0_ from users user0_ where user0_.id<>? and distance(transform(),transform(user0_.position,?))<?
(ErrorCounter.java:68) DEBUG org.hibernate.hql.ast.ErrorCounter  - throwQueryException() : no errors
(HQLQueryPlan.java:219) DEBUG org.hibernate.engine.query.HQLQueryPlan  - HQL param location recognition took 18 mills (from User where id<>:src and distance(transform((from User where id=:src),:srid), transform(position,:srid)) < :dist)
(TransactionSynchronizationManager.java:134) DEBUG org.springframework.transaction.support.TransactionSynchronizationManager  - Retrieved value [org.springframework.orm.hibernate3.SessionHolder@86f987] for key [org.hibernate.impl.SessionFactoryImpl@683bfc] bound to thread [main]
(QueryPlanCache.java:69) DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate HQL query plan in cache; generating (from User where id<>:src and distance(transform((from User where id=:src),:srid), transform(position,:srid)) < :dist)
(QueryTranslatorImpl.java:236) DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - parse() - HQL: from net.voxme.portal.model.User where id<>:src and distance(transform((from net.voxme.portal.model.User where id=:src),:srid), transform(position,:srid)) < :dist


I'm sure I must be missing something in my code to get things to generate properly. I'm going to start fishing around in the SQL AST generator, but I'm really wishing that I could avoid this!! :-/

Any thoughts appreciated!
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.