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

Parameters doesn't work with NOT IN from a selectable procedure [CORE3094] #3473

Closed
firebird-automations opened this issue Aug 3, 2010 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @kattunga

Attachments:
CONT_SIG.FDB

Attached is database example

The following query, with parameter :GROUP = 100, return incorrect result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

instead, the following query return correct result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(100))

This was working fine in previous Firebird versions

Commits: 7adb129 fc5c390

@firebird-automations
Copy link
Collaborator Author

Modified by: @kattunga

environment: Windows XP, Client compiled with delphi 7, connector with UIB => Windows XP

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Reproducible test case required

@firebird-automations
Copy link
Collaborator Author

Modified by: @kattunga

Attachment: CONT_SIG.FDB [ 11693 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @kattunga

description: The following query works fine in Firebird 2.1 and before.
if instead of the parameter :grupo you replace it for the wanted value, it works.
This query return wrong results, other queries that use sub selects from select procedures sometimes raise internal errors.

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM XXX_CON_GRUP_CUENTA(:grupo))
ORDER BY 1

=>

Attached is database example

The following query with parameter :GROUP = 100 return incorrect result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

instead the followin query return correct result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(100))

This was working fine in previous Firebird versions

@firebird-automations
Copy link
Collaborator Author

Modified by: @kattunga

description: Attached is database example

The following query with parameter :GROUP = 100 return incorrect result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

instead the followin query return correct result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(100))

This was working fine in previous Firebird versions

=>

Attached is database example

The following query with parameter :GROUP = 100 return incorrect result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

instead the following query return correct result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(100))

This was working fine in previous Firebird versions

@firebird-automations
Copy link
Collaborator Author

Modified by: @kattunga

description: Attached is database example

The following query with parameter :GROUP = 100 return incorrect result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

instead the following query return correct result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(100))

This was working fine in previous Firebird versions

=>

Attached is database example

The following query, with parameter :GROUP = 100, return incorrect result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

instead, the following query return correct result:

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(100))

This was working fine in previous Firebird versions

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Confirmed.

Note, NOT EXISTS works correctly despite of usage of parameter\literal.
Probably it is related with optimization of NOT IN handling.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I'm trying:

execute block returns (fcuenta integer, fnombre varchar(10))
as
declare gro integer = 100;
begin
for SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:gro))
into fcuenta, fnombre
do
begin
suspend;
end
end!

And it returns no rows, like passing 100 directly.

AFAIK, in most cases this should work as passing the parameter from the client. I've no tool to do that now.

What is the incorrect result?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Adriano,

SELECT with parameter returns 474 rows, as if it have no WHERE clause. I.e.

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN
WHERE FCUENTA NOT IN (SELECT FCUENTA FROM SP_TEST(:GROUP))

and

SELECT FCUENTA, FNOMBRE
FROM CON_CUEN

returns the same number of rows (474)

Also, both queries reads only CON_CUEN table, i.e. first query never call procedure SP_TEST

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: parameters doesn't work in select procedures used for subselects => Parameters doesn't work with NOT IN from a selectable procedure

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.0 [ 10221 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

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