wrong results using minvalue/maxvalue in join condition

[tonne1]

Using minvalue in a join condition can cause the effect that records are not in the result set although they should be. Affects Firebird 5 and up, Firebird 4 and lower are not affected.

Test case:

1. preparation - setup test tables

CREATE TABLE TBL1 (
  DS INTEGER NOT NULL,
  RU SMALLINT DEFAULT 0 NOT NULL,
  WI SMALLINT DEFAULT 0 NOT NULL,
  KO SMALLINT DEFAULT 0 NOT NULL
);

commit work;

CREATE TABLE TBL2 (
  ID     INTEGER NOT NULL,
  RU     SMALLINT DEFAULT 0 NOT NULL,
  RU_TXT VARCHAR(100) NOT NULL,
  WI     SMALLINT DEFAULT 0 NOT NULL,
  WI_TXT VARCHAR(100) NOT NULL,
  KO     SMALLINT DEFAULT 0 NOT NULL,
  KO_TXT VARCHAR(100) NOT NULL
);

commit work;

INSERT INTO TBL1 (DS, RU, WI, KO) VALUES(50, 1, 1, 0);

INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(1, 1, 'a', 1, 'a', 1, 'a');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(2, 1, 'b', 1, 'b', 0, 'b');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(3, 1, 'c', 0, 'c', 1, 'c');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(4, 1, 'd', 0, 'd', 0, 'd');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(5, 0, 'e', 1, 'e', 1, 'e');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(6, 0, 'f', 1, 'f', 0, 'f');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(7, 0, 'g', 0, 'g', 1, 'g');
INSERT INTO TBL2 (ID, RU, RU_TXT, WI, WI_TXT, KO, KO_TXT) VALUES(8, 0, 'h', 0, 'h', 0, 'h');

commit work;

2. run these statements and compare result sets:

-- no record - wrong:
select a.*
from tbl1 a
join tbl2 b on minvalue(a.ko, 1) = b.ko and
               minvalue(a.ru, 1) = b.ru and
               minvalue(a.wi, 1) = b.wi

-- one record - correct:
select a.*
from tbl1 a
join tbl2 b on decode(a.ko, 0, 0, 1) = b.ko and
               decode(a.ru, 0, 0, 1) = b.ru and
               decode(a.wi, 0, 0, 1) = b.wi

[omachtandras]

"Firebird 4 and lower are not affected."

For information only: 3.0.11.33703 also affected.