/* Firebird 1.5.1  ---  created by eMeL   ---  www.emel.hu */

/* Test for "Join with stored procedure" */


create table AAA
(
  A integer,
  B integer
);

create table BBB
(
  A integer,
  C integer
);

/*---*/

insert into AAA values (1,101);
insert into AAA values (2,102);
insert into AAA values (3,103);


insert into BBB values (2,1020);
insert into BBB values (3,1031);
insert into BBB values (3,1032);


/*---*/

set term ¤ ;

create procedure BBB1 (pDummy integer)
returns (A integer, C integer)
as
begin
  for select A,C from BBB
    into A, C
  do
  begin
    suspend;
  end
end ¤


create procedure BBB2 (pA integer)
returns (A integer, C integer)
as
begin
  for select A,C from BBB
    where A = :pA
    into A, C
  do
  begin
    suspend;
  end
end ¤

set term ; ¤

/*-- Tests --*/

/* Test 1: OK */
select AAA.*, BBB.*
  from AAA
  left outer join BBB on AAA.A = BBB.A;

/* Test 2: OK */
select AAA.*, BB.*
  from AAA
  left outer join BBB1(9999) BB on AAA.A = BB.A;

/* Test 3: OK */
select AAA.*, BB.*
  from AAA
  left outer join BBB2(AAA.A) BB on AAA.A = BB.A;

/* Test 4: OK */
select AAA.*, BB.*
  from AAA
  left outer join BBB2(AAA.A) BB on 1 = 1;