Derek, I was able to replicate all the behaviors you describe in 5.0.21.
I noticed you have a signed INT in one table and an UNsigned INT in the other. I changed t1 to UNsigned and then the query returns the results you would expect: +-----------+ | course_id | +-----------+ | -2 | | -1 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 68 | +-----------+ seems like the signed/unsigned data is not being converted before comparison, perhaps. If you can't change your column type in the table, perhaps you could use the CAST function in your queries? HTH, Dan On 9/29/06, Derek Fountain <[EMAIL PROTECTED]> wrote:
Can someone tell me what's wrong with this test: create table t1 ( course_id int(10) signed not null, primary key (course_id) ); create table t2 ( course_id int(10) unsigned not null, primary key (course_id) ); insert into t1 values (1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68); insert into t2 values (65),(66),(67); select distinct course_id from t1 where course_id not in (select course_id from t2); drop table t1; drop table t2; Running on 4.1.13 on SUSE Linux, this doesn't print anything. It should print those values in t1 but not t2. If I replace the subselect with the result of the subselect (65,66,67) then it works as expected. It also works if I remove the primary key from t2. If I just remove the primary key from t1 it prints a somewhat mysterious '1'. Can anyone explain what's going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
