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

LP:795530 - Wrong result with subquery semijoin materialization and outer join

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12
    • None
    • None

    Description

      The following query over the 'world' database incorrectly produces wrong result
      of 1 row instead of an empty result:

      create database world;
      use world;
      source mysql-test/include/world_schema.inc
      source mysql-test/include/world.inc

      set @@optimizer_switch='materialization=on,semijoin=on';

      MariaDB [world]> select * from CountryLanguage where (Language, Country) IN (SELECT City.Name, Country.Code FROM City LEFT JOIN Country ON (Country = Code and Code > 'LLL'));
      ---------------------------

      Country Language Percentage

      ---------------------------

      KEN Meru 5.5

      ---------------------------
      1 row in set (0.22 sec)

      set @@optimizer_switch='materialization=on,semijoin=off';
      => empty result;

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            timour Timour Katchaounov (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.