[#CORE-3094] Parameters doesn't work with NOT IN from a selectable procedure

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. 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:

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 02/Aug/10 09:43 PM

Reproducible test case required

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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?

[ Permalink | « Hide ]

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