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

Semijoin inflates number of rows in query result

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

          Ascending order - Click to sort in descending order
          mefju Mateusz Michalowski created issue -
          mefju Mateusz Michalowski made changes -
          Field Original Value New Value
          Description Turning semijoin optimization on/off can affect number of rows returned by a query.

          Enabling semojoin optimisation can cause a query like that:
          {code:sql}
          SELECT * FROM manufacturers WHERE (...);
          {code}
          to return more rows than:
          {code:sql}
          SELECT * FROM manufacturers;
          {code}

          Please, find a full use case setup attached.

          Just to sum up the test case:
          It executes the query below: (manufacturers table size: 2)
          {code:sql}
          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
          );
          {code}

          Then the query returns:
          - 2 rows (SET optimizer_switch='semijoin=off';)
          - 3 rows (SET optimizer_switch='semijoin=on';)
          		 Turning semijoin optimization on/off can affect number of rows returned by a query.

          Enabling semojoin optimisation can cause a query like that:
          {code:sql}
          SELECT * FROM manufacturers WHERE (...);
          {code}
          to return more rows than:
          {code:sql}
          SELECT * FROM manufacturers;
          {code}

          Please, find a full use case setup attached.

          Just to sum up the test case:
          It executes the query below: (manufacturers table size: 2)
          {code:sql}
          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
          );
          {code}

          Then the query returns:
          - 2 rows ( SET optimizer_switch='semijoin=off'; )
          - 3 rows ( SET optimizer_switch='semijoin=on'; )
          mefju Mateusz Michalowski made changes -
          Description Turning semijoin optimization on/off can affect number of rows returned by a query.

          Enabling semojoin optimisation can cause a query like that:
          {code:sql}
          SELECT * FROM manufacturers WHERE (...);
          {code}
          to return more rows than:
          {code:sql}
          SELECT * FROM manufacturers;
          {code}

          Please, find a full use case setup attached.

          Just to sum up the test case:
          It executes the query below: (manufacturers table size: 2)
          {code:sql}
          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
          );
          {code}

          Then the query returns:
          - 2 rows ( SET optimizer_switch='semijoin=off'; )
          - 3 rows ( SET optimizer_switch='semijoin=on'; )
          		 Turning semijoin optimization on/off can affect number of rows returned by a query.

          Enabling semojoin optimisation can cause a query like that:
          {code:sql}
          SELECT * FROM manufacturers WHERE (...);
          {code}
          to return more rows than:
          {code:sql}
          SELECT * FROM manufacturers;
          {code}

          Please, find a full test case setup attached.

          Just to sum up the test case:
          It executes the query below: (manufacturers table size: 2)
          {code:sql}
          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
          );
          {code}

          Then the query returns:
          - 2 rows ( SET optimizer_switch='semijoin=off'; )
          - 3 rows ( SET optimizer_switch='semijoin=on'; )
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 5.5 [ 15800 ]
          Affects Version/s 5.3.12 [ 12000 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 5.5 [ 15800 ]
          Affects Version/s 10.0.18 [ 18702 ]
          Assignee Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia made changes -
          Sprint 5.5.45 [ 9 ]
          serg Sergei Golubchik made changes -
          Sprint 5.5.45 [ 9 ] 5.5.45, 10.1.7-1 [ 9, 10 ]
          serg Sergei Golubchik made changes -
          Rank Ranked higher
          elenst Elena Stepanova made changes -
          Assignee Sergei Petrunia [ psergey ] Elena Stepanova [ elenst ]
          elenst Elena Stepanova made changes -
          Assignee Elena Stepanova [ elenst ] Sergei Petrunia [ psergey ]
          serg Sergei Golubchik made changes -
          Rank Ranked higher
          psergei Sergei Petrunia made changes -
          Fix Version/s 10.0.22 [ 19700 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Confirmed [ 10101 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 69873 ] MariaDB v4 [ 149252 ]

          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.