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 the column with collation using option (NUMERIC-SORT=1) is in where clause [CORE3947] #4280

Closed
firebird-automations opened this issue Oct 5, 2012 · 17 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: JDenk (kub)

Is duplicated by CORE3999
Is related to QA579
Is related to CORE3545

create collation utf8_num for UTF8 from UNICODE 'NUMERIC-SORT=1';
ALTER CHARACTER SET UTF8 SET DEFAULT COLLATION utf8_num;
commit;

CREATE TABLE TEST_TABLE (TEST_FIELD VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8_NUM);
commit;

INSERT into TEST_TABLE values('a1');
INSERT into TEST_TABLE values('a01');
INSERT into TEST_TABLE values('a001');
------------------------------------------------------------------

Run select :
------------------------------------------------------------------
select * from TEST_TABLE where TEST_FIELD = 'a1'
------------------------------------------------------------------
This select returns three results : 'a1', 'a01', 'a001'.

When I run the same example (but with the default collation), everything seems to be ok.

Commits: 046355b e339e46 FirebirdSQL/fbt-repository@09988b3

====== Test Details ======

See also: http://www.sql.ru/forum/1093394/select-from-t1-order-by-s-ne-uzaet-uniq-indeks-esli-s-utf8-coll-numeric-sort-1?mid=15987488#15987488
(changing in 3.0: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups).
------
And just for additional info:
non-unique index on field which was defined with "collate unicode_ci" is not used in GROUP BY on that field, see comments by ASF in CORE4787

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

This result is absolutely correct and match logic of numeric-sort collation.

@firebird-automations
Copy link
Collaborator Author

Commented by: JDenk (kub)

Ok.
So could you help me with this? : I need the varchar columns to be sorted in numeric meaning i.e. (a1, a2, a3, a10) and NOT in the aplphanumeric (a1, a10, a2, a3). Also I need the select to be correct (select * from TEST_TABLE where TEST_FIELD = 'a1' from the example above must return only ONE rs row).

How do I achieve this?
Thanks.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: Create database, table, init data :
---------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE 'c:\tmp\test.fdb' user 'SYSDBA' password 'masterkey' PAGE_SIZE 8192;

create collation utf8_num for UTF8 from UNICODE 'NUMERIC-SORT=1';
ALTER CHARACTER SET UTF8 SET DEFAULT COLLATION utf8_num;
commit;

CREATE TABLE TEST_TABLE (TEST_FIELD VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8_NUM);
commit;

INSERT into TEST_TABLE values('a1');
INSERT into TEST_TABLE values('a01');
INSERT into TEST_TABLE values('a001');
---------------------------------------------------------------------------------------------------------------------------------------------

Run select :
---------------------------------------------------------------------------------------------------------------------------------------------
select * from TEST_TABLE where TEST_FIELD = 'a1'
---------------------------------------------------------------------------------------------------------------------------------------------

This select returns three results : 'a1', 'a01', 'a001'.

When I run the same example (but with the default collation), everything seems to be ok.

=>

create collation utf8_num for UTF8 from UNICODE 'NUMERIC-SORT=1';
ALTER CHARACTER SET UTF8 SET DEFAULT COLLATION utf8_num;
commit;

CREATE TABLE TEST_TABLE (TEST_FIELD VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8_NUM);
commit;

INSERT into TEST_TABLE values('a1');
INSERT into TEST_TABLE values('a01');
INSERT into TEST_TABLE values('a001');
------------------------------------------------------------------

Run select :
------------------------------------------------------------------
select * from TEST_TABLE where TEST_FIELD = 'a1'
------------------------------------------------------------------
This select returns three results : 'a1', 'a01', 'a001'.

When I run the same example (but with the default collation), everything seems to be ok.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I agree with Dimitry, although named 'NUMERIC-SORT' without consider this behavior.

But what should like return? It current returns only 'a1'...

select * from TEST_TABLE where TEST_FIELD like 'a1';

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Looks like it's better to make NUMERIC-SORT work only for ordering purposes.

I've already a fix for that. I'll commit only in trunk, cause it plays with index keys.

NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE3999 [ CORE3999 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Patrick Marten (patrick marten)

Hello,

I had created the duplicate entry CORE3999 a while ago. Now seeing the statement "NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups. " I'm wondering, whether the case I had described there will be working or not, i.e. whether it will be possible to use NUMERIC-SORT on columns defined as unique in order to get them sorted properly.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Patrick, this index will not be used for an ORDER plan.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA579 [ QA579 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: See also: http://www.sql.ru/forum/1093394/select-from-t1-order-by-s-ne-uzaet-uniq-indeks-esli-s-utf8-coll-numeric-sort-1?mid=15987488#15987488
(changing in 3.0: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Test Details: See also: http://www.sql.ru/forum/1093394/select-from-t1-order-by-s-ne-uzaet-uniq-indeks-esli-s-utf8-coll-numeric-sort-1?mid=15987488#15987488
(changing in 3.0: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups).

=>

See also: http://www.sql.ru/forum/1093394/select-from-t1-order-by-s-ne-uzaet-uniq-indeks-esli-s-utf8-coll-numeric-sort-1?mid=15987488#15987488
(changing in 3.0: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups).
------
And just for additional info:
Index on field defined with "collate unicode_ci" is not used in GROUP BY on that field, see comments by ASF in CORE4787

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Test Details: See also: http://www.sql.ru/forum/1093394/select-from-t1-order-by-s-ne-uzaet-uniq-indeks-esli-s-utf8-coll-numeric-sort-1?mid=15987488#15987488
(changing in 3.0: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups).
------
And just for additional info:
Index on field defined with "collate unicode_ci" is not used in GROUP BY on that field, see comments by ASF in CORE4787

=>

See also: http://www.sql.ru/forum/1093394/select-from-t1-order-by-s-ne-uzaet-uniq-indeks-esli-s-utf8-coll-numeric-sort-1?mid=15987488#15987488
(changing in 3.0: NUMERIC-SORT UNIQUE indexes will not be usable for ORDER, only for lookups).
------
And just for additional info:
non-unique index on field which was defined with "collate unicode_ci" is not used in GROUP BY on that field, see comments by ASF in CORE4787

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is related to CORE3545 [ CORE3545 ]

@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
Projects
None yet
Development

No branches or pull requests

2 participants