Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Jun 16, 2015 · 14 comments
Open

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

Attachment: HIBAS.ZIP [ 12767 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: Omacht András (aomacht)

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

@EPluribusUnum
Copy link

This is still an issue (Tested with FB30 and FB40)

@EPluribusUnum
Copy link

EPluribusUnum commented May 24, 2022

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants