mysema/querydsl — Issue #80: JPASQL: List<Object[]> contains wrong results
Author: nyc2
Created: 2012-01-12 08:05:59+00:00
State: open

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 hour = createDateTime("hour", java.sql.Timestamp.class);

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

public QTbl(Path 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 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