Rich,
Not quite sure how you arrived at this conclusion. See below.
mysql> use test;
Database changed
mysql> CREATE TABLE Rich (
-> my_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
-> , my_char CHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO Rich (my_char) VALUES ('house'),('street');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DELIMITER //
mysql> CREATE TRIGGER upd_trg BEFORE UPDATE ON Rich
-> FOR EACH ROW
-> BEGIN
-> IF OLD.my_char != NEW.my_char THEN
-> INSERT INTO Rich2 VALUES ('not the same');
-> ELSE
-> INSERT INTO Rich2 VALUES ('same');
-> END IF;
-> END//
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> UPDATE Rich SET my_char = 'house2' WHERE my_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Rich SET my_id = 3 WHERE my_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM Rich2;
+--------------+
| logdata |
+--------------+
| not the same |
| same |
+--------------+
2 rows in set (0.00 sec)
Unless I've got something mixed up, by your argument, the second logged trigger insert would have said "Not the same", since you say that NULL != value. But, as far as I can see, MySQL does indeed return that the OLD.my_char = NEW.my_char when the my_char field is not updated.
Looking forward to seeing how you tested this... Please let me know if you can reproduce your scenario, as it would indeed be a bug.
Thanks!
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com