JPQL
- Since Expressions are an internal API, and JPQL and criteria are our primary API, this should be exposed in JPQL and criteria to be of use to our users.
- James.sutherland.oracle.com 09:06, 6 April 2010 (UTC)
- JPQL is definitely in the plans. Criteria is a good idea, I will have to look into extensibility of Criteria.
Operator name
- I would prefer "cast" to downcast, as it is the term generally used in Java. Also could be usable for interfaces or other usages than inheritance.
- I don't think it should use an outerjoin by default to join subclass tables. It should use what ever join was used to join the reference (if it was inner, then inner, if it was outer then outer). The cast should only be supported in the "from" clause in JPQL, not the where clause (as all join options are only supported in the from clause). Also avoid the double alias, it make no sense to have two aliases required for the same thing.
- i.e. "Select e from Employee e join e.address a cast(HomeAddress)", "Select e from Employee e left join e.address a cast(HomeAddress)"
- James.sutherland.oracle.com 09:06, 6 April 2010 (UTC)
- --Frank Schwarz 03:18, 14 April 2010 (UTC): How about: Select e from Employee e join (HomeAddress)e.address a and Select e from Employee e left join (HomeAddress)e.address a?
- --Tom.ware.oracle.com 19:21, 14 April 2010 (UTC)Based on my understanding of James proposal "Select e from Employee e join (HomeAddress)e.address a" would produce an inner join for both the address table and the HomeAddress tables and "Select e from Employee e left join (HomeAddress)e.address a" would produce an outer join for both the address table and the HomeAddress tables
- How about adding downcastAllowingNull() (or castAllowingNull()) to allow outer joins?
- --Tom.ware.oracle.com 13:52, 7 April 2010 (UTC)
- --Frank Schwarz 03:18, 14 April 2010 (UTC): I strongly second this proposal: We started this feature request with a strong hope that outer joins will be supported out of the box, i.e.: employeeE.anyOfAllowingNone("addresses", HomeAddress.class).get("x").greaterThan(5). Without outer-joins, this feature is of no practical value for us, as we need it to downcast to different subclasses or to use it with or-ed expressions in one query.
- --Tom.ware.oracle.com 19:25, 14 April 2010 (UTC) I am wondering how granular you need this to be. Will it be sufficient if employeeE.anyOfAllowingNone("addresses", HomeAddress.class) causes outer joins to both the Address table and the HomeAddress table? Or: Do you need something like: employeeE.anyOf("addresses").castAllowingNull(HomeAddress.cass) that would allow you to have an inner join to the Address table, but an outer Join to the HomeAddress table.
- --Frank Schwarz 15:00, 15 April 2010 (UTC) IMHO all joins have to be outer joins in this case. For testing purposes a little database:
drop table EMPLOYEE;
drop table ADDRESS;
drop table HOMEADDRESS;
drop table SUBHOMEADDRESS;
create table EMPLOYEE (id number primary key, name varchar2(100));
create table ADDRESS (id number primary key, type varchar2(100), employee_id number, city varchar2(100));
create table HOMEADDRESS (id number primary key, street varchar2(100));
create table SUBHOMEADDRESS (id number primary key, house varchar2(100));
insert into EMPLOYEE (id, name) values (1, 'p1');
--insert into ADDRESS (id, type, emplyoee_id, city) values (1, 'SUBHOMEADDRESS', 1, 'c1');
--insert into HOMEADDRESS (id, street) values (1, 's1');
--insert into SUBHOMEADDRESS (id, house) values (1, 'h1');
insert into EMPLOYEE (id, name) values (2, 'p2');
insert into ADDRESS (id, type, employee_id, city) values (2, 'SUBHOMEADDRESS', 2, 'c2');
insert into HOMEADDRESS (id, street) values (2, 's2');
insert into SUBHOMEADDRESS (id, house) values (2, 'h2');
--select all employees with either name p1 or house h2:
--correct
select t0.name from EMPLOYEE t0
left outer join ADDRESS t1 on (t0.id = t1.employee_id and (t1.TYPE = 'SUBHOMEADDRESS' or t1.TYPE = 'HOMEADDRESS' ))
left outer join HOMEADDRESS t2 on (t1.id = t2.id)
left outer join SUBHOMEADDRESS t3 on (t2.id = t3.id)
where t0.name = 'p1' or t3.house = 'h2';
--not correct
select t0.name from EMPLOYEE t0
join ADDRESS t1 on (t0.id = t1.employee_id and (t1.TYPE = 'SUBHOMEADDRESS' or t1.TYPE = 'HOMEADDRESS' ))
left outer join HOMEADDRESS t2 on (t1.id = t2.id)
left outer join SUBHOMEADDRESS t3 on (t2.id = t3.id)
where t0.name = 'p1' or t3.house = 'h2';
Informix
- Our Informix outer join support is broken, wrong, and outdated and should be removed in general. Either ignore the Informix outerjoin code, or remove it. We already have a bug logged by users to remove this as it prevents outer join working on Informix, which has supported the standard SQL join syntax for quite some time.
- I will remove
InheritancePolicy
- I would avoid adding additional state to InheritancePolicy. I should be possible to just dynamically add the join expressions as required when normalizing.
- James.sutherland.oracle.com 09:06, 6 April 2010 (UTC)
- Is there a particular reason why we would want to avoid that? InheritancePolicy already stores a bunch of join criteria related to children and constructs similar expressions in its initializeMultipleTablePrimaryKeyFields() method. I am having a hard time finding a reasonable way of building the downcast criteria anywhere else.
Table per class, interfaces
- We should support this for table per class and interfaces. Currently we don't support joining these, as we can't join in all of the distinct tables, but cast to one class would provide us a way to support this.
- James.sutherland.oracle.com 09:06, 6 April 2010 (UTC)
- Table-per-class support is currently very limited, my vote is against this, but I will defer to the majority.
