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

Wrong results when PLAN MERGE is chosen and datatypes of the equality predicate arguments are different [CORE1274] #1695

Closed
firebird-issue-importer opened this issue May 21, 2007 · 18 comments

Comments

@firebird-issue-importer

Submitted by: @dyemanov

Relate to CORE1752
Is duplicated by CORE3274
Is related to QA263
Relate to CORE3553

Votes: 1

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.

Commits: 9dfe2fe 9fe05cc 2f28556

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

Workflow: jira [ 12160 ] => Firebird [ 15575 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 21, 2008

Modified by: @dyemanov

Link: This issue relate to CORE1752 [ CORE1752 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 16, 2008

Commented by: Volker Rehn (vr2_s18)

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2009

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 19, 2009

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 14, 2010

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Modified by: @dyemanov

Link: This issue is duplicated by CORE3274 [ CORE3274 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Commented by: Michiel Spoor (mdspoor)

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Commented by: @dyemanov

Yes, it will be done this week.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Commented by: @dyemanov

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Commented by: Michiel Spoor (mdspoor)

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 CORE1274 and CORE3274.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Commented by: @dyemanov

Backported into v2.5.1 and v2.1.4.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 8, 2010

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 2.5.1 [ 10333 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 23, 2011

Modified by: @pcisar

Link: This issue is related to QA263 [ QA263 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 24, 2011

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 12, 2011

Modified by: @dyemanov

Link: This issue relate to CORE3553 [ CORE3553 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test => Done successfully

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