[MDEV-8359] WHERE condition referring to inner table of left join can be sargable - Jira

Some ERP generate that type of queries

explain

select count(0) AS `COUNT(*)`

from

   E_relance

   left join E_action on ((E_action.id_demande = E_relance.id_demande))

where

  ((E_relance.id_demande = 88224) or (E_action.id_demande = 88224))

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: E_relance

         type: index

possible_keys: fk_E_relance_E_demande1

          key: fk_E_relance_E_demande1

      key_len: 5

          ref: NULL

         rows: 205655

        Extra: Using index

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: E_action

         type: ref

possible_keys: FI__4381

          key: FI__4381

      key_len: 4

          ref: siam2.E_relance.id_demande

         rows: 2

        Extra: Using where; Using index

The full index scan on primary table is not necessary if const on joined table is not NULL.

Rewriting the query is hard to be done in the application because it may happen that the application is doing a lookup for NULL on left joined table

indeed query rewriting change the lookup to const or range in case the second condition is not null and propagated to the upper table

MAD_WEB_DEV (madweb@localhost) [siam2]>

explain select count(0) AS `COUNT(*)`

from

  E_relance left join E_action on ((E_action.id_demande = E_relance.id_demande))

where

  ((E_relance.id_demande = 88224) or (E_relance.id_demande = 88224))\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: E_relance

         type: ref

possible_keys: fk_E_relance_E_demande1

          key: fk_E_relance_E_demande1

      key_len: 5

          ref: const

         rows: 1

        Extra: Using index

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: E_action

         type: ref

possible_keys: FI__4381

          key: FI__4381

      key_len: 4

          ref: siam2.E_relance.id_demande

         rows: 2

        Extra: Using where; Using index

relates to

MDEV-10946 Partition pruning not working with LEFT JOIN

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server