Issue Details (XML | Word | Printable)

Key: CORE-5776
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Vlad Khorsun
Votes: 0
Watchers: 1
Operations

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

"Input parameter mismatch" error after altering external function into PSQL function

Created: 15/Mar/18 09:30 AM   Updated: 18/Mar/18 03:37 PM
Component/s: Engine
Affects Version/s: 4.0 Initial, 3.0.0, 3.0.1, 3.0.2, 4.0 Alpha 1, 3.0.3
Fix Version/s: 3.0.4, 4.0 Beta 1

QA Status: Done with caveats
Test Details:
2do: check result of fb_run when issue related "GetThreadId could not be located in the dynamic link library KERNEL32.dll" will be fixed on Win XP (see letter to dimitr, hvlad 18.03.2018)


 Description  « Hide
Firebird 3 introduced PSQL FUNCTION's and now it is recommended to convert UDF's into PSQL functions when possible.
ALTER FUNCTION statement could convert UDF into PSQL functions but sometime PSQL function become unusable after
such conversion. Example:

a) declare UDF

declare external function sright
  varchar(100) by descriptor, smallint,
  varchar(100) by descriptor returns parameter 3
  entry_point 'right' module_name 'fbudf';


b) make sure it works

select sright('function', 2) from rdb$database;

SRIGHT
===============================================================================
on


c) convert UDF into PSQL function

commit;
set term ^;
alter function sright (str varchar(100), len int)
  returns varchar(100)
as
begin
  return right(str, len);
end^
set term ;^


d) check if it works

select sright('function', 2) from rdb$database;

Statement failed, SQLSTATE = 07001
Dynamic SQL Error
-Input parameter mismatch for function SRIGHT


The problem is that ALTER FUNCTION didn't changed RDB$FUNCTIONS.RDB$RETURN_ARGUMENT value :

a) original UDF

select rdb$return_argument from rdb$functions
  where rdb$function_name = 'SRIGHT';

RDB$RETURN_ARGUMENT
===================
                  3

select rdb$argument_position, rdb$argument_name from rdb$function_arguments
 where rdb$function_name = 'SRIGHT';

RDB$ARGUMENT_POSITION RDB$ARGUMENT_NAME
===================== ===============================
                    1 <null>
                    2 <null>
                    3 <null>


b) after ALTER FUNCTION

select rdb$return_argument from rdb$functions
 where rdb$function_name = 'SRIGHT';

RDB$RETURN_ARGUMENT
===================
                  3

select rdb$argument_position, rdb$argument_name from rdb$function_arguments
 where rdb$function_name = 'SRIGHT';

RDB$ARGUMENT_POSITION RDB$ARGUMENT_NAME
===================== ===============================
                    0 <null>
                    1 STR
                    2 LEN



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.