Mysql SQL query DATEDIFF failed in H2 where mode was MYSQL

Question

Background: In one of my projects I am doing component testing on Spring Batch using JUnit. Here application DB is MYSQL. In Junit test execution I let the data-source switch between

• MYSQL and
• H2(jdbc:h2:mem:MYTESTDB;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL)

based on configuration. Use MYSQL as the data source for debugging purpose and H2 to run the test in isolation in build servers.

Everything works fine until in application logic I had to use a query with DATEDIFF.

Issue: Query fails with

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

Reason: Even through H2 run on MySQL mode it uses H2 Functions and those functions are different

MYSQL DATEDIFF definition is DATEDIFF(expr1,expr2)
 e.g. SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31') 
      ==> 1

H2 DATEDIFF definision is DATEDIFF(unitstring, expr1, expr2)
 unitstring =  { YEAR | YY | MONTH | MM | WEEK | DAY | DD | DAY_OF_YEAR
 | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS }
 e.g. SELECT DATEDIFF(dd, '2010-11-30 23:59:59','2010-12-31') 
      ==> 1

Solutions tried and failed: I tried to write a custom function

 package com.asela.util;                                                                                                                                

 import java.lang.reflect.Field;                                                                                                                                                         
 import java.sql.Date;                                                                                                                                                                   
 import java.time.temporal.ChronoUnit;                                                                                                                                                   
 import java.util.Map;                                                                                                                                                                   
 import java.util.Objects;                                                                                                                                                               

 import org.h2.expression.Function;                                                                                                                                                      

 public class H2Function {                                                                                                                                                               
    public static long dateDifference(Date date1, Date date2) {                                                                                                                          
        Objects.nonNull(date1);                                                                                                                                                          
        Objects.nonNull(date2);                                                                                                                                                          
        return ChronoUnit.DAYS.between(date1.toLocalDate(), date2.toLocalDate());                                                                                                        
    }                                                                                                                                                                                    
 } 

And set it with H2

DROP ALIAS IF EXISTS DATEDIFF;
CREATE ALIAS DATEDIFF FOR "com.asela.util.H2Function.dateDifference";

Above was not able to replace existing DATEDIFF still fails with

org.h2.jdbc.JdbcSQLException: Function alias "DATEDIFF" already exists; SQL statement:

Any other approach I can try to make this work?

Answer 1

Got a workaround with Reflection for the problem. Access H2 Functions map and remove DATEDIFF from there. Then add the replacement function.

package com.asela.util;

import java.lang.reflect.Field;
import java.sql.Date;
import java.time.temporal.ChronoUnit;
import java.util.Map;
import java.util.Objects;

import org.h2.expression.Function;

public class H2Function {

    @SuppressWarnings("rawtypes")
    public static int removeDateDifference() {
        try {
              Field field = Function.class.getDeclaredField("FUNCTIONS");
              field.setAccessible(true);
              ((Map)field.get(null)).remove("DATEDIFF");
        } catch (Exception e) {
            throw new RuntimeException("failed to remove date-difference");
        }
        return 0;
    }

    public static long dateDifference(Date date1, Date date2) {
        Objects.nonNull(date1);
        Objects.nonNull(date2);
        return ChronoUnit.DAYS.between(date1.toLocalDate(), date2.toLocalDate());
    }
}

Then in schema

CREATE ALIAS IF NOT EXISTS REMOVE_DATE_DIFF FOR "com.asela.util.H2Function.removeDateDifference";
CALL REMOVE_DATE_DIFF();
DROP ALIAS IF EXISTS DATEDIFF;
CREATE ALIAS DATEDIFF FOR "com.asela.util.H2Function.dateDifference";