[ 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
