Re: [h2] Problem with new Datetrunc function conflicting with user defined function.

[Noel Grandin]

You could connect with the old version of H2, drop the alias, then reconnect with the newer version.

Or you can use connect with this in your connection string

BUILTIN_ALIAS_OVERRIDE=1

to allow you to temporarily override the system alias.

[Jack Green]

From my testing, using this option allows the USER to override the SYSTEM alias'

However, this option does not allow the SYSTEM to override the USER alias'

What this means is that with the option turned on, the user can create a function (e.g. date_trunc) that overrides the systems function

However, if an existing database is loaded with a user alias (e.g. date_trunc) that overlaps with a new system function, the system will be unable to start because it cannot override the user function.

An unfortunate consequence of this is that the user is unable to issue a command to delete the alias, as they cannot connect to the database.

Is this correct?

[Noel Grandin]

No, you should be able to load such a database, and then drop the user created alias​

SYSTEM aliases are always created first, then USER aliases are created as the DB is loaded, so the option only affects whether or not the USER alias in the DB is allowed to override the SYSTEM alias

[Jack Green]

Noel,

Thankyou for your reply.

I've produced a minimised test case for this issue and I still can't get it working.

Instructions:
Create a test database in H2 (1.4.196),

Create an alias for date_trunc,

Upgrade H2 version (1.4.197),

Try to connect to test database - irrespective of whether option is or isn't used, you can't connect.

I've attached the sql to create the alias (it uses an inline function declaration, so no dependencies), the database pre-upgrade and a screenshot of the error I received.

Are you able to give it a try in case I'm doing it wrong?

Regards,

Jack.

[Jack Green]

As an extension to this, you can reproduce it in an even simpler case - you don't even need to use an old version of H2.

You can create the alias with the extension enabled - but if you disconnect from the database, even with the option enabled you will not be able to reconnect to the database.

[Evgenij Ryazanov]

Hello.

You're right, there were different issues with BUILTIN_ALIAS_OVERRIDE=1. They are now fixed, these fixes will be in the next release.

Also it's possible to drop such alias in an old version that does not have builtin function with such name.

[Robert St-Pierre]

OK i succeded in starting the database with BUILTIN_ALIASES_OVERRIDE=1

BUT

then I cannot see or access any objects from a user therefore cannot get rid of a function alias created by the user.

I did have a UUID function created and the database startup complains that it already exists.

Can you help please.

Robert

I found this data in the SCHEMATA table

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

CATALOG_NAME	SCHEMA_NAME	SCHEMA_OWNER	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	IS_DEFAULT	REMARKS	ID
BUILTIN_ALIASES_OVERRIDE=1	INFORMATION_SCHEMA	ROBERT	Unicode	OFF	FALSE		-1
BUILTIN_ALIASES_OVERRIDE=1	PUBLIC	ROBERT	Unicode	OFF	TRUE		0

[Evgenij Ryazanov]

Hello.

You created a new empty database with such weird name that you can see in CATALOG_NAME column.

You need to add

;BUILTIN_ALIASES_OVERRIDE=1

after name of the your database in your connection URL instead.

Also a bug with this setting was fixed only after the last release, you need to build H2 from its current sources.

https://github.com/h2database/h2database

Use a jar target:

http://h2database.com/html/build.html#building