Native Query resulting in error only in H2 database-CodePudding

I have a query that runs on MySQL databases but not on h2.

This is my repository:

 @Query(value = "SELECT r.* FROM rewards r "
        "INNER JOIN models m ON r.model_id = m.model_pk  "
        "WHERE m.printer_family = :businessPrinterFamily "
        "AND r.reward_type IN (:rewardTypes) "
        "AND IF(:isOpportunities, m.model_pk IN (:businessPrinterModels), TRUE) "
        "ORDER BY :sortingMethod",
      countQuery = "SELECT r.* FROM rewards r "
            "INNER JOIN models m ON r.model_id = m.model_pk  "
            "WHERE m.printer_family = :businessPrinterFamily "
            "AND r.reward_type IN (:rewardTypes) "
            "AND IF(:isOpportunities, m.model_pk IN (:businessPrinterModels), TRUE) ",
      nativeQuery = true)
  List<Reward> getFilteredRewards(@Param("sortingMethod") String sortingMethod,
      @Param("isOpportunities") boolean isOpportunities,
      @Param("businessPrinterModels") List<Integer> businessPrinterModels,
      @Param("rewardTypes") List<Integer> rewardTypes,
      @Param("businessPrinterFamily") int businessPrinterFamily, Pageable pageable);

But only on h2 I get the following error:

could not prepare statement; SQL [SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND [*]IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ? [42001-214]

In the logs I get the following:

2022-09-15 09:35:15.647 ERROR 267713 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   : Syntax error in SQL statement "SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND [*]IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ? [42001-214]

My h2 database is configured as follows:

spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=MySQL
    username: sa
    password:
    driver-class-name: org.h2.Driver
  jpa:
    defer-datasource-initialization: false
  h2:
    console:
      enabled: true
      path: /h2-console

Any ideas of whats wrong?

CodePudding user response：

The H2 MySQL compatibility mode doesn't mention anything about an IF() function support. Your options are (at least):

Request it from H2
Replace IF(a, b, c) by standard SQL CASE WHEN a THEN b ELSE c END
Use testcontainers to run integration tests directly on MySQL rather than on H2 (I've blogged about this here, as I highly recommend it, irrespective of the ORM used)
Use a SQL translator like jOOQ to translate your native SQL to the target dialect, under the hood.

The quickest fix is to use CASE. But personally, I recommend integration testing directly on MySQL, unless you're using H2 also as a production database product.