Description:
MySql server on Linux Ubuntu 10.04: mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper
The select:
"select id,v from test where ( k,v ) in ((12,'zzzzzzzz zzz'),({0},'robe de soirée'))" did not match any results, since 'robe de soirée' is in database.
The select:
select id,v from test where ( k,v ) in (({0},'robe de soirée')) daes
The select match on Mysql Query Browser
How to repeat:
Follow this C# program:
static string connection = "Database=test;Data Source=XXXXXXXXX;User Id=test;Password=test";
static Random rnd = new Random ();
static void Main(string[] args)
{
using (MySqlConnection conn = new MySqlConnection( connection ))
{
conn.Open();
execute("create table if not exists test ( id int unsigned primary key auto_increment, k int not null, v varchar(255) not null, index iKey (k) ) engine = MyIsam, ROW_FORMAT = DYNAMIC,character set = 'UTF8'", conn);
int aaa = insert("aaaa",conn);
insert("bbb bbbbb",conn);
insert("cc cc cc",conn);
int key = insert("robe de soirée",conn);
insert ("eeeeee",conn);
insert ("ffffff",conn);
// First Select is Ok
bool found = false;
string sql = string.Format( "select id,v from test where ( k,v ) in (({0},'robe de soirée'))",key);
MySqlCommand cmd = new MySqlCommand (sql,conn);
using ( MySqlDataReader r = cmd.ExecuteReader() )
{
while ( r.Read() ) found = true;
}
Console.WriteLine("Fisrt Select Found: {0}" , found ? "OK" : "NOK");
found = false;
sql = string.Format( "select id,v from test where ( k,v ) in ((12,'zzzzzzzz zzz'),({0},'robe de soirée'))",key);
cmd = new MySqlCommand (sql,conn);
using ( MySqlDataReader r = cmd.ExecuteReader() )
{
while ( r.Read() ) found = true;
}
Console.WriteLine("Second Select Found: {0}" , found ? "OK" : "NOK");
}
}
static void execute ( string sql , MySqlConnection conn )
{
MySqlCommand cmd = new MySqlCommand (sql,conn);
cmd.ExecuteNonQuery();
}
static int insert ( string value, MySqlConnection conn )
{
MySqlCommand cmd = new MySqlCommand ("insert into test (k,v) values (@key,@value)",conn);
int key = rnd.Next(1000000000)- 200000000;
cmd.Parameters.Add("@key",MySqlDbType.Int32).Value = key;
cmd.Parameters.Add("@value",MySqlDbType.VarString).Value = value;
cmd.ExecuteNonQuery();
return key;
}
Description: MySql server on Linux Ubuntu 10.04: mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper The select: "select id,v from test where ( k,v ) in ((12,'zzzzzzzz zzz'),({0},'robe de soirée'))" did not match any results, since 'robe de soirée' is in database. The select: select id,v from test where ( k,v ) in (({0},'robe de soirée')) daes The select match on Mysql Query Browser How to repeat: Follow this C# program: static string connection = "Database=test;Data Source=XXXXXXXXX;User Id=test;Password=test"; static Random rnd = new Random (); static void Main(string[] args) { using (MySqlConnection conn = new MySqlConnection( connection )) { conn.Open(); execute("create table if not exists test ( id int unsigned primary key auto_increment, k int not null, v varchar(255) not null, index iKey (k) ) engine = MyIsam, ROW_FORMAT = DYNAMIC,character set = 'UTF8'", conn); int aaa = insert("aaaa",conn); insert("bbb bbbbb",conn); insert("cc cc cc",conn); int key = insert("robe de soirée",conn); insert ("eeeeee",conn); insert ("ffffff",conn); // First Select is Ok bool found = false; string sql = string.Format( "select id,v from test where ( k,v ) in (({0},'robe de soirée'))",key); MySqlCommand cmd = new MySqlCommand (sql,conn); using ( MySqlDataReader r = cmd.ExecuteReader() ) { while ( r.Read() ) found = true; } Console.WriteLine("Fisrt Select Found: {0}" , found ? "OK" : "NOK"); found = false; sql = string.Format( "select id,v from test where ( k,v ) in ((12,'zzzzzzzz zzz'),({0},'robe de soirée'))",key); cmd = new MySqlCommand (sql,conn); using ( MySqlDataReader r = cmd.ExecuteReader() ) { while ( r.Read() ) found = true; } Console.WriteLine("Second Select Found: {0}" , found ? "OK" : "NOK"); } } static void execute ( string sql , MySqlConnection conn ) { MySqlCommand cmd = new MySqlCommand (sql,conn); cmd.ExecuteNonQuery(); } static int insert ( string value, MySqlConnection conn ) { MySqlCommand cmd = new MySqlCommand ("insert into test (k,v) values (@key,@value)",conn); int key = rnd.Next(1000000000)- 200000000; cmd.Parameters.Add("@key",MySqlDbType.Int32).Value = key; cmd.Parameters.Add("@value",MySqlDbType.VarString).Value = value; cmd.ExecuteNonQuery(); return key; }