New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

JPASQL: List<Object[]> contains wrong results #80

Closed
nyc2 opened this Issue Jan 12, 2012 · 5 comments

Comments

Projects
None yet
2 participants
@nyc2

nyc2 commented Jan 12, 2012

Original post: https://groups.google.com/forum/#!topic/querydsl/bITZvV1rIqA

Values of second column are equal to values of first column, follow these steps to reproduce the issue:
On SQL Server:
create table tbl (
[hour] smalldatetime not null,
[count] bigint not null
)

declare @from smalldatetime=getdate()
declare @to smalldatetime=dateadd(hour,1,@from)
insert tbl values (@from,100000000000000)
insert tbl values (@from,200000000000000)
insert tbl values (@to,100000000000000)

QTbl:
import static com.mysema.query.types.PathMetadataFactory.forVariable;

import com.mysema.query.types.Path;
import com.mysema.query.types.PathMetadata;
import com.mysema.query.types.path.DateTimePath;
import com.mysema.query.types.path.NumberPath;

/**

  • QTbl is a Querydsl query type for QTbl
    */
    public class QTbl extends com.mysema.query.sql.RelationalPathBase {

    private static final long serialVersionUID = -66574608;

    public static final QTbl tbl = new QTbl("tbl");

    public final NumberPath count = createNumber("count", Long.class);

    public final DateTimePath<java.sql.Timestamp> hour = createDateTime("hour", java.sql.Timestamp.class);

    public QTbl(String variable) {
    super(QTbl.class, forVariable(variable), "dbo", "tbl");
    }

    public QTbl(Path<? extends QTbl> entity) {
    super(entity.getType(), entity.getMetadata(), "dbo", "tbl");
    }

    public QTbl(PathMetadata<?> metadata) {
    super(QTbl.class, metadata, "dbo", "tbl");
    }

}

Test:
import java.math.BigInteger;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import org.apache.log4j.Logger;
import org.testng.Assert;
import org.testng.annotations.Test;

import com.mysema.query.jpa.sql.JPASQLQuery;
import com.mysema.query.sql.SQLServerTemplates;

public class AggregateHoursTest {

private static Logger logger = Logger.getLogger(AggregateHoursTest.class);

@Test
public void aggregateHours() {
    EntityManager em = null;
    try {
        em = Persistence.createEntityManagerFactory("TestPU").createEntityManager();
        em.getTransaction().begin();

        JPASQLQuery query = new JPASQLQuery(em, new SQLServerTemplates());
        List<Object[]> rows = query.from(QTbl.tbl).groupBy(QTbl.tbl.hour.hour()).orderBy(QTbl.tbl.hour.hour().asc()).list(QTbl.tbl.hour.hour(),
                QTbl.tbl.count.sum());
        for (Object[] row : rows) {
            Assert.assertFalse(BigInteger.valueOf((Integer) row[0]).equals(row[1]));
        }

        em.getTransaction().commit();
    }
    catch (Exception ex) {
        Assert.fail();
        logger.error(ex);
        em.getTransaction().rollback();
    }
    finally {
        try {
            em.close();
        }
        catch (Exception ex) {
        }
    }
}

}

Persistence provider: Hibernate 3.2.4
JDBC driver: jTDS 1.2.5

Generated SQL:
select datepart(hour, tbl.hour), sum(tbl.count)
from tbl tbl
group by datepart(hour, tbl.hour)
order by datepart(hour, tbl.hour) asc

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 12, 2012

Member

Do you get the same results when you execute the query directly via the JPA API?

There is no transformation of the query results so this might be an issue with Hibernate

AbstractJPASQLQuery:

@Override
public List<Object[]> list(Expression<?>[] args) {
    Query query = createQuery(args);
    reset();
    return query.getResultList();
}
Member

timowest commented Jan 12, 2012

Do you get the same results when you execute the query directly via the JPA API?

There is no transformation of the query results so this might be an issue with Hibernate

AbstractJPASQLQuery:

