different sql for same query

cameronjones · **Joined:** Fri Oct 10, 2003 10:12 am **Posts:** 39

hi,

i have tried running the following query in hibern8ide with sucess, but within my app a different sql query is generated which fails. i am using the same properties and classpath for both.

thanks, cam

Query

Code:

from Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport

Hibern8ide SQL

Code:

10:03,954 DEBUG QueryTranslator:199 - HQL: from com.rcp.client.internal.core.catalogue.Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport

10:03,964 DEBUG QueryTranslator:200 - SQL: select transport0_.id as id, case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end as clazz_, transport0_.arrivalTime as arrivalT2_9_, transport0_.departureTime as departur3_9_, transport0_.destination as destinat4_9_, transport0_.origin as origin9_, transport0__1_.company as company6_, transport0__1_.inventory as inventory6_, transport0__1_.unitCost as unitCost6_, transport0__1_.unitPrice as unitPrice6_, transport0__1_.refundable as refundable6_, transport0__1_.retailItem as retailItem6_, transport0__2_.version as version4_, transport0__2_.category as category4_, transport0__2_.updated as updated4_, transport0__2_.created as created4_, transport0__2_.updatedBy as updatedBy4_, transport0__2_.createdBy as createdBy4_, transport0__3_.bus as bus10_, transport0__3_.driver as driver10_, transport0__3_.kitty as kitty10_ from Transport transport0_ inner join Product transport0__1_ on transport0_.id=transport0__1_.id inner join CatalogueItem transport0__2_ on transport0_.id=transport0__2_.id left outer join Tour transport0__3_ on transport0_.id=transport0__3_.id where (case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end=0 )

App SQL

Code:

14:03:00,495 DEBUG QueryTranslator:199 - HQL: from com.rcp.client.internal.core.catalogue.Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport
14:03:00,495 DEBUG QueryTranslator:200 - SQL: select transport0_.id as x0_0_ from Transport transport0_ inner join Product transport0__1_ on transport0_.id=transport0__1_.id inner join CatalogueItem transport0__2_ on transport0_.id=transport0__2_.id where (case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end=0 )

App Error

Code:

java.sql.SQLException: General error,  message from server: "Unknown table 'transport0__3_' in where clause"
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
   at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:848)
   at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1601)
   at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27

max · **Posted:** Sat May 08, 2004 5:03 am

if hibern8ide and your app server is using the EXACT same hibernate, mapping files and dialect (+ all other settings) then you would definitly not see two different sql's! (we are not throwing with dices in the core ;)

...so i'm 100% sure that you have some difference in your setups!

cameronjones · **Joined:** Fri Oct 10, 2003 10:12 am **Posts:** 39

mabye that should have been 99.99%....

i thought this would be thr problem too until i did some tests on queries and finds. the first two tests using session.find() pass, but when using a query the error occurs. is this a bug or a limitation?

cam

Code:

        Session session = null;
        try {
            session = factory.openSession();

            List results = session.find("from Transport as t where t.class = ?", Transport.class,
                    Hibernate.CLASS);
            for (Iterator iter = results.iterator(); iter.hasNext();) {
                ITransport transport = (ITransport) iter.next();
                System.out.println(transport);
            }
            
            results = session.find("from Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport");
            for (Iterator iter = results.iterator(); iter.hasNext();) {
                ITransport transport = (ITransport) iter.next();
                System.out.println(transport);
            }

            Query query = session
                    .createQuery("from Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport");
            for (Iterator iter = query.iterate(); iter.hasNext();) {
                ITransport transport = (ITransport) iter.next();
                System.out.println(transport);
            }

        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

Code:

14:18:41,535 DEBUG QueryTranslator:199 - HQL: from com.rcp.client.internal.core.catalogue.Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport
14:18:41,535 DEBUG QueryTranslator:200 - SQL: select transport0_.id as x0_0_ from Transport transport0_ inner join Product transport0__1_ on transport0_.id=transport0__1_.id inner join CatalogueItem transport0__2_ on transport0_.id=transport0__2_.id where (case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end=0 )
14:18:41,535 DEBUG BatcherImpl:196 - about to open: 0 open PreparedStatements, 0 open ResultSets
14:18:41,555 DEBUG SQL:237 - select transport0_.id as x0_0_ from Transport transport0_ inner join Product transport0__1_ on transport0_.id=transport0__1_.id inner join CatalogueItem transport0__2_ on transport0_.id=transport0__2_.id where (case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end=0 )
Hibernate: select transport0_.id as x0_0_ from Transport transport0_ inner join Product transport0__1_ on transport0_.id=transport0__1_.id inner join CatalogueItem transport0__2_ on transport0_.id=transport0__2_.id where (case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end=0 )
14:18:41,555 DEBUG BatcherImpl:241 - preparing statement
14:18:41,605 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: General error,  message from server: "Unknown table 'transport0__3_' in where clause"
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
   at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:848)
   at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1601)
   at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27)

cameronjones · **Joined:** Fri Oct 10, 2003 10:12 am **Posts:** 39

it also appears that it doesn't work for session.iterate() either.

emmanuel · **Posted:** Mon May 10, 2004 11:58 am

iterate and find/list does not do the same thing, check the JavaDoc.
And there should be somethink wrong in you mapping.