openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Siegel" <marc.siegel...@gmail.com>
Subject Bug in OpenJPA or bad JPQL?
Date Tue, 11 Dec 2007 21:44:38 GMT
Hi all,

I have found a bug in the JPQL to SQL compilation of OpenJPA.

The following JPQL query attempts to find all objects of type GaAdGroup that:
  - have a name that fits a certain LIKE pattern
  - has less than the maximum number of GaKeywords associated with it
  - has less than the maximum number of GaKeywords associated with
any of the GaAdGroups associated with the single GaCampaign which is
associated with it
  - has less than the maximum number of GaKeywords associated with
any of the GaAdGroups associated with any of the GaCampaigns associated with
the single GaAccount which is associated with the single GaCampaign which is
associated with it

Here is the JPQL:
 String jpql = "SELECT g FROM GaAdGroup g " +
            "WHERE (g.name LIKE '[" + exGroup.getName() + " #%]') " +
            "AND (" + MAX_KEYWORDS_ADGROUP + " > " +
            "     (SELECT COUNT(k) FROM g.gaKeywords k " +
            "      WHERE (k.status IS NULL OR k.status <> :kwDeleted)))" +
            "AND (" + MAX_KEYWORDS_CAMPAIGN + " > " +
            "     (SELECT COUNT(k) FROM " +
            "             IN ( g.gaCampaign.gaAdGroups ) g1, " +
            "             IN ( g1.gaKeywords ) k " +
            "      WHERE (k.status IS NULL OR k.status <> :kwDeleted)))" +
            "AND (" + MAX_KEYWORDS_ACCOUNT + " > " +
            "     (SELECT COUNT(k) FROM " +
            "             IN ( g.gaCampaign.gaAccount.gaCampaigns ) c, " +
            "             IN ( c.gaAdGroups ) g1, " +
            "             IN ( g1.gaKeywords ) k " +
            "      WHERE (k.status IS NULL OR k.status <> :kwDeleted)))" +
            "ORDER BY g.name ASC ";

Here is the generated SQL:
SELECT
    t2.id, t2.createdAt, t2.updatedAt, t2.isChanged, t2.synchronizedAt,
    t17.id, t17.createdAt, t17.updatedAt, t17.isChanged,
t17.synchronizedAt, t17.dailyBudget, t17.endDay,
    t18.id, t18.createdAt, t18.updatedAt, t18.isChanged,
t18.synchronizedAt, t18.clientEmail, t18.customerId,
t18.descriptiveName,
    t17.googleId, t17.name, t17.startDay, t17.status,
    t2.googleId, t2.maxCpc, t2.name, t2.status
FROM GaAdGroup t2
LEFT OUTER JOIN GaCampaign t17 ON t2.gaCampaignId = t17.id
LEFT OUTER JOIN GaAccount t18 ON t17.gaAccountId = t18.id
WHERE (t2.name LIKE '[AARP #%]' ESCAPE '\' AND 1500 >
(SELECT COUNT(t5.id) FROM GaKeyword t3, GaKeyword t4, GaKeyword t5
WHERE ((t4.status IS NULL OR t4.status <> 'Deleted') AND t3.id =
t4.id) AND t2.id = t3.gaAdGroupId)
AND 7500 > (SELECT COUNT(t1.id) FROM GaAdGroup t0, GaKeyword t1,
GaCampaign t6, GaAdGroup t7
WHERE ((t1.status IS NULL OR t1.status <> 'Deleted') AND t7.id =
t0.id) AND t0.id = t1.gaAdGroupId AND t2.gaCampaignId = t6.id AND
t6.id = t7.gaCampaignId)
AND 40000 > (SELECT COUNT(t9.id) FROM GaAdGroup t8, GaKeyword t9,
GaCampaign t10, GaAccount t11, GaCampaign t12, GaCampaign t13,
GaAdGroup t14, GaAdGroup t15, GaKeyword t16
WHERE ((t9.status IS NULL OR t9.status <> 'Deleted') AND t12.id =
t13.id AND t14.id = t15.id AND t16.id = t9.id) AND t2.gaCampaignId =
t10.id AND t10.gaAccountId = t11.id
AND t10.id = t15.gaCampaignId AND t11.id = t12.gaAccountId AND t15.id
= t16.gaAdGroupId AND t8.id = t9.gaAdGroupId AND t13.id =
t14.gaCampaignId)) ORDER BY
t2.name ASC


Specifically breaking this down to the first subquery, the
COUNT(t5.id) counts *ALL* GaKeyword objects, not limited to those
subject to the where constraints.

Am I misusing JPQL, or is this a bug?

Thanks
-Marc

Mime
View raw message