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.

Sign up for GitHub

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

Jump to bottom

Select Starting with :Param (Empty string) doesn't work if using index with many fields [CORE1188] #1613

Closed
firebird-automations opened this issue Mar 30, 2007 · 22 comments
Closed

Select Starting with :Param (Empty string) doesn't work if using index with many fields [CORE1188] #1613

firebird-automations opened this issue Mar 30, 2007 · 22 comments
Assignees
@asfernandes
Labels
affect-version: 2.0.0 affect-version: 2.0.1 affect-version: 2.0.2 affect-version: 2.0.3 affect-version: 2.0.4 affect-version: 2.0.5 affect-version: 2.1.0 affect-version: 2.1.1 affect-version: 2.1.2 affect-version: 2.1.3 affect-version: 2.5 Alpha 1 affect-version: 2.5 Beta 1 affect-version: 2.5 Beta 2 affect-version: 2.5 RC1 affect-version: 2.5 RC2 affect-version: 3.0 Initial component: engine fix-version: 3.0 Alpha 1 priority: major qa: done successfully type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Evelyne Girard (evelyne)

Is related to CORE230
Is duplicated by CORE2232
Is related to CORE3052
Block progress on CORE3052
Is duplicated by CORE3452
Is related to QA506
Is duplicated by CORE3971

Votes: 3

The following Select returns no row if we use a natural scan or if we pass directly the '' Value instead of using a parameter in Firebird 2.1 (Alpha) and 2.0 (It is OK in 1.5.3).

create table TestX(
StringField1 varchar(10),
StringField2 varchar(10)
);
create index X_TestX_1_2 on Testx(StringField1,StringField2);
insert into testx values('A','B');

select * from testx
where stringfield1 ='A'
and stringfield2 starting with :Param
plan (testx natural);
Returns 1 row;

select * from testx
where stringfield1 ='A'
and stringfield2 starting with :Param
plan (testx index (x_testx_1_2));
returns 0 row;

select * from testx
where stringfield1 ='A'
and stringfield2 starting with ''
plan (testx natural);
Returns 1 row;

I wanted to reproduce this in ISQL but I couldn't find how to use parameters in ISQL (can we?) ... I had this problem in Delphi using FIBPlus components, PlanAnalyzer and also in IBExpert.

Thank you

Evelyne

Commits: c1c5d2b 57ddc9e FirebirdSQL/fbt-repository@a290acc
@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE230 [ CORE230 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11725 ] => Firebird [ 15411 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5 RC2 [ 10372 ]

Version: 2.5 RC1 [ 10362 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.5 Beta 2 [ 10300 ]

Version: 2.5 Beta 1 [ 10251 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.0 [ 10091 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE2232 [ CORE2232 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is related to CORE3052 [ CORE3052 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue block progress on CORE3052 [ CORE3052 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The description looks incorrect to me. I see that the first query returns one row but the second one returns zero rows. Can anybody else confirm that?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Dmitry, that confirms my understanding of this bug and its description. Empty parameter plus indexed access triggers it, so bug "subject" is correct and description is not.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Thanks, this was my understanding as well. I just wanted to be sure I'm researching this bug and not another one ;-)

@firebird-automations
Copy link
Collaborator Author

Commented by: Evelyne Girard (evelyne)

I made the correction so the description gives the accurate results (I unfortunately switched the first two results when I posted this issue at first). Sorry about that.

@firebird-automations
Copy link
Collaborator Author

Modified by: Evelyne Girard (evelyne)

description: The following Select returns no row if we use a natural scan or if we pass directly the '' Value instead of using a parameter in Firebird 2.1 (Alpha) and 2.0 (It is OK in 1.5.3).

create table TestX(
StringField1 varchar(10),
StringField2 varchar(10)
);
create index X_TestX_1_2 on Testx(StringField1,StringField2);
insert into testx values('A','B');

select * from testx
where stringfield1 ='A'
and stringfield2 starting with :Param
plan (testx natural);
Returns 0 row;

select * from testx
where stringfield1 ='A'
and stringfield2 starting with :Param
plan (testx index (x_testx_1_2));
returns 1 row;

select * from testx
where stringfield1 ='A'
and stringfield2 starting with ''
plan (testx natural);
Returns 1 row;

I wanted to reproduce this in ISQL but I couldn't find how to use parameters in ISQL (can we?) ... I had this problem in Delphi using FIBPlus components, PlanAnalyzer and also in IBExpert.

Thank you

Evelyne

=>

The following Select returns no row if we use a natural scan or if we pass directly the '' Value instead of using a parameter in Firebird 2.1 (Alpha) and 2.0 (It is OK in 1.5.3).

create table TestX(
StringField1 varchar(10),
StringField2 varchar(10)
);
create index X_TestX_1_2 on Testx(StringField1,StringField2);
insert into testx values('A','B');

select * from testx
where stringfield1 ='A'
and stringfield2 starting with :Param
plan (testx natural);
Returns 1 row;

select * from testx
where stringfield1 ='A'
and stringfield2 starting with :Param
plan (testx index (x_testx_1_2));
returns 0 row;

select * from testx
where stringfield1 ='A'
and stringfield2 starting with ''
plan (testx natural);
Returns 1 row;

I wanted to reproduce this in ISQL but I couldn't find how to use parameters in ISQL (can we?) ... I had this problem in Delphi using FIBPlus components, PlanAnalyzer and also in IBExpert.

Thank you

Evelyne

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE3452 [ CORE3452 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I see that compress is generating a pad byte for empty strings. What about this patch (for trunk)?

-----------------------------------
diff --git a/src/jrd/btr.cpp b/src/jrd/btr.cpp
index 437a8a9..a6ddef8 100644
--- a/src/jrd/btr.cpp
+++ b/src/jrd/btr.cpp
@@ -2409,10 +2409,13 @@ static void compress(thread_db* tdbb,
while (p > key->key_data)
{
if (*--p != pad)
+ {
+ ++p;
break;
+ }
}

- key->key_length = p + 1 - key->key_data;
+ key->key_length = p - key->key_data;
return;
}

-----------------------------------

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ] => Adriano dos Santos Fernandes [ asfernandes ]

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA506 [ QA506 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE3971 [ CORE3971 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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

@asfernandes asfernandes

Labels
affect-version: 2.0.0 affect-version: 2.0.1 affect-version: 2.0.2 affect-version: 2.0.3 affect-version: 2.0.4 affect-version: 2.0.5 affect-version: 2.1.0 affect-version: 2.1.1 affect-version: 2.1.2 affect-version: 2.1.3 affect-version: 2.5 Alpha 1 affect-version: 2.5 Beta 1 affect-version: 2.5 Beta 2 affect-version: 2.5 RC1 affect-version: 2.5 RC2 affect-version: 3.0 Initial component: engine fix-version: 3.0 Alpha 1 priority: major qa: done successfully type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations