>>>>> "Hugo" == Hugo van der Sanden <hugo@stripped>
Hugo> In <14272.14293.779425.635005@stripped>, Michael Widenius writes:
Hugo> Were that the case, I would have expected the same to happen in my
Hugo> test table with one record. However, it is not how I understand the SQL
Hugo> should work: regardless of implementation, I would expect the effect to
Hugo> be the same as if the where clause were first applied to find the set
Hugo> of matching records, and then the update clause applied (once!) to each
Hugo> member of the matching set.
Hugo> I suspect the fact that I am updating one field of a multi-field key is
Hugo> part of the reason for the problem.
Monty> Yes, this explain this case (This is fixed in 3.23.2)
Hugo> I've finally managed to get 3.23.2 installed here, and confirmed that
Hugo> the update works correctly with that version. Is there any prospect that
Hugo> the relevant patches will make it into a 3.22 release at some point in
Hugo> the near future? If not, I'd appreciate being pointed at the patches
Hugo> so I can attempt to retrofit them myself.
The problem is that there was a lot of things involved in this patch.
If we would put this in 3.22, then 3.22 have to be declared as 'alpha'
You can find 'most things' that changed by doing:
diff -c mysql-3.22.25/sql/sql_update.c mysql-3.23.2/sql/sql_update.c
Some calls have got more parameters (like make_select), so to get this
to work in 3.22 you have to change the call back to the old format and
check that everything still works.
Hugo> As well as the reluctance to touch software marked 'alpha', the person
Hugo> who installed 3.23.2 on a test machine here also thought to check the
Hugo> benchmarks comparing 3.22.25 and 3.23.2, and while most operations are
Hugo> as fast or faster, there are a couple of worrying anomalies:
Hugo> Benchmark DBD suite: 2.2
Hugo> Running tests on: SunOS 5.7 sun4u
Hugo> alpha: Server version: MySQL 3.23.2 alpha
Hugo> stable: Server version: MySQL 3.22.25
Hugo> alpha: All 8 test executed successfully
Hugo> stable: All 8 test executed successfully
Hugo> Tests with estimated time have a + at end of line
Hugo> Totals per operation:
Hugo> Operation seconds usr sys cpu tests
Hugo> alpha: select_key 91476 123.29 53.27 0.00 200000 +
Hugo> stable: select_key 300 138.33 56.57 0.00 200000
Hugo> alpha: select_range 1945 38.02 30.16 0.00 25410 +
Hugo> stable: select_range 509 36.60 24.98 0.00 25420
Hugo> alpha: select_range_prefix 9551 13.44 7.20 0.00 25000 +
Hugo> stable: select_range_prefix 60 13.01 6.58 0.00 25010
Hugo> alpha: TOTALS 111222 747.01 569.89 0.00 1511298 ++++
Hugo> stable: TOTALS 10908 781.22 489.97 0.00 1511318 +
I agree; There seems to be something wrong in the above. On the
other hand, I don't have this problems with my copy of 3.23.3.
I would suggest that you for a while remove the second part of the
index (message) until 3.23 reaches beta (hopefully within a couple of weeks).