Comment 1 Chris Delahunt 2008-09-03 10:46:34 EDT

Hello,

The bug in essentials does not mention an error, just that the incorrect results are returned - has something changed in your testing?

Please also try removing the join clauses and see if that is related to the problem, as it would simplify the issue greatly and allow us to reproduce it - otherwise we would need a full testcase to duplicate your object model.  Also, since EclipseLink is being worked on, can you please run this in EclipseLink and provide the SQL that gets generated in the working case and the error case?  If an exception is thrown, please provide that as well.  

I believe calling Count(Distinct dm.mistake) when you are grouping by dm.mistake should cause it to always return a count of 1, is this correct?

Comment 2 Markus KARG 2008-09-04 05:57:41 EDT

(In reply to comment #1)

> The bug in essentials does not mention an error, just that the incorrect
> results are returned - has something changed in your testing?

I have not changed my testing. The query is a new one, never did GROUP BY before in JPA.

> Please also try removing the join clauses and see if that is related to the
> problem, as it would simplify the issue greatly and allow us to reproduce it -
> otherwise we would need a full testcase to duplicate your object model.

I have stripped down the model to this simple one, which still reproduces the problem:

Problematic Query: "SELECT a.b, COUNT(DISTINCT a.b) FROM A a GROUP BY a.b"

Code of Entity A:

@Entity
public class A {
    @Id private int aid;
    @ManyToOne private B b;
}

Code of Entity B:

@Entity
public class B {
    @EmbeddedId private BPK pk;
}

Code of Primary Key Class BPK:

@Embeddable
public class BPK implements Serializable {
    private int bid;
    public int hashCode() {
        return this.bid;
    }
    public boolean equals(Object other) {
        if (other instanceof BPK)
            return this.bid == (((BPK) other).bid);
        return false;
    }
}

The exception is exactly the same, so now you have the most simple test case to reproduce it on any DBMS you like. :-)

Playing around with the code I found two more different workarounds, and it might be interesting for you to know about it:

(1) Removing the DISTINCT keyword makes exception go away, and produces the following, correct SQL: "SELECT t0.BID, COUNT(t0.BID) FROM B t0, A t1 WHERE (t0.BID = t1.BID) GROUP BY t0.BID".

(2) Not using the primary key class BPK but instead using "@Id private int bid;" directly inside of entity B makes exception go away, and produces the following, correct SQL: "SELECT t0.BID, COUNT(DISTINCT(t0.BID)) FROM B t0, A t1 WHERE (t0.BID = t1.B_BID) GROUP BY t0.BID".

So for me it seems the cause is the combination of @EmbeddedId and DISTINCT. Maybe fixing the bug now is easier with this information.

> Also,
> since EclipseLink is being worked on, can you please run this in EclipseLink
> and provide the SQL that gets generated in the working case and the error case?

I have run the above simplified case on EclipseLink and the exception is the same as in TopLink:

