Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25647

Strange optimizer behaviour on optional conditions with functions

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.9
    • None
    • Optimizer
    • None

    Description

      Running the following query in Maria 10.5 produces a table scan on table "h":

      explain SELECT *
      FROM
      rdMPGGeraet g JOIN
      rdMPGBezeichnung b ON g.BezeichnungID=b.ID JOIN
      rdMPGAdresse h ON (b.Hersteller=h.ID )  LEFT JOIN
      rdFunkruf rf ON rf.ID=(IF(g.Funkruf=0,g.StammFunkrufID,g.Funkruf) ) AND rf.OrganisationID IN(42) LEFT JOIN
      rdFahrzeug f ON (f.ID=COALESCE(rf.Fahrzeug,rf.Stammfahrzeug,g.FesteinbauFID) AND f.OrganisationID IN(42)) LEFT JOIN
      rdWache w ON (w.ID=COALESCE(rf.Wache,f.Wache,g.werkstatt) AND w.OrganisationID IN(42)) 
       
      WHERE
      g.OrganisationID IN(42) AND
      g.Status="in Betrieb" AND
      ISNULL(g.del) 
      

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	g	ref	einweisungPfl,BezeichnungID,ClusterID,Status,del	Status	7	const,const,const	1157	Using index condition
      1	SIMPLE	rf	eq_ref	PRIMARY,Wache	PRIMARY	2	func	1	Using where
      1	SIMPLE	f	eq_ref	PRIMARY,Status	PRIMARY	2	func	1	Using where
      1	SIMPLE	w	eq_ref	PRIMARY,Ressource,Shop,Strassensperrung	PRIMARY	2	func	1	Using where
      1	SIMPLE	h	ALL	PRIMARY	NULL	NULL	NULL	5180	Using join buffer (flat, BNL join)
      1	SIMPLE	b	ref	PRIMARY,Hersteller	Hersteller	3	qmsystems.h.ID	2	Using where
      

      Same query in Maria 10.3.27

      +------+-------------+-------+--------+--------------------------------------------------+---------+---------+---------------------------+------+-----------------------+
      | id   | select_type | table | type   | possible_keys                                    | key     | key_len | ref                       | rows | Extra                 |
      +------+-------------+-------+--------+--------------------------------------------------+---------+---------+---------------------------+------+-----------------------+
      |    1 | SIMPLE      | g     | ref    | einweisungPfl,BezeichnungID,ClusterID,Status,del | Status  | 7       | const,const,const         | 1157 | Using index condition |
      |    1 | SIMPLE      | rf    | eq_ref | PRIMARY,Wache                                    | PRIMARY | 2       | func                      |    1 | Using where           |
      |    1 | SIMPLE      | f     | eq_ref | PRIMARY,Status                                   | PRIMARY | 2       | func                      |    1 | Using where           |
      |    1 | SIMPLE      | w     | eq_ref | PRIMARY,Ressource,Shop,Strassensperrung          | PRIMARY | 2       | func                      |    1 | Using where           |
      |    1 | SIMPLE      | b     | eq_ref | PRIMARY,Hersteller                               | PRIMARY | 3       | qmsystems.g.BezeichnungID |    1 |                       |
      |    1 | SIMPLE      | h     | eq_ref | PRIMARY                                          | PRIMARY | 3       | qmsystems.b.Hersteller    |    1 |                       |
      +------+-------------+-------+--------+--------------------------------------------------+---------+---------+---------------------------+------+-----------------------+
      

      removing AND *.OrganisationID IN(42) from joins gives the same result as MariaDB 10.3.
      All columns have an index and *.OrganisationID IN(42) is added for security.

      I have no clue how to reduce this to a smaller example. At least not using h primary key and doing a table scan is total nonsense.

      Attachments

        Activity

          People

            Unassigned Unassigned
            mokraemer Marc
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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