Duplicates on Table format view using pagination and sorting on a date field that has duplicate values for multiple rows

Hello, I had the same issue on an unformatted view, I don't think it is specific to the table format. Some entities showed up twice on page 1 and page 2 sort criteria are the same. I fixed it by adding sort on entity ID.

I also encounter this issue. My case is:

There are two different nodes (Node1 and Node2) which has a custom field 'Event date' of type Date range;
Thees nodes has the same value for 'Event date'; like

"field_event_date" => array:1 [▼
      "x-default" => array:1 [▼
        0 => array:2 [▼
          "value" => "2024-08-30T09:00:00"
          "end_value" => "2024-08-30T12:00:00"
        ]
      ]

The view has sorting by two criteria: 1) Content: Event date:end_value (asc); 2) Content: Event date (asc);
The view has a pagination;
There are some other nodes on the results: some of them before Node1 and Node2, some of them after;
When Node1 is the last node on the view page, then on the next page the same node is the first. Node2 is never shown;

This could be solved by adding an adding sort by entity ID

I have just run into this exact issue and it was driving me crazy. I thought it was the way I was rendering the View programmatically, but turns out the culprit was in the View itself.

I had the same symptoms as outlined in this issue, that when using pagination, some items were missing and some items were duplicated.

I had noticed that the content that was being queried in the View had 10 our of 15 of them had the exact same "Created/authored on" date timestamps. These same timestamps were throwing off the sort that was set, which was to sort them by the "Created/authored on" field.

The quick solution for us was to add a secondary sort to the View (Title ASC), so that if any of the items (10 in our case) has the same values, that the group of those items would then be sorted using the Title. This solved our issue.

The other option, that we didn't go down, was to manually or programmatically update these nodes to adjust their "Created/authored on" date timestamps so the sort and pagination could work as expected.

Just in case this helps anyone else in a similar situation.

Updating title to make clear this is a pager isssue.
Updating summary with comments info and own testing experience (a normal textfield can also be used to replicate the problem)

Hey i just ran into similar issues, I had content type with an unlimited date range field,

first i added a filter to only show dates great than or equal to "+0 day", was showing duplicates, turned on aggregation it removed them.

then when i added a sort by date asc, it wuld show the duplicates again, but then i set the aggregation type to minimum on the date sort and it finally all worked how i wanted with 0 duplicates

I think this snippet, from \Drupal\views\ViewExecutable::build is where the issue gets introduced:

    // Build our sort criteria if we were instructed to do so.
    if (!empty($this->build_sort)) {
      // Allow the style handler to deal with sorting.
      if ($this->style_plugin->buildSort()) {
        $this->_build('sort');
      }
      // Allow the plugin to build second sorts as well.
      $this->style_plugin->buildSortPost();
    }

My understanding, which is limited, is that when a table style plugin has the "Override normal sorting if click sorting is used" option enabled, the $this->style_plugin->buildSort() conditional returns false because the table plugin is telling ViewExecutable to NOT add normal sorting handlers to the query statement. It should be noted that if you set one of the table columns as the default sort, then click sorting is considered enabled.

The override option can make sorting unreliable if the field that you are click sorting by is not specific enough. Once sorting is unreliable, then pagination becomes a problem because a row that was potentially on the first page could not be on the second page if your click sorted column has the same values across page 1 and page 2. In our case, we were using the entity's 'changed' field as the default sort in the table settings. There were over 2 pages of rows that had the same value for the changed field and pagination would sometimes cause the same rows to appear in multiple pages and some rows were never displayed at all. The order of the rows was essentially random within those pages.

With the override option enabled, the ONLY field that is used for sorting is the 'changed' field. In my opinion, it would be better to allow additional fields, from view's normal sort, added after the click sort field. A simple example would be to add the entity ID or the entity revision ID so that rows with the same click-sorted value can have an additional sort level with more precision.

I don't have a suggestion for how to change/improve \Drupal\views\ViewExecutable::build or \Drupal\views\Plugin\views\style\Table::buildSortPost and there will also be backwards compatibility issues to consider.

What I am experimenting with is something simple like:

/**
 * Implements hook_views_query_alter().
 *
 * Click sorting, which may have a default sort option, will completely replace
 * regular sorting in the view configuration. This hook adds the regular
 * sorting options after the click-sorted field has been set.
 */
function my_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query): void {

  // If the table style plugin has blocked regular sorting, then add the regular sorting
  // options back in after the click-sorted field has been set.
  if ($view->style_plugin->getPluginId() === 'table' && !$view->style_plugin->buildSort()) {
    $view->_build('sort');
  }
}