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


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.

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]
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

View this message in context:
Sent from the EclipseLink - Users mailing list archive at

eclipselink-users mailing list

Back to the top