Skip to content

Commit 72d902f

Browse files
author
Neeraj Bisht
committed
Bug#16691598 - ORDER BY LOWER(COLUMN) PRODUCES OUT-OF-ORDER RESULTS
Problem:- We have created a table with UTF8_BIN collation. In case, when in our query we have ORDER BY clause over a function call we are getting result in incorrect order. Note:the bug is not there in 5.5. Analysis: In 5.5, for UTF8_BIN, we are using my_strnxfrm_mb_bin() to transfer the locale.But from 5.6, we started using my_strnxfrm_unicode() for which we have to define MY_CS_STRNXFRM MACRO(which is set,if strnxfrm is used for sort). In 5.6: In case of UTF32_BIN, for 4 byte multi-byte character, we are getting 3 byte sorting _field_length, through my_strnxfrmlen_unicode_full_bin(). Then we are using this length as a result pointer length to store sorting weights. Which result in incorrect sorting weight,So we should give correct length that is result pointer length. So to give the correct length in the case where MY_CS_STRNXFRM MACRO is set we are not changing the length on the basis of sort_field->length.
2 parents 0f6837a + 97657db commit 72d902f

7 files changed

Lines changed: 91 additions & 17 deletions

File tree

‎mysql-test/include/ctype_filesort2.inc‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,3 +16,12 @@ SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
1616
--echo # Additional test for bug#37244 Character sets: short utf8_bin weight_string value
1717
SELECT HEX(a), HEX(WEIGHT_STRING(a)) FROM t1 ORDER BY a;
1818
DROP TABLE IF EXISTS t1;
19+
--echo #
20+
--echo # BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
21+
--echo # OUT-OF-ORDER RESULTS
22+
--echo #
23+
CREATE TABLE t1 SELECT ('a a') as n;
24+
INSERT INTO t1 VALUES('a b');
25+
SELECT * FROM t1 ORDER BY LOWER(n) ASC;
26+
SELECT * FROM t1 ORDER BY LOWER(n) DESC;
27+
DROP TABLE t1;

‎mysql-test/r/ctype_utf16.result‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -643,6 +643,21 @@ FF9D 00FF9D
643643
D800DF84 010384
644644
DBC0DC00 100000
645645
DROP TABLE IF EXISTS t1;
646+
#
647+
# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
648+
# OUT-OF-ORDER RESULTS
649+
#
650+
CREATE TABLE t1 SELECT ('a a') as n;
651+
INSERT INTO t1 VALUES('a b');
652+
SELECT * FROM t1 ORDER BY LOWER(n) ASC;
653+
n
654+
a a
655+
a b
656+
SELECT * FROM t1 ORDER BY LOWER(n) DESC;
657+
n
658+
a b
659+
a a
660+
DROP TABLE t1;
646661
select @@collation_connection;
647662
@@collation_connection
648663
utf16_bin

‎mysql-test/r/ctype_utf16le.result‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -686,6 +686,21 @@ HEX(a) HEX(WEIGHT_STRING(a))
686686
00D884DF 010384
687687
C0DB00DC 100000
688688
DROP TABLE IF EXISTS t1;
689+
#
690+
# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
691+
# OUT-OF-ORDER RESULTS
692+
#
693+
CREATE TABLE t1 SELECT ('a a') as n;
694+
INSERT INTO t1 VALUES('a b');
695+
SELECT * FROM t1 ORDER BY LOWER(n) ASC;
696+
n
697+
a a
698+
a b
699+
SELECT * FROM t1 ORDER BY LOWER(n) DESC;
700+
n
701+
a b
702+
a a
703+
DROP TABLE t1;
689704
select @@collation_connection;
690705
@@collation_connection
691706
utf16le_bin

‎mysql-test/r/ctype_utf32.result‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -642,6 +642,21 @@ HEX(a) HEX(WEIGHT_STRING(a))
642642
00010384 010384
643643
00100000 100000
644644
DROP TABLE IF EXISTS t1;
645+
#
646+
# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
647+
# OUT-OF-ORDER RESULTS
648+
#
649+
CREATE TABLE t1 SELECT ('a a') as n;
650+
INSERT INTO t1 VALUES('a b');
651+
SELECT * FROM t1 ORDER BY LOWER(n) ASC;
652+
n
653+
a a
654+
a b
655+
SELECT * FROM t1 ORDER BY LOWER(n) DESC;
656+
n
657+
a b
658+
a a
659+
DROP TABLE t1;
645660
select @@collation_connection;
646661
@@collation_connection
647662
utf32_bin

