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
Comments
Commented by: @aafemt This result is absolutely correct and match logic of numeric-sort collation. |
Commented by: JDenk (kub) Ok. How do I achieve this? |
Modified by: Sean Leyne (seanleyne)description: Create database, table, init data : create collation utf8_num for UTF8 from UNICODE 'NUMERIC-SORT=1'; CREATE TABLE TEST_TABLE (TEST_FIELD VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8_NUM); INSERT into TEST_TABLE values('a1'); Run select : 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'; CREATE TABLE TEST_TABLE (TEST_FIELD VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8_NUM); INSERT into TEST_TABLE values('a1'); Run select : When I run the same example (but with the default collation), everything seems to be ok. |
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'; |
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. |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Alpha 1 [ 10331 ] |
Modified by: @asfernandes |
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. |
Commented by: @asfernandes Patrick, this index will not be used for an ORDER plan. |
Modified by: @pavel-zotovstatus: 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 |
Modified by: @pavel-zotovstatus: 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 => 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 |
Modified by: @pavel-zotovstatus: 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 => 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 |
Modified by: @asfernandes |
Commented by: @pcisar Test created. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: