H2 DB supporting IF conditionals from SQL

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 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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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,&&

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0