RDBPROD: Column order can impact results when used in a Function (Doc ID 1420703.1)

Last updated on AUGUST 09, 2016

Applies to:

Oracle Rdb Server on OpenVMS - Version 7.1 and later
HP OpenVMS Itanium
HP OpenVMS Alpha

Symptoms

Incorrect values are passed to a column that has an automatic insert calling a function, which in turn references another column in the same table.

Take the following example:

create table tab_a (col_1 int, col_2 int);
create table tab_b (col_2 int, col_1 int);

create MODULE mod_a
FUNCTION get_next_col_2 (IN :col_1 int)
RETURNS int LANGUAGE SQL;
BEGIN
   DECLARE :next_col_2 int;
   DECLARE :temp_col_2 int;
   SELECT max(col_2) INTO :temp_col_2 FROM tab_a WHERE col_1 = :col_1;
   SELECT max(col_2) INTO :temp_col_2 FROM tab_b WHERE col_1 = :col_1;

   SELECT COALESCE (MAX(col_2), 0) + 1 INTO :next_col_2
   FROM (SELECT col_2 FROM tab_a WHERE col_1 = :col_1
         UNION ALL
         SELECT col_2 FROM tab_b WHERE col_1 = :col_1
        );

   RETURN :next_col_2;
END;
END MODULE;
commit;
alter table tab_a
  alter column col_2 automatic insert as get_next_col_2(col_1);
alter table tab_b
  alter column col_2 automatic insert as get_next_col_2(col_1);
commit;

insert into tab_a values (1);
insert into tab_b values (1);
insert into tab_b values (1);
select * from tab_a;
select * from tab_b;


Based on the function, the expected values for col_2 are 2 and 3 in the display, but 1 is returned for both rows.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms