Insert with subquery doesn't return error

Hi All,

I'm writing a row to a table using:

"INSERT INTO volunteerevents
SELECT volunteer.id, event.id, 0, null
FROM volunteer, event
WHERE volunteer.userid = "{$user}"
AND event.location = "{$location}"
AND event.dateofevent = "{$dateofevent}"";

There is a primary key on the volunteerevents table (volunteerid, eventid).

If this query is run twice with the same values I would expect an 'Error 1062' duplicate key error. However, that's not the case, the query returns no error but does show via mysql_affected_rows() ==0 that it didn't insert a second row. I've written code to detect the 1062 error and send the user a message.

If I execute basically the same query but no subselect:

"INSERT INTO volunteerevents
VALUES (1,1,0,NULL)";

I do receive the expected error 1062.

Is this a MySQL bug? Feature?

Is there a workaround or should I first check the table to see if the row exists? Which I would rather not do since the database access costs a bit.

"Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, you should specify IGNORE explicitly to ignore records that would cause duplicate-key violations."

http://www.mysql.com/doc/en/INSERT_SELECT.html