PSQLException: The column name clazz_ was not found in this ResultSet

Question

I am trying to fetch a PlaceEntity. I've previously stored a bunch of GooglePlaceEntity objects where

@Entity
@Table(name = "place")
@Inheritance(
        strategy = InheritanceType.JOINED
)
public class PlaceEntity extends AbstractTimestampEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
}

and

@Entity
@Table(name = "google_place")
public class GooglePlaceEntity extends PlaceEntity {
    // Additional fields ..
}

However, neither do I want to send information stored in google_place nor do I want to load it unnecessarily. For this reason I am only fetching

public interface PlaceRepository extends JpaRepository<PlaceEntity, Long> {

    @Query(value = "" +
            "SELECT * " +
            "FROM place " +
            "WHERE earth_distance( " +
            "   ll_to_earth(place.latitude, place.longitude), " +
            "   ll_to_earth(:latitude, :longitude) " + 
            ") < :radius",
            nativeQuery = true)
    List<PlaceEntity> findNearby(@Param("latitude") Float latitude,
                                 @Param("longitude") Float longitude,
                                 @Param("radius") Integer radius);

}

and what I get is this:

org.postgresql.util.PSQLException: The column name clazz_ was not found in this ResultSet.
    at org.postgresql.jdbc.PgResultSet.findColumn(PgResultSet.java:2588) ~[postgresql-9.4.1208-jdbc42-atlassian-hosted.jar:9.4.1208]
    at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2481) ~[postgresql-9.4.1208-jdbc42-atlassian-hosted.jar:9.4.1208]
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getInt(HikariProxyResultSet.java) ~[HikariCP-2.7.8.jar:na]
    at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    ...

I am able to run this statement in pure PostgreSQL:

SELECT * FROM place WHERE 
earth_distance( 
  ll_to_earth(place.latitude, place.longitude), 
  ll_to_earth(17.2592522, 25.0632745)
) < 1500;

but not using the JpaRepository.

And by the way, fetching a GooglePlaceEntity is working however:

@Query(value = "" +
        "SELECT * " +
        "FROM place JOIN google_place ON google_place.id = place.id " +
        "WHERE earth_distance( " +
        "   ll_to_earth(place.latitude, place.longitude), " +
        "   ll_to_earth(:latitude, :longitude) " + 
        ") < :radius",
        nativeQuery = true)
List<GooglePlaceEntity> findNearby(@Param("latitude") Float latitude,
                                   @Param("longitude") Float longitude,
                                   @Param("radius") Integer radius);

deleted answer, found a pretty identical one https://stackoverflow.com/questions/2140992/jpa-native-query-for-entity-with-inheritance — Zeromus, Apr 12 '18 at 23:46
@Zeromus I would love to hear the technical story behind this. I debugged the code and it seems that it branches when it detects that a class has been derived and then it tries to build such a joined query which it, at least as I see it, shouldn't do. However, writing native queries is not the reason why I am using Hibernate. At this point I converted the *is-a* to a *has-a* relation. — Stefan Falk, Apr 13 '18 at 07:57

score 11 · Answer 1 · answered Oct 09 '20 at 09:45

In case of @Inheritance(strategy = InheritanceType.JOINED), when you retrieve data without nativeQuery=True in JPA repository, Hibernate will execute SQL like the following:

SELECT
table0_.id as id1_1_,
table0_.column2 as column2_2_1_,
... (main_table cols)
table0_1_.column1 as column1_1_0_,
... (table1 to N-1 cols)
table0_N_.column1 as column1_1_9_,
... (tableN-th cols)
CASE WHEN table0_1_.id is not null then 1
    ... (table1 to N-1 cols)
    WHEN table0_N_.id is not null then N
    WHEN table0_.id is not null then 0
    END as clazz_
FROM table table0_
left outer join table1 table0_1_ on table0_.id=table0_1_.id
... (other tables join)
left outer join table2 table0_N_ on table0_.id=table0_N_.id

From the above SQL you can see clazz specification. If you want to map ResultSet to your super instance (PlaceEntity), you should specify clazz_ column in SELECT by yourself.

In your case it will be:

@Query(value = "" +
            "SELECT *, 0 AS clazz_ " +
            "FROM place " +
            "WHERE earth_distance( " +
            "   ll_to_earth(place.latitude, place.longitude), " +
            "   ll_to_earth(:latitude, :longitude) " + 
            ") < :radius",
            nativeQuery = true)

@AradGonen I think it's because of `InheritanceType.JOINED`. When you are mapping your result set to class , it will ask for some sort of **discriminator column**, which is not provided in `nativeQuery=true`. But I also don't know why it works for derived class and does not work for super class. — Meirkhan Yesseyev, Oct 22 '20 at 08:44

score 0 · Answer 2 · answered Dec 31 '19 at 12:21

You should use the name of the class instead of the table name on the query. Change place to PlaceEntity.

@Query(value = "" +
    "SELECT * " +
    "FROM place JOIN google_place ON google_place.id = place.id " +
    "WHERE earth_distance( " +
    "   ll_to_earth(place.latitude, place.longitude), " +
    "   ll_to_earth(:latitude, :longitude) " + 
    ") < :radius",
    nativeQuery = true)
List<GooglePlaceEntity> findNearby(@Param("latitude") Float latitude,
                               @Param("longitude") Float longitude,
                               @Param("radius") Integer radius);

PSQLException: The column name clazz_ was not found in this ResultSet

2 Answers2

Linked