Strange bug with LCASE in SELECT statement - asking for support

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Olaf
Posts: 4
Joined: Tue 16 May 2006 16:47

Strange bug with LCASE in SELECT statement - asking for support

Post by Olaf » Tue 16 May 2006 16:51

Dear all,

take a look at the following SELECT statement (which doesn't make much sense the way it is shown here, but we have a very complicated, automatically created WHERE clause that follows the same principle):

SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc' AND myfield = 'ABC'

This will always return 0 when executes with MySQLDirect .NET, even if there is a row in mytable that matches myfield='ABC'!

If you separate the WHERE clause, the results are correct:

SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc'

as well as

SELECT COUNT(*) FROM mytable WHERE myfield = 'ABC'

shows the same correct result > 0.

The code is:

Code: Select all

MySqlConnection con = new MySqlConnection(YOUR SETTINGS);
MySqlCommand cmd;
con.Open();

cmd = new MySqlCommand("SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc' AND myfield = 'ABC'", con);
Debug.WriteLine(cmd.ExecuteScalar()); // always returns 0

cmd = new MySqlCommand("SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc'", con);
Debug.WriteLine(cmd.ExecuteScalar()); // returns 5

cmd = new MySqlCommand("SELECT COUNT(*) FROM mytable WHERE myfield = 'ABC'", con);
Debug.WriteLine(cmd.ExecuteScalar()); // returns 5
con.Close();

We are running MySql Version 5.0.21 on Suse 9.3, MySQLDirect .NET Version 3.50.10.0, .NET 2 with Csharp.

The same statement works fine for any other client, like the one integrated in php or the command line!

We'd be very grateful for help as we are about to finish a project.

Thanks, Olaf Schreiber, Berlin

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 17 May 2006 06:41

Any result you get, returns mysql server, but not MySQLDirect .NET. We just pass SQL statement to the server and receive results from it. Make sure your mysql server returns what you expect.

Olaf
Posts: 4
Joined: Tue 16 May 2006 16:47

Post by Olaf » Mon 22 May 2006 09:12

For those who might be interested: the phenomenon described above occurs when UTF8_bin is used as table collation. The whole thing works fine with, i.e., latin1_general_cs.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 22 May 2006 09:28

This must be happening because client charset and charset of a table are different.
Please execute tha following statement and send us its resultset

Code: Select all

show variables like 'character_set%'

Olaf
Posts: 4
Joined: Tue 16 May 2006 16:47

Post by Olaf » Mon 22 May 2006 12:55

Indeed! We never really thought about that. The same thing happens with other clients as well unless they use utf8 as character_set_client.

Sorry for assuming it had to do with CoreLab :-)

Is there a way to set the client character set with CoreLab? Something like

Code: Select all

cmd = new MySqlCommand("SET NAMES utf8;SET CHARACTER SET utf8;", con);
cmd.ExecuteNonQuery();
has proven unsuccessful.

Here's the list:

character_set_client - latin1
character_set_connection - latin1
character_set_database - utf8
character_set_filesystem - binary
character_set_results - latin1
character_set_server - latin1
character_set_system - utf8
character_sets_dir - /usr/share/mysql/charsets/

So far, we've solved the issue by modifying the character set of the field in the database.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 22 May 2006 13:08

Code: Select all

Something like 

Code: 
cmd = new MySqlCommand("SET NAMES utf8;SET CHARACTER SET utf8;", con); 
cmd.ExecuteNonQuery(); 
This exactly is implemented in setting Unicode=true for the MySqlConnection component.

Olaf
Posts: 4
Joined: Tue 16 May 2006 16:47

Post by Olaf » Mon 22 May 2006 13:18

It works!!!

Goodbye, and thanks...

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 23 May 2006 07:41

You are always welcome

Post Reply