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-issue-importer opened this issue Oct 5, 2012 · 17 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Oct 5, 2012

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

firebird-issue-importer commented Oct 5, 2012

Commented by: @aafemt

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 5, 2012

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

firebird-issue-importer commented Oct 5, 2012

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

firebird-issue-importer commented Oct 5, 2012

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

firebird-issue-importer commented Oct 5, 2012

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

firebird-issue-importer commented Oct 5, 2012

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 5, 2012

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 28, 2012

Modified by: @asfernandes

Link: This issue is duplicated by CORE3999 [ CORE3999 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 22, 2014

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

firebird-issue-importer commented Jan 30, 2014

Commented by: @asfernandes

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA579 [ QA579 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

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

firebird-issue-importer commented May 29, 2015

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

firebird-issue-importer commented May 30, 2015

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

firebird-issue-importer commented Aug 7, 2015

Modified by: @asfernandes

Link: This issue is related to CORE3545 [ CORE3545 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 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
Projects
None yet
Development

No branches or pull requests

2 participants