Affected Rows in MySQL

[luisbelloch]

Hi! The dataset method update returns the number of matched rows in MySQL, but in the documentation says that the returned value is the number of rows updated instead.

The message MySQL returns separates between matched rows and changed ones, I was wondering if it would be more correct to return the changed ones instead:

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

This comes from here, I can submit a PR if needed: https://github.com/jeremyevans/sequel/blob/1be7f5e0935b0d5c0fc1eb7b6a6c92d359d10d33/lib/sequel/adapters/mysql.rb#L235-L236

A complete sample:

DB.create_table?(:foo) do
  primary_key :id
  String :some_value, null: true
end

#1533 DB[:foo].insert(id: 1)
p DB[:foo].where(id: 1).update(some_value: 'bar') # returns 1
p DB[:foo].where(id: 1).update(some_value: 'bar') # returns 1
p DB[:foo].where(id: 0).update(some_value: 'bar') # returns 0

When running this in plain SQL:

mysql> UPDATE `foo` SET `some_value` = 'bar' WHERE (`id` = 1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `foo` SET `some_value` = 'bar' WHERE (`id` = 1);
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE `foo` SET `some_value` = 'bar' WHERE (`id` = 2);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

I didn't try other databases to check the behaviour. I guess this change wouldn't be backwards compatible anyway, maybe we can clarify the documentation or find a way to return the number of changed rows.

Thanks!

[jeremyevans]

This behavior is by design, for consistent behavior across databases. The fact that MySQL will skip the update if you use the same values is an optimization on MySQL's part.

If you think this should be clarified in the documentation, please submit a documentation patch. However, be aware that all other databases Sequel supports do not differentiate between matched and changed rows.