Strange bug with LCASE in SELECT statement - asking for support

Strange bug with LCASE in SELECT statement - asking for support

Postby 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
Olaf
 
Posts: 4
Joined: Tue 16 May 2006 16:47

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
Olaf
 
Posts: 4
Joined: Tue 16 May 2006 16:47

Postby 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%'
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
Olaf
 
Posts: 4
Joined: Tue 16 May 2006 16:47

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Olaf » Mon 22 May 2006 13:18

It works!!!

Goodbye, and thanks...
Olaf
 
Posts: 4
Joined: Tue 16 May 2006 16:47

Postby Alexey » Tue 23 May 2006 07:41

You are always welcome
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL