openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Patrick Linskey" <plins...@gmail.com>
Subject Re: Bug in OpenJPA or bad JPQL?
Date Sun, 16 Dec 2007 12:52:08 GMT
Hi,

It looks like a bug to me. What happens if you simplify the query by
removing some of the other subqueries and WHERE clauses?

It looks like both the t4 and t5 aliases are unnecessary, and, more
seriously, the t5 alias is not being joined to the t3 alias (which is
what the WHERE clause is evaluated against).

-Patrick

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



-- 
Patrick Linskey
202 669 5907

Mime
View raw message