Works fine on 5.0.87 (rows returned as expected).

Confirmed on 5.1.51-log.

Most likely problem with VARCHAR behavior, because this one works fine:
select * from test_nkomp_admin where host_id=6 and trim(admin_id)='luke';

Regards,
m

-----Original Message-----
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de] 
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51

Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 - 
maybe a fight against myself ;-)

The following query works exactly as assumed in version 5.1.41 with 
the given testbed:
  select * from test_nkomp_admin where host_id=6 and admin_id="yoda";

/* version 5.1.41 ubuntu 10.04 */
mysql>  select * from test_nkomp_admin where host_id=6 and 
admin_id="yoda" ;
+---------+----------+
| host_id | admin_id |
+---------+----------+
|       6 | yoda     |
+---------+----------+
1 row in set (0.00 sec)

whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from 
debian experimental,os ubuntu 10.10) the resultset is:
mysql> select * from test_nkomp_admin where host_id=6 and 
admin_id="yoda" ;
Empty set (0.00 sec)

prove:
  select * from test_nkomp_admin where host_id=6 ;
+---------+----------+
| host_id | admin_id |
+---------+----------+
|       6 | luke     |
|       6 | yoda     |
+---------+----------+
2 rows in set (0.00 sec)

Could anyone prove this behavior, or can give hint what the problem 
might be ?


/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;

create table test_nkomp
(
  host_id int unsigned auto_increment not null primary key
)engine=innodb;

create table test_admin
(
  admin_id varchar(15) not null primary key
)engine=innodb;

create table test_nkomp_admin
(
  host_id int unsigned,
  admin_id varchar(15),
  foreign key (host_id) references test_nkomp (host_id) on delete 
cascade on update cascade,
  foreign key (admin_id) references test_admin (admin_id) on delete 
cascade on update cascade

)engine=innodb;

insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);

insert into  test_admin (admin_id) values 
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');

insert into  test_nkomp_admin (host_id,admin_id) values 
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');

insert into  test_nkomp_admin (host_id,admin_id) values 
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');

insert into test_nkomp_admin (host_id,admin_id) values 
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');

insert into  test_nkomp_admin (host_id,admin_id) values 
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');

insert into test_nkomp_admin (host_id,admin_id) values 
(5,'luke'),(5,'yoda'),(5,'anakin');

insert into test_nkomp_admin (host_id,admin_id) values 
(6,'luke'),(6,'yoda');

cheers
gregor


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mis...@poczta.fm


---------------------------------------------------------------
Pobierz slownik angielsko-polski na telefon!
Kliknij >>> http://linkint.pl/f2839


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to