MySQL Bugs: #232: Problem sortting registers with accents in character set latin1

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:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

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;

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.

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.

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.

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.

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.