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

ALTER DOMAIN ... TO <new_name> allows to specify <new_name> matching to 'RDB$[[:DIGIT:]]*' [CORE4917] #5209

Closed
firebird-issue-importer opened this issue Aug 30, 2015 · 9 comments

Comments

@firebird-issue-importer

Submitted by: @pavel-zotov

On empty database do:

Database: /3255:e25, User: SYSDBA
SQL> set list on;
SQL> set count on;
SQL> select rdb$field_name from rdb$fields where trim(rdb$field_name) SIMILAR TO 'RDB$[[:DIGIT:]]*';
Records affected: 0
SQL> create domain rdb$1 int;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -637
-Implicit domain name RDB$1 not allowed in user created domain // OK, it's expected

-- But using ALTER DOMAIN statement one may see that such domain CAN be created:

SQL> create domain foo int;
SQL> alter domain foo to rdb$1;
SQL> commit;
SQL> select rdb$field_name from rdb$fields where trim(rdb$field_name) SIMILAR TO 'RDB$[[:DIGIT:]]*';

RDB$FIELD_NAME RDB$1

Records affected: 1
SQL> commit;
SQL> show domains;
There are no domains in this database

===
PS. Checked on: WI-V2.5.5.26916, WI-V3.0.0.32008

Commits: d2e67ca 182016b FirebirdSQL/fbt-repository@f26c600 FirebirdSQL/fbt-repository@b782899

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 30, 2015

Commented by: Sean Leyne (seanleyne)

What I find interesting is that you cant create the RDB$ domain in the first place.

There is no restriction against creating other RDB$ objects; tables, views, indexes, triggers and procedures. So, why should domains be special?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 30, 2015

Commented by: @pavel-zotov

> What I find interesting is
The more interesting is that my domain (with final name = 'RDB$1') is not displayed by SHOW command ;-)

As of 'special missing' of 'RDB$' prefix, I think that it serves at least one purpose: to detect that some procedure parameter was defined as 'direct' reference to some base type (e.g, integer, varchar etc) rather than via domain.
This fact can help when trying to get proper source of stored procedure header from data in RDB$ tables (some kind of 'reverse-engineering').

Consider following sample:

create or alter procedure sp_test as begin end;
recreate table test(id int);
commit;
set term ^;
execute block as begin execute statement 'drop domain dm_test'; when any do begin end end
^
set term ;^
commit;

create domain dm_test int default 111;
commit;

set term ^;
create or alter procedure sp_test(
a_f01 dm_test
,a_f02 dm_test default 222
,a_f03 type of dm_test default 333
,a_f04 type of column http://test.id default 444
,a_f05 int default 555
) as
begin
end
^
set term ;^
commit;

show proc sp_test;

set width proc_name 8;
set width par_name 8;
set width ppar_fsource 12;
set width ppar_default_src 12;
set width rdbf_default_src 12;
select
p.rdb$procedure_name proc_name
,p.rdb$parameter_number par_num
,p.rdb$parameter_name par_name
,p.rdb$field_source ppar_fsource
,cast(p.rdb$default_source as varchar(20)) ppar_default_src
,cast(f.rdb$default_source as varchar(20)) rdbf_default_src
--,p.rdb$default_value rdb_ppar_default_val
from rdb$procedure_parameters p
left join rdb$fields f on p.rdb$field_source = f.rdb$field_name
where trim(p.rdb$procedure_name) = 'SP_TEST'
order by 1,p.rdb$parameter_number
;

Its output will be (on 2.5):

Procedure text:

begin
end

Parameters:
A_F01 INPUT (DM_TEST) INTEGER
A_F02 INPUT (DM_TEST) INTEGER default 222
A_F03 INPUT (TYPE OF DM_TEST) INTEGER default 333
A_F04 INPUT (TYPE OF COLUMN http://TEST.ID) INTEGER default 444
A_F05 INPUT INTEGER default 555

PROC_NAM PAR_NUM PAR_NAME PPAR_FSOURCE PPAR_DEFAULT RDBF_DEFAULT
========= ======= ======== ============ ================ ================
SP_TEST 0 A_F01 DM_TEST <null> default 111
SP_TEST 1 A_F02 DM_TEST default 222 default 111
SP_TEST 2 A_F03 DM_TEST default 333 default 111
SP_TEST 3 A_F04 RDB$20 default 444 <null>
SP_TEST 4 A_F05 RDB$21 <null> default 555

Note in the header of procedure SP_TEST on parameters 'A_F01' and 'A_F05':
A_F01 INPUT (DM_TEST) INTEGER
vs
A_F05 INPUT INTEGER default 555

A_F01 has *no* default value rather than A_F05. But when we select data from rdb$procedure_parameters and rdb$fields - how can we find this from resultset ?

Rows #⁠1 and #⁠5 from this resultset are:

SP_TEST 0 A_F01 DM_TEST <null> default 111
SP_TEST 4 A_F05 RDB$21 <null> default 555

BOTH these rows has non-empty 'default' expressions but only row #⁠5 actually should be reflected to the clause with 'DEFAULT ...'
I could find the only way to recognize that parameter A_F01 should not take 'DEFAULT ...' in its definition: reference to domain that is NOT starting with 'RDB$' prefix.

PS. Adriano or Dmitry, of course, will correct my wrong opinion :-)

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 31, 2015

Commented by: @asfernandes

Pavel, please..... Do not mark "Affects version" with unreleased releases!

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 31, 2015

Modified by: @asfernandes

Version: 3.0 Beta 2 [ 10586 ]

Version: 2.5.4 [ 10585 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

Version: 3.0 RC 1 [ 10584 ] =>

Version: 2.5.5 [ 10670 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 31, 2015

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

Fix Version: 2.5.5 [ 10670 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 2, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 2, 2015

Commented by: @pavel-zotov

Statements with prefixes 'MON$' and 'SEC$' work w/o exception (checked on 3.0.0.32020):

create domain sec$passwords int;
create domain mon$syslocks int;

Is it OK ?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 2, 2015

Commented by: @asfernandes

Yes.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 2, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

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

Successfully merging a pull request may close this issue.

None yet
2 participants