|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 copiesthe 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 =>  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 (selectc.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.htmlSent 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