Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Wrong SQL from EJBQL 
Please log a bug.

Usually Eclipselink would create the following:
"delete from cw_node WHERE EXISTS (select * from cw_cache_child_node c where c.node_id = 11000 and cw_node.node_id = c.child_node_id)" 
but that is not supported by MySQL.
The reason Eclipselink uses WHERE EXISTS instead of IN is that IN doesn't work if there's more than one field should be compared with subquery, for instance: "delete from cw_node WHERE EXISTS (select * from cw_cache_child_node c where c.node_id = 11000 and cw_node.node_id = c.child_node_id AND cw_node.node_name=c.child_node_name)" 
would be impossible to express using IN.
Because WHERE EXISTS is not supported by the platform, Eclipselink creates a temporary table. It uses LIKE because it's the easiest way to create temp table (don't need to guess fields' definitions). Of course the idesal would be LIKE with the list of field we want in the temp table - but that's not supported. But some platforms would pass nulls on insert for omitted fields - and I thought MySQL does just that.
----- Original Message ----- From: "ossaert" <decooman@xxxxxxxxx> 
To: <eclipselink-users@xxxxxxxxxxx>
Sent: Monday, November 10, 2008 3:59 AM
Subject: [eclipselink-users] Wrong SQL from EJBQL




Hi,
I have the following problem. I try to execute this EJBQL query: (on MySQL) 

Query query = super.entityManager.createQuery(
               "delete from NodeEntity n " +
               "where n.nodeId in " +
               "(select c.childNodeEntity.nodeId from CachedNodeEntity c "
+
               "where c.nodeEntity = :nodeEntity)");

Well, i try to delete all NodeEntity elements which are in the
CachedNodeEntity table where the parameter "nodeEntity" is the parent. I
think this is a legitimate piece of EJBQL?
unfortunately, EclipseLink tries to generate a temporary table and tries to insert the "nodeId" in this table. It takes the "LIKE" statement and copies 
the cw_node table. It inserts only the "nodeId" property, so all other
"not-null" field without a default are not copied, leading to this error.

CREATE TEMPORARY TABLE IF NOT EXISTS TL_cw_node LIKE cw_node
INSERT INTO TL_cw_node (node_id) SELECT t0.node_id FROM cw_node t0 WHERE
t0.node_id IN (SELECT t1.node_id FROM cw_cache_child_node t2, cw_node t1
WHERE (t2.node_id = ?))
       bind => [404]
SELECT 1
DELETE FROM TL_cw_node
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0.2 (Build
20081024)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Field 'date_creation' doesn't
have a default value
Error Code: 1364

It works in TopLink. There the table is created (without the constraints).
But WHY does Eclipse/TopLink use a temporary table with ALL fields?

By the way, rewriting the query as:

Query query = super.entityManager.createQuery(
               "delete from NodeEntity n " +
               "where n in " +
               "(select c.childNodeEntity from CachedNodeEntity c " +
               "where c.nodeEntity = :nodeEntity)");
does NOT function. The generated EJBQL is invalid: it has " .... WHERE ( IN 
(....)". It should be something like "WHERE t0 IN (....) ".
But hey, the SQL is very easy: "delete from cw_node where node_id in (select 
c.child_node_id from cw_cache_child_node c where c.node_id = 11000)". Why
can't the persistence layers create this code?

Thank you
Jan

--
View this message in context: http://www.nabble.com/Wrong-SQL-from-EJBQL-tp20361408p20361408.html 
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top