#2262 MEMBER OF Bug in SQL92 Compiler

[Jens Braeuer]

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

[Jens Braeuer]

Logged In: YES
user_id=480617

Also i found the following post in the CMP-forum which seems
to be related:
http://www.jboss.org/index.html?module=bb&op=viewtopic&t=54880

[Scott M Stark]

Logged In: YES
user_id=175228

All issues have been moved to http://jira.jboss.com. Existing
issues have been moved. New issues will be closed with this
canned reponse.

[Alexey Loubyansky]

Logged In: YES
user_id=543482

Resubmit to jira.jboss.com and what are the two entries in
the db?

[Jens Braeuer]

Logged In: YES
user_id=480617

Resubmitted to jira.jboss.org as requested.

http://jira.jboss.com/jira/browse/JBAS-1364