After spending sometime on the issue, I would suggest to remove the alias for now as the problem it cause is quite severe. IMHO, adding a alias attribute in the mapping file might be a better solution than the current alias implementation. i.e. <map-to table="table_name" xml="xml_name" alias="alias_name"/> replacing <map-to table="table_name" xml="xml_name" /> Regards, Low Heng Sin ( [EMAIL PROTECTED] ) -----Original Message----- From: Thomas Yip [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 21, 2001 7:08 To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Castor 0.9.3 generating bogus SQL from OQL query after working in 0.9.2 In theory, joins should be done using "table alias" to avoid the namespace collide with other condition. At first, I thought the alias syntax was the problem for some database but not the other, so wasted some time in the wrong way. I later found that it is not the syntax problem. I do need some time to research on the proper translation (that probably become quite mathematics), and then will try to figure out how to correct the current implementation. Thorsten, will you have time to take a look before I do? But, before all that, I will finish the implementation of the better dependent object creation, which I've already half way. Before I work on the OQL, I really want to spend a few days to a week to the new lock layer that is essential to the implementation of many requested features. Maybe I will just rollback the alias for now if I keep getting more pressure from the mailing list? Thomas ---Original Message----- >From: Aberbach, Joel [mailto:[EMAIL PROTECTED]] >Sent: Friday, July 20, 2001 2:52 PM >To: [EMAIL PROTECTED] >Subject: [castor-dev] Castor 0.9.3 generating bogus SQL from OQL query after working in 0.9.2 > >Hello, > >I've come across a situation where some OQL queries that worked fine in >0.9.2 no longer work in 0.9.3. The problem is in the SQL that gets >generated - specifically a table that should only be in the FROM clause once >is ending up in there twice, one time with an alias, and one time without >one (this also affects the WHERE clause). I don't want to clutter things up >with all the files and database setup necessary to reproduce this, but I >wanted to see if this rang a bell as far as something that was changed from >0.9.2 to 0.9.3. Here is the final difference in the generated SQL in 0.9.2. >and 0.9.3 as copied from the QueryResults object in a debugger using the >following OQL query: "SELECT object FROM >authorization.persistent.TPrincipalPermissionDB object WHERE principalId=$1 >AND resourceSpecifier.resourceType.resourceTypeName=$2". It's doing a two >table join, and all relationships are 1-1. > >0.9.2 >"SELECT >"principal_permission"."id", >"principal_permission"."principal_id", >"principal_permission"."permission_id", >"principal_permission"."resource_specifier_id", >"principal_permission"."allow" >FROM >"resource_type","resource_specifier","principal_permission" >WHERE >"principal_permission"."resource_specifier_id" >="resource_specifier"."resource_specifier_id" AND > >"resource_specifier"."resource_type_id" >="resource_type"."resource_type_id" AND > >("principal_permission"."principal_id" = ? AND >"resource_type"."resource_type_name" = ?)" > >0.9.3 (notice the different FROM and WHERE clauses...no aliasing was >required in 0.9.2, and now it appears to be aliasing the resource_specifier >table after including it in the FROM clause unaliased) >"SELECT >"principal_permission"."id", >"principal_permission"."principal_id", >"principal_permission"."permission_id", >"principal_permission"."resource_specifier_id", >"principal_permission"."allow" > >FROM >"resource_type" "resource_type_0", >"resource_specifier", >"principal_permission", >"resource_specifier" "resource_specifier_0" > >WHERE >"principal_permission"."resource_specifier_id" >="resource_specifier_0"."resource_specifier_id" AND > >"resource_specifier"."resource_type_id" >="resource_type_0"."resource_type_id" AND > >("principal_permission"."principal_id" >= ? AND >"resource_type_0"."resource_type_name" >= ?)" > >This causes way too many records to be retrieved, including duplicate >records. If anyone wants the gory details on setting this up, let me know >and I'll send you some more information. I would greatly appreciate any >information regarding why this could be happening. > >thanks, > >-Joel Aberbach > >----------------------------------------------------------- >If you wish to unsubscribe from this mailing, send mail to >[EMAIL PROTECTED] with a subject of: > unsubscribe castor-dev > > ----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to [EMAIL PROTECTED] with a subject of: unsubscribe castor-dev ----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to [EMAIL PROTECTED] with a subject of: unsubscribe castor-dev
