Issue Details (XML | Word | Printable)

Key: CORE-3094
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Christian Pradelli
Votes: 0
Watchers: 4
Operations

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

Parameters doesn't work with NOT IN from a selectable procedure

Created: 02/Aug/10 09:28 PM   Updated: 14/Feb/11 12:04 PM
Component/s: None
Affects Version/s: 2.5 RC2
Fix Version/s: 2.5.0, 3.0 Alpha 1

Time Tracking:
Not Specified

File Attachments: 1. File CONT_SIG.FDB (784 kB)

Environment: Windows XP

Planning Status: Unspecified


 Description  « Hide
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

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 02/Aug/10 09:43 PM
Reproducible test case required

Vlad Khorsun added a comment - 02/Aug/10 10:42 PM
Confirmed.

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

Adriano dos Santos Fernandes added a comment - 02/Aug/10 11:12 PM
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?

Vlad Khorsun added a comment - 03/Aug/10 06:38 AM
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