SQL rewriting by _node_query_node_access_alter is broken in MariaDB 11.x and higher

[herbdool]

Description of the bug

Some of the exploration is in #Backdrop > Views' "Is none of" not playing nice with SQL rewriting though note that I discovered the problem is a bit more general.

Steps To Reproduce

Warning: I was experiencing this on a client site, but when I try to recreate this bug on a fresh site, I cannot. I can create a functionally identical query, but it shows all the items. (Perhaps the issue is needing a few more records? Is it that particular?) At any rate, I'll post the steps and query I've got.

To reproduce the behavior:

• Create a content type: Team
• Create a Paragraph type: Person (Presumably the bug would also be present if linking two content types via entity reference)
• On Team add a Person field of paragraph field type: First, Last, Date Added custom fields.
• Create a Taxonomy vocab: "Access" and some terms: "Editor"
• On Team add a term reference field for that vocab.
• Create a View with a block starting with the Team content type, contextual filter: Content ID of Team node, Relationship: Person field. Add fields for First, Last, Date Added (via relationships) and sort by Date Added.
• Add the block to display on the layout to appear for Team nodes.
• Enable some content access modules (I used Content View Access, Taxonomy Access).
• Permit a role (e.g. editor) to have view access for Team in Content View Access, and set up Taxonomy Access so editor role can view content with the "Editor" term and authenticated cannot.
• Create a Team node and add a bunch of persons.
• As a test user with the editor role, view the Team node.
• Additional testing: enable Devel, configure to display queries and give editor role permission to view Devel info.

SELECT
  `paragraphs_item_field_data_field_person`.`item_id` AS `paragraphs_item_field_data_field_person_item_id`,
  `paragraphs_item_field_data_field_person__field_data_field_date`.`field_date_value` AS `paragraphs_item_field_data_field_person__field_data_field_da`,
  'paragraphs_item' AS `field_data_field_first_name_paragraphs_item_entity_type`,
  'paragraphs_item' AS `field_data_field_last_name_paragraphs_item_entity_type`,
  'paragraphs_item' AS `field_data_field_date_paragraphs_item_entity_type`
FROM
  `node` `node`
  LEFT JOIN `field_data_field_person` `field_data_field_person` ON node.nid = field_data_field_person.entity_id
  AND (
    field_data_field_person.entity_type = 'node'
    AND field_data_field_person.deleted = '0'
  )
  LEFT JOIN `paragraphs_item` `paragraphs_item_field_data_field_person` ON field_data_field_person.field_person_value = paragraphs_item_field_data_field_person.item_id
  LEFT JOIN `field_data_field_date` `paragraphs_item_field_data_field_person__field_data_field_date` ON paragraphs_item_field_data_field_person.item_id = paragraphs_item_field_data_field_person__field_data_field_date.entity_id
  AND (
    paragraphs_item_field_data_field_person__field_data_field_date.entity_type = 'paragraphs_item'
    AND paragraphs_item_field_data_field_person__field_data_field_date.deleted = '0'
  )
WHERE
  (
    ((node.nid = '223'))
    AND(
      (
        (`node`.`status` = '1')
        AND (`node`.`type` IN ('team'))
      )
    )
  )
  AND (
    EXISTS (
      SELECT
        `na`.`nid` AS `nid`
      FROM
        `node_access` `na`
      WHERE
        (
          (
            (`na`.`gid` IN ('0'))
            AND (`na`.`realm` = 'all')
          )
          OR(
            (`na`.`gid` IN ('88'))
            AND (`na`.`realm` = 'cva_view_any_item')
          )
          OR(
            (`na`.`gid` IN ('88'))
            AND (`na`.`realm` = 'cva_view_any_unpublished_item')
          )
          OR(
            (`na`.`gid` IN ('88'))
            AND (`na`.`realm` = 'cva_view_any_page')
          )
          OR(
            (`na`.`gid` IN ('88'))
            AND (`na`.`realm` = 'cva_view_any_unpublished_page')
          )
          OR(
            (`na`.`gid` IN ('80'))
            AND (`na`.`realm` = 'cva_view_own_team')
          )
          OR(
            (`na`.`gid` IN ('80'))
            AND (`na`.`realm` = 'cva_view_own_unpublished_team')
          )
          OR(
            (`na`.`gid` IN ('1', '2'))
            AND (`na`.`realm` = 'taxonomy_access_role')
          )
        )
        AND (`na`.`grant_view` >= '1')
        AND (node.nid = na.nid)
    )
  )
ORDER BY
  paragraphs_item_field_data_field_person__field_data_field_da DESC
LIMIT
  5 OFFSET 0

Actual behavior

Only shows one item.

Expected behavior

Should show multiple items.

Additional information

Add any other information that could help, such as:

• Backdrop CMS version:
• PHP version: 8.3
• Database sever (MySQL or MariaDB?) and its version: MariaDB 11.x and up

[herbdool]

Note that I created a PR, but until this can be replicated consistently it might not be worth the effort.

I attempted to make the PR limited and universal for MySQL/MariaDB regardless of version. It puts the subquery which fetches node_access into a "derived table" and then it has a "wrapper" select that is true if the derived table is true. Sort of like this:

WHERE ( EXISTS (
  SELECT 1 AS `expression` FROM (SELECT DISTINCT `na`.`nid` AS `nid` FROM `node_access` `na` WHERE ...) node_access_grants
  WHERE (node.nid = node_access_grants.nid)
))

Both nesting and putting a DISTINCT on the derived table appear to do the trick. Now if I can just confirm that it will fail consistently without this change.

[indigoxela]

... Both nesting and putting a DISTINCT on the derived table appear to do the trick

It's that "distinct", that causes some concerns. I suspect, this might have side effects elsewhere.
But I didn't try to follow the STR, yet.

So, it's the change to the Optimizer in MariaDB 11.x, that causes the different result?

[herbdool]

@indigoxela I'm speculating that it's the optimizer. But definitely seems to be a bug with some versions.

The DISTINCT appeared necessary for it to work so I hope it doesn't cause side effects.

[indigoxela]

@herbdool a quick idea, as it seems tricky to find a way to reproduce in core:

Temporarily switch the mariadb version in our simpletest workflow to 11.8 or 12.3 to see if some existing tests fail on it.

See #7189 for more info.

[herbdool]

@indigoxela thanks for the idea. Not sure when I'll be able to get back to this. I spent a lot of time on it thus far and may need to just rely on the workaround unless it crops up again.