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

Wrong result (extra rows) with an index on a VIRTUAL column

    XMLWordPrintable

Details

    Description

      CREATE TABLE t1 (
        pk INT(11) NOT NULL,
        c VARCHAR(3) DEFAULT NULL,
        vc VARCHAR(3) GENERATED ALWAYS AS (c) VIRTUAL,
        PRIMARY KEY (pk),
        KEY (vc)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 (pk,c) VALUES 
      (1,'foo'),(2,'bar'),(3,'qux'),(4,'foo'),(5,'bar'),
      (6,'qux'),(7,'foo'),(8,'bar'),(9,'qux'),(10,'foo');
       
      CREATE TABLE t2 (
        pk INT(11) NOT NULL,
        c VARCHAR(3),
        PRIMARY KEY (pk)
      ) ENGINE=InnoDB;
       
      INSERT INTO t2 VALUES (1,'foo'),(2,'bar');
      

      SELECT t1.pk, t2.pk FROM t1, t2 WHERE t2.c = t1.vc AND t2.c = t1.c AND t2.pk > t1.pk;
      +----+----+
      | pk | pk |
      +----+----+
      |  1 |  1 |
      |  4 |  1 |
      |  7 |  1 |
      | 10 |  1 |
      |  2 |  2 |
      |  5 |  2 |
      |  8 |  2 |
      +----+----+
      7 rows in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.