Submitted by: Erik Jõeveer (erikj)

Is related to CORE5149

Hello,

FB3 returns wrong results on left join selects with specific UDF condition (no problems with FB1.5 and FB2.5).
It seems to be similar with this issue: 'left join with COALESCE where clause return different results in 2.5.5 and 3.0 RC2' (CORE5150).

Here is example with Firebird standard ib_udf library function STRLEN , but it can be reproduced with other UDF functions too (we having same problem with our proprietary UDF-s).

CREATE TABLE TEST_TABLE1 (
ID INTEGER NOT NULL,
TESTTABLE2_ID INTEGER
);
ALTER TABLE TEST_TABLE1 ADD PRIMARY KEY (ID);
COMMIT;

CREATE TABLE TEST_TABLE2 (
ID INTEGER NOT NULL,
GROUPCODE VARCHAR(10)
);
ALTER TABLE TEST_TABLE2 ADD PRIMARY KEY (ID);
COMMIT;

INSERT INTO TEST_TABLE1 (ID,TESTTABLE2_ID) VALUES (1,100);
INSERT INTO TEST_TABLE2 (ID,GROUPCODE) VALUES (100,'A');
COMMIT;

DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';
COMMIT;

--------- NEXT QUERY SHOULD RETURN EMPTY RESULT SET BUT IT RETURNS 1 RECORD:

SELECT http://T1.ID
FROM TEST_TABLE1 T1
LEFT JOIN TEST_TABLE2 T2 ON (T2.ID=T1.TESTTABLE2_ID)
WHERE (STRLEN(T2.GROUPCODE)=0)

      ID

============
1

Intrestingly same query with conditon 'STRLEN(T2.GROUPCODE)=1' or with condition 'STRLEN(T2.GROUPCODE)=2' works as expected (first returns 1 record and second returns no records).

Commits: da2bccb 372af3f