Overcome nondeterministic behaviour in mysql ORDER BY

This is a MySQL/RDBMS problem, a SELECT query doesn't return results in the same order for the same query, if the query does not have a deterministic ORDER BY criterion (or more) defined.

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

(taken from https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html)

I have the feeling this could affect in the same way 7.x of VBO, will take a look at that eventually.

In the meantime, an idea that worked for me:

1. Capture an ViewsBulkOperationsEvent $event
2. Get the view from the event: $view = $event->getView()
3. Set a deterministic-sort handler on the view, this should be properly adjusted for the type of entities at hand:
   

   $view->setHandler($this->bulkFormData['display_id'], 'sort', 'nid', [
         "id" => "nid",
         "table" => "node_field_data",
         "field" => "nid",
         "relationship" => "none",
         "group_type" => "group",
         "admin_label" => '',
         "order" => "ASC",
         "exposed" => true,
         "expose" => [
           "label" => ''
         ],
         "entity_type" => "node",
         "entity_field" => "nid",
         "plugin_id" => "standard",      
       ]);

Overall, this is a nasty edge case that IMHO should be handled by VBO.

Nice find!

Yes, we can add a sort, but I think it's best to use the processed view's base field for that and maybe check if there are no sorts already defined?

See some code for reference in ViewsBulkOperationsActionProcessor::populateQueue():

    // Modify the view query: determine and apply the base field condition.
    $base_field = $this->view->storage->get('base_field');
    if (isset($this->view->query->fields[$base_field])) {
      $base_field_alias = $this->view->query->fields[$base_field]['table'] . '.' . $this->view->query->fields[$base_field]['alias'];
    }
    else {
      $base_field_alias = $base_field;
    }

Hi @mike, here is a first review of the patch after reading the code:

+      "table" => $base_table,

I am not sure if the 'table' option passed to the handler is always the same as the 'base_table' configuration property of the View. Can you shed some light here?

+      "entity_type" => $base_table,

I am not sure this is always gonna work, I assume it worked for you. For example the User entity uses as id 'user' and as base_table 'users'. You may be able to use ViewExecutable::getBaseEntityType to get that part reliably.

Thanks for the patch in any case!

Here's a patch that probably does the trick, has some of the previous issues resolved and actually removes all sort handlers. Whoever is experiencing the issue please test, I can only confirm it applies the sort handler to the view and it sorts the way it's supposed to.

The patch adds a single block of code in ViewsBulkOperationsActionProcessor::getPageList() so it can be applied also to the 2.x branch easily.

Also it seems that entity_type and entity_field parameters are not needed.

I can confirm this issue exist and the patch fixes it. Marking this as critical as this actually really breaks the entire point of this module. (if you use MySQL or equivalent, we are using mariadb)

Our case:
We are using views_bulk_operations in several projects on the user view to send mails in bulk and synchronise data to external API's in bulk through actions. Handling several thousands of users.

We had a complaint that the bulk mail action was sending duplicates, upon closer inspection some users also did not receive a mail.

So did some quick and easy debugging in our mail action and added this piece of code to quickly see what the issue was.

\Drupal::logger('waldo')->info($account->id());

See below queries and their results on a batch of 2.556 users, batch size = 50:

SELECT message FROM watchdog WHERE type = 'waldo' => 2.556 records
SELECT DISTINCT message FROM watchdog WHERE type = 'waldo' => 1.8855 records

After applying the patch:
SELECT message FROM watchdog WHERE type = 'waldo' => 2.556 records
SELECT DISTINCT message FROM watchdog WHERE type = 'waldo' => 2.556 records

Ok then. Committed.

These changes raise this issue when we are trying to execute some Action for all items.

Was hoping this fixed the "vbo does not process all items" which seems to still be around from D7 days. But i have vbo on the default Content admin page and it fails to process "Publish content item" on all items when all items are selected.

I am using 4.0.0