‎mysql-test/r/ctype_utf8mb4.result‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1075,6 +1075,21 @@ EFBE9D 00FF9D
10751075
F0908E84 010384
10761076
F4808080 100000
10771077
DROP TABLE IF EXISTS t1;
1078+
#
1079+
# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
1080+
# OUT-OF-ORDER RESULTS
1081+
#
1082+
CREATE TABLE t1 SELECT ('a a') as n;
1083+
INSERT INTO t1 VALUES('a b');
1084+
SELECT * FROM t1 ORDER BY LOWER(n) ASC;
1085+
n
1086+
a a
1087+
a b
1088+
SELECT * FROM t1 ORDER BY LOWER(n) DESC;
1089+
n
1090+
a b
1091+
a a
1092+
DROP TABLE t1;
10781093
select @@collation_connection;
10791094
@@collation_connection
10801095
utf8mb4_bin

‎sql/filesort.cc‎

Lines changed: 18 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -995,8 +995,6 @@ void make_sortkey(Sort_param *param, uchar *to, uchar *ref_pos)
995995
{
996996
const CHARSET_INFO *cs=item->collation.collation;
997997
char fill_char= ((cs->state & MY_CS_BINSORT) ? (char) 0 : ' ');
998-
int diff;
999-
uint sort_field_length;
1000998

1001999
if (maybe_null)
10021000
*to++=1;
@@ -1024,25 +1022,13 @@ void make_sortkey(Sort_param *param, uchar *to, uchar *ref_pos)
10241022
break;
10251023
}
10261024
uint length= res->length();
1027-
sort_field_length= sort_field->length - sort_field->suffix_length;
1028-
diff=(int) (sort_field_length - length);
1029-
if (diff < 0)
1030-
{
1031-
diff=0;
1032-
length= sort_field_length;
1033-
}
1034-
if (sort_field->suffix_length)
1035-
{
1036-
/* Store length last in result_string */
1037-
store_length(to + sort_field_length, length,
1038-
sort_field->suffix_length);
1039-
}
10401025
if (sort_field->need_strxnfrm)
10411026
{
10421027
char *from=(char*) res->ptr();
10431028
uint tmp_length;
10441029
if ((uchar*) from == to)
10451030
{
1031+
DBUG_ASSERT(sort_field->length >= length);
10461032
set_if_smaller(length,sort_field->length);
10471033
memcpy(param->tmp_buffer,from,length);
10481034
from=param->tmp_buffer;
@@ -1056,6 +1042,23 @@ void make_sortkey(Sort_param *param, uchar *to, uchar *ref_pos)
10561042
}
10571043
else
10581044
{
1045+
uint diff;
1046+
uint sort_field_length= sort_field->length -
1047+
sort_field->suffix_length;
1048+
if (sort_field_length < length)
1049+
{
1050+
diff= 0;
1051+
length= sort_field_length;
1052+
}
1053+
else
1054+
diff= sort_field_length - length;
1055+
if (sort_field->suffix_length)
1056+
{
1057+
/* Store length last in result_string */
1058+
store_length(to + sort_field_length, length,
1059+
sort_field->suffix_length);
1060+
}
1061+
10591062
my_strnxfrm(cs,(uchar*)to,length,(const uchar*)res->ptr(),length);
10601063
cs->cset->fill(cs, (char *)to+length,diff,fill_char);
10611064
}

‎strings/ctype-utf8.c‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6130,7 +6130,8 @@ CHARSET_INFO my_charset_utf8_general_mysql500_ci=
61306130
CHARSET_INFO my_charset_utf8_bin=
61316131
{
61326132
83,0,0, /* number */
6133-
MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_UNICODE, /* state */
6133+
MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_STRNXFRM|
6134+
MY_CS_UNICODE, /* state */
61346135
"utf8", /* cs name */
61356136
"utf8_bin", /* name */
61366137
"", /* comment */
@@ -8535,7 +8536,8 @@ CHARSET_INFO my_charset_utf8mb4_general_ci=
85358536
CHARSET_INFO my_charset_utf8mb4_bin=
85368537
{
85378538
46,0,0, /* number */
8538-
MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_UNICODE|MY_CS_UNICODE_SUPPLEMENT, /* state */
8539+
MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_STRNXFRM|
8540+
MY_CS_UNICODE|MY_CS_UNICODE_SUPPLEMENT, /* state */
85398541
MY_UTF8MB4, /* cs name */
85408542
MY_UTF8MB4_BIN, /* name */
85418543
"UTF-8 Unicode", /* comment */

0 commit comments

Comments
 (0)