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

Metadata extration (ISQL -X): "CREATE PROCEDURE/FUNCTION" statement contains reference to column of table(s) that not yet exists if this procedure had parameter of such type when it was created [CORE5089] #5374

Closed
firebird-issue-importer opened this issue Jan 29, 2016 · 9 comments

Comments

@firebird-issue-importer

Submitted by: @pavel-zotov

Attachments:
test-metadata-extraction-core-5089.zip

Create new database and apply this script:

create or alter procedure sp\_test \(ix int\) returns\(ox int\) as begin end;
commit;

recreate table test\(
    x int
\);
commit;

set term ^;

alter procedure sp\_test\(
    ix type of column test\.x
\) 
returns\(
    ox type of column test\.x
\) as
begin
  /\* foo \*/
end

^
set term ;^
commit; 

===

Then run: ISQL -X this_database.fdb 1>metadata.log

File "metadata.log" will contain following rows:

SET SQL DIALECT 3;

/* CREATE DATABASE '/3333:e30' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE; */

COMMIT WORK;

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures headers */
CREATE OR ALTER PROCEDURE SP_TEST (IX TYPE OF COLUMN TEST.X) ------------------------------------------ [ 1 ]
RETURNS (OX TYPE OF COLUMN TEST.X) --------------------------------------------------------------------------------------- [ 2 ]
AS
BEGIN EXIT; END ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

/* Table: TEST, Owner: SYSDBA */
CREATE TABLE TEST (X INTEGER);

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures bodies */

ALTER PROCEDURE SP_TEST (IX TYPE OF COLUMN TEST.X)
RETURNS (OX TYPE OF COLUMN TEST.X)
AS
begin
/* foo */
end ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

This script will not be compiled because of invalid references marked as [ 1 ] and [ 2 ].

PS. Found while preparing test for CORE2792; I'm not sure that this issue should be added there thus created new ticket.

Commits: 44cf5c7 27dc928 624d338 FirebirdSQL/fbt-repository@8e2a262 FirebirdSQL/fbt-repository@21f9fd4 FirebirdSQL/fbt-repository@f133f78

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 29, 2016

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 29, 2016

Modified by: @asfernandes

summary: Metadata extration (ISQL -X): "CREATE PROCEDURE" statement contains reference to column of table(s) that not yet exists if this procedure had parameter of such type when it was created => Metadata extration (ISQL -X): "CREATE PROCEDURE/FUNCTION" statement contains reference to column of table(s) that not yet exists if this procedure had parameter of such type when it was created

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 29, 2016

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC2 [ 10048 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 30, 2016

Modified by: @pavel-zotov

Attachment: test-metadata-extraction-core-5089.zip [ 12891 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 30, 2016

Commented by: @pavel-zotov

It seems that fix of 29-jan-2016 02:57 PM was not completed.
Consider script "c5089.sql " from attached .zip - it is compiled OK on empty database and will produce:

MODE RESULT
standalone proc 121
standalone func 275
packaged proc 477
packaged func 331

Then apply extraction (ISQL -X) to that database and save it to somewhere ("testmeta.sql" in attached .zip).
Further attempt to apply "testmeta.sql" to empty database issues:
Statement failed, SQLSTATE = 42S22
CREATE PACKAGE PG_TEST failed
-Dynamic SQL Error
-SQL error code = -607
-Invalid command
-column RESULT does not exist in table/view TEST
After line 48 in file testmeta.sql

This is because of packaged procedure & function that still contain forward references to the table.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 30, 2016

Modified by: @pavel-zotov

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

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 2, 2016

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 3, 2016

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 3, 2016

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
Development

No branches or pull requests

2 participants