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

Semijoin inflates number of rows in query result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5, 10.0, 10.1
    • Fix Version/s: 10.0.22
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      mysql Ver 15.1 Distrib 10.0.18-MariaDB, for osx10.10 (x86_64) using readline 5.1
      MariaDB installed with homebrew
    • Sprint:
      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

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: