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

Semijoin inflates number of rows in query result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.0.22
    • Optimizer
    • None
    • mysql Ver 15.1 Distrib 10.0.18-MariaDB, for osx10.10 (x86_64) using readline 5.1
      MariaDB installed with homebrew
    • 5.5.45, 10.1.7-1

    Description

      Turning semijoin optimization on/off can affect number of rows returned by a query.

      Enabling semojoin optimisation can cause a query like that:

      SELECT * FROM manufacturers WHERE (...);

      to return more rows than:

      SELECT * FROM manufacturers;

      Please, find a full test case setup attached.

      Just to sum up the test case:
      It executes the query below: (manufacturers table size: 2)

      SELECT * FROM manufacturers WHERE manufacturers.id IN (             
        SELECT ships.manufacturers_id FROM owners JOIN ships ON owners.id = ships.owners_id WHERE owners.id = 1
      );

      Then the query returns:

      • 2 rows ( SET optimizer_switch='semijoin=off'; )
      • 3 rows ( SET optimizer_switch='semijoin=on'; )

      Attachments

        1. semijoin.result
          1 kB
          Mateusz Michalowski
        2. semijoin.test
          1 kB
          Mateusz Michalowski

        Activity

          People

            psergei Sergei Petrunia
            mefju Mateusz Michalowski
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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