firebird
firebird copied to clipboard
FirebirdSQL
CREATE OR ALTER VIEW and column type (domain) bug [CORE4844]
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)
Modified by: Attila Molnár (e_pluribus_unum)
Attachment: HIBAS.ZIP [ 12767 ]
Commented by: Sean Leyne (seanleyne)
The attached zip includes only a file with an unknown .gdb3 extension, what does this file represent?
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)
Commented by: Attila Molnár (e_pluribus_unum)
It's a Firebird ODS11.2 database file. We use this extension not the standard fdb.
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.
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.
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!
Modified by: Attila Molnár (e_pluribus_unum)
priority: Major [ 3 ] => Blocker [ 1 ]
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.
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.
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...
Commented by: Omacht András (aomacht)
Hi All! Is there a chance that this problem will be solved at least in 4.0? Thanks!
This is also an issue in case of union usage in view. In this case also RDB domains will be created automatically. Create a dependenc on this view make dependency on the RDB domain also. Altering the original view want to drop the RDB, but it is still in use.
CREATE OR ALTER VIEW a AS SELECT d.rdb$relation_id val FROM rdb$database d UNION ALL SELECT d.rdb$relation_id FROM rdb$database d
CREATE VIEW b AS SELECT val FROM a
ALTER VIEW a AS SELECT d.rdb$relation_id val FROM rdb$database d
"This operation is not defined for system tables. unsuccessful metadata update. cannot delete. DOMAIN RDB$2198841. there are 1 dependencies."
Now we have to make an other ugly workaround for this issue also. Please fix this annoying and old bug. It's very hard to make performant workarounds.
Thank You!