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

LP:641203 - Query returns rows where no result is expected (impossible WHERE) in maria-5.3-mwl89

    XMLWordPrintable

Details

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

    Description

      The following query:

      SELECT table1 .`col_varchar_nokey`
      FROM A table1 RIGHT JOIN C ON table1 .`col_int_nokey`
      WHERE ( 's' , 'm' ) IN (
      SELECT `col_varchar_nokey` , `col_varchar_key`
      FROM C ) ;

      returns rows even though there is no value 's' in table C

      Test case:

      SET LOCAL optimizer_switch='semijoin=off,materialization=off';

      --disable_warnings
      DROP TABLE /*! IF EXISTS */ A;
      DROP TABLE /*! IF EXISTS */ C;
      --enable_warnings

      CREATE TABLE `A` (
      `col_int_nokey` int(11) DEFAULT NULL,
      `col_int_key` int(11) DEFAULT NULL,
      `col_varchar_key` varchar(1) DEFAULT NULL,
      `col_varchar_nokey` varchar(1) DEFAULT NULL,
      KEY `col_int_key` (`col_int_key`),
      KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      CREATE TABLE `C` (
      `col_int_nokey` int(11) DEFAULT NULL,
      `col_int_key` int(11) DEFAULT NULL,
      `col_varchar_key` varchar(1) DEFAULT NULL,
      `col_varchar_nokey` varchar(1) DEFAULT NULL,
      KEY `col_int_key` (`col_int_key`),
      KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      INSERT INTO `C` VALUES (NULL,2,'w','w');
      INSERT INTO `C` VALUES (7,9,'m','m');
      INSERT INTO `C` VALUES (9,3,'m','m');
      INSERT INTO `C` VALUES (7,9,'k','k');
      INSERT INTO `C` VALUES (4,NULL,'r','r');
      INSERT INTO `C` VALUES (2,9,'t','t');
      INSERT INTO `C` VALUES (6,3,'j','j');
      INSERT INTO `C` VALUES (8,8,'u','u');
      INSERT INTO `C` VALUES (NULL,8,'h','h');
      INSERT INTO `C` VALUES (5,53,'o','o');
      INSERT INTO `C` VALUES (NULL,0,NULL,NULL);
      INSERT INTO `C` VALUES (6,5,'k','k');
      INSERT INTO `C` VALUES (188,166,'e','e');
      INSERT INTO `C` VALUES (2,3,'n','n');
      INSERT INTO `C` VALUES (1,0,'t','t');
      INSERT INTO `C` VALUES (1,1,'c','c');
      INSERT INTO `C` VALUES (0,9,'m','m');
      INSERT INTO `C` VALUES (9,5,'y','y');
      INSERT INTO `C` VALUES (NULL,6,'f','f');
      INSERT INTO `C` VALUES (4,2,'d','d');

      SELECT table1 .`col_varchar_nokey`
      FROM A table1 RIGHT JOIN C ON table1 .`col_int_nokey`
      WHERE ( 's' , 'm' ) IN (
      SELECT `col_varchar_nokey` , `col_varchar_key`
      FROM C ) ;

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            philipstoev Philip Stoev (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.