Something odd with wrong numbers of parameters for user defined functions

[justin_willey]

v 10.0.1.3931

This script demonstrates the problem:

create table stuff(SomeStuff char(10) primary key);
insert into stuff(somestuff) values ('a');
insert into stuff(somestuff) values ('b');
insert into stuff(somestuff) values ('c');
insert into stuff(somestuff) values ('d');
insert into stuff(somestuff) values ('e');
insert into stuff(somestuff) values ('f');
insert into stuff(somestuff) values ('g');
insert into stuff(somestuff) values ('h');

create function Needs2Parameters(a int, b int)
returns int
begin
return a+b
end;

When you run this statement in ISQL:

select somestuff, Needs2Parameters(13) from stuff order by somestuff;

You get a dialog box entitled "Interactive SQL" saying: The following error occured while fetching results: Wrong number of parameters to function 'Needs2Parameters' SQLCODE=-154, ODBC 3 State="42000".

Fair enough, except that you also get a result set looking like this:

somestuff  Needs2Parameters(13)
'b'        (NULL)
'c',       (NULL)
'd',       (NULL)
'e',       (NULL)
'f',       (NULL)
'g',       (NULL)
'h',       (NULL)

note that the first line is missing. With an ODBC client it's even odder, you don't get an error, and the first line consists of random data from a buffer (it's often recognisable as something you selected recently), then you get the remainder of the result set seen in ISQL.

If the function is a built in one, eg

select somestuff, greater(13) from stuff order by somestuff

Then you get a different dialog box, enttled "ISQL Error", Could not execute statement. Wrong number of parameters ...... In this case you get no result set.

The same behaviour is seen in v9.0.2

[justin_willey]

The same thing - partial results sets + garbage - also seems to be a possibility when you get the "Null value eliminated in aggregate function" message

[reimer_pods]

With 12.0.0.2483 there's only the error message (SQLCODE=-154, ODBC 3 State="42000"), but no result set.

[MarkCulp]

I get an appropriate error (not enough parameters) with 10.0.1.3938.

[justin_willey]

@Mark - and no result set?

[MarkCulp]

@Justin: Yes, once the error is raised the query stops (or more correctly, doesn't even start) and there is no result set returned. I think there must have been a fix made that detected the syntactic error but I have not had the time to track down the change (yet... I will try to do this and will post an answer if I find it). Prior to the fix (presuming there was one), my theory is that the error would go undetected and the 'b' parameter would get a value of NULL. This would result in a return value of NULL since int_value+NULL equals NULL and hence the result set that you have seen.

[MarkCulp]

@Justin: Hmmm, I've been trying to figure out which change fixed this issue. There are not a lot of changes between 3931 and 3938 and didn't find anything that would account for the difference... so I tried to repro the issue on 3931 and did not see the behaviour that you saw - i.e. I got the appropriate error. Can you double check the version that you are using?

[justin_willey]

@Mark: Hmmmmmmmm... very odd. I think there is more than one thing involved here. I've just tested another (older) build 3680, and the behaviour in isql is as you describe and ODBC gets a proper error as well,could there be something about the database? I'll try a new db in 3931.

[jack_schueler]

For a 10.0 server, a warning (SQL_SUCCESS_WITH_INFO) occurs in SQLFetch:

[1] [42000] [SAP][ODBC Driver][SQL Anywhere]Wrong number of parameters to function 'Needs2Parameters', result rows=8, error in row=1

In my test ODBC app I use a row status array, something like this...

SQLSetStmtAttr( HStmt, SQL_ATTR_ROW_STATUS_PTR, row_status, 0 );


You see the following results (status, column values).
Row:     1 [SQL_ROW_ERROR] [ 1]: ═══════════b [ 2]: -842150451
Row:     2 [SQL_ROW_SUCCESS] [ 1]: b [ 2]: <NULL>
Row:     3 [SQL_ROW_SUCCESS] [ 1]: c [ 2]: <NULL>
Row:     4 [SQL_ROW_SUCCESS] [ 1]: d [ 2]: <NULL>
Row:     5 [SQL_ROW_SUCCESS] [ 1]: e [ 2]: <NULL>
Row:     6 [SQL_ROW_SUCCESS] [ 1]: f [ 2]: <NULL>
Row:     7 [SQL_ROW_SUCCESS] [ 1]: g [ 2]: <NULL>
Row:     8 [SQL_ROW_SUCCESS] [ 1]: h [ 2]: <NULL>


Looks like the 10.0 server produces all rows of the rowset, with no values set for the first row, and valid column values for somestuff and SQL_NULL_DATA for Needs2Parameters(13) in the remaining rows. SQL_ROW_ERROR indicates that the columns values in the first row should be ignored (nothing was written to memory).
Interesting behavior. 
For an 11.0 server, an error (SQL_ERROR) occurs in SQLFetch and no fetching is done.
For a 16.0 server, an error (SQL_ERROR) results in SQLExecDirect and no fetching is done.