Exception [EclipseLink-6121] (Eclipse Persistence Services - 1.0 (Build 1.0 - 20080707)): org.eclipse.persistence.exceptions.QueryException
Exception Description: The query has not been defined correctly, the expression builder is missing.  For sub and parallel queries ensure the queries builder is always on the left.
	at org.eclipse.persistence.exceptions.QueryException.invalidBuilderInQuery(QueryException.java:614)
	at org.eclipse.persistence.internal.expressions.SQLSelectStatement.appendFromClauseToWriter(SQLSelectStatement.java:496)
	at org.eclipse.persistence.internal.expressions.SQLSelectStatement.printSQL(SQLSelectStatement.java:1421)
	at org.eclipse.persistence.internal.expressions.SubSelectExpression.printSQL(SubSelectExpression.java:196)
	at org.eclipse.persistence.expressions.Expression.printSQLWithoutConversion(Expression.java:3274)
	at org.eclipse.persistence.expressions.ExpressionOperator.printCollection(ExpressionOperator.java:1774)
	at org.eclipse.persistence.internal.expressions.FunctionExpression.printSQL(FunctionExpression.java:391)
	at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:1812)
	at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:277)
	at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.translateExpression(ExpressionSQLPrinter.java:288)
	at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.printExpression(ExpressionSQLPrinter.java:118)
	at org.eclipse.persistence.internal.expressions.SQLSelectStatement.printSQL(SQLSelectStatement.java:1425)
	at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.printSQLSelectStatement(DatabasePlatform.java:2508)
	at org.eclipse.persistence.internal.expressions.SQLSelectStatement.buildCall(SQLSelectStatement.java:742)
	at org.eclipse.persistence.descriptors.ClassDescriptor.buildCallFromStatement(ClassDescriptor.java:610)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.setCallFromStatement(StatementQueryMechanism.java:387)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareReportQuerySelectAllRows(ExpressionQueryMechanism.java:1513)
	at org.eclipse.persistence.queries.ReportQuery.prepareSelectAllRows(ReportQuery.java:1221)
	at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:787)
	at org.eclipse.persistence.queries.ReportQuery.prepare(ReportQuery.java:993)
	at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:463)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:616)
	at org.eclipse.persistence.queries.DatabaseQuery.prepareCall(DatabaseQuery.java:1533)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:230)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:170)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:134)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:95)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:80)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:733)
	at client.Client.main(Client.java:17)

For the created SQL: The correct SQL is found above in case (2), while you will never see any incorrect SQL as the EclipseLink exception happens BEFORE the SQL string is created (or tell me how to convince EclipseLink to print the SQL before crashing -- I set logging level to ALL and see a lot of lines, but none of them contains any SQL).

> I believe calling Count(Distinct dm.mistake) when you are grouping by
> dm.mistake should cause it to always return a count of 1, is this correct?

Yes, that is true. But the original JPA QL in my actual use case is not counting over the group itself, but certainly over a different entity of the original JOIN. I stripped that down to this more simple but senseless JPA QL to show that the problem is not caused by the other entity, but it happens also when using the same one.

Is there anything more you want me to do?

Comment 3 Peter Krogh 2009-11-27 13:41:04 EST

This bug fix did not make the cut off for 2.0.0. We are deferring the bugs to Future where we can properly sort them all together based on community votes and severity. We will then assign them accordingly to future patch sets and releases.

Comment 4 Peter Krogh 2009-11-30 11:36:19 EST

Changing the priority of the bugs that have been recently triaged to future.  Targetting them to P2 will differentiate them from the P3s that have been triaged into future earlier.

Comment 5 James Sutherland 2011-11-10 11:08:37 EST

I looked into this, and found a few things,

- count distinct only allow one column on most databases, so we do a crazy workaround by translating the query into a subquery to work around the limitation, however this workaround is very fragile and does not work with group by (or many other things), this is the error he is getting
- MySQL actually allows count distinct with multiple columns, so we should be bouncing this off the platform, and at least doing the simple thing on MySQL, other platforms could potentially use concat to make a single field which would be much simpler
- his specific example does not have a composite primary key, so does need all of this anyway, a simple count on the id will work, but the crazy code is trying to use attributes instead of fields so makes it way more complicated than it needs to be

Comment 6 James Sutherland 2011-11-10 11:09:43 EST

fixing the issues for singleton embedded id

Comment 7 James Sutherland 2011-11-14 09:25:13 EST

added support for platform that support multiple fields in distinct such as MySQL.

Comment 8 James Sutherland 2011-11-16 11:25:13 EST

See, Bug#314025

Note this specific issue is fixed.  But count distinct on an object with a composite id will still have issues on some database platforms (other than MySQL)

But I'm not sure these can be supported without database support.

Comment 9 Eclipse Webmaster 2022-06-09 10:15:58 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink

Comment 10 Eclipse Webmaster 2022-06-09 10:31:27 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink