Bugs item #518617, was opened at 2002-02-16 17:55
You can respond by visiting:
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=518617&group_id=22866
Category: JBossCMP
Group: v3.0 Rabbit Hole
>Status: Closed
>Resolution: Fixed
Priority: 5
Submitted By: Thomas Hamann (thomash76)
Assigned to: Dain Sundstrom (dsundstrom)
Summary: unfavorable query composition
Initial Comment:
win2k, jdk1.3.1_001
Hi,
when the following is specified in jbosscmp-jdbc.xml...
<declared-sql>
<from>t, TITLE tt</from>
<where>tt.locale_column={0} and tt.uid_column=t.uid_column</where>
<order>title_column</order>
</declared-sql>
... for a bean with table 'TEMPLATE', JBoss generates:
SELECT TEMPLATE.uid_column FROM TEMPLATE t, TITLE tt WHERE tt.locale_column=? and
tt.uid_column=t.uid_column ORDER BY title_column
unfortunately Oracle8 does not accept this statement, because it does not manage to
match
'TEMPLATE' (in select clause) with 't' (in where clause).
when writing the above query manually, it is most likely to be:
SELECT t.uid_column FROM TEMPLATE t, TITLE tt WHERE tt.locale_column=? and
tt.uid_column=t.uid_column ORDER BY title_column
which will be accepted/and executed by Oracle.
i suggest to insert the alias (if specified) of a table into the select clause of the
query instead of
using the original table name. so a modification of the query composer would be
necessary...
however, there is a workaround for this bug - just avoid to use an alias for the table
of a bean and
additionally prepend that table name to the corresponding columns in the where clause,
like that:
SELECT TEMPLATE.uid_column FROM TEMPLATE, TITLE tt WHERE tt.locale_column=? and
tt.uid_column=TEMPLATE.uid_column ORDER BY title_column
i believe that this workaround is suitable for simple queries only. i expect ambiguity
exceptions
(raised by Oracle) when using the same table several times in a query without and with
aliases in
parallel...
Thomas
----------------------------------------------------------------------
>Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-03-03 14:03
Message:
Logged In: YES
user_id=251431
I added optional alias element to select in declared-sql
for specifing the exact alias that will be used for the
main select table.
----------------------------------------------------------------------
You can respond by visiting:
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=518617&group_id=22866
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
