CODEWITHSUNDEEP
javasql-serverhibernatejpa
Thomas Duchatelle
Thomas Duchatelle

Reputation: 385

Why ORDER BY DATEDIFF raise an exception on SQL server using JPA?

This is working as expected:

jdbcTemplate.query(
        "select datediff(week, '2017-01-02', creation_date), count(*) from somewhere group by datediff(week, '2017-01-02', creation_date)",
        new RowMapper() {
            Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return [rs.getObject(1), rs.getObject(2)]
            }
        }
)

But I get this exception when I run query below:

SQLServerException: Column 'somewhere.creation_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The query:

def date = new Date(117, 0, 2)
jdbcTemplate.query(
        "select datediff(week, ?, creation_date), count(*) from somewhere group by datediff(week, ?, creation_date)",
        new RowMapper() {
            Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return [rs.getObject(1), rs.getObject(2)]
            }
        },
        date,
        date
)

JPA Implementation is Hibernate 5.0.11. I'm using Spring & Groovy for the demo but actual code is plain java + JPA Criteria, obviously with the same issue.

Why this is not working? I'm selecting only things that are in Group By clause. And the date is the same: it's even the same instance!

Upvotes: 1

Views: 460

Answers (2)

Andreas
Andreas

Reputation: 159165

Because the query uses parameter markers (?), the SQL Optimizer cannot know that both markers will have the same value, so the result expression is considered to be different from the grouping expression.

You need to calculate the expression in a nested statement, so grouping and result can use the same intermediate value:

def date = new Date(117, 0, 2)
jdbcTemplate.query(
        "select weekdiff, count(*)" +
         " from ( select datediff(week, ?, creation_date) as weekdiff" +
                  " from somewhere" +
              " ) x" +
        " group by weekdiff",
        new RowMapper() {
            Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return [rs.getObject(1), rs.getObject(2)]
            }
        },
        date
)

Upvotes: 2

Tim
Tim

Reputation: 6060

Not being familiar with JPA, I can't say for certain, but I'm guessing those ? get expanded into @P1 and @P2 by the time it makes it to SQL Server, so your GROUP BY would be different than your SELECT.

Your SQL Statement probably looks like this to SQL Server:

SELECT @P1 = '2017-01-02', @P2 = '2017-01-02';
select datediff(week, @P1, creation_date), count(*) 
from somewhere 
group by datediff(week, @P2, creation_date)

Upvotes: 2

Related Questions