Commented by: @hvlad

In EVL_expr i see

*v++ = EVL_expr(tdbb, *ptr++);
// ASF: CAST target type may be constrained
if (node->nod_type != nod_cast && (request->req_flags & req_null))
return NULL;

In our case it is really cast operation and expression result is null
If i remove this check (so EVL_expr will return NULL) error is gone

Adriano, can you look at it, please ?

Commented by: @pcisar

Fix confirmed for 2.1 Alpha 1. Test added.

Commented by: @pcisar

Reopened to update ticket information.

Commented by: cosmin apreutesei (cosmin_ap)

I get the exact same error with v2.1.17755-rc2 superserver. Strangely, the example reported above doesn't trigger the error for me, but my own full outer join on integer fields does. I also tested my example with v2.0.3-12981 superserver and it also works correctly so it's seem to be a 2.1 issue.

Please note that the error is triggered only when there are records that does not match the full outer join filter condition (like with the records in the example below). If all records match the filter, the error is not triggered. This doesn't seem to have anything to do with the fact that the joined table is not a real table, but a subquery. Oh, and "dual" is any table with a single record in it.

Dump all the sql below to a dummy database, then try to "select * from CAT_VERIFY_PARENTS".

SET NAMES UTF8;
CREATE DOMAIN CNAMES AS
VARCHAR(200) CHARACTER SET UTF8;
CREATE DOMAIN COUNTING AS INTEGER CHECK (VALUE IS NULL OR VALUE >=0);
CREATE DOMAIN IDS AS INTEGER;

CREATE TABLE CAT (
CAT_ID IDS NOT NULL /* IDS = INTEGER */,
CAT_NAME CNAMES NOT NULL /* CNAMES = VARCHAR(200) */,
PARENT_CAT_ID IDS /* IDS = INTEGER */
);

CREATE TABLE CAT_PARENTS (
CAT_ID IDS NOT NULL /* IDS = INTEGER */,
PARENT_CAT_ID IDS NOT NULL /* IDS = INTEGER */,
ANCESTRY_LEVEL COUNTING NOT NULL /* COUNTING = INTEGER CHECK (VALUE IS NULL OR VALUE >=0) */
);

INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (1, 'Masini de spalat', 4);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (2, 'Frigidere', 4);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (3, 'Electrocasnice', NULL);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (4, 'Electrocasnice mari', 3);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (5, 'Electrocasnice mici', 11);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (6, 'Aspiratoare', 5);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (7, 'Aragaze', 4);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (8, 'Toastere', 5);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (9, 'Mixere', 5);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (10, 'Aragaze electrice', 7);
INSERT INTO CAT (CAT_ID, CAT_NAME, PARENT_CAT_ID) VALUES (11, 'Aragaze cu gaz', 7);

COMMIT WORK;

INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (7, 4, 1);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (2, 4, 44);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (1, 4, 55);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (4, 3, 1);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (7, 3, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (2, 3, 55);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (2, 2, 77);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (3, 3, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (4, 4, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (10, 10, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (10, 7, 1);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (10, 4, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (10, 3, 3);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (11, 7, 1);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (11, 4, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (11, 3, 3);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (5, 5, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (5, 7, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (5, 4, 3);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (5, 3, 4);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (6, 6, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (6, 5, 1);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (6, 11, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (6, 7, 3);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (6, 4, 4);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (6, 3, 5);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (8, 8, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (8, 5, 1);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (8, 11, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (8, 7, 3);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (8, 4, 4);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (8, 3, 5);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (9, 9, 0);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (9, 11, 2);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (9, 7, 3);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (9, 4, 4);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (9, 3, 5);
INSERT INTO CAT_PARENTS (CAT_ID, PARENT_CAT_ID, ANCESTRY_LEVEL) VALUES (1, 2, 5645);

COMMIT WORK;

ALTER TABLE CAT ADD CONSTRAINT CHK_CAT_DIFF_PARENT check (CAT_ID <> PARENT_CAT_ID);
ALTER TABLE CAT ADD CONSTRAINT PK_CAT PRIMARY KEY (CAT_ID);
ALTER TABLE CAT_PARENTS ADD CONSTRAINT PK_CAT_PARENTS PRIMARY KEY (CAT_ID, PARENT_CAT_ID);
ALTER TABLE CAT ADD CONSTRAINT FK_CAT_PARENT_CAT_ID FOREIGN KEY (PARENT_CAT_ID) REFERENCES CAT (CAT_ID);
ALTER TABLE CAT_PARENTS ADD CONSTRAINT FK_CAT_PARENTS_CAT_ID FOREIGN KEY (CAT_ID) REFERENCES CAT (CAT_ID) ON DELETE CASCADE;
ALTER TABLE CAT_PARENTS ADD CONSTRAINT FK_CAT_PARENTS_PARENT_CAT_ID FOREIGN KEY (PARENT_CAT_ID) REFERENCES CAT (CAT_ID) ON DELETE CASCADE;

CREATE PROCEDURE CAT_GET_PARENTS (
TARGET_CAT_ID IDS)
RETURNS (
CAT_ID IDS,
PARENT_CAT_ID IDS,
ANCESTRY_LEVEL COUNTING)
AS
begin
cat_id = target_cat_id;
ancestry_level = 0;

while \(target\_cat\_id is not null\) do
begin
    select
        parent\_cat\_id
    from
        cat
    where
        cat\_id = :target\_cat\_id
    into
        :parent\_cat\_id;

    ancestry\_level = ancestry\_level \+ 1;
    target\_cat\_id = parent\_cat\_id;

    if \(target\_cat\_id is not null\) then
        suspend;

end

end;

CREATE PROCEDURE CAT_VERIFY_PARENTS
RETURNS (
CAT_ID IDS,
RIGHT_PARENT_CAT_ID IDS,
EXISTING_PARENT_CAT_ID IDS,
RIGHT_ANCESTRY_LEVEL COUNTING,
EXISTING_ANCESTRY_LEVEL COUNTING)
AS
begin
for
select cat_id from cat into :cat_id
do
for
select
cp.parent_cat_id as existing_parent_cat_id,
cp.ancestry_level as existing_ancestry_level,
cgp.parent_cat_id as right_parent_cat_id,
cgp.ancestry_level as right_ancestry_level
from
cat_parents cp
full outer join (
select cat_id, parent_cat_id, ancestry_level from cat_get_parents(:cat_id)
union select :cat_id, :cat_id, 0 from dual
) cgp
on cgp.cat_id = cp.cat_id
and cgp.parent_cat_id = cp.parent_cat_id
where
cp.cat_id = :cat_id
and (coalesce(cp.parent_cat_id, -1) <> coalesce(cgp.parent_cat_id, -1)
or coalesce(cp.ancestry_level, -1) <> coalesce(cgp.ancestry_level, -1))
into
:existing_parent_cat_id,
:existing_ancestry_level,
:right_parent_cat_id,
:right_ancestry_level
do
suspend;
end;