CREATE OR ALTER VIEW and column type (domain) bug [CORE4844]

[firebird-automations]

Submitted by: Attila Molnár (e_pluribus_unum)

Attachments:
HIBAS.ZIP

Votes: 1

running the next command throws error

EXECUTE BLOCK
AS
BEGIN
rdb$set_context('USER_SESSION', 'VIEW_CREATE_USER', 'SYSDBA');
rdb$set_context('USER_SESSION', 'VIEW_CREATE_PASSWORD', 'masterkey');
EXECUTE PROCEDURE fk_minosites_view_create('*');
END

"This operation is not defined for system tables.
unsuccessful metadata update.
cannot delete.
DOMAIN RDB$245643.
there are 2 dependencies.
At procedure 'FK_MINOSITES_VIEW_CREATE' line: 95, col: 1
At procedure 'FK_MINOSITES_VIEW_CREATE' line: 95, col: 1."

Where RDB$245643 domain comes from? If you see the view source you see specific domain definition, but the view was created with this generated domain.

SELECT *
FROM rdb$relation_fields f
where trim(f.rdb$relation_name) LIKE 'FK_MINOSITES_'

As I see there are two bugs :
#⁠1 : create/alter view not uses the specified domain, but generates a new one and uses that when null casted to domain.
#⁠2 : alter view should not fail ICO domain change (domain drop)

[firebird-automations]

Modified by: Attila Molnár (e_pluribus_unum)

Attachment: HIBAS.ZIP [ 12767 ]

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

The attached zip includes only a file with an unknown .gdb3 extension, what does this file represent?

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

description: Hi!

running the next command throws error

EXECUTE BLOCK
AS
BEGIN
rdb$set_context('USER_SESSION', 'VIEW_CREATE_USER', 'SYSDBA');
rdb$set_context('USER_SESSION', 'VIEW_CREATE_PASSWORD', 'masterkey');
EXECUTE PROCEDURE fk_minosites_view_create('*');
END

"This operation is not defined for system tables.
unsuccessful metadata update.
cannot delete.
DOMAIN RDB$245643.
there are 2 dependencies.
At procedure 'FK_MINOSITES_VIEW_CREATE' line: 95, col: 1
At procedure 'FK_MINOSITES_VIEW_CREATE' line: 95, col: 1."

Where RDB$245643 domain comes from? If you see the view source you see specific domain definition, but the view was created with this generated domain.

SELECT *
FROM rdb$relation_fields f
where trim(f.rdb$relation_name) LIKE 'FK_MINOSITES_'

As I see there are two bugs :
#⁠1 : create/alter view not uses the specified domain, but generates a new one and uses that when null casted to domain.
#⁠2 : alter view should not fail ICO domain change (domain drop)

Thank you!

=>

running the next command throws error

EXECUTE BLOCK
AS
BEGIN
rdb$set_context('USER_SESSION', 'VIEW_CREATE_USER', 'SYSDBA');
rdb$set_context('USER_SESSION', 'VIEW_CREATE_PASSWORD', 'masterkey');
EXECUTE PROCEDURE fk_minosites_view_create('*');
END

"This operation is not defined for system tables.
unsuccessful metadata update.
cannot delete.
DOMAIN RDB$245643.
there are 2 dependencies.
At procedure 'FK_MINOSITES_VIEW_CREATE' line: 95, col: 1
At procedure 'FK_MINOSITES_VIEW_CREATE' line: 95, col: 1."

Where RDB$245643 domain comes from? If you see the view source you see specific domain definition, but the view was created with this generated domain.

SELECT *
FROM rdb$relation_fields f
where trim(f.rdb$relation_name) LIKE 'FK_MINOSITES_'

As I see there are two bugs :
#⁠1 : create/alter view not uses the specified domain, but generates a new one and uses that when null casted to domain.
#⁠2 : alter view should not fail ICO domain change (domain drop)

[firebird-automations]

Commented by: Attila Molnár (e_pluribus_unum)

It's a Firebird ODS11.2 database file. We use this extension not the standard fdb.

[firebird-automations]

Commented by: Attila Molnár (e_pluribus_unum)

clarification
#⁠1 : create/alter view not uses the specified domain, but generates a new one and uses that when null or literal casted to domain.

[firebird-automations]

Commented by: @asfernandes

I reproduced it, but it would be good if you have a simpler test case, with only DDL comands (no execute statement) and no need of a database backup.

[firebird-automations]

Commented by: Attila Molnár (e_pluribus_unum)

Cannot find any workaround for this problem, so from now this is BLOCKER for us.
We cannot release our software next version because we can't update our database schema!

[firebird-automations]

Modified by: Attila Molnár (e_pluribus_unum)

priority: Major [ 3 ] => Blocker [ 1 ]

[firebird-automations]

Commented by: Attila Molnár (e_pluribus_unum)

The deeper problem is : view depending on view with generated domain

Simple test case :

CREATE DOMAIN xint AS INTEGER

CREATE VIEW view_level1 (id)
AS
SELECT CAST(NULL AS xint)
FROM rdb$database

CREATE VIEW view_level2(id)
AS
SELECT id
FROM view_level1

CREATE TABLE t(id xint)

CREATE OR ALTER VIEW view_level1(id)
AS
SELECT id
FROM t

last create or alter view is the problematic

Cannot commit transaction:
This operation is not defined for system tables.
unsuccessful metadata update.
cannot delete.
DOMAIN RDB$1.
there are 1 dependencies.

[firebird-automations]

Commented by: Attila Molnár (e_pluribus_unum)

Hi!

What is the state of this bug? We need the fix.
We managed to do a very ugly workaround at that time, but now we have a case where no viable workaround exists.
This a REAL BLOCKER for us right now.

[firebird-automations]

Commented by: @hvlad

I looking into it but there is a lot of other things to do - so no quick solution, sorry.

Probably another ugly workaround is to convert direct reference on view fields (from "caller" view) to the expressions, like:
id -> id + 0, name -> name || ''

But it will make "caller" view not-updatable...

[firebird-automations]

Commented by: Omacht András (aomacht)

Hi All!
Is there a chance that this problem will be solved at least in 4.0?
Thanks!