Bug 335622

Description Will Butler 2011-01-27 16:18:23 EST

Build Identifier: 2.1.2

Forum post: http://www.eclipse.org/forums/index.php?t=msg&th=203591&start=0&S=4ed628cf51810b529364ad539df555fb

When setting up a query using an expression build like (parentBuilder is the main expression builder for this query):

ExpressionBuilder parentBuilder = new ExpressionBuilder();
ExpressionBuilder childBuilder = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(DomainObject.class, childBuilder);
Expression startWith = childBuilder.equal(parentBuilder.get("parent"));
Expression connectBy = childBuilder.get("children");
subQuery.setHierarchicalQueryClause(startWith, connectBy, null);
subQuery.addAttribute("oid");
return parentBuilder.notExists(subQuery);

I expect the resultant sql to look like:

SELECT t0.* FROM DOMAIN_OBJECT t0 WHERE NOT EXISTS (SELECT t1.OID FROM DOMAIN_OBJECT t1 START WITH t1.OID = t0.PARENT_OID CONNECT BY t1.PARENT_OID = PRIOR t1.OID);


However, the generated sql ends up using the wrong alias (t1) in the start with clause:

SELECT t0.* FROM DOMAIN_OBJECT t0 WHERE NOT EXISTS (SELECT t1.OID FROM DOMAIN_OBJECT t1 START WITH t1.OID = t1.PARENT_OID CONNECT BY t1.PARENT_OID = PRIOR t1.OID);

This appears to be caused by the normalization of the start with expression - an assumption is made that all portions of the expression should be built on the expression builder for the sub query, which isn't true in this case.

Reproducible: Always

Steps to Reproduce:
1. Execute a query using the expression as described above.