Bug #232 Problem sortting registers with accents in character set latin1
Submitted: 5 Apr 2003 8:05 Modified: 5 Apr 2003 10:10
Reporter: Victor Herraiz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.12 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[5 Apr 2003 8:05] Victor Herraiz
Description:
This problem appears when executing a statement "Select" like this:
Select * from table order by char_or_varchar_col.
The fields containing accents appear bad sorted. 

How to repeat:
Execute this in mysql client:

CREATE DATABASE BIBLIOTECA;

USE BIBLIOTECA;

CREATE TABLE IDIOMAS (
IDIDIOMA TINYINT UNSIGNED NOT NULL AUTO_INCREMENT ,  
IDIOMA VARCHAR(20) NOT NULL   ,
PRIMARY KEY (IDIDIOMA)   ,
UNIQUE (IDIOMA) );

insert into idiomas(idioma) values ("Afrikaans"),("Alemán"),("Amárico"),("Árabe"),("Zulú");

select * from idiomas order by idioma;
[5 Apr 2003 8:17] Sinisa Milivojevic
This is not a bug.

A column is not binary, so it is not case sensitive.

Next, it is possible that for those accented characters
that are used, some other character set should be selected.
[5 Apr 2003 8:50] Victor Herraiz
Sorry, I'll try to explain better :D
In Character set latin1 (a = A = á = Á....).
if you execute this:
insert into idiomas (idioma) values ("a"),("b"),("A"),("B"),("á"),("Á");

select * from idiomas order by idioma;

You'll see the problem.
[5 Apr 2003 8:53] Victor Herraiz
The main problem is that this bug makes it impossible to order alphabetically a set of names in Spanish (or Italian) if some of them wear an accent.
[5 Apr 2003 10:10] Miguel Solorzano
That isn't a bug. The result depends in your OS/Hardware
configuration, below your example using a graphical tool
working with MyODBC which worked as expected. Generally
on Win the console and graphical environment aren't
using the same configuration:
SQLExecDirect:
In:		hstmt = 0x00851908, 
szSqlStr = "create table idiomas (idioma char(1))", cbSqlStr = -3
Return:	SQL_SUCCESS=0
SQLExecDirect:
In:hstmt = 0x00851908, 
szSqlStr = "insert into idiomas (idioma) values ("a"),("b"),("A"),...", cbSqlStr = -3
Return:	SQL_SUCCESS=0
SQLExecDirect:
In:hstmt = 0x00851908, 
szSqlStr = "select * from idiomas order by idioma", cbSqlStr = -3
Return:	SQL_SUCCESS=0

Get Data All:
"idioma"
"a"
"A"
"á"
"Á"
"b"
"B"
6 rows fetched from 1 column.
[5 Apr 2003 17:00] Victor Herraiz
I own a Spanish version of WinXP and I've tried the same query with J/conector(JDBC) and MysqlClient. The server charcarter set is latin1. In both queries the results are bad sorted.

I don't understand the problem and i can't use MyODBC, i'm realy sorry.