Bugs item #1112731, was opened at 2005-01-30 20:00 Message generated for change (Tracker Item Submitted) made by Item Submitter You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1112731&group_id=22866
Category: JBossCMP Group: v4.0 Status: Open Resolution: None Priority: 5 Submitted By: Jens Bräuer (jensbr) Assigned to: Nobody/Anonymous (nobody) Summary: MEMBER OF Bug in SQL92 Compiler Initial Comment: 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. Environment is: - JBoss 4.0.1 - included Hypersonic - Java 1.4.2 regards, Jens Braeuer ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1112731&group_id=22866 ------------------------------------------------------- This SF.Net email is sponsored by: IntelliVIEW -- Interactive Reporting Tool for open source databases. Create drag-&-drop reports. Save time by over 75%! Publish reports on the web. Export to DOC, XLS, RTF, etc. Download a FREE copy at http://www.intelliview.com/go/osdn_nl _______________________________________________ JBoss-Development mailing list JBoss-Development@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jboss-development
