left join with UDF where clause return different results in 2.5.5 and 3.0 -------------------------------------------------------------------------
Key: CORE-5351 URL: http://tracker.firebirdsql.org/browse/CORE-5351 Project: Firebird Core Issue Type: Bug Affects Versions: 3.0.0 Reporter: Erik Jõeveer 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' (http://tracker.firebirdsql.org/browse/CORE-5150). 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 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). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel