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

Query produce different results, depending on the presence of an index [CORE2232] #1008

Closed
firebird-automations opened this issue Dec 9, 2008 · 9 comments
Closed

Query produce different results, depending on the presence of an index [CORE2232] #1008

firebird-automations opened this issue Dec 9, 2008 · 9 comments
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.1.0 affect-version: 2.1.1 affect-version: 2.5 Alpha 1 component: charsets/collation component: engine priority: major resolution: duplicate type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Svend Meyland Nicolaisen (smndk)

Duplicates CORE1188

The following test cases produce different results, depending on the presence of an index:

==============================================================================

CREATE DATABASE 'localhost/3050:C:\Test1156.fdb' DEFAULT CHARACTER SET UTF8;
CREATE COLLATION MyCollation FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=da_DK';
CREATE TABLE MyTable (Deleted SMALLINT,Name VARCHAR(30) COLLATE MyCollation);
COMMIT;

INSERT INTO MyTable(Deleted,Name) VALUES(0,'Anders');
COMMIT;

SELECT * FROM MyTable WHERE Name STARTING WITH 'A' COLLATE MyCollation AND Deleted=0;

CREATE INDEX MyIndex ON MyTable (Deleted,Name);
COMMIT;

SELECT * FROM MyTable WHERE Name STARTING WITH 'A' COLLATE MyCollation AND Deleted=0;

==============================================================================

create database 't2.fdb';
create table t (Deleted SMALLINT,c varchar(10) character set win1250 collate pxw_csy); insert into t values (0,'ch');
SET PLAN;
select *
from t where c starting with 'c' AND Deleted=0; commit;

create index t_c on t (Deleted,c);
select * from t where c starting with 'c' and Deleted=0;

==============================================================================

Adriano noted:

Seems for me that when the last char is removed and an empty segment is looked, problem happen. It correct finds that one:
insert into t values (0, 'chch');
select * from t where c starting with 'chc' AND deleted = 0;

Note:
Probably applies to other versions too, such as 2.1.0.
@firebird-automations
Copy link
Collaborator Author

Modified by: Svend Meyland Nicolaisen (smndk)

description: The following test cases produce different results, depending on the presence of an index:

==============================================================================

CREATE DATABASE 'localhost/3050:C:\Test1156.fdb' DEFAULT CHARACTER SET UTF8;
CREATE COLLATION MyCollation FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=cs_CZ';
CREATE TABLE MyTable (Deleted SMALLINT,Name VARCHAR(30) COLLATE MyCollation);
COMMIT;

INSERT INTO MyTable(Deleted,Name) VALUES(0,'Anders');
COMMIT;

SELECT * FROM MyTable WHERE Name STARTING WITH 'A' COLLATE MyCollation AND Deleted=0;

CREATE INDEX MyIndex ON MyTable (Deleted,Name);
COMMIT;

SELECT * FROM MyTable WHERE Name STARTING WITH 'A' COLLATE MyCollation AND Deleted=0;

==============================================================================

create database 't2.fdb';
create table t (Deleted SMALLINT,c varchar(10) character set win1250 collate pxw_csy); insert into t values (0,'ch');
SET PLAN;
select *
from t where c starting with 'c' AND Deleted=0; commit;

create index t_c on t (Deleted,c);
select * from t where c starting with 'c' and Deleted=0;

==============================================================================

Adriano noted:

Seems for me that when the last char is removed and an empty segment is looked, problem happen. It correct finds that one:
insert into t values (0, 'chch');
select * from t where c starting with 'chc' AND deleted = 0;

Note:
Probably applies to other versions too, such as 2.1.0.

=>

The following test cases produce different results, depending on the presence of an index:

==============================================================================

CREATE DATABASE 'localhost/3050:C:\Test1156.fdb' DEFAULT CHARACTER SET UTF8;
CREATE COLLATION MyCollation FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=da_DK';
CREATE TABLE MyTable (Deleted SMALLINT,Name VARCHAR(30) COLLATE MyCollation);
COMMIT;

INSERT INTO MyTable(Deleted,Name) VALUES(0,'Anders');
COMMIT;

SELECT * FROM MyTable WHERE Name STARTING WITH 'A' COLLATE MyCollation AND Deleted=0;

CREATE INDEX MyIndex ON MyTable (Deleted,Name);
COMMIT;

SELECT * FROM MyTable WHERE Name STARTING WITH 'A' COLLATE MyCollation AND Deleted=0;

==============================================================================

create database 't2.fdb';
create table t (Deleted SMALLINT,c varchar(10) character set win1250 collate pxw_csy); insert into t values (0,'ch');
SET PLAN;
select *
from t where c starting with 'c' AND Deleted=0; commit;

create index t_c on t (Deleted,c);
select * from t where c starting with 'c' and Deleted=0;

==============================================================================

Adriano noted:

Seems for me that when the last char is removed and an empty segment is looked, problem happen. It correct finds that one:
insert into t values (0, 'chch');
select * from t where c starting with 'chc' AND deleted = 0;

Note:
Probably applies to other versions too, such as 2.1.0.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

The following test case demonstrates it's not a specific problem with INTL contractions. It should return two rows, but returns only one (with n = 1).

execute block returns (n integer, deleted smallint, c varchar(10))
as
declare x varchar(10) = '';
begin
n = 0;
for select deleted, c from t
where c starting with :x and Deleted = 0
into deleted, c
do
suspend;

n = 1;
for select deleted, c from t
where c starting with '' and Deleted = 0
into deleted, c
do
suspend;
end!

@firebird-automations
Copy link
Collaborator Author

Commented by: Svend Meyland Nicolaisen (smndk)

I have now verified that the problem exists in version 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.0.4, 2.1.0, 2.1.1, 2.5 Alpha 1

The problem does not exist in version 1.5.5.

I have used the following script for the tests:

create database 't2.fdb';
create table t (Deleted SMALLINT,c varchar(10) character set win1250 collate pxw_csy);
insert into t values (0,'ch');
SET PLAN;
select *
from t where c starting with 'c' AND Deleted=0;
commit;
create index t_c on t (Deleted,c);
select * from t where c starting with 'c' and Deleted=0;

@firebird-automations
Copy link
Collaborator Author

Modified by: Svend Meyland Nicolaisen (smndk)

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.1 [ 10090 ]

Version: 2.0.0 [ 10091 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue duplicates CORE1188 [ CORE1188 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Duplicates CORE1188.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
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.1.0 affect-version: 2.1.1 affect-version: 2.5 Alpha 1 component: charsets/collation component: engine priority: major resolution: duplicate type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations