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

LEFT JOIN and JOIN behaves differently since upgrade to 10.4

    XMLWordPrintable

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

          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.