[ http://jira.jboss.com/jira/browse/JBAS-1364?page=history ]
Scott M Stark updated JBAS-1364: -------------------------------- Assign To: Alexey Loubyansky (was: Scott M Stark) Priority: Major (was: Critical) > MEMBER OF Bug in SQL92 Compiler > ------------------------------- > > Key: JBAS-1364 > URL: http://jira.jboss.com/jira/browse/JBAS-1364 > Project: JBoss Application Server > Type: Bug > Components: CMP service > Versions: JBossAS-4.0.1 Final > Environment: Java 1.4.2, Hypersonic DB that comes with 4.0.1, i686, Linux 2.6 > Reporter: Jens Braeuer > Assignee: Alexey Loubyansky > Attachments: insert-testdata-hypersonic.sql, localDB.log > > > Hi there ! > I was experimenting with dynamic created > JBossQL-Queries, when i found what i expect to be a bug. > Ok, here some facts about the app i am writing: > 2 (for this post relevant) Entity-Beans: > - Publication with a 1:M CMR to Author > - Author with fields firstName and lastName > Writing a method that creates queries on the fly, i > found that using the EJBToSQL92Compiler delivers > incorrect results. > I am searching a Publication written by 2 Authors i > know the "firstName" of. So i expected the query > SELECT p.id FROM Publication AS p, Author AS a1, Author > AS a2 WHERE (a1.firstName = 'Timo') AND (a1 MEMBER OF > p.author) AND ( a2.firstName = 'Jens' ) AND ( a2 MEMBER > OF p.author ) > to deliver the id of this Publication. > This is true for the default compiler, but using the > SQL92 one also delivers another Publication in the > result. As i do only tests, there are only two entries > in the db. I double-checked the database and returning > only one Publication would definately be correct. > Ok: here is a test-run with the default EJBQL-Compiler: > ------------------------------------------------------- > [PublicationLocalHome] queryString: SELECT p.id FROM > Publication AS p, Author AS a1, Author AS a2 WHERE > (a1.firstName = 'Timo') AND (a1 MEMBER OF p.author) AND > ( a2.firstName = 'Jens' ) AND ( a2 MEMBER OF p.author ) > org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Publication#ejbSelectGeneric > null - Executing SQL: > SELECT t0_p.id FROM PUBLICATION t0_p, AUTHOR t1_a1, > AUTHOR t4_a2 WHERE ((t1_a1.firstName = 'Timo') AND > (EXISTS (SELECT t3_p_author_RELATION_TABLE.authorId > FROM PUBLICATIONAUTHORS t3_p_author_RELATION_TABLE > WHERE t0_p.id=t3_p_author_RELATION_TABLE.publicationId > AND t1_a1.id=t3_p_author_RELATION_TABLE.authorId)) AND > (t4_a2.firstName = 'Jens') AND (EXISTS (SELECT > t3_p_author_RELATION_TABLE.authorId FROM > PUBLICATIONAUTHORS t3_p_author_RELATION_TABLE WHERE > t0_p.id=t3_p_author_RELATION_TABLE.publicationId AND > t4_a2.id=t3_p_author_RELATION_TABLE.authorId))) > 20:29:44,215 WARN [PublicationLocalHome] result was : [1] > 20:29:44,216 WARN [PublicationLocalHome] result class: > java.util.ArrayList > And here a run using the EJBQLToSQL92Compiler: > ---------------------------------------------- > [PublicationLocalHome] queryString: SELECT p.id FROM > Publication AS p, Author AS a1, Author AS a2 WHERE > (a1.firstName = 'Timo') AND (a1 MEMBER OF p.author) AND > ( a2.firstName = 'Jens' ) AND ( a2 MEMBER OF p.author ) > org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Publication#ejbSelectGeneric > null - Executing SQL: SELECT t0_p.id FROM AUTHOR t5_a2, > AUTHOR t1_a1, PUBLICATION t0_p WHERE (t1_a1.firstName = > 'Timo') AND (EXISTS (SELECT t2_p_local.id FROM > PUBLICATION t2_p_local INNER JOIN PUBLICATIONAUTHORS > t4_p_author_local_RELATION_TABLE ON > t2_p_local.id=t4_p_author_local_RELATION_TABLE.publicationId > INNER JOIN AUTHOR t3_p_author_local ON > t3_p_author_local.id=t4_p_author_local_RELATION_TABLE.authorId > WHERE t1_a1.id=t3_p_author_local.id)) AND > (t5_a2.firstName = 'Jens') AND (EXISTS (SELECT > t2_p_local.id FROM PUBLICATION t2_p_local INNER JOIN > PUBLICATIONAUTHORS t4_p_author_local_RELATION_TABLE ON > t2_p_local.id=t4_p_author_local_RELATION_TABLE.publicationId > INNER JOIN AUTHOR t3_p_author_local ON > t3_p_author_local.id=t4_p_author_local_RELATION_TABLE.authorId > WHERE t5_a2.id=t3_p_author_local.id)) > 20:35:44,763 WARN [PublicationLocalHome] result was : > [1, 2] > 20:35:44,766 WARN [PublicationLocalHome] result class: > java.util.ArrayList > The query is exactly the same in both test-runs, but > the result is different. I will supply any > descriptors/source/etc needed. > regards, Jens Braeuer -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click _______________________________________________ JBoss-Development mailing list JBoss-Development@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jboss-development