hibernate query in spring boot with H2: Values of types "BOOLEAN" and "INTEGER"-CodePudding

I'm trying to run a custom query, and getting a strange (Seemingly unrelated) error. Error I'm getting is: o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90110, SQLState: 90110 Values of types "BOOLEAN" and "INTEGER" are not comparable; SQL statement:

select episode0_.episode_id as episode_1_1_, episode0_.air_date as air_date2_1_, episode0_.episode_number as episode_3_1_, episode0_.name as name4_1_, episode0_.season as season5_1_, episode0_.show_id as show_id7_1_, episode0_.watched as watched6_1_ from episode episode0_ where episode0_.episode_number=(select min(episode1_.episode_number) from episode episode1_ where episode1_.watched=0) and episode0_.season=(select min(episode2_.season) from episode episode2_ where episode2_.watched=0) and episode0_.show_id=? [90110-210]

Things I've tried: native query, and this: Custom Query H2 - Spring Boot

My repository:

import model.Episode;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Optional;

public interface EpisodeRepository extends JpaRepository<Episode, Long> {
    Optional<Episode> findByEpisodeId(Long episodeId);

    @Query("from EPISODE where episodeNumber = (SELECT MIN(episodeNumber) FROM EPISODE WHERE watched is false)"  
            " AND season = (SELECT MIN(season) FROM EPISODE WHERE watched is false) "  
            " AND show.showId = :showId")
    Optional<Episode> findNextUnwatchedEpisode(@Param("showId") Long showId);
}

My entity:

package model;

import lombok.*;

import javax.persistence.*;
import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
@Entity(name = "EPISODE")
public class Episode {
    @Id
    @Column(name = "EPISODE_ID", nullable = false)
    private Long episodeId;

    @Column(name = "NAME", nullable = false)
    private String name;

    @Column(name = "SEASON", nullable = false)
    private Integer season;

    @Column(name = "EPISODE_NUMBER", nullable = false)
    private Integer episodeNumber;

    @Column(name = "AIR_DATE", nullable = false)
    private Date airDate;

    @Column(name = "WATCHED", nullable = false)
    private boolean watched;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="SHOW_ID")
    private TVShow show;
}

application.properties:

spring.datasource.url=jdbc:h2:mem:my_tv;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

CodePudding user response：

Hibernate ORM supports H2 2.x.y only since the version 5.6.5.Final. Older versions produce invalid SQL that isn't accepted by new versions of H2 in default configuration.

You need to check version of hibernate-core jar used by your application and update it to a some recent version (current version is 5.6.7.Final).