select * from
t1_proc T1
LEFT JOIN
(
SELECT 10 AS VALX FROM rdb$database
) T2
ON T1.VAL=T2.VALX
UNION
select * from
t1_proc T1
LEFT JOIN
(
SELECT 20 AS VALX FROM rdb$database
) T2
ON T1.VAL=T2.VALX

Regards
=========
|| ISMAEL ||
=========

----- Original Message -----
From: emb_blaster
To: firebird-support@yahoogroups.com
Sent: Friday, February 25, 2011 3:32 PM
Subject: [firebird-support] Possible BUG in Left join on Stored Procedures...



Hi all,

I was trying to test the suggestion of Karol in another post (http://tech.dir.groups.yahoo.com/group/firebird-support/message/112303), but accidentally I found something interesting, that seems a BUG:

If you run a query that do Left Join of a Select with simple line in a Stored Procedure, it can return data that would not be there.
I have a sample code to anyone try to reproduce.

Indeed, i have tried this:

SET TERM ^ ;

CREATE OR ALTER procedure T1_PROC
returns (
VAL integer)
as
begin
for SELECT 1 FROM rdb$database
UNION SELECT 2 FROM rdb$database
UNION SELECT 3 FROM rdb$database
UNION SELECT 4 FROM rdb$database
into val do
suspend;
end
^

SET TERM ; ^

and

select * from
t1_proc T1
LEFT JOIN
(
SELECT 10 AS VALX FROM rdb$database
) T2
ON T1.VAL=T2.VALX

This should result in a set with values from t1_proc on first column and NULL in second column... but it returns the data from T2. O.O''

if I change the query as below, it runs as expected.

select * from
t1_proc T1
LEFT JOIN
(
SELECT 10 AS VALX FROM rdb$database
UNION
SELECT 20 AS VALX FROM rdb$database
) T2
ON T1.VAL=T2.VALX

In this case, I am using to test:
Windows XP SP3
The Server installed and Client Library Version is Firebird 2.1.3.18185
Database ODS is 11.0

Advise?





[Non-text portions of this message have been removed]