JPASQL: List<Object[]> contains wrong results

[nyc2]

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]

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]

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]

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]

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]

released in 2.3.1