Bug 124679

Summary: OQL compatibility with MS SQL Server
Product: [Retired] Red Hat Web Application Framework Reporter: Brett Prucha <pruchaba_bah>
Component: persistenceAssignee: Rafael H. Schloming <rafaels>
Status: CLOSED WONTFIX QA Contact: Jon Orris <jorris>
Severity: medium Docs Contact:
Priority: medium    
Version: nightlyCC: richardl, tross
Target Milestone: ---Keywords: FutureFeature
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2005-09-28 21:37:42 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Brett Prucha 2004-05-28 13:58:55 UTC 
Description of problem:

When joining two objects with an association where the retrieve 
operator is used and the association contains an object with more 
than one column as key then OQL joins the tables using:

table1 join table2 on
    (table2.column1, table2.column2) in ([static retrieve])

An example of this is the association between Party and PartyEmail

SQL server does not support this kind of in expression.  I suggest 
using:

table1 join table2 on 
    exists (select column1, column2 from ([static retrieve]) _temp
        where _temp.column1 = table2.column1
              and _temp.column2 = table2.column2)

You can do this by modifying 
com.redhat.persistence.oql.Get.PropertyCondition.emitStatic();

replace lines 250-259 with:

in.append("exists (select ");
for (int i = 0; i < cols.length; i++) {
	in.append(cols[i]);
	if (i < cols.length - 1) {
		in.append(", ");
	}
}
in.append(" from (");
in(m.getRetrieve().getSQL(), cols, bindings, in);
in.append(") _temp ");
in.append("where ");
for (int i = 0; i < cols.length; i++) {
	in.append("_temp." + cols[i] + " = " + to[i]);
	if (i < to.length - 1) {
		in.append(" and ");
	}
}
in.append(")");
Comment 1 Rafael H. Schloming 2004-05-28 15:09:05 UTC 
We'll have to parameterize this on db. I used to have it the way you
describe, but oracle has problems with that version. Not sure which
version makes a better default.