Wrong SQL from EJBQL

[ossaert]

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

[Andrei Ilitchev]

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@...>
To: <eclipselink-users@...>
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@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

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