When is an INSERT DETERMINISTIC?
This is a question that has long confused me, and from various searches I don't seem to be alone. It appears to stem from a total lack of good examples that I can find in the MySQL documentation, which seems pretty deficient when it comes to a lot of Stored Procedure details.
In my particular case I'm wondering when an INSERT query can be considered DETERMINISTIC; if we assume that a procedure provides all data necessary to fill out an entire row, does that constitute a DETERMINISTIC operation?
For example, consider the following table:
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT '',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
With the following accompanying procedure:
DELIMITER ;;
CREATE PROCEDURE `insert_user`(a_id INT, a_name TEXT)
BEGIN
INSERT INTO `users` (`id`, `name`)
VALUES (a_id)
ON DUPLICATE KEY UPDATE
`name` = IFNULL(a_name, `name`),
`time` = NOW();
END;;
DELIMITER ;
Given that the stated requirement for a procedure to be DETERMINISTIC is that it produce the same result (or be replicatable) for the same inputs, is this procedure DETERMINISTIC? For the same `id` and `name` the result will always be a row with `id` and `name` set accordingly, with its `time` updated, is that sufficient?
If this INSERT is DETERMINISTIC, when would it cease to be? I'm assuming an INSERT/SELECT statement would not be, since its result is dependent upon the data that it obtains, but what other features of INSERT would cause it to become NON DETERMINISTIC.
If the above statement is NOT DETERMINISTIC, given that it is nonetheless very simple in its operation, what would be the downsides of declaring it DETERMINISTIC anyway? e.g- will MySQL skip the NOW() condition, or will it execute as expected? I've tried testing it and it seems to work as I expect, but perhaps it would not under replication?
In fact, in general, are there are any really good guides out there with examples explaining what exactly is and is not DETERMINISTIC? I find stored procedures a great way to model many of my databases, but I all too often just omit this condition, along with most others, because the documentation is, to put it politely, rather light on detail.
As an aside, I really wish stored procedures would see more development; it seems like detecting many breaches of the [NOT] DETERMINISTIC condition would be possible, and as far as I'm aware stored procedure caches are still per connection, though I use persistent connections so this isn't really a problem.
As another aside; is there any markup on this forum? I tried a few commons ones but none seemed to work.