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

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

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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11736 InnoDB: Failing assertion: n < rec_offs_n_fields(offsets)

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-5800 indexes on virtual (not materialized) columns

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            On bb-10.2-monty as of 349d69e2e the SELECT from the test case causes the assertion failure:

            2017-01-02 14:49:04 0x7f663e813300  InnoDB: Assertion failure in file /data/src/bb-10.2-monty/storage/innobase/include/rem0rec.ic line 1175
            		 
            InnoDB: Failing assertion: n < rec_offs_n_fields(offsets)
            		 
             
            		 
            #6  0x00007f663dde0eba in ut_dbg_assertion_failed (expr=0x7f663e302f28 "n < rec_offs_n_fields(offsets)", file=0x7f663e302d40 "/data/src/bb-10.2-monty/storage/innobase/include/rem0rec.ic", line=1175) at /data/src/bb-10.2-monty/storage/innobase/ut/ut0dbg.cc:59
            		 
            #7  0x00007f663dd34742 in rec_offs_nth_extern (offsets=0x7f663e80fee0, n=316) at /data/src/bb-10.2-monty/storage/innobase/include/rem0rec.ic:1175
            		 
            #8  0x00007f663dd3c90f in row_sel_store_mysql_field_func (mysql_rec=0x7f65f4c4d544 "", prebuilt=0x7f65f4cf8088, rec=0x7f66086ac0d0 "\200", index=0x7f65f4c7ea08, offsets=0x7f663e80fee0, field_no=316, templ=0x7f65f4cd3370) at /data/src/bb-10.2-monty/storage/innobase/row/row0sel.cc:3075
            		 
            #9  0x00007f663dd3d22c in row_sel_store_mysql_rec (mysql_rec=0x7f65f4c4d544 "", prebuilt=0x7f65f4cf8088, rec=0x7f66086ac0d0 "\200", vrow=0x0, rec_clust=1, index=0x7f65f4c7ea08, offsets=0x7f663e80fee0) at /data/src/bb-10.2-monty/storage/innobase/row/row0sel.cc:3307
            		 
            #10 0x00007f663dd42185 in row_search_mvcc (buf=0x7f65f4c1d588 "\376\001", mode=PAGE_CUR_GE, prebuilt=0x7f65f4cf8088, match_mode=1, direction=0) at /data/src/bb-10.2-monty/storage/innobase/row/row0sel.cc:5485
            		 
            #11 0x00007f663dbbc90a in ha_innobase::index_read (this=0x7f65f4caa088, buf=0x7f65f4c1d588 "\376\001", key_ptr=0x7f65f4c65288 "", key_len=6, find_flag=HA_READ_KEY_EXACT) at /data/src/bb-10.2-monty/storage/innobase/handler/ha_innodb.cc:10327
            		 
            #12 0x00007f663d98016a in handler::index_read_map (this=0x7f65f4caa088, buf=0x7f65f4c1d588 "\376\001", key=0x7f65f4c65288 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /data/src/bb-10.2-monty/sql/handler.h:3092
            		 
            #13 0x00007f663d9766e4 in handler::ha_index_read_map (this=0x7f65f4caa088, buf=0x7f65f4c1d588 "\376\001", key=0x7f65f4c65288 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /data/src/bb-10.2-monty/sql/handler.cc:2627
            		 
            #14 0x00007f663d7782e4 in join_read_always_key (tab=0x7f65f4c64b08) at /data/src/bb-10.2-monty/sql/sql_select.cc:19096
            		 
            #15 0x00007f663d7768cd in sub_select (join=0x7f65f4c61d28, join_tab=0x7f65f4c64b08, end_of_records=false) at /data/src/bb-10.2-monty/sql/sql_select.cc:18340
            		 
            #16 0x00007f663d776ff8 in evaluate_join_record (join=0x7f65f4c61d28, join_tab=0x7f65f4c64758, error=0) at /data/src/bb-10.2-monty/sql/sql_select.cc:18565
            		 
            #17 0x00007f663d77692f in sub_select (join=0x7f65f4c61d28, join_tab=0x7f65f4c64758, end_of_records=false) at /data/src/bb-10.2-monty/sql/sql_select.cc:18343
            		 
            #18 0x00007f663d775eed in do_select (join=0x7f65f4c61d28, procedure=0x0) at /data/src/bb-10.2-monty/sql/sql_select.cc:17887
            		 
            #19 0x00007f663d750f64 in JOIN::exec_inner (this=0x7f65f4c61d28) at /data/src/bb-10.2-monty/sql/sql_select.cc:3388
            		 
            #20 0x00007f663d7504ae in JOIN::exec (this=0x7f65f4c61d28) at /data/src/bb-10.2-monty/sql/sql_select.cc:3199
            		 
            #21 0x00007f663d751605 in mysql_select (thd=0x7f65f4c16070, tables=0x7f65f4c60410, wild_num=0, fields=..., conds=0x7f65f4c61788, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f65f4c61d08, unit=0x7f65f4c19b48, select_lex=0x7f65f4c1a278) at /data/src/bb-10.2-monty/sql/sql_select.cc:3584
            		 
            #22 0x00007f663d7466bd in handle_select (thd=0x7f65f4c16070, lex=0x7f65f4c19a80, result=0x7f65f4c61d08, setup_tables_done_option=0) at /data/src/bb-10.2-monty/sql/sql_select.cc:373
            		 
            #23 0x00007f663d712e69 in execute_sqlcom_select (thd=0x7f65f4c16070, all_tables=0x7f65f4c60410) at /data/src/bb-10.2-monty/sql/sql_parse.cc:6396
            		 
            #24 0x00007f663d708958 in mysql_execute_command (thd=0x7f65f4c16070) at /data/src/bb-10.2-monty/sql/sql_parse.cc:3426
            		 
            #25 0x00007f663d716828 in mysql_parse (thd=0x7f65f4c16070, rawbuf=0x7f65f4c60088 "SELECT t1.pk, t2.pk FROM t1, t2 WHERE t2.c = t1.vc AND t2.c = t1.c AND t2.pk > t1.pk", length=84, parser_state=0x7f663e811e20, is_com_multi=false, is_next_command=false) at /data/src/bb-10.2-monty/sql/sql_parse.cc:7839
            		 
            #26 0x00007f663d7043ee in dispatch_command (command=COM_QUERY, thd=0x7f65f4c16070, packet=0x7f65f4c54071 "", packet_length=84, is_com_multi=false, is_next_command=false) at /data/src/bb-10.2-monty/sql/sql_parse.cc:1799
            		 
            #27 0x00007f663d702dc8 in do_command (thd=0x7f65f4c16070) at /data/src/bb-10.2-monty/sql/sql_parse.cc:1359
            		 
            #28 0x00007f663d84ac5a in do_handle_one_connection (connect=0x7f663a05ff70) at /data/src/bb-10.2-monty/sql/sql_connect.cc:1354
            		 
            #29 0x00007f663d84a9e7 in handle_one_connection (arg=0x7f663a05ff70) at /data/src/bb-10.2-monty/sql/sql_connect.cc:1260
            		 
            #30 0x00007f663cd370a4 in start_thread (arg=0x7f663e813300) at pthread_create.c:309
            		 
            #31 0x00007f663ace487d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

            elenst Elena Stepanova added a comment - On bb-10.2-monty as of 349d69e2e the SELECT from the test case causes the assertion failure: 2017-01-02 14:49:04 0x7f663e813300 InnoDB: Assertion failure in file /data/src/bb-10.2-monty/storage/innobase/include/rem0rec.ic line 1175 InnoDB: Failing assertion: n < rec_offs_n_fields(offsets)   #6 0x00007f663dde0eba in ut_dbg_assertion_failed (expr=0x7f663e302f28 "n < rec_offs_n_fields(offsets)", file=0x7f663e302d40 "/data/src/bb-10.2-monty/storage/innobase/include/rem0rec.ic", line=1175) at /data/src/bb-10.2-monty/storage/innobase/ut/ut0dbg.cc:59 #7 0x00007f663dd34742 in rec_offs_nth_extern (offsets=0x7f663e80fee0, n=316) at /data/src/bb-10.2-monty/storage/innobase/include/rem0rec.ic:1175 #8 0x00007f663dd3c90f in row_sel_store_mysql_field_func (mysql_rec=0x7f65f4c4d544 "", prebuilt=0x7f65f4cf8088, rec=0x7f66086ac0d0 "\200", index=0x7f65f4c7ea08, offsets=0x7f663e80fee0, field_no=316, templ=0x7f65f4cd3370) at /data/src/bb-10.2-monty/storage/innobase/row/row0sel.cc:3075 #9 0x00007f663dd3d22c in row_sel_store_mysql_rec (mysql_rec=0x7f65f4c4d544 "", prebuilt=0x7f65f4cf8088, rec=0x7f66086ac0d0 "\200", vrow=0x0, rec_clust=1, index=0x7f65f4c7ea08, offsets=0x7f663e80fee0) at /data/src/bb-10.2-monty/storage/innobase/row/row0sel.cc:3307 #10 0x00007f663dd42185 in row_search_mvcc (buf=0x7f65f4c1d588 "\376\001", mode=PAGE_CUR_GE, prebuilt=0x7f65f4cf8088, match_mode=1, direction=0) at /data/src/bb-10.2-monty/storage/innobase/row/row0sel.cc:5485 #11 0x00007f663dbbc90a in ha_innobase::index_read (this=0x7f65f4caa088, buf=0x7f65f4c1d588 "\376\001", key_ptr=0x7f65f4c65288 "", key_len=6, find_flag=HA_READ_KEY_EXACT) at /data/src/bb-10.2-monty/storage/innobase/handler/ha_innodb.cc:10327 #12 0x00007f663d98016a in handler::index_read_map (this=0x7f65f4caa088, buf=0x7f65f4c1d588 "\376\001", key=0x7f65f4c65288 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /data/src/bb-10.2-monty/sql/handler.h:3092 #13 0x00007f663d9766e4 in handler::ha_index_read_map (this=0x7f65f4caa088, buf=0x7f65f4c1d588 "\376\001", key=0x7f65f4c65288 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /data/src/bb-10.2-monty/sql/handler.cc:2627 #14 0x00007f663d7782e4 in join_read_always_key (tab=0x7f65f4c64b08) at /data/src/bb-10.2-monty/sql/sql_select.cc:19096 #15 0x00007f663d7768cd in sub_select (join=0x7f65f4c61d28, join_tab=0x7f65f4c64b08, end_of_records=false) at /data/src/bb-10.2-monty/sql/sql_select.cc:18340 #16 0x00007f663d776ff8 in evaluate_join_record (join=0x7f65f4c61d28, join_tab=0x7f65f4c64758, error=0) at /data/src/bb-10.2-monty/sql/sql_select.cc:18565 #17 0x00007f663d77692f in sub_select (join=0x7f65f4c61d28, join_tab=0x7f65f4c64758, end_of_records=false) at /data/src/bb-10.2-monty/sql/sql_select.cc:18343 #18 0x00007f663d775eed in do_select (join=0x7f65f4c61d28, procedure=0x0) at /data/src/bb-10.2-monty/sql/sql_select.cc:17887 #19 0x00007f663d750f64 in JOIN::exec_inner (this=0x7f65f4c61d28) at /data/src/bb-10.2-monty/sql/sql_select.cc:3388 #20 0x00007f663d7504ae in JOIN::exec (this=0x7f65f4c61d28) at /data/src/bb-10.2-monty/sql/sql_select.cc:3199 #21 0x00007f663d751605 in mysql_select (thd=0x7f65f4c16070, tables=0x7f65f4c60410, wild_num=0, fields=..., conds=0x7f65f4c61788, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f65f4c61d08, unit=0x7f65f4c19b48, select_lex=0x7f65f4c1a278) at /data/src/bb-10.2-monty/sql/sql_select.cc:3584 #22 0x00007f663d7466bd in handle_select (thd=0x7f65f4c16070, lex=0x7f65f4c19a80, result=0x7f65f4c61d08, setup_tables_done_option=0) at /data/src/bb-10.2-monty/sql/sql_select.cc:373 #23 0x00007f663d712e69 in execute_sqlcom_select (thd=0x7f65f4c16070, all_tables=0x7f65f4c60410) at /data/src/bb-10.2-monty/sql/sql_parse.cc:6396 #24 0x00007f663d708958 in mysql_execute_command (thd=0x7f65f4c16070) at /data/src/bb-10.2-monty/sql/sql_parse.cc:3426 #25 0x00007f663d716828 in mysql_parse (thd=0x7f65f4c16070, rawbuf=0x7f65f4c60088 "SELECT t1.pk, t2.pk FROM t1, t2 WHERE t2.c = t1.vc AND t2.c = t1.c AND t2.pk > t1.pk", length=84, parser_state=0x7f663e811e20, is_com_multi=false, is_next_command=false) at /data/src/bb-10.2-monty/sql/sql_parse.cc:7839 #26 0x00007f663d7043ee in dispatch_command (command=COM_QUERY, thd=0x7f65f4c16070, packet=0x7f65f4c54071 "", packet_length=84, is_com_multi=false, is_next_command=false) at /data/src/bb-10.2-monty/sql/sql_parse.cc:1799 #27 0x00007f663d702dc8 in do_command (thd=0x7f65f4c16070) at /data/src/bb-10.2-monty/sql/sql_parse.cc:1359 #28 0x00007f663d84ac5a in do_handle_one_connection (connect=0x7f663a05ff70) at /data/src/bb-10.2-monty/sql/sql_connect.cc:1354 #29 0x00007f663d84a9e7 in handle_one_connection (arg=0x7f663a05ff70) at /data/src/bb-10.2-monty/sql/sql_connect.cc:1260 #30 0x00007f663cd370a4 in start_thread (arg=0x7f663e813300) at pthread_create.c:309 #31 0x00007f663ace487d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
            elenst Elena Stepanova added a comment -

            The assertion failure didn't make it to 10.2 tree (as of fd0479ce592e0b7), but the wrong result is still there.

            Additionally, here is another test case (from MDEV-11736):

            MariaDB [test]> CREATE TABLE IF NOT EXISTS t1 (`pk` INT AUTO_INCREMENT PRIMARY KEY, i INT, vi INT AS (i*2) VIRTUAL, KEY(vi)) ENGINE=InnoDB;
            		 
            Query OK, 0 rows affected (0.45 sec)
            		 
             
            MariaDB [test]> INSERT INTO t1 (i) VALUES (1),(2),(3),(4);
            		 
            Query OK, 4 rows affected (0.04 sec)
            		 
            Records: 4  Duplicates: 0  Warnings: 0
            		 
             
            MariaDB [test]>  
            MariaDB [test]> SELECT * FROM t1 WHERE vi < 3;
            		 
            +----+------+------+
            		 
            | pk | i    | vi   |
            		 
            +----+------+------+
            		 
            |  1 |    1 |    2 |
            		 
            |  2 |    2 |    4 |
            		 
            |  3 |    3 |    6 |
            		 
            |  4 |    4 |    8 |
            		 
            +----+------+------+
            		 
            4 rows in set (0.00 sec)

            elenst Elena Stepanova added a comment - The assertion failure didn't make it to 10.2 tree (as of fd0479ce592e0b7), but the wrong result is still there. Additionally, here is another test case (from MDEV-11736 ): MariaDB [test]> CREATE TABLE IF NOT EXISTS t1 (`pk` INT AUTO_INCREMENT PRIMARY KEY , i INT , vi INT AS (i*2) VIRTUAL, KEY (vi)) ENGINE=InnoDB; Query OK, 0 rows affected (0.45 sec) MariaDB [test]> INSERT INTO t1 (i) VALUES (1),(2),(3),(4); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> MariaDB [test]> SELECT * FROM t1 WHERE vi < 3; + ----+------+------+ | pk | i | vi | + ----+------+------+ | 1 | 1 | 2 | | 2 | 2 | 4 | | 3 | 3 | 6 | | 4 | 4 | 8 | + ----+------+------+ 4 rows in set (0.00 sec)
            serg Sergei Golubchik added a comment -

            cannot repeat on bb-10.2-serg as of 5da30851319

            serg Sergei Golubchik added a comment - cannot repeat on bb-10.2-serg as of 5da30851319
            elenst Elena Stepanova added a comment -

            Fixed by this commit:

            commit 9e5f32b2596d27a6b41455ea0513da009d4daca1
            		 
            Author: Sergei Golubchik <serg@mariadb.org>
            		 
            Date:   Sat Feb 4 19:58:32 2017 +0100
            		 
             
            		 
                bugfix: disable ICP in InnoDB for indexes on virtual columns
            		 
                
                because it doesn't work, vcols are never calculated for ICP
            		 
             
            		 
            diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
            		 
            index b353be6..c6f98bd 100644
            		 
            --- a/storage/innobase/handler/ha_innodb.cc
            		 
            +++ b/storage/innobase/handler/ha_innodb.cc
            		 
            @@ -22647,6 +22647,12 @@ ha_innobase::idx_cond_push(
            		 
                    DBUG_ASSERT(keyno != MAX_KEY);
            		 
                    DBUG_ASSERT(idx_cond != NULL);
            		 
             
            +       /* We can only evaluate the condition if all columns are stored.*/
            		 
            +       dict_index_t* idx  = innobase_get_index(keyno);
            		 
            +       if (idx && dict_index_has_virtual(idx)) {
            		 
            +               DBUG_RETURN(idx_cond);
            		 
            +       }
            		 
            +
            		 
                    pushed_idx_cond = idx_cond;
            		 
                    pushed_idx_cond_keyno = keyno;
            		 
                    in_range_check_pushed_down = TRUE;

            The relation is also confirmed by the fact that the wrong result on the bugged tree goes away if the test is run with optimizer_switch="index_condition_pushdown=off".

            elenst Elena Stepanova added a comment - Fixed by this commit: commit 9e5f32b2596d27a6b41455ea0513da009d4daca1 Author: Sergei Golubchik <serg@mariadb.org> Date: Sat Feb 4 19:58:32 2017 +0100   bugfix: disable ICP in InnoDB for indexes on virtual columns because it doesn't work, vcols are never calculated for ICP   diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index b353be6..c6f98bd 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -22647,6 +22647,12 @@ ha_innobase::idx_cond_push( DBUG_ASSERT(keyno != MAX_KEY); DBUG_ASSERT(idx_cond != NULL); + /* We can only evaluate the condition if all columns are stored.*/ + dict_index_t* idx = innobase_get_index(keyno); + if (idx && dict_index_has_virtual(idx)) { + DBUG_RETURN(idx_cond); + } + pushed_idx_cond = idx_cond; pushed_idx_cond_keyno = keyno; in_range_check_pushed_down = TRUE; The relation is also confirmed by the fact that the wrong result on the bugged tree goes away if the test is run with optimizer_switch="index_condition_pushdown=off" .

            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.