| Subject: | ResultSet update_all updated rows not reflected in ResultSet, can "lose" rows |
ResultSet update_all() fetches each row object and updates it. However, the row in the
ResultSet is not updated. If one of the updated fields happens to be used in the where clause
to create the ResultSet then attempt to read from result set either returns empty data (if no
more matching rows) or different rows. In short, calling update_all causes you to lose the rows
in the result set.
The attached file shows a test program that illustrates this, including the SQL executed and
the output generated. The first test uses update_all and loses all the rows. The second test
does the same thing manually and shows the rows getting updated, but the result set loses
them after the update.
The update() documentation kind of implies the contents of the result set are not updated. It
also "loses" the rows if one of the fields in where is changed, however it implies using
update_all will update the rows objects.
Not sure if it's a bug in code or bug (or ambiguity) in the docs.
| Subject: | dbix-bug.pl |
#!/usr/bin/perl
use strict;
use warnings;
use DBIx::Class;
use DBIx::Class::Schema::Loader;
use Bugtest::Schema;
$|++;
DBIx::Class::Schema::Loader->naming('v7');
my $schema = Bugtest::Schema->connect(
'dbi:mysql:bugtest',
'bugtest',
'passw0rd123$',
{
RaiseError => 1,
PrintError => 1,
},
);
print "Test #1\n";
$resultset = $schema->resultset('BugTest')->search(
{
'field2' => 'test',
'field6' => undef,
'field5' => 2
},
{
'for' => 'update',
'order_by' => {
'-asc' => 'bug_test_id'
}
}
);
$update = $resultset->update_all({
field5 => 3
});
# 506 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( ( field2 = 'test' AND field6 IS NULL AND field5 = '2' ) ) ORDER BY bug_test_id ASC FOR UPDATE
# 506 Query UPDATE bug_test SET field5 = '3' WHERE ( bug_test_id = '1' )
# 506 Query UPDATE bug_test SET field5 = '3' WHERE ( bug_test_id = '2' )
# 506 Query UPDATE bug_test SET field5 = '3' WHERE ( bug_test_id = '3' )
# doesn't make a difference if this is commented or not
#$resultset->reset();
while (my $row = $resultset->next) {
printf("Id: %s; Field5: %s\n", $row->bug_test_id, $row->field5);
}
# 506 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( ( field2 = 'test' AND field6 IS NULL AND field5 = '2' ) ) ORDER BY bug_test_id ASC FOR UPDATE
print "reset data\n";
sleep 15; # allow me time to reset the data
print "Test #2\n";
$resultset = $schema->resultset('BugTest')->search(
{
'field2' => 'test',
'field6' => undef,
'field5' => 2
},
{
'for' => 'update',
'order_by' => {
'-asc' => 'bug_test_id'
}
}
);
while (my $row = $resultset->next) {
printf("Id: %s; Field5: %s\n", $row->bug_test_id, $row->field5);
my $ok = $row->update({ field5 => 3 });
printf("Id: %s; Field5: %s; OK: %s\n", $row->bug_test_id, $row->field5, $ok);
print "Refreshing\n";
$row->discard_changes();
printf("Id: %s; Field5: %s\n", $row->bug_test_id, $row->field5);
}
#110722 19:41:16 508 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( ( field2 = 'test' AND field6 IS NULL AND field5 = '2' ) ) ORDER BY bug_test_id ASC FOR UPDATE
# 508 Query UPDATE bug_test SET field5 = '3' WHERE ( bug_test_id = '1' )
# 508 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( me.bug_test_id = '1' )
# 508 Query UPDATE bug_test SET field5 = '3' WHERE ( bug_test_id = '2' )
# 508 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( me.bug_test_id = '2' )
# 508 Query UPDATE bug_test SET field5 = '3' WHERE ( bug_test_id = '3' )
# 508 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( me.bug_test_id = '3' )
$resultset->reset();
while (my $row = $resultset->next) {
printf("Id: %s; Field5: %s\n", $row->bug_test_id, $row->field5);
}
# 508 Query SELECT me.bug_test_id, me.field2, me.field3, me.field4, me.field5, me.field6 FROM bug_test me WHERE ( ( field2 = 'test' AND field6 IS NULL AND field5 = '2' ) ) ORDER BY bug_test_id ASC FOR UPDATE
#### output
#Test #1
#reset data
#Test #2
#Id: 1; Field5: 2
#Id: 1; Field5: 3; OK: Bugtest::Schema::Result::BugTest=HASH(0x10207e0d0)
#Refreshing
#Id: 1; Field5: 3
#Id: 2; Field5: 2
#Id: 2; Field5: 3; OK: Bugtest::Schema::Result::BugTest=HASH(0x10207f2c0)
#Refreshing
#Id: 2; Field5: 3
#Id: 3; Field5: 2
#Id: 3; Field5: 3; OK: Bugtest::Schema::Result::BugTest=HASH(0x10207b178)
#Refreshing
#Id: 3; Field5: 3