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

Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key)

    Details

      Description

      Sorry, but I'm not sure if it's a bug ... but I think so:

      CREATE TABLE `tab1` (
        `Id` int(11) NOT NULL,
        PRIMARY KEY (`Id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
      INSERT INTO `tab1` (`Id`) VALUES (1);
       
      CREATE TABLE `tab2` (
        `tab1_Id` int(11) NOT NULL DEFAULT 0,
        `col1` int(11) DEFAULT NULL,
        UNIQUE KEY `col1` (`col1`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
      INSERT INTO `tab2` (`tab1_Id`, `col1`) VALUES (1, NULL), (1, NULL);
      

      Now two Statements:

      SELECT Id FROM tab1 WHERE EXISTS
        (SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id AND tab2.col1 IS NULL);
      

      SELECT Id FROM tab1 WHERE EXISTS 
        (SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id);
      

      Statement 1 gets a result with 2 identical rows, Statement 2 gets a result with (the correct, I mean) 1 row.

      If I delete the UNIQUE Key on tab2:

      ALTER TABLE `tab2` DROP INDEX `col1`;
      

      both Statements gets a result with only 1 row. Why does the UNIQUE Key have such a effect? It's a bug?

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              dave_bowman Chris N.
            • Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: