MySQL Bugs: #71388: order by error

Bug #71388	order by error
Submitted:	15 Jan 2014 8:14	Modified:	24 Feb 2014 12:45
Reporter:	qinglin zhang (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.15, 5.5.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	select order by varchar text

Description:
select result with same content is not same with different column datatype

How to repeat:
create table A(name char(20));
insert into A values('a');
insert into A values('A');
select * from A order by name ;
+------+
| name |
+------+
| A    |
| a    |
+------+
root@test 04:10:35>show full fields from A;
+-------+------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| name  | char(20) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+------+-------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

create table B (name text);
insert into B values('a');
insert into B values('A');
select * from B order by name ;
root@test 04:10:35>show full fields from B;
+-------+------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| name  | text | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+------+-------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
+------+
| name |
+------+
| a    |
| A    |
+------+

the sequence is error

my configuration is :
[client]
socket = /u01/5615/run/mysql.sock

[innotop]
socket = /u01/5615/run/mysql.sock

[mysql]
auto-rehash

[mysqld]
sort_buffer_size=1k
core-file

#### Baes dir ####
basedir = /u01/5615
datadir = /u01/5615/data
tmpdir = /u01/5615/tmp
socket = /u01/5615/run/mysql.sock
#### Base configure info ####
port = 3406#5615
skip-name-resolve
old_passwords = 0
lower_case_table_names = 1
open_files_limit = 65535
read_rnd_buffer_size = 5M
max_allowed_packet = 24M
max_connect_errors = 50000
max_connections = 5000
max_user_connections = 2000
thread_cache_size=64
#rpl_semi_sync_slave_enabled=1
relay_log_recovery=1
table_open_cache_instances=5
metadata_locks_hash_instances = 32
metadata_locks_cache_size=2048
slave_pending_jobs_size_max = 1073741824

innodb_use_native_aio = 1
innodb_file_format=Barracuda
bind-address = 0.0.0.0

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup = 1
innodb_print_all_deadlocks = 1
innodb_buffer_pool_instances=4
innodb_online_alter_log_max_size=2G

binlog_format=row
#### Log info ####
log-error = /u01/5615/log/alert.log
log-queries-not-using-indexes
long_query_time = 1

#### Binary log && Relay log ####
log-bin = /u01/5615/log/mysql-bin
log-slave-updates = 1
relay-log = /u01/5615/log/mysqld-relay-bin
relay-log-index = /u01/5615/log/mysqld-relay-bin.index
master-info-file = /u01/5615/log/master.info
relay-log-info-file = /u01/5615/log/relay-log.info
#expire_logs_days=1
max_binlog_size = 500M
max_binlog_cache_size = 2G
relay_log_purge=1
### Server id ####
server-id=3406

#### query cache ####
query_cache_size = 0 
query_cache_limit = 1K
query_cache_min_res_unit = 1K
query_cache_type=0

#innodb
default-storage-engine = INNODB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 100
innodb_buffer_pool_size = 4G
innodb_log_buffer_size= 400M
innodb_log_file_size = 400M 
innodb_log_files_in_group = 4
innodb_max_dirty_pages_pct = 50
innodb_thread_concurrency=16
innodb_purge_threads = 4
innodb_io_capacity=1000
innodb_lru_scan_depth=8192
#innodb_flush_method = O_DIRECT_NO_FSYNC 
innodb_flush_method = O_DIRECT

innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=2G

performance_schema_instrument = '%=on'
performance_schema=ON
slave_parallel_workers= 8

relay_log_info_repository='TABLE'
master_info_repository='TABLE'
enforce_gtid_consistency = OFF
gtid_mode = OFF

explicit_defaults_for_timestamp=ON

#memcache 
#innodb_api_enable_binlog = 1
#daemon_memcached_option="-p11222"
#daemon_memcached_r_batch_size=1
#daemon_memcached_w_batch_size=1

thread_stack = 1524288
[mysqld_safe]
pid-file = /u01/5615/run/mysql.pid

This may be some imperfection and unexpected but unles you use a binary collation or a COLLATE clause in the query I think nobody were ever promised any specific ordering of uppercases or lowercases.

Try 
SELECT * FROM A ORDER BY name COLLATE charset_bin; 
-- replace "COLLATE charset_bin" with "COLLATE utf8_bin" if your charset is UTF8 etc.

Peter
(not a MySQL/Oracler person)

Thank you for the report.

I can not repeat described behavior. Please indicate which exact package of MySQL server you are using (file name you downloaded) and provide output of SHOW VARIABLES LIKE '%char%" and SHOW VARIABLES LIKE '%col%'

the version is 5.6.15 while the information you need is :
root@(none) 09:27:17>SHOW VARIABLES LIKE '%char%';
+--------------------------+---------------------------+
| Variable_name            | Value                     |
+--------------------------+---------------------------+
| character_set_client     | gbk                       |
| character_set_connection | gbk                       |
| character_set_database   | gbk                       |
| character_set_filesystem | binary                    |
| character_set_results    | gbk                       |
| character_set_server     | gbk                       |
| character_set_system     | utf8                      |
| character_sets_dir       | /u01/5615/share/charsets/ |
+--------------------------+---------------------------+
8 rows in set (0.01 sec)

root@(none) 09:27:24>SHOW VARIABLES LIKE '%col%';
+---------------------------+----------------+
| Variable_name             | Value          |
+---------------------------+----------------+
| collation_connection      | gbk_chinese_ci |
| collation_database        | gbk_chinese_ci |
| collation_server          | gbk_chinese_ci |
| protocol_version          | 10             |
| slave_compressed_protocol | OFF            |
+---------------------------+----------------+
5 rows in set (0.00 sec)

Thank you for the feedback.

I still cannot repeat described behavior:

set names gbk;
SHOW VARIABLES LIKE '%col%';
Variable_name	Value
collation_connection	gbk_chinese_ci
collation_database	gbk_chinese_ci
collation_server	gbk_chinese_ci
protocol_version	10
slave_compressed_protocol	OFF
create table A(name char(20))  engine=innodb;
insert into A values('a');
insert into A values('A');
select * from A order by name ;
name
a
A
show full fields from A;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
name	char(20)	gbk_chinese_ci	YES		NULL		select,insert,update,references	
create table B (name text) engine=innodb;
insert into B values('a');
insert into B values('A');
select * from B order by name ;
name
a
A
show full fields from B;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
name	text	gbk_chinese_ci	YES		NULL		select,insert,update,references	

Please provide output of SELECT name, HEX(name) FROM A; and SELECT name, HEX(name) FROM B;

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".