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

LEFT JOIN and JOIN behaves differently since upgrade to 10.4

Details

    Description

      I had to upgrade to 10.4 yesterday from 10.1. I went through each major version and ran mysql_upgrade after each.

      Today I noticed strange problems on our selects:

      This select returns 1 result:

      SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published AND pr_publ_index.site_id = '83' WHERE prod.cod_varianta=6922 
      

      This select returns 0 results:

      SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published AND pr_publ_index.site_id = '83'
      

      The same selct on the same database structure on version 10.2.25 returns a a whole lot of results.

      I noticed similar problems on LEFT JOINS, even if there is data to be joined, it is NOT joined and the result set is empty, unless there is a condition on the main table on the left side.
      The left side select should not affect the joined result.

      I am not sure which version is related to (10.3 or 10.4) nor where to start to debug.

      I tried setting optimizer switch for 10.1 defaults but no change:
      https://mariadb.com/kb/en/library/optimizer-switch/

      Attachments

        Activity

          freedomizer Andras added a comment -

          I noticed the following:
          this result now returns 511 rows:

          SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published=1 AND pr_publ_index.site_id = '83' 
          

          seems to me the is_published is not evaluated to true, even if it contains 1
          is_published was defined as boolean:

          CREATE TABLE `product_site_publication_index_790` (
          `product_id` int(11) NOT NULL,
          `site_id` int(11) NOT NULL,
          `is_promo` tinyint(1) NOT NULL DEFAULT 0,
          `is_published` tinyint(1) NOT NULL DEFAULT 1,
          KEY `is_published` (`is_published`),
          KEY `product_id` (`product_id`),
          KEY `site_id` (`site_id`),
          KEY `is_promo` (`is_promo`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1

          freedomizer Andras added a comment - I noticed the following: this result now returns 511 rows: SELECT prod.* FROM product_groups_790 AS prod JOIN product_site_publication_index_790 AS pr_publ_index ON pr_publ_index.product_id=prod.cod_varianta AND pr_publ_index.is_published= 1 AND pr_publ_index.site_id = '83' seems to me the is_published is not evaluated to true, even if it contains 1 is_published was defined as boolean: CREATE TABLE `product_site_publication_index_790` ( `product_id` int(11) NOT NULL, `site_id` int(11) NOT NULL, `is_promo` tinyint(1) NOT NULL DEFAULT 0, `is_published` tinyint(1) NOT NULL DEFAULT 1, KEY `is_published` (`is_published`), KEY `product_id` (`product_id`), KEY `site_id` (`site_id`), KEY `is_promo` (`is_promo`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          freedomizer Andras added a comment -

          I also tried removing all indexes on the joined table and the results change. WHY would having or not having indexes affect the result, it should only affect the speed.

          freedomizer Andras added a comment - I also tried removing all indexes on the joined table and the results change. WHY would having or not having indexes affect the result, it should only affect the speed.
          freedomizer Andras added a comment -

          this seems to be related to https://jira.mariadb.org/browse/MDEV-19911
          after setting optimizer switch rowid_filter=off, the queries seem to return identical results.

          I will be monitoring and be back with an update

          If someone is looking into this, can you please check:

          • why adding/removing indexes would affect joins
          • why left join would react as a right join
          • why join would return no data, unless there is a condition on the left table's primary key
          freedomizer Andras added a comment - this seems to be related to https://jira.mariadb.org/browse/MDEV-19911 after setting optimizer switch rowid_filter=off, the queries seem to return identical results. I will be monitoring and be back with an update If someone is looking into this, can you please check: why adding/removing indexes would affect joins why left join would react as a right join why join would return no data, unless there is a condition on the left table's primary key
          igor Igor Babaev added a comment -

          Hi Andras,
          With no test case I won't be able to check whether you problem has been already solved.

          igor Igor Babaev added a comment - Hi Andras, With no test case I won't be able to check whether you problem has been already solved.
          freedomizer Andras added a comment -

          it is hard to create a test case now as this is on a production server with hundreds of databases.
          if 10.4.7 comes out, I will try disabling the rowid_filter and test
          if it still presents itself I will create a sample database to simulate
          but in principle:
          table A with 3 columns id(primary),name(tinytext),status_id(index)
          table B with 3 columns status_id(index),status_text(tinytext),active(tinyint(1)) (1 or 0)
          SELECT A.* FROM A
          JOIN B ON B.status_id=A.status_id AND B.active
          this gave a different result than
          SELECT A.* FROM A
          JOIN B ON B.status_id=A.status_id AND B.active
          WHERE A.id=1
          also different result if B had no indexes
          using innodb, uf_unicode_8bit for text fields, 10.4.6 mariadb

          freedomizer Andras added a comment - it is hard to create a test case now as this is on a production server with hundreds of databases. if 10.4.7 comes out, I will try disabling the rowid_filter and test if it still presents itself I will create a sample database to simulate but in principle: table A with 3 columns id(primary),name(tinytext),status_id(index) table B with 3 columns status_id(index),status_text(tinytext),active(tinyint(1)) (1 or 0) SELECT A.* FROM A JOIN B ON B.status_id=A.status_id AND B.active this gave a different result than SELECT A.* FROM A JOIN B ON B.status_id=A.status_id AND B.active WHERE A.id=1 also different result if B had no indexes using innodb, uf_unicode_8bit for text fields, 10.4.6 mariadb

          People

            igor Igor Babaev
            freedomizer Andras
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.