MERGE / HASH JOINs produce incorrect results when VARCHAR join keys differ only by trailing spaces [CORE4909] #5201
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovVersion: 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 ] |
Modified by: @dyemanovsummary: 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 |
Modified by: @dyemanovsummary: 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 |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 RC 1 [ 10584 ] Fix Version: 2.5.5 [ 10670 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
|
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 |
|
Commented by: @dyemanov I don't see any trailing spaces in your example. And I get: ID ID |
|
Commented by: Alex Bekhtin (afgm) i'm sorry. SET NAMES WIN1251; |
|
Commented by: @dyemanov Fixed now, thanks. |
firebird-automations
added
affect-version: 3.0 Beta 2
affect-version: 2.5.4
affect-version: 2.5.3 Update 1
affect-version: 2.1.7
affect-version: 3.0 Beta 1
affect-version: 2.5.3
affect-version: 2.1.6
affect-version: 3.0 Alpha 2
affect-version: 3.0 Alpha 1
affect-version: 2.5.2 Update 1
affect-version: 2.1.5 Update 1
affect-version: 2.5.2
affect-version: 2.5.1
affect-version: 2.5.0
fix-version: 3.0 RC1
fix-version: 2.5.5
resolution: fixed
priority: major
labels
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
The text was updated successfully, but these errors were encountered: