6

When attempting to delete a batch of records, only the odd rows are deleted!

val byUser = Orders.createFinderBy(_.userID)
byUser(id).mutate(_.delete)

If I instead print the record, I get the correct number of rows.

byUser(id).mutate{x => x.echo}

I worked around the issue like this, which generates the desired SQL.

(for{o <- Orders if o.userID is id.bind } yield o).delete

But, why or how does the mutate version affect only the odd rows?

Anthony Mastrean
  • 21,850
  • 21
  • 110
  • 188
virtualeyes
  • 11,147
  • 6
  • 56
  • 91
  • 5
    Although I don't know what's going on in this particular case, it's a very common issue with linked lists: when you try to delete while iterating, you delete one, then iterate one, then delete one, etc. leaving half your list remaining. Any iterator on a mutable collection can suffer from this problem, depending on how it's created. I don't know what alternate strategies are available to you here--with linked lists, you solve the problem by only deleting and let the deletions themselves iterate you through the entire list. – Rex Kerr Jun 27 '12 at 15:48
  • @RexKerr must be something similar to what you're describing. The alternative is to create a query that does not invoke MutatingUnitInvoker (the type of query generated by prepared statement expressions like createFinderBy and for expressions that bind via Parameters[T]) – virtualeyes Jun 27 '12 at 16:09
  • @RexKerr It looks like you've got the only answer, I would post it as such and get some up votes :) – Anthony Mastrean Jun 18 '13 at 14:13
  • @AnthonyMastrean - The question isn't properly answered, so I'd rather leave it in comments. (It's not like I'm short on reputation.) – Rex Kerr Jun 18 '13 at 16:24

1 Answers1

1

I've had a dig around in the source code and it seems to be as @RexKerr says - an iterator is used to process the elements, applying the deletions as it iterates (the while loop in the mutate method here):

https://github.com/rjmac/scala-query/blob/master/src/main/scala/org/scalaquery/MutatingInvoker.scala

Interestingly there is a previousAfterDelete flag that can be used to force the iterator backwards after each deletion. This appears to be set to true for Access databases (see the AccessQueryInvoker class) but not others:

https://github.com/rjmac/scala-query/blob/master/src/main/scala/org/scalaquery/ql/extended/AccessDriver.scala

I would recommend downloading the sources and debugging the code. Perhaps this flag should be set for the database vendor you are using. I'd also consider filing a bug report:

http://scalaquery.org/community.html

PS. I know this is an old question but answered it just in case anyone else has had this problem

paulyb
  • 168
  • 1
  • 7
  • The community has long since moved on to the next version of the library, Slick, but I still have a project on SQ, so thanks for the long awaited answer ;-) – virtualeyes Apr 30 '15 at 05:15