You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Clóvis Wichoski <cl...@uol.com.br> on 2004/07/01 20:07:46 UTC
wrong SQL generated when use path expression for extent with Multiple
Joined Tables
Hi,
I'm getting wrong results when executing a Query with Criteria that uses
path expression for extent with Multiple Joined Tables:
The QueryByCriteria from class myproject.User where [myCompany.shortcut
= COMPANYX, [login = UserX]]
The expected result is that returns only the User of COMPANYX with login
UserX, but this results in ALL login equals UserX, the OR part in SQL
generate the wrong result, because the field shortcut exists only in
superclass Person
In list archives I found some messages that may make sense for this error:
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=ojb-user@db.apache.org&msgNo=7587
maybe
that says "..if no field is found the name from the criteria is used.."
but for the multiple joined extend this must be false, or my mappings
are wrong?
and maybe this message started this trouble with multiple joined tables:
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=ojb-user@db.apache.org&msgId=612081
Follow the part of trace I'm getting here:
...
[org.apache.ojb.broker.core.PersistenceBrokerImpl] DEBUG: Creating
RsIterator for class [myproject.User]
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG: executeQuery :
QueryByCriteria from class myproject.User where [myCompany.shortcut =
COMPANYX, [login = UserX]]
[org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
DEBUG: TableAlias(): using hints ? false
[org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
DEBUG: TableAlias(): using hints ? false
[org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
DEBUG: TableAlias(): using hints ? false
[org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
DEBUG: TableAlias(): using hints ? false
[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
Result of getTableAlias(): PERSON A1
[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
SQL:SELECT
A0.VERSION,A0.STATUS,A0.PASSWORD,A0.LOGIN,A0.OID,A0.MY_COMPANY,A0.MY_EMPLOYEE
FROM USER A0,PERSON A1,EMPLOYEE A1E0,COMPANY A1E1 WHERE
A0.MY_COMPANY=A1.OID(+) AND A0.MY_COMPANY=A1E0.OID(+) AND
A0.MY_COMPANY=A1E1.OID(+) AND (( (A1.SHORTCUT = ? OR shortcut = ? OR
shortcut = ?)) AND (A0.LOGIN = ?))
...
but the correct SQL is:
SELECT
A0.VERSION,A0.STATUS,A0.PASSWORD,A0.LOGIN,A0.OID,A0.MY_COMPANY,A0.MY_EMPLOYEE
FROM USER A0,PERSON A1,EMPLOYEE A1E0,COMPANY A1E1 WHERE
A0.MY_COMPANY=A1.OID(+) AND A0.MY_COMPANY=A1E0.OID(+) AND
A0.MY_COMPANY=A1E1.OID(+) AND (( (A1.SHORTCUT = ? ) AND (A0.LOGIN = ?))
I'm using a compiled CVS checkout of version 1.0, with a SapDB database.
TIA
Clóvis
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: wrong SQL generated when use path expression for extent with
Multiple Joined Tables
Posted by Clóvis Wichoski <cl...@uol.com.br>.
I solved and post the patch to commit, please the solution in
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=ojb-dev@db.apache.org&msgId=1750208
Clóvis Wichoski wrote:
> Hi,
>
> I'm getting wrong results when executing a Query with Criteria that
> uses path expression for extent with Multiple Joined Tables:
>
> The QueryByCriteria from class myproject.User where
> [myCompany.shortcut = COMPANYX, [login = UserX]]
>
> The expected result is that returns only the User of COMPANYX with
> login UserX, but this results in ALL login equals UserX, the OR part
> in SQL generate the wrong result, because the field shortcut exists
> only in superclass Person
>
> In list archives I found some messages that may make sense for this
> error:
>
> http://nagoya.apache.org/eyebrowse/ReadMsg?listName=ojb-user@db.apache.org&msgNo=7587
> maybe
>
> that says "..if no field is found the name from the criteria is
> used.." but for the multiple joined extend this must be false, or my
> mappings are wrong?
>
> and maybe this message started this trouble with multiple joined tables:
>
> http://nagoya.apache.org/eyebrowse/ReadMsg?listName=ojb-user@db.apache.org&msgId=612081
>
>
> Follow the part of trace I'm getting here:
>
> ...
> [org.apache.ojb.broker.core.PersistenceBrokerImpl] DEBUG: Creating
> RsIterator for class [myproject.User]
> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG: executeQuery
> : QueryByCriteria from class myproject.User where [myCompany.shortcut
> = COMPANYX, [login = UserX]]
> [org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
> DEBUG: TableAlias(): using hints ? false
> [org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
> DEBUG: TableAlias(): using hints ? false
> [org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
> DEBUG: TableAlias(): using hints ? false
> [org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
> DEBUG: TableAlias(): using hints ? false
> [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> Result of getTableAlias(): PERSON A1
> [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:SELECT
> A0.VERSION,A0.STATUS,A0.PASSWORD,A0.LOGIN,A0.OID,A0.MY_COMPANY,A0.MY_EMPLOYEE
> FROM USER A0,PERSON A1,EMPLOYEE A1E0,COMPANY A1E1 WHERE
> A0.MY_COMPANY=A1.OID(+) AND A0.MY_COMPANY=A1E0.OID(+) AND
> A0.MY_COMPANY=A1E1.OID(+) AND (( (A1.SHORTCUT = ? OR shortcut = ? OR
> shortcut = ?)) AND (A0.LOGIN = ?))
> ...
>
> but the correct SQL is:
> SELECT
> A0.VERSION,A0.STATUS,A0.PASSWORD,A0.LOGIN,A0.OID,A0.MY_COMPANY,A0.MY_EMPLOYEE
> FROM USER A0,PERSON A1,EMPLOYEE A1E0,COMPANY A1E1 WHERE
> A0.MY_COMPANY=A1.OID(+) AND A0.MY_COMPANY=A1E0.OID(+) AND
> A0.MY_COMPANY=A1E1.OID(+) AND (( (A1.SHORTCUT = ? ) AND (A0.LOGIN = ?))
>
> I'm using a compiled CVS checkout of version 1.0, with a SapDB database.
>
> TIA
>
> Clóvis
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org