Issue Details (XML | Word | Printable)

Key: CORE-3947
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: JDenk
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Wrong results when the column with collation using option (NUMERIC-SORT=1) is in where clause

Created: 05/Oct/12 01:54 PM   Updated: 23/Sep/15 12:48 PM
Component/s: None
Affects Version/s: 2.5.0
Fix Version/s: 3.0 Alpha 1

Environment: Win7 x64
Issue Links:
Duplicate
 
Relate
 

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).
------
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 CORE-4787


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 05/Oct/12 02:33 PM
This result is absolutely correct and match logic of numeric-sort collation.

JDenk added a comment - 05/Oct/12 02:44 PM
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.

Adriano dos Santos Fernandes added a comment - 05/Oct/12 02:46 PM
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';

Adriano dos Santos Fernandes added a comment - 05/Oct/12 04:52 PM
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.

Patrick Marten added a comment - 22/Jan/14 03:37 PM
Hello,

I had created the duplicate entry CORE-3999 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.

Adriano dos Santos Fernandes added a comment - 30/Jan/14 02:29 PM
Patrick, this index will not be used for an ORDER plan.

Pavel Cisar added a comment - 23/Sep/15 12:48 PM
Test created.