Catalina Wei/Silicon Valley/IBM is out of the office.
I will be out of the office starting 11/13/2006 and will not return until 12/04/2006. I will respond to your message when I return.
Re: JOIN in subselect problems
Roger,
There is a similar problem already reported under bug OPENJPA-51.
Catalina
On 3/13/07, roger.keays [EMAIL PROTECTED] wrote:
Is there anything wrong with the following query?
UPDATE Email e SET e.totalDeliveries = e.totalDeliveries + 1
WHERE e.email IN (
SELECT m.email FROM MailingList l JOIN l.subscribers m)
OpenJPA (0.9.6) parses this correctly, but can't seems to be missing the
JOIN clause or some table names from the generated SQL:
org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: syntax error at
or near WHERE {prepstmnt 27475707 UPDATE FB_EMAILS SET totalDeliveries =
(totalDeliveries + ?) WHERE email IN (SELECT DISTINCT t2.email FROM WHERE
(t2.email IN (SELECT t3.email FROM FB_EMAILS t3 WHERE t0.id = t1.id AND
t1.subscribers_email = t3.email))) [params=(long) 1]} [code=0,
state=42601]
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(
LoggingConnectionDecorator.java:188)
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(
LoggingConnectionDecorator.java:53)
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate
(LoggingConnectionDecorator.java:854)
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(
DelegatingPreparedStatement.java:266)
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(
DelegatingPreparedStatement.java:266)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate
(JDBCStoreManager.java:1360)
org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation(
JDBCStoreQuery.java:491)
org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate(
JDBCStoreQuery.java:420)
org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeUpdate
(ExpressionStoreQuery.java:685)
org.apache.openjpa.datacache.QueryCacheStoreQuery$QueryCacheExecutor.executeUpdate
(QueryCacheStoreQuery.java:343)
org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1028)
org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:793)
org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:868)
org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:864)
org.apache.openjpa.kernel.DelegatingQuery.updateAll(DelegatingQuery.java
:560)
org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:304)
Thanks,
Roger
--
View this message in context:
http://www.nabble.com/JOIN-in-subselect-problems-tf3394694.html#a9450588
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: [jira] Resolved: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
Abe, I have verified this fix with EagerFetchMode parallel and join. However, there is an outstanding problem for the following query with inner join fetch while left join fetch generates correct SQL; the inner join fetch generates extra unneeded left join: select o from Order o inner join fetch o.lineitems the generated SQL has an extra left join that causes the result set empty, I will create a new JIRA issue for this problem: 1297 demo TRACE [main] openjpa.Query - Executing query: select o from Order o inner join fetch o.lineitems 1297 demo TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 1831234854 executing prepstmnt 726281034 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM TORDER t0 INNER JOIN TORDERITEM t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN TORDERITEM t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC FOR READ ONLY Catalina On 4/11/07, Abe White (JIRA) [EMAIL PROTECTED] wrote: [ https://issues.apache.org/jira/browse/OPENJPA-134?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel] Abe White resolved OPENJPA-134. --- Resolution: Fixed Assignee: (was: Abe White) Fixed the most egregious issue, which was the cyclic fetching of eager bidirectional relations. Changed to cut off SELECTs when we're traversing the back-ptr to the owning side of a relation we've already fetched. I'm not convinced all the other issues mentioned are bugs given the eager fetch settings used. Please open new JIRAs for any individual issues that you believe remain. Fixed in revision 527565. Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER - Key: OPENJPA-134 URL: https://issues.apache.org/jira/browse/OPENJPA-134 Project: OpenJPA Issue Type: Bug Components: sql Reporter: Catalina Wei Fix For: 0.9.8 Running JPAConfiguration default setting for EagerFetchMode ( FetchModeValue.EAGER_PARALLEL), the SQL generated is sub-optimal. Consider the following entities: lineitems( OneToMany ) Order === OrderItem order ( ManyToOne ) Case 1: why not combining 2 SQL to 1 SQL ? = Order.lineitmes(EAGER): OrderItem.order(LAZY): Executing query: select o from Order o 2173 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 1299336562 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0 2213 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 [40 ms] spent 2223 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 1406424020 SELECT t0.oid, t1.lid, t1.version, t1.cost, t1.order_oid, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oidORDER BY t0.oid ASC Case 2: extra unneeded LEFT OUTER JOIN, if eliminated, the selection aliase t2 should change to t1: = Order.lineitmes(EAGER): OrderItem.order(LAZY): Executing query: select o from Order o left join fetch o.lineitems 2403 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1314410072 executing prepstmnt 1500797300 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC Case 3: why not generating 1 SQL ? == Order.lineitmes(EAGER): OrderItem.order(EAGER): Executing query: select o from Order o 2343 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 384833264 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0 2383 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 [40 ms] spent 2393 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 1722705582 SELECT t0.oid, t1.lid, t1.version, t1.cost, t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered, t2.shipaddr, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN Order t2 ON t1.order_oid = t2.oid ORDER BY t0.oid ASC 2393 demo TRACE [main
[DISCUSS] Mapping persistent field to Database back-end XML columns
[DISCUSS 1]:
Many database systems now have support for XML types which includes DB2 v9.1,
DB2 zOS v9, Oracle 10g, and SQLServer 2005.
Tables may contain XML columns where you can store XML documents.
What is OpenJPA's position of supporting the XML columns ?
The current mapping choices for XML columns are:
1. Java String
2. Java Byte array
Can openjpa provide other mapping options for XML columns?
[DISCUSS 2]:
Should XML mapping function be considered a new feature ?
[DISCUSS 3]:
My second part of the discussion is related to an XML Value Handler that I
implemented by extending AbstractValueHandler as described under Mapping
Extensions of OpenJPA developer'sGguide. My implementation has dependency on
JAXB bindings.
This XmlValueHandler is annotated on @Strategy(XmlValueHandler) for the
field that maps to XML column.
My XmlValueHandler worked fine for inserts and deletes, but not for updates.
The problem in updates is that the value passed to toDataStoreValue() is
wrapped in a Proxy, I can't get the original Class for the value object.
val.getClass() is returning the Proxy wrapped class, not the original class.
To workaround it, I have to use some klugy code - checking if val is a
proxy or not.
Is there a better way to find out the actual class from a proxy ?
public Object toDataStoreValue(ValueMapping vm, Object val,
JDBCStore store) {
// check for null value.
if (val==null) return null;
try {
//JAXBContext jc = JAXBContext.newInstance(val.getClass());
JAXBContext jc = JAXBContext.newInstance(
// on update val is a proxy, that can not be marshalled.
// use the declared type in this case.
(val.getClass().getName().contains($proxy))
? vm.getDeclaredType()
: val.getClass());
Marshaller m = jc.createMarshaller();
Writer result = new StringWriter();
m.marshal( val, result );
return result.toString();
}
Catalina
Re: xml store help?
Anh, I have created issue OPENJPA-238 for keeping track of this problem. The patch that I sent to you is also attached under OPENJPA-238. Catalina On 5/8/07, Ngo, Anh (ISS Southfield) [EMAIL PROTECTED] wrote: Hello, I am evaluating openJPA to use for our current project. It works fine for our need with database. But I am having problem with the XMLStoreManager. I have two simple objects – Customer and User. Customer can have multiple users. I look at the object store in xml file. It stored like this: ?xml version=1.0 encoding=UTF-8? extent object class=net.iss.mss.jpa.examples.remedy.Customer oid=1 version=0 field name=customerId 1 /field field name=customerName customer1 /field field name=users element *org.apache.openjpa.util.StringId:1* /element element *org.apache.openjpa.util.StringId:2* /element /field /object /extent Shoud the Users element store User object? The XMLStoreManager failed to load data in because it doest not know how to create* this object:org.apache.openjpa.util.StringId:1* * * Please help… * * I attached my orm.xml and persisentene.xml for your references. Sincerely, * * Anh Ngo Senior Application Programmer IBM Internet Security Systems 248 226 9059
[jira] Created: (OPENJPA-25) Incorrect SQL generated for queries involving more than one AbstractSchemaNames, generated SQL FROM clause is missing 'Table alias'
Incorrect SQL generated for queries involving more than one AbstractSchemaNames, generated SQL FROM clause is missing 'Table alias' --- Key: OPENJPA-25 URL: http://issues.apache.org/jira/browse/OPENJPA-25 Project: OpenJPA Issue Type: Bug Components: query Reporter: Catalina Wei select e, d from EmpBean e, DeptBean d generates following SQL where t2 is undefined: SELECT t1.empid, t2.deptno FROM EmpBean t0 JOIN EmpBean t1 ON (1 = 1) 'DeptBean t2' is missing in the FROM clause. Similar problem occurs to the following query: select d from EmpBean e left join e.dept d, ProjectBean p where e.salary = p.budget == incorrect SQL (t3 is undefined): SELECT t1.deptno, t1.budget, t1.mgr_empid, t1.name, t1.reportsTo_deptno FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno JOIN EmpBean t2 ON (1 = 1) WHERE (t2.salary = t3.budget) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (OPENJPA-26) JPQL parser failed to handle unary operations in arithmetic comparisons, eg. where -(e.salary+10) -10
JPQL parser failed to handle unary operations in arithmetic comparisons, eg. where -(e.salary+10) -10 --- Key: OPENJPA-26 URL: http://issues.apache.org/jira/browse/OPENJPA-26 Project: OpenJPA Issue Type: Bug Components: query Reporter: Catalina Wei Negation only works for numeric literals, should be generalized to arithmetic expressions. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (OPENJPA-27) SQL Parameter markers generated for literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE 42610)
SQL Parameter markers generated for literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE 42610) --- Key: OPENJPA-27 URL: http://issues.apache.org/jira/browse/OPENJPA-27 Project: OpenJPA Issue Type: Bug Components: query Reporter: Catalina Wei over usage of parameter markers for LITERALS causes DB2 SQL errors. Literals in the JP query if generated 'as is' in the pushdown SQL, can avoid DB2 SQL errors. Simple predicates caused DB2 SQL errors: where substring(e.name, 1, 5) = 'Harry' where mod(e.empid, 2) 0 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (OPENJPA-15) EJBQL grammar needs to be updated to relfect JPA specification
[ http://issues.apache.org/jira/browse/OPENJPA-15?page=all ] Catalina Wei updated OPENJPA-15: Attachment: OPENJPA-15.openjpa-kernel.txt This patch is applied successfully against openjpa-kenel. please ignore the previous attachments. EJBQL grammar needs to be updated to relfect JPA specification -- Key: OPENJPA-15 URL: http://issues.apache.org/jira/browse/OPENJPA-15 Project: OpenJPA Issue Type: Bug Components: query Reporter: David Wisneski Assigned To: Catalina Wei Attachments: OPENJPA-15.openjpa-kernel.txt, OPENJPA-15.txt when used in subquery, SELECT has different grammar than in top most query. SELECT NEW is not allowed. FROM in subuqery has different syntax than in top most query. For exampleFROM d.employees as e in subquery where it would be invalid in top query. Expression syntax in HAVING clause needs to allow HAVING min(e.salary) 10 is valid provided that e.salary is a grouping field. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER - Key: OPENJPA-134 URL: https://issues.apache.org/jira/browse/OPENJPA-134 Project: OpenJPA Issue Type: Bug Components: sql Reporter: Catalina Wei Running JPAConfiguration default setting for EagerFetchMode (FetchModeValue.EAGER_PARALLEL), the SQL generated is sub-optimal. Consider the following entities: lineitems( OneToMany ) Order === OrderItem order ( ManyToOne ) Case 1: why not combining 2 SQL to 1 SQL ? = Order.lineitmes(EAGER): OrderItem.order(LAZY): Executing query: select o from Order o 2173 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 1299336562 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0 2213 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 [40 ms] spent 2223 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 1406424020 SELECT t0.oid, t1.lid, t1.version, t1.cost, t1.order_oid, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid ORDER BY t0.oid ASC Case 2: extra unneeded LEFT OUTER JOIN, if eliminated, the selection aliase t2 should change to t1: = Order.lineitmes(EAGER): OrderItem.order(LAZY): Executing query: select o from Order o left join fetch o.lineitems 2403 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1314410072 executing prepstmnt 1500797300 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC Case 3: why not generating 1 SQL ? == Order.lineitmes(EAGER): OrderItem.order(EAGER): Executing query: select o from Order o 2343 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 384833264 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0 2383 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 [40 ms] spent 2393 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 1722705582 SELECT t0.oid, t1.lid, t1.version, t1.cost, t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered, t2.shipaddr, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN Order t2 ON t1.order_oid = t2.oid ORDER BY t0.oid ASC 2393 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 [0 ms] spent 3134 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 executing prepstmnt 950548648 SELECT t0.lid, t0.version, t0.cost, t1.oid, t1.version, t1.amount, t1.customer_countryCode, t1.customer_id, t1.delivered, t1.shipaddr, t0.part_partno, t0.quantity FROM OrderItem t0 LEFT OUTER JOIN Order t1 ON t0.order_oid = t1.oid WHERE t0.order_oid = ? [params=(int) 88] 3134 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1318342292 [0 ms] spent Case 4: duplicate selections and redundant joins == Order.lineitmes(EAGER): OrderItem.order(EAGER): Executing query: select o from Order o left join fetch o.lineitems 2273 demo TRACE [main] openjpa.jdbc.SQL - t 1094730048, conn 1307463150 executing prepstmnt 1565154634 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t3.oid, t3.version, t3.amount, t3.customer_countryCode, t3.customer_id, t3.delivered, t3.shipaddr, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid LEFT OUTER JOIN Order t3 ON t2.order_oid = t3.oid ORDER BY t2.order_oid ASC -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Created: (OPENJPA-135) join fetch not returning duplicate references which not conforming to ejb3.0 spec
join fetch not returning duplicate references which not conforming to ejb3.0 spec - Key: OPENJPA-135 URL: https://issues.apache.org/jira/browse/OPENJPA-135 Project: OpenJPA Issue Type: Bug Components: jpa Reporter: Catalina Wei In the description in EJB 3.0 JPA spec, section 4.4.5.3, the following query example SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1 The spec says this query returns 5 references to the department 1 entity if department 1 has 5 employees. The same query running with openjpa code, it returns only 1 reference to department 1 entity. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Created: (OPENJPA-215) unneeded left join SQL for queries with inner join fetch, related to issue OPENJPA-134
unneeded left join SQL for queries with inner join fetch, related to issue OPENJPA-134 Key: OPENJPA-215 URL: https://issues.apache.org/jira/browse/OPENJPA-215 Project: OpenJPA Issue Type: Bug Components: sql Affects Versions: 0.9.6, 0.9.7, 0.9.8 Reporter: Catalina Wei In verifying fixes for issue OPEN-134, found an outstanding problem. Extra unneeded join generated for the following query with inner join fetch while left join fetch generates correct SQL: select o from Order o inner join fetch o.lineitems the generated SQL has an extra left join that causes the result set empty, I will create a new JIRA issue for this problem: 1297 demo TRACE [main] openjpa.Query - Executing query: select o from Order o inner join fetch o.lineitems 1297 demo TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 1831234854 executing prepstmnt 726281034 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM TORDER t0 INNER JOIN TORDERITEM t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN TORDERITEM t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC FOR READ ONLY Abe, I have verified this fix with EagerFetchMode parallel and join. However, there is an outstanding problem for the following query with inner join fetch while left join fetch generates correct SQL: select o from Order o inner join fetch o.lineitems the generated SQL has an extra left join that causes the result set empty, I will create a new JIRA issue for this problem: 1297 demo TRACE [main] openjpa.Query - Executing query: select o from Order o inner join fetch o.lineitems 1297 demo TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 1831234854 executing prepstmnt 726281034 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM TORDER t0 INNER JOIN TORDERITEM t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN TORDERITEM t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC FOR READ ONLY -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12491902
]
Catalina Wei commented on OPENJPA-51:
-
Abe,
I am working on issue OPENJPA-51 for David Wisneski.
I need some education before attempting to resolve this issue.
How aliases in the subqueries are created and resolved ?
Consider the following 2 queries:
1. select o from Customer c, in(c.orders)o where o.amount (select
sum(o2.amount) from c.orders o2)
2. select o from Order where o.amount (select sum(o2.amount) from
Customer c, in(c.orders) o2)
Aside from the semantic difference, in the 2 queries, the syntax difference is
where Customer c is defined.
When Customer c is defined in the subquery, the generated SQL has syntax error,
essentially the FROM clause is missing the body and t2 in the selections is
undefined:
SELECT t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id,
t2.delivered
FROM TCUSTOMER t0 INNER JOIN TORDER t1 ON t0.countryCode =
t1.customer_countryCode AND t0.id = t1.customer_id WHERE (t2.amount (SELECT
SUM(t1.amount) FROM ))
Under the debugger, it shows in which SelectImpl the aliases/tables are
created/defined:
Query 1:_aliases
_tables
SelectImpl (main):TCUSTOMER=0 0=TCUSTOMER t0
TORDER=1
1=TORDER t1
oders.o
SelectImpl(subQ):TORDER=2 2=TORDER t2
orders
TORDER=3
jpqlalias1:
Query 2 (all aliase/tables are in the main SelectImpl, null in subQ's
SelectImpl)
SelectImpl(main): TCUSTOMER=0 0=TCUSTOMER t0
jpqlalias1:
TORDER=1
1=TORDER t1
jpqlalias1:.orders.o2
TORDER=2
2=TORDER t2
It seems to me that openjpa assumes the alias defined in subquery is always
derived from (i.e. correlated to) the main select.
Correct me if I am wrong.
I have tried changing the method in SelectImpl.findAlias(Table)
to call recordTableAlias() for subselect when the alias was found in parent
select by the following code fragment:
if (!fromParent _parent != null) {
boolean removeFromParent = key.toString().contains(:); //+
alias = _parent.findAlias(table, key, removeFromParent, this);
//false = removeFromParent
if (alias != null) {
if (removeFromParent) //+
recordTableAlias(table, key, alias); //+
return alias;
}
}
after this change, the SQL generates OK, but I do not think it is correct,
because the subselect is missing the join predicate (missing where clause) for
the path c.orders.
In debugging, I saw selectJoins for c.order s were created; one for the parent
select and one for the subselect. But somehow the one created for subselect
disappeared
( or never got set in SelectImpl's _join field for subselect), the main
SelectImpl's _join field seems to have the selectJoin for c.orders
( but no SQL predicate was generated out of this selectJoin).
Should the selectionJoin on the parent be moved to subselect ? How/When should
this be done?
Am I on the right track of resolving this issue?
Your input is much appreciated.
Catalina
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0
[jira] Updated: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Catalina Wei updated OPENJPA-51:
Attachment: openjpa-51.patch
Abe,
Could you please verify and comment on the patch I am providing for this issue.
Thanks.
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar, openjpa-51.patch
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE
(t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
s/b
select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno =
t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno
WHERE t1.empid ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5
ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int
200}
0|false|0.0.0 org.apache.openjpa.persistence.PersistenceException: Syntax
error: Encountered WHERE at line 1, column 520. {SELECT t2.empid, t2.bonus,
t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime,
t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager,
t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0
INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean
t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON
t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street =
t5.street WHERE (t2.empid ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno
AS BIGINT) = CAST(? AS BIGINT)} [code=3, state=42X01]
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
at
org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
at
org.apache.openjpa.lib.rop.EagerResultList.init(EagerResultList.java:37)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
at
org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
at
org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
at
com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:615)
at
junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
at
junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
at junit.framework.TestResult.runProtected(Unknown Source)
at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
at junit.framework.TestSuite.runTest(Unknown Source)
at junit.framework.TestSuite.run
[jira] Updated: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Catalina Wei updated OPENJPA-51:
Attachment: SelectImpl.java.patch
Abe,
Based on your comment, I have revised my patch.
Please verify if the new patch is a better solution.
Thanks.
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar, openjpa-51.patch,
SelectImpl.java.patch
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE
(t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
s/b
select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno =
t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno
WHERE t1.empid ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5
ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int
200}
0|false|0.0.0 org.apache.openjpa.persistence.PersistenceException: Syntax
error: Encountered WHERE at line 1, column 520. {SELECT t2.empid, t2.bonus,
t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime,
t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager,
t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0
INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean
t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON
t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street =
t5.street WHERE (t2.empid ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno
AS BIGINT) = CAST(? AS BIGINT)} [code=3, state=42X01]
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
at
org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
at
org.apache.openjpa.lib.rop.EagerResultList.init(EagerResultList.java:37)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
at
org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
at
org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
at
com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:615)
at
junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
at
junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
at junit.framework.TestResult.runProtected(Unknown Source)
at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
at junit.framework.TestSuite.runTest(Unknown
[jira] Updated: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Catalina Wei updated OPENJPA-51:
Attachment: SelectImpl.java.patch
Abe,
I make some mistake in my previous patch, attaching a revised version.
your conceren about removedAliasFromParent[] flag once set, was never reset;
that is purposely done.
In case any flag (base on number of aliases created so far) is on, there is
potentially duplicate joins in the parent select (those joins were initially
created for aliases initially attached in parent which was removed from parent,
and now attached on subselect), we need to remove the duplicate joins on the
parent, since there is one also created for subselect. The duplicate joins once
removed, will never be seen on the next call to removeJoinsFromParent().
The local boolean flag is set when joins are indeed removed from parent.
Otherwise the normal processing of removing duplcate joins from subselect is
performed.
I think there is a much better solution to this issue, that is to create
SelectImpl when a SELECT is encountered, rather than delaying it later as it
is currently done.
Current code creates aliases for the subselect to come later -- in the middle
of alias creating for subselect, there isn't a SelectImpl created for subselect
yet.. So the alias is temporarily attached under the parent SelectImpl. (This
could be based on the assumption that any alias defined in subselect is
correated to outer subselect).
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar, openjpa-51.patch,
SelectImpl.java.patch, SelectImpl.java.patch
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE
(t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
s/b
select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno =
t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno
WHERE t1.empid ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5
ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int
200}
0|false|0.0.0 org.apache.openjpa.persistence.PersistenceException: Syntax
error: Encountered WHERE at line 1, column 520. {SELECT t2.empid, t2.bonus,
t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime,
t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager,
t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0
INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean
t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON
t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street =
t5.street WHERE (t2.empid ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno
AS BIGINT) = CAST(? AS BIGINT)} [code=3, state=42X01]
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
at
org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
at
org.apache.openjpa.lib.rop.EagerResultList.init(EagerResultList.java:37)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
at
org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512
[jira] Commented: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493764
]
Catalina Wei commented on OPENJPA-51:
-
Abe,
Is it really a regression? I replaced the copy of SelectImpl with revision
52881 (the original without my fixes),
the similar query I ran gets missing FROM body:
1078 demo TRACE [main] openjpa.Query - Executing query: select c from
Customer c where exists(select o from c.orders o where o.oid = 1) or
exists(select o from c.orders o where o.oid = 2)
1156 demo TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 1643274738
executing prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version,
t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT
t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND
t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id =
t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS
BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode =
t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long)
2]
1172 demo TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 1643274738
[16 ms] spent
1172 demo TRACE [main] openjpa.jdbc.JDBC - t 1094861122, conn 0 [0 ms]
close
Exception: 0.0.0 nonfatal general error
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE:
-104, SQLSTATE: 42601, SQLERRMC: (;t0.name FROM WHERE;WHERE {prepstmnt
2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state,
t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2
WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND
t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS
(SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT)
AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id =
t1.customer_id )) [params=(long) 1, (long) 2]} [code=-104, state=42601]SQLCA
OUTPUT[Errp=SQLNP012, Errd=[-2145779603, 0, 0, 0, -705, 0]]
0.0.0 nonfatal general error
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE:
-104, SQLSTATE: 42601, SQLERRMC: (;t0.name FROM WHERE;WHERE {prepstmnt
2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state,
t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2
WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND
t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS
(SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT)
AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id =
t1.customer_id )) [params=(long) 1, (long) 2]} [code=-104, state=42601]SQLCA
OUTPUT[Errp=SQLNP012, Errd=[-2145779603, 0, 0, 0, -705, 0]]
Are you sure your query ever worked before this fix ?
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar, SelectImpl.java.patch
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE
(t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
s/b
select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno =
t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno
WHERE t1.empid ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5
ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int
200}
0|false|0.0.0 org.apache.openjpa.persistence.PersistenceException: Syntax
error: Encountered WHERE at line 1, column 520. {SELECT t2.empid, t2.bonus,
t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime,
t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager,
t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0
[jira] Commented: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494138
]
Catalina Wei commented on OPENJPA-51:
-
Abe,
You are right, the code before theispatch (SelectImpl.java revision 52881)
worked for to-one relation,
but never worked for to-many relation.
Do you have any idea of fixing this problem?
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar, SelectImpl.java.patch
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE
(t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
s/b
select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno =
t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno
WHERE t1.empid ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5
ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int
200}
0|false|0.0.0 org.apache.openjpa.persistence.PersistenceException: Syntax
error: Encountered WHERE at line 1, column 520. {SELECT t2.empid, t2.bonus,
t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime,
t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager,
t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0
INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean
t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON
t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street =
t5.street WHERE (t2.empid ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno
AS BIGINT) = CAST(? AS BIGINT)} [code=3, state=42X01]
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
at
org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
at
org.apache.openjpa.lib.rop.EagerResultList.init(EagerResultList.java:37)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
at
org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
at
org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
at
com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:615)
at
junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
at
junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
at junit.framework.TestResult.runProtected(Unknown Source)
at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109
[jira] Created: (OPENJPA-238) Error in XMLFileHandler, failed to load field element with type org.apace.openjpa.util.StringId.
Error in XMLFileHandler, failed to load field element with type org.apace.openjpa.util.StringId. Key: OPENJPA-238 URL: https://issues.apache.org/jira/browse/OPENJPA-238 Project: OpenJPA Issue Type: Bug Components: xml store Reporter: Catalina Wei Problem reported by Ngo, Anh (full description with attachments can be found in openjpa-dev mails, subject title: xml store help? In evaluating openJPA to use for our current project. It works fine for our need with database. But I am having problem with the XMLStoreManager. I have two simple objects - Customer and User. Customer can have multiple users. I look at the object store in xml file. It stored like this: ?xml version=1.0 encoding=UTF-8? extent object class=net.iss.mss.jpa.examples.remedy.Customer oid=1 version=0 field name=customerId 1 /field field name=customerName customer1 /field field name=users element org.apache.openjpa.util.StringId:1 /element element org.apache.openjpa.util.StringId:2 /element /field /object /extent Shoud the Users element store User object? The XMLStoreManager failed to load data in because it doest not know how to create this object: org.apache.openjpa.util.StringId:1 Please help... -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-238) Error in XMLFileHandler, failed to load field element with type org.apace.openjpa.util.StringId.
[ https://issues.apache.org/jira/browse/OPENJPA-238?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Catalina Wei updated OPENJPA-238: - Attachment: XMLFileHandler.patch The attached patch has been sent to Anh Ngo, and it resolved his problem. I need some openjpa developer to verify the patch is correct before committing this patch. Error in XMLFileHandler, failed to load field element with type org.apace.openjpa.util.StringId. Key: OPENJPA-238 URL: https://issues.apache.org/jira/browse/OPENJPA-238 Project: OpenJPA Issue Type: Bug Components: xml store Reporter: Catalina Wei Attachments: XMLFileHandler.patch Problem reported by Ngo, Anh (full description with attachments can be found in openjpa-dev mails, subject title: xml store help? In evaluating openJPA to use for our current project. It works fine for our need with database. But I am having problem with the XMLStoreManager. I have two simple objects - Customer and User. Customer can have multiple users. I look at the object store in xml file. It stored like this: ?xml version=1.0 encoding=UTF-8? extent object class=net.iss.mss.jpa.examples.remedy.Customer oid=1 version=0 field name=customerId 1 /field field name=customerName customer1 /field field name=users element org.apache.openjpa.util.StringId:1 /element element org.apache.openjpa.util.StringId:2 /element /field /object /extent Shoud the Users element store User object? The XMLStoreManager failed to load data in because it doest not know how to create this object: org.apache.openjpa.util.StringId:1 Please help... -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-238) Error in XMLFileHandler, failed to load field element with type org.apace.openjpa.util.StringId.
[ https://issues.apache.org/jira/browse/OPENJPA-238?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Catalina Wei updated OPENJPA-238: - Attachment: XMLFileHandler.patch New patch for OPENJPA-238. The previous patch won't compile with Java1.4.2. Error in XMLFileHandler, failed to load field element with type org.apace.openjpa.util.StringId. Key: OPENJPA-238 URL: https://issues.apache.org/jira/browse/OPENJPA-238 Project: OpenJPA Issue Type: Bug Components: xml store Reporter: Catalina Wei Attachments: XMLFileHandler.patch, XMLFileHandler.patch Problem reported by Ngo, Anh (full description with attachments can be found in openjpa-dev mails, subject title: xml store help? In evaluating openJPA to use for our current project. It works fine for our need with database. But I am having problem with the XMLStoreManager. I have two simple objects - Customer and User. Customer can have multiple users. I look at the object store in xml file. It stored like this: ?xml version=1.0 encoding=UTF-8? extent object class=net.iss.mss.jpa.examples.remedy.Customer oid=1 version=0 field name=customerId 1 /field field name=customerName customer1 /field field name=users element org.apache.openjpa.util.StringId:1 /element element org.apache.openjpa.util.StringId:2 /element /field /object /extent Shoud the Users element store User object? The XMLStoreManager failed to load data in because it doest not know how to create this object: org.apache.openjpa.util.StringId:1 Please help... -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-51) bad sql pushdown, sub select is missing from clause
[
https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Catalina Wei updated OPENJPA-51:
Attachment: SelectImpl.java.patch
Abe,
Could you please verify if this patch regress your tests ?
thanks.
bad sql pushdown, sub select is missing from clause
---
Key: OPENJPA-51
URL: https://issues.apache.org/jira/browse/OPENJPA-51
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
Assigned To: David Wisneski
Fix For: 0.9.8
Attachments: failureEntities.jar, SelectImpl.java.patch,
SelectImpl.java.patch
451 - bad sql pushdown sub select is missing from clause
TEST451; select e from EmpBean e where e.empid any (select e1.empid from
DeptBean d, in(d.emps) e1 where d.no = 200)
28344 TRACE [main] openjpa.jdbc.SQL - t 1094861122, conn 295440796 [0 ms]
executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget,
t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street,
t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street,
t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON
t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street =
t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE
(t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
s/b
select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno =
t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno
WHERE t1.empid ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5
ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int
200}
0|false|0.0.0 org.apache.openjpa.persistence.PersistenceException: Syntax
error: Encountered WHERE at line 1, column 520. {SELECT t2.empid, t2.bonus,
t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime,
t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager,
t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0
INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean
t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON
t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street =
t5.street WHERE (t2.empid ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno
AS BIGINT) = CAST(? AS BIGINT)} [code=3, state=42X01]
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
at
org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
at
org.apache.openjpa.lib.rop.EagerResultList.init(EagerResultList.java:37)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
at
org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
at
org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
at
com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
at
com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:615)
at
junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
at
junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
at junit.framework.TestResult.runProtected(Unknown Source)
at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
at junit.framework.TestSuite.runTest(Unknown Source
[jira] Created: (OPENJPA-240) Persistent field mappings to database supported XML columns
Persistent field mappings to database supported XML columns --- Key: OPENJPA-240 URL: https://issues.apache.org/jira/browse/OPENJPA-240 Project: OpenJPA Issue Type: New Feature Components: jdbc Reporter: Catalina Wei Many database systems such as DB2 version 9, Oracle 10g, and SQLServer 2005 have support for XML column types. XML documents can be stored natively in the XML columns in the database. What is OpenJPA's position of supporting the XML columns ? -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
