[FIXED] H2 DB supporting IF conditionals from SQL

Issue

I have a sql native query in JDBC that uses an IF clause (I also tried with CASE WHEN) and it works fine in production, since I use MariaDB. But for my unit tests we use H2, the issue is that H2 does not understand some sql syntax.

I found here href="https://stackoverflow.com/questions/46408493/dbunit-jdbcsqlexception-function-not-found">DbUnit - JdbcSQLException: Function "*" not found about user-defined SQL functions, I created my alias function for the IF() sql method and it works for one of the cases I need it to work. The first case is as follows IF(table.value = 0, 'yes', 'no'), but the second case uses a nested comparator as follows IF(table.value1 = 0 && table.value2 =0, 'yes', 'no'), in this case it is failing.

This is my IF user-defined SQL function

@SuppressWarnings("unused")
public class H2Function {
    public static String IF(Boolean condition, String ifTrue, String ifFalse) {
        return condition ? ifTrue: ifFalse;
    }
}

Here how I link it in the properties.yaml file

spring:
  datasource:
    url: jdbc:h2:mem:db__PRIMARY__;MODE=MySQL;DB_CLOSE_ON_EXIT=FALSE
    driver-class-name: org.h2.Driver
    hikari:
      connection-init-sql: "CREATE ALIAS IF NOT EXISTS IF DETERMINISTIC FOR \"com.package.test.H2Function.IF\";"

And the native query definition

"SELECT user, Coalesce(AVG(value1), 0) AS avg1,"
" Coalesce(AVG(value2), 0) AS avg2, Coalesce(AVG(value3), 0) AS avg3, "
" IF(Coalesce(AVG(value1), 0)=0 && Coalesce(AVG(value2), 0)=0, 'yes', 'no') AS bottom " " FROM table tb ... "

I cannot use internally the avg1 and avg2 due to query aliases not working properly. Using just IF(Coalesce(AVG(value1), 0)=0, 'yes', 'no') or IF(Coalesce(AVG(value2), 0)=0, 'yes', 'no') works fine with my user-defined sql function, but using both with && fails. P.D.: All of this is to be able to sort all records that have value1=value2=0 always to the bottom of the list, no matter the sorting order, if you know a way to accomplish this without going into so much trouble, please let me know.

Solution

Solved it: Apparently H2 also has problems interpreting the && operator. I changed it to AND, which is interchangeable with && in mySQL, and now H2 interprets it and test passes, and I get the desired result when deployed using MariaDB.

stackoverflow question about AND-&&

doc on operators precedence

mySQL manual on AND,&&

Answered By - Zhmel
Answer Checked By - Marilyn (JavaFixing Volunteer)