Migration of 5.2.17.FINAL to 6.4.1 problems

Hibernate ORM
1

Migrating from Spring 2.0.3 to 3.2.2 Loaded Hibernate via the standard Spring packages via MVN.

I have looked at the Hibernate Migration guides on GitHub but did not find a note which seems to apply. A query which used to work is throwing an error now. I have not run all the queries the app does but this runs at startup so a good place to start I guess.

Here’s the HQSL:

String minuteIncrementCheckSql = "SELECT t FROM TestSuiteSchedule t "
				+ "WHERE "
				+ " (SYS_EXTRACT_UTC(SYSTIMESTAMP)) >= (t.startDt - (1/24/60)) and (SYS_EXTRACT_UTC(SYSTIMESTAMP)) <= t.endDt and "
				+ " (CASE WHEN t.lastScheduled is null "  
				+ "       then mod(round(((cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date) - cast(t.startDt as date)) * 60 * 24)), t.schedule) "
				+ "		  else mod(round((cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date)-cast(t.lastScheduled as date))*60*24), t.schedule)END)= 0 "
				+ " and t.testSuiteId in (select ts.testSuiteID from TestSuite ts where UPPER(ts.testType) in ('REGRESSION','BASIC') and UPPER(ts.executionType) in ('SCHEDULE','BOTH'))";
		List<TestSuiteSchedule> list = em.createQuery(minuteIncrementCheckSql).getResultList();

when it runs I see:
org.hibernate.query.SemanticException: Operand of - is of type 'java.lang.Integer' which is not a temporal amount (it is not an instance of 'java.time.TemporalAmount')

What I have done is:
in previous version there was a dialect class:

public class OracleDialectNvarchar extends org.hibernate.dialect.Oracle12cDialect {

    public OracleDialectNvarchar() {
        registerHibernateType(Types.NVARCHAR, 2000, "string");
    }
}

I tried putting that in but registerHibernateType seems to work a bit different.
I tried not have any dialect as this seems to be what the recommendation is.
I tried using the org.hibernate.dialect.OracleDialect which is in 6.4.1
I tried using community dialects for Oracle12
I tried using a newer JDBC driver. I have used 12.2.0.1.0 & 19.21.0.0

I guess I need a custom dialect class like what was done before but not really sure the how to on doing that.

What is it I need to do to get this to work

2

I don’t know for sure what you’re trying to do here, but this mostly worked before because Hibernate ORM 5 did not understand the types of expressions and just passed stuff through to the database.
ORM 6 now understands expression types which allows certain emulations etc., but also comes with stricter validations.

You will have to form a type checkable query by rewriting your query. You use expressions like t.startDt - (1/24/60) or (cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date)-cast(t.lastScheduled as date) which don’t necessarily have a well defined meaning.

The error you are getting is for the first expression i.e. you can’t subtract an integer from a date, because the integer has no unit. I have no idea why you’re trying to subtract 1/24/60 from a date since that is a very small number. I guess your intention was to subtract one day? If so, then you can use t.startDt - 1 day. See the documentation for more information about duration literals.

The next error you will probably see is about your use of durations for methods that expect a number. Subtraction of dates will result in a duration with day precision. If you want to get the amount of hours, you can use the by unit syntax to do the conversion e.g. (cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date)-cast(t.lastScheduled as date)) by hour.

3

hum … interesting … so re-write the query is what you are recommending? At a high level what the query does is return the list of test suites which need to be run. will post the updated query.

4

well it runs now but seems like a value is being injected somehow.

here’s the rewrite HSQL

		String minuteIncrementCheckSql = "SELECT t FROM TestSuiteSchedule t "
				+ "WHERE "
				+ " (SYS_EXTRACT_UTC(SYSTIMESTAMP)) >= function('min_ago', t.startDt)"
				+ " and cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as timestamp) <= t.endDt"
				+ " and "
				+ " (CASE WHEN t.lastScheduled is null "  
				+ "       then mod(round( cast((cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date) - cast(t.startDt as date)) as integer) *60* 24), t.schedule) "
				+ "		  else mod(round( cast((cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date) - cast(t.lastScheduled as date)) as integer) *60*24), t.schedule) "
				+ " END) = 0 "
				+ " and t.testSuiteId in (select ts.testSuiteID from TestSuite ts where UPPER(ts.testType) in ('REGRESSION','BASIC') and UPPER(ts.executionType) in ('SCHEDULE','BOTH'))";

I had to create a function in Oracle as Oracle’s interval type kept generating errors

here’s the SQL hibernate created:

select tss1_0.test_suite_schedule_id,tss1_0.application_url,tss1_0.end_dt,tss1_0.environment,tss1_0.env_parms,tss1_0.ins_by,tss1_0.ins_ts,tss1_0.is_active,tss1_0.last_scheduled_ts,tss1_0.lst_upd_by,tss1_0.lst_upd_ts,tss1_0.release,tss1_0.schedule,tss1_0.start_dt,tss1_0.test_phase,tss1_0.test_suite_id,tss1_0.user_variable from test_suite_schedule tss1_0 where sys_extract_utc(systimestamp)>=min_ago(tss1_0.start_dt) and cast(sys_extract_utc(systimestamp) as timestamp(6))<=tss1_0.end_dt and case when tss1_0.last_scheduled_ts is null then mod(round(((cast(((cast(sys_extract_utc(systimestamp) as date)-cast(tss1_0.start_dt as date))*86400)*1e9 as number(10,0))*60)*24)),tss1_0.schedule) else mod(round(((cast(((cast(sys_extract_utc(systimestamp) as date)-cast(tss1_0.last_scheduled_ts as date))*86400)*1e9 as number(10,0))*60)*24)),tss1_0.schedule) end=0 and tss1_0.test_suite_id in (select ts1_0.test_suite_id from test_suite ts1_0 where upper(ts1_0.test_type) in ('REGRESSION','BASIC') and upper(ts1_0.execution_type) in ('SCHEDULE','BOTH'))

notice the *86400)*1e9 where is that coming from???

So we get invalid results. I guess I will try running it as a native query.

5

You apparently didn’t read what I wrote or the documentation about durations and how to extract values from them. If you do date1 - date2 you get a duration object. You can’t just cast that to an integer and expect it to return whatever unit you are “assuming”. You will have to use the by hour operator or even just use the timestampdiff function if you prefer that.

SELECT t 
FROM TestSuiteSchedule t 
WHERE CURRENT_TIMESTAMP >= t.startDt - 1 minute
  and CURRENT_TIMESTAMP <= t.endDt
  and mod(round(timestampdiff(hour, CURRENT_TIMESTAMP, coalesce(t.lastScheduled, t.startDt))), t.schedule) = 0
  and exists (
		select 1
		from TestSuite ts 
		where UPPER(ts.testType) in ('REGRESSION','BASIC') 
		  and UPPER(ts.executionType) in ('SCHEDULE','BOTH')
		  and t.testSuiteId = ts.testSuiteID 
  )