openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Siegel" <marc.siegel...@gmail.com>
Subject Re: Bug in OpenJPA or bad JPQL?
Date Wed, 12 Dec 2007 13:42:39 GMT
Is there a qualified person who can clarify for me whether queries of
this sort are supposed to work?

Many thanks,
-Marc

On Dec 11, 2007 4:44 PM, Marc Siegel <marc.siegel.17@gmail.com> wrote:
> 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