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

Inconsistent query result caused by index

Details

    Description

      I run the following cases, which are expected to return the same query result.

      -- case 1
      		 
      CREATE TABLE t1 (c1 TEXT) ENGINE=InnoDB;
      		 
      INSERT INTO t1 (c1) VALUES ('a');
      		 
      SELECT c1 FROM t1 WHERE CAST(c1 AS BINARY(7)) NOT IN (SELECT c1 FROM t1); -- {a}


      -- case 2
      		 
      CREATE TABLE t1 (c1 TEXT, UNIQUE (c1(1))) ENGINE=InnoDB;
      		 
      INSERT INTO t1 (c1) VALUES ('a');
      		 
      SELECT c1 FROM t1 WHERE CAST(c1 AS BINARY(7)) NOT IN (SELECT c1 FROM t1); -- actual: {}, expected: {a}

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          John Jove John Jove created issue -
          John Jove John Jove added a comment -

          I also try other variants of the above test cases, which results are as follows.

          -- case 3
          		 
          CREATE TABLE t1 (c1 TEXT, UNIQUE (c1)) ENGINE=InnoDB;
          		 
          INSERT INTO t1 (c1) VALUES ('a');
          		 
          SELECT c1 as r2 FROM t1 WHERE CAST(c1 AS BINARY(7)) NOT IN (SELECT c1 FROM t1); -- {a}


          -- case 4
          		 
          CREATE TABLE t1 (c1 TEXT) ENGINE=InnoDB;
          		 
          INSERT INTO t1 (c1) VALUES ('a');
          		 
          SELECT c1 FROM t1 WHERE CAST(c1 AS BINARY) NOT IN (SELECT c1 FROM t1); -- {}

          John Jove John Jove added a comment - I also try other variants of the above test cases, which results are as follows. -- case 3 CREATE TABLE t1 (c1 TEXT, UNIQUE (c1)) ENGINE=InnoDB; INSERT INTO t1 (c1) VALUES ( 'a' ); SELECT c1 as r2 FROM t1 WHERE CAST (c1 AS BINARY (7)) NOT IN ( SELECT c1 FROM t1); -- {a} -- case 4 CREATE TABLE t1 (c1 TEXT) ENGINE=InnoDB; INSERT INTO t1 (c1) VALUES ( 'a' ); SELECT c1 FROM t1 WHERE CAST (c1 AS BINARY ) NOT IN ( SELECT c1 FROM t1); -- {}
          alice Alice Sherepa made changes -
          Field Original Value New Value
          Affects Version/s 11.4 [ 29301 ]
          Affects Version/s 11.8 [ 29921 ]
          alice Alice Sherepa made changes -
          Fix Version/s 11.4 [ 29301 ]
          Fix Version/s 11.8 [ 29921 ]
          alice Alice Sherepa made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          alice Alice Sherepa added a comment -

          Thanks! I repeated as described on 11.4,11.8, with InnoDB. Myisam returns the expected result

          alice Alice Sherepa added a comment - Thanks! I repeated as described on 11.4,11.8, with InnoDB. Myisam returns the expected result
          alice Alice Sherepa made changes -
          Component/s Optimizer [ 10200 ]
          Component/s Storage Engine - InnoDB [ 10129 ]
          alice Alice Sherepa made changes -
          Labels wrong_result
          alice Alice Sherepa made changes -
          Assignee Sergei Petrunia [ psergey ]

          People

            psergei Sergei Petrunia
            John Jove John Jove
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.