[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2258) Bad SQL Grammer: Implicit join when using r.fooClass.attributes[?]=?

[Darryl Miles (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2258?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=31573#action_31573 ] 

Darryl Miles commented on HHH-2258:
-----------------------------------


HQL:

13:34:03,477 DEBUG QueryTranslatorImpl:241 - HQL: SELECT r FROM org.hibernate.test.joinedmapfilter.Toplevel AS r WHERE r.toplevelId=? AND r.productDetail.attributes[?]=?


SQL (written out nicer) :

13:34:03,478 DEBUG QueryTranslatorImpl:242 - SQL:
    select
        toplevel0_.toplevel_id as toplevel1_0_,
        toplevel0_.long_value_one as long2_0_,
        toplevel0_.product_detail_id as product3_0_,
        toplevel0_.description as descript4_0_
    from
        toplevel toplevel0_,
        /////////////// MISSING TABLE NOT EMITTED BY HIB "product_detail productdet1_, " ///////////////
        product_detail_attrs attributes2_
    where
        toplevel0_.product_detail_id=productdet1_.product_detail_id
        and productdet1_.product_detail_id=attributes2_.product_detail_id
        and attributes2_.name = ?
        and toplevel0_.toplevel_id=?
        and attributes2_.value=?


I inserted the missing: "product_detail productdet1_" in the FROM clause.  That missing table is the reason for this bug report (just in case it hasn't been obvious so far).

> Bad SQL Grammer: Implicit join when using r.fooClass.attributes[?]=?
> --------------------------------------------------------------------
>
>                 Key: HHH-2258
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2258
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.1.3
>         Environment: MySQL server this has been tested with.
>            Reporter: Darryl Miles
>            Priority: Critical
>         Attachments: HHH-2258.txt, test_joinedmapfilter.zip
>
>
> Please find this test case atteched to demonstrate the bug, maybe you can add to the hibernate distribution for future testing and piece of mind for me.
> The error:
> Testsuite: org.hibernate.test.joinedmapfilter.JoinedMapFilterTest
> Tests run: 1, Failures: 0, Errors: 1, Time elapsed: 0.498 sec
> Testcase: testJoinedMapFilter took 0.495 sec
>         Caused an ERROR
> could not execute query
> org.hibernate.exception.SQLGrammarException: could not execute query
>         at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
>         at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>         at org.hibernate.loader.Loader.doList(Loader.java:2147)
>         at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
>         at org.hibernate.loader.Loader.list(Loader.java:2023)
>         at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
>         at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
>         at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
>         at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
>         at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
>         at org.hibernate.test.joinedmapfilter.JoinedMapFilterTest.testJoinedMapFilter(JoinedMapFilterTest.java:116)
>         at org.hibernate.test.TestCase.runTest(TestCase.java:250)
>         at org.hibernate.test.TestCase.runBare(TestCase.java:316)
> Caused by: java.sql.SQLException: Unknown table 'productdet1_' in where clause
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
>         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
>         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
>         at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
>         at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
>         at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
>         at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
>         at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
>         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:2144)
>         ... 25 more
> SELECT r FROM com.company.Toplevel AS r WHERE r.toplevel=? AND rec.productDetail.attributes[?]=? 
> Also my recent post: http://forum.hibernate.org/viewtopic.php?t=966374&start=0&postdays=0&postorder=asc&highlight=
> This testcase has been tested with 3.1.3 and 3.2.0-ga.
> 2nd problem:
> There is also another situation in the testcase which I found through serendipity which causes a NPE inside hibernate because I misspelled "r" as "rec".  Check out the commented out section and swap over the createQuery() lines to demonstrate that NPE.

-- 
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