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

Executing empty EXECUTE BLOCK with NotNull output parameter raised error [CORE4184] #4510

Closed
firebird-issue-importer opened this issue Aug 20, 2013 · 8 comments

Comments

@firebird-issue-importer

Submitted by: Maxim Kuzmin (cybermax)

Relate to CORE2204

Example:
EXECUTE BLOCK
RETURNS (ID INTEGER NOT NULL)
AS
BEGIN
END

When executing this query, FB raised error:
The insert failed because a column definition includes validation constraints.
validation error for variable ID, value "*** null ***".

But there not insert's! There just empty recordset.

Commits: bb86ab0 FirebirdSQL/fbt-repository@c241e3c

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2013

Commented by: @dyemanov

The error message surely deserves a better wording. As for the error itself, this issue is more complicated than it looks at the first glance. Personally, I'd expect it to execute without errors if used as a result set (prepare/fetch) but throw an error is executed as the statement (execute immediate). However, I doubt this is technically doable.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2013

Commented by: Maxim Kuzmin (cybermax)

We have reports, queries that run through the EXECUTE BLOCK, where the output parameters are set as domain. And if some report returns an empty set, then this error occurs. Use TYPE OF or type the domain breaks the uniformity, convenience and check output parameters.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2013

Modified by: @asfernandes

Link: This issue relate to CORE2204 [ CORE2204 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2013

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2013

Commented by: @asfernandes

Dmitry, is it well known and documented that EXECUTE BLOCK can work different (like select * from SP / execute procedure SP) depending on the API function used?

I never paid attention to this. I always executed it in ISQL and for me the standard behavior has to need to issue a SUSPEND.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2013

Commented by: @dyemanov

Nope. EXECUTE BLOCK with output params is described as select and hence usually executed via execute/fetch, thus needing SUSPEND. But, similar to any other [singleton] SELECT, nobody prevents you from executing it via execute_immediate. I suppose the caller might expect NULLs to be returned for the no-rows case. Our API wierdness, sigh (someone would call this flexibility).

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 26, 2013

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment