"could not extract ResultSet" in JPQL GROUP BY query

Question

In my Spring Boot 1.5 application with Spring Data JPA, I have a TDR table like:

     Table TDR
-----------------------
id | c_id | m_id | p_id
 1 |   1  |    1 |    1
 2 |   1  |    1 |    2
 3 |   2  |    1 |    1
 4 |   2  |    1 |    2
 5 |   3  |    1 |    1

where I need to find the last records grouped by m_id and p_id. With PostgreSQL SQL, I do this with the MAX aggregate function:

SELECT t.id, t.c_id, t.m_id, t.p_id
FROM (
  SELECT MAX(id) AS id, m_id, p_id 
  FROM tdr
  WHERE m_id = 1                      -- <<< parameterized below
  GROUP BY m_id, p_id
  ) m JOIN tdr t ON t.id = m.id

which returns the correct records:

     Table TDR
-----------------------
id | c_id | m_id | p_id
 4 |   2  |    1 |    2
 5 |   3  |    1 |    1

Since the GROUP BY does not include c_id, the SQL is structured as correlated sub-query and matched with a join (see this question for details).

I'd like to convert this SQL to JPQL with this Lombok @Data entity:

@Data
@Entity
@Table(name = "TDR")
public class TDR implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false)
    private Long id;

    @ManyToOne(optional=false, cascade = { CascadeType.ALL })
    @JoinColumn(name="m_id")
    private M m;

    @ManyToOne(optional=false, cascade = { CascadeType.ALL })
    @JoinColumn(name="p_id")
    private P p;

    @ManyToOne(optional=false, cascade = { CascadeType.ALL })
    @JoinColumn(name="c_id")
    private C c;
}

but my JPQL for the correlated sub-query with parameterized m ID (:m_id):

SELECT t FROM TDR t WHERE t.id in (
  SELECT MAX(t.id) AS id, t.m, t.p FROM TDR t WHERE t.m = :m GROUP BY t.m, t.p
  )

results in this error:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   // at ...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   // at ...
Caused by: org.postgresql.util.PSQLException: ERROR: subquery has too many columns

It'd be helpful if you posted the actual error – Catchwa Mar 30 '17 at 23:36 — Catchwa, Mar 30 '17 at 23:36

score 0 · Answer 1 · answered Mar 31 '17 at 01:08

0

The thing that jumps out to me is that your first SELECT:

SELECT FROM

...doesn't state which columns you're selecting

answered Mar 31 '17 at 01:08

Catchwa

5,845
4
31
57

score 0 · Accepted Answer · edited May 23 '17 at 12:25

0

Select just the join column in the correlated sub-query:

SELECT t FROM TDR t WHERE t.id in (
  SELECT MAX(t.id) FROM TDR t WHERE t.m = :m GROUP BY t.m, t.p
  )

The ObjectDB GROUP BY article was helpful as was this question.

edited May 23 '17 at 12:25

Community

1
1

answered Mar 31 '17 at 09:27

Jan Nielsen

10,892
14
65
119

"could not extract ResultSet" in JPQL GROUP BY query

2 Answers2