Issue Details (XML | Word | Printable)

Key: CORE-4844
Type: Bug Bug
Status: Open Open
Priority: Blocker Blocker
Assignee: Unassigned
Reporter: Attila Molnár
Votes: 1
Watchers: 5
Operations

If you were logged in you would be able to see more operations.
Firebird Core

CREATE OR ALTER VIEW and column type (domain) bug

Created: 16/Jun/15 01:36 PM   Updated: 11/Apr/18 09:57 AM
Component/s: None
Affects Version/s: 2.5.4
Fix Version/s: None

File Attachments: 1. Zip Archive HIBAS.ZIP (158 kB)


QA Status: No test


 Description  « Hide
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)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 16/Jun/15 01:46 PM
The attached zip includes only a file with an unknown .gdb3 extension, what does this file represent?

Attila Molnár added a comment - 16/Jun/15 01:53 PM
It's a Firebird ODS11.2 database file. We use this extension not the standard fdb.

Attila Molnár added a comment - 18/Jun/15 02:38 PM
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.

Adriano dos Santos Fernandes added a comment - 18/Jun/15 02:47 PM
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.

Attila Molnár added a comment - 18/Jun/15 03:14 PM
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!

Attila Molnár added a comment - 18/Jun/15 03:26 PM
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.

Attila Molnár added a comment - 04/Apr/18 12:34 PM
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.

Vlad Khorsun added a comment - 11/Apr/18 09:57 AM
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...