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

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

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 30, 2007

Modified by: @dyemanov

Link: This issue is related to CORE230 [ CORE230 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2007

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 11, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 11, 2010

Modified by: @asfernandes

Link: This issue is duplicated by CORE2232 [ CORE2232 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

Modified by: @asfernandes

Link: This issue is related to CORE3052 [ CORE3052 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

Modified by: @asfernandes

Link: This issue block progress on CORE3052 [ CORE3052 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 16, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 16, 2010

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 26, 2011

Modified by: @dyemanov

Link: This issue is duplicated by CORE3452 [ CORE3452 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 14, 2012

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 20, 2012

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-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 28, 2012

Modified by: @asfernandes

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

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 27, 2014

Modified by: @pcisar

Link: This issue is related to QA506 [ QA506 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 17, 2015

Modified by: @dyemanov

Link: This issue is duplicated by CORE3971 [ CORE3971 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 25, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 25, 2015

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