Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

LEFT JOIN incorrectly pushes UDF into the inner stream causing wrong results [CORE5351] #5624

Closed
firebird-issue-importer opened this issue Sep 19, 2016 · 8 comments

Comments

@firebird-issue-importer

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Modified by: @dyemanov

summary: left join with UDF where clause return different results in 2.5.5 and 3.0 => LEFT JOIN incorrectly pushes UDF into the inner stream causing wrong results

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Modified by: @dyemanov

Link: This issue is related to CORE5149 [ CORE5149 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Modified by: @dyemanov

Version: 4.0 Initial [ 10621 ]

Fix Version: 3.0.1 [ 10730 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Commented by: @dyemanov

The same effect can be seen with PSQL function:

create function my_strlen (val varchar(10))
returns int
as
begin
return char_length(coalesce(val, ''));
end

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 20, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 20, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment