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

MERGE / HASH JOINs produce incorrect results when VARCHAR join keys differ only by trailing spaces [CORE4909] #5201

Closed
firebird-issue-importer opened this issue Aug 16, 2015 · 11 comments

Comments

@firebird-issue-importer

Submitted by: @sim1984

RECREATE TABLE T (
V VARCHAR(10),
X INTEGER
);

INSERT INTO T (V, X) VALUES ('ww', 1);
INSERT INTO T (V, X) VALUES ('ww ', 2);

COMMIT;

--CREATE INDEX IDX_T_V ON T(V); -- If you remove the comment behavior changes

select
REPLACE(T1.V, ' ', '0') AS V1,
T1.X AS X1,
REPLACE(T2.V, ' ', '0') AS V2,
T2.X AS X2
from T T1 join T T2 on T1.V = T2.V

Firebird 3.0 Beta 2

PLAN HASH (T2 NATURAL, T1 NATURAL)

V1 X1 V2 X2
ww 1 ww 1
ww0 2 ww0 2

Firebird 2.5.5

PLAN MERGE (SORT (T2 NATURAL), SORT (T1 NATURAL))

V1 X1 V2 X2
ww 1 ww 1
ww0 2 ww0 2

Is replaced by the LEFT JOIN. There is only the optimizer can use NESTED LOOP

select
REPLACE(T1.V, ' ', '0') AS V1,
T1.X AS X1,
REPLACE(T2.V, ' ', '0') AS V2,
T2.X AS X2
from T T1 left join T T2 on T1.V = T2.V

Firebird 3.0 Beta 2 AND 2.5.5

PLAN JOIN (T1 NATURAL, T2 NATURAL)

V1 X1 V2 X2
ww 1 ww 1
ww 1 ww0 2
ww0 2 ww 1
ww0 2 ww0 2

If you uncomment the creation of the index, the result is the same as for a LEFT JOIN, well, except for the plan.

Commits: 125a332 47979ea 6131cf1 FirebirdSQL/fbt-repository@f5567d7 FirebirdSQL/fbt-repository@2f3a8e2 FirebirdSQL/fbt-repository@5cdfb4b

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 16, 2015

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 16, 2015

Modified by: @dyemanov

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.1.7 [ 10651 ]

Version: 3.0 Beta 1 [ 10332 ]

Version: 2.5.3 [ 10461 ]

Version: 2.1.6 [ 10460 ]

Version: 3.0 Alpha 2 [ 10560 ]

Version: 3.0 Alpha 1 [ 10331 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.1.5 Update 1 [ 10522 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

Component: Engine [ 10000 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 16, 2015

Modified by: @dyemanov

summary: The incorrect result HASH / MERGE JOIN in the fields of type VARCHAR with a trailing space => MERGE / HASH JOINs produce incorrect results when VARCHARs join keys differ only by trailing spaces

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 16, 2015

Modified by: @dyemanov

summary: MERGE / HASH JOINs produce incorrect results when VARCHARs join keys differ only by trailing spaces => MERGE / HASH JOINs produce incorrect results when VARCHAR join keys differ only by trailing spaces

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 11, 2015

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

Fix Version: 2.5.5 [ 10670 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 14, 2015

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 14, 2015

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2018

Commented by: Alex Bekhtin (afgm)

The problem is back in version 4.0 (Firebird-4.0.0.1250-0_x64)

query to check:

with a(id) as
(
select 1 from rdb$database
union all
select 2 from rdb$database
)
select * from a a1
inner join a a2 on a1.id||''=a2.id||''

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2018

Commented by: @dyemanov

I don't see any trailing spaces in your example. And I get:

ID ID
1 1
2 2

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2018

Commented by: Alex Bekhtin (afgm)

i'm sorry.

SET NAMES WIN1251;

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2018

Commented by: @dyemanov

Fixed now, thanks.

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