[#CORE-1274] Wrong results when PLAN MERGE is chosen and datatypes of the equality predicate arguments are different

Firebird Core

Wrong results when PLAN MERGE is chosen and datatypes of the equality predicate arguments are different

Created: 21/May/07 08:22 PM Updated: 12/Jul/11 05:46 PM

Component/s:

Engine

Affects Version/s:

1.5.0, 1.5.1, 1.5.2, 1.5.3, 2.0.0, 1.5.4, 2.0.1, 2.1 Initial, 2.1 Alpha 1

Fix Version/s:

2.1.4, 2.5.1, 3.0 Alpha 1

Time Tracking:

Not Specified

Environment:

Win XP SP2

Issue Links:

Duplicate

This issue is duplicated by:

~~CORE-3274~~ Different results in SQL when using ''|| in join clause (to avoid using unselective index)

Relate

This issue relate to:

~~CORE-1752~~ Results of a join with different collations depend on the execution plan

~~CORE-3553~~ Nested loop plan is chosen instead of the sort merge for joining independent streams using keys of different types

This issue is related to:

~~QA-263~~ Test for CORE-1274

Description

« Hide

create table t1 (col1 int);
create table t2 (col2 varchar(10));
commit;

insert into t1 values (100);
insert into t1 values (20);
insert into t1 values (3);
commit;

insert into t2 values ('100');
insert into t2 values ('20');
insert into t2 values ('3');
commit;

select * from t1 join t2 on col1 = col2
-- returns only {100, '100'} instead of three rows

When the engine chooses the MERGE plan, it doesn't check the equality class datatypes. If they have different sorting rules, we're in trouble.

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Volker Rehn added a comment - 16/May/08 02:42 AM

Also affects 2.1 final, 2.5 alpha.

Moving from 2.0 to 2.1: This bug is a nasty one for 2.0 systems which were built on the assumption that implicit type casts in join comparisons give correct results, even if this only happened because the varchar column was indexed and no MERGE plan was used.

2.1 doesn't use the index in those situations. For those systems, upgrading to 2.1 therefore means reviewing all statements and updating part of them. Not exactly a blocker, but close.

[ Permalink | « Hide ]

Michiel Spoor added a comment - 08/Dec/10 10:06 AM

Any chance this fix can be backported to the 2.1.x branch?

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 08/Dec/10 10:14 AM

Yes, it will be done this week.

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 08/Dec/10 10:21 AM - edited

But please note that performance may suffer in your case. Usually, hints (field || '' in your case) are used to disable a single index of a few ones chosen by the optimizer, or to change the join order. But you seem to disable the only one suitable index and the plan changes from JOIN to MERGE. Once this fix is backported, the MERGE plan will no longer be possible and the resulting effect would be a JOIN plan again, but this time a not-indexed one, which effectively means a cartesian join. It will be terribly slow.

I hope your real situation is somewhat different. Otherwise, I'd rather suggest to either use hints properly (|| '' for strings and + 0 for numerics) or find some alternative solution.

[ Permalink | « Hide ]

Michiel Spoor added a comment - 08/Dec/10 10:40 AM

Thanks for the quick response!
I am very glad the fix will be backported. We will probably need to go use +0 anyway, but I am very relieved that places we may overlook/forget just turn slow instead of unnoticeably return bad results.

By the way:
I've just tested the current snapshot of 3.0 Alpha (8 dec 2010), and can confirm that the fix works for both ~~CORE-1274~~ and ~~CORE-3274~~.

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 08/Dec/10 10:57 AM

Backported into v2.5.1 and v2.1.4.