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
