Create alias if not exists threw exception

[Yuqiang Fan]

Hi,

I am using the newest version of H2, and recently I encountered a weird issue.

Under mysql mode, I tried to use "CREATE ALIAS IF NOT EXISTS UNIX_TIMESTAMP FOR "com.xxx.xxx.xxx.xx", but it threw me :

org.h2.jdbc.JdbcSQLSyntaxErrorException: Function alias "UNIX_TIMESTAMP" already exists; SQL statement:
CREATE ALIAS IF NOT EXISTS UNIX_TIMESTAMP FOR "com.xxx.xxx.xxx.xx".

This also happened on FROM_UNIXTIME as well as DATE.

After doing research, I found out that these three functions have been built-in under mysql mode since 1.4.194, and this might be the cause of that exception.

However, my *.sql scripts writing such user-defined functions are runned under either oracle or mysql mode, due to different needs. It works under oracle, but throws exceptions under mysql mode, is there any adjustment I can made to make them work fine on both modes?

I have tried to use DROP ALIAS IF EXISTS, while it seems that these built-in functions are different from user-defined functions, such dropping only worked on "real" aliases, not on "built-in" or say "reserved" functions. And I need to keep these codes, because I still need to call these functions under oracle mode using the same scripts. Hope anyone can advise.

Many thanks,

Yuqiang

[Evgenij Ryazanov]

Hello!

You can append

;BUILTIN_ALIAS_OVERRIDE=TRUE

to JDBC connection URL.

But actually you shouldn't expect that these compatibility modes are compatible with each other, every command that works in one mode may not work or work in a different way in another.

[Yuqiang Fan]

Thanks a lot, and I will keep your advice in mind.