@Override
public List<Object[]> list(Expression<?>[] args) {
    Query query = createQuery(args);
    reset();
    return query.getResultList();
}
@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Jan 13, 2012

You're right, it is indeed an issue with Hibernate 3.2.4, as EntityManager.createNativeQuery(...).getResultList() delivers the very same results.

Switching to Hibernate 4.0.1 makes things much more clearly. There, I get a meaningful exception:

org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [] during auto-discovery of a native-sql query

It seems that Hibernate expects an unique alias for each column and does not generate them itself. In the above query we had two times the same alias, namely an empty string.

Explicit definition of column aliases works:

List<Object[]> rows = em.createNativeQuery("select datepart(hour,tbl.hour) col1,sum(tbl.count) col2 from tbl tbl group by datepart(hour,tbl.hour) order by datepart(hour,tbl.hour) asc").getResultList();
List<Object[]> rows = query.from(QTbl.tbl).groupBy(QTbl.tbl.hour.hour()).orderBy(QTbl.tbl.hour.hour().asc()).list(QTbl.tbl.hour.hour().as("col1"), QTbl.tbl.count.sum().as("col2"));

nyc2 commented Jan 13, 2012

You're right, it is indeed an issue with Hibernate 3.2.4, as EntityManager.createNativeQuery(...).getResultList() delivers the very same results.

Switching to Hibernate 4.0.1 makes things much more clearly. There, I get a meaningful exception:

org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [] during auto-discovery of a native-sql query

It seems that Hibernate expects an unique alias for each column and does not generate them itself. In the above query we had two times the same alias, namely an empty string.

Explicit definition of column aliases works:

List<Object[]> rows = em.createNativeQuery("select datepart(hour,tbl.hour) col1,sum(tbl.count) col2 from tbl tbl group by datepart(hour,tbl.hour) order by datepart(hour,tbl.hour) asc").getResultList();
List<Object[]> rows = query.from(QTbl.tbl).groupBy(QTbl.tbl.hour.hour()).orderBy(QTbl.tbl.hour.hour().asc()).list(QTbl.tbl.hour.hour().as("col1"), QTbl.tbl.count.sum().as("col2"));
@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 13, 2012

Member

Ok, so it seems for non-Path projections a generic alias could be added, e.g col1, col2 ...

That can be internally in Querydsl. What do you think?

Member

timowest commented Jan 13, 2012

Ok, so it seems for non-Path projections a generic alias could be added, e.g col1, col2 ...

That can be internally in Querydsl. What do you think?

@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Jan 13, 2012

Yes, having more than one non-Path projections will lead to this behavior.

In my opinion, this would be the job of Hibernate, but as a workaround Querydsl could do that.
This works for me at the moment, but it covers SimpleExpressions only:

    protected final QueryResults executeQuery(JPASQLQuery query, Expression<?>... projections) {
        for (int i = 0; i < projections.length; i++) {
            if (projections[i] instanceof SimpleExpression<?>) {
                SimpleExpression<?> expr = (SimpleExpression<?>) projections[i];
                projections[i] = expr.as("col" + i);
            }
        }
        List<Object[]> rows = query.list(projections);
        return new QueryResults(rows);
    }

nyc2 commented Jan 13, 2012

Yes, having more than one non-Path projections will lead to this behavior.

In my opinion, this would be the job of Hibernate, but as a workaround Querydsl could do that.
This works for me at the moment, but it covers SimpleExpressions only:

    protected final QueryResults executeQuery(JPASQLQuery query, Expression<?>... projections) {
        for (int i = 0; i < projections.length; i++) {
            if (projections[i] instanceof SimpleExpression<?>) {
                SimpleExpression<?> expr = (SimpleExpression<?>) projections[i];
                projections[i] = expr.as("col" + i);
            }
        }
        List<Object[]> rows = query.list(projections);
        return new QueryResults(rows);
    }
@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 7, 2012

Member

released in 2.3.1

Member

timowest commented Feb 7, 2012

released in 2.3.1

@timowest timowest closed this Feb 7, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment