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-issue-importer opened this issue Aug 3, 2010 · 14 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

Modified by: @kattunga

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

Commented by: @hvlad

Reproducible test case required

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

Modified by: @kattunga

Attachment: CONT_SIG.FDB [ 11693 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 3, 2010

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 4, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 4, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 14, 2011

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 27, 2015

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants