[MDEV-11541] Wrong result (extra rows) with an index on a VIRTUAL column Created: 2016-12-11  Updated: 2017-02-09  Resolved: 2017-02-09

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: N/A
Fix Version/s: 10.2.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: 10.2-rc

Issue Links:
Duplicate
duplicates MDEV-11736 InnoDB: Failing assertion: n < rec_of... Closed
Relates
relates to MDEV-5800 indexes on virtual (not materialized)... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2017-01-02 ]

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

Comment by Elena Stepanova [ 2017-01-17 ]

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)

Comment by Sergei Golubchik [ 2017-02-09 ]

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

Comment by Elena Stepanova [ 2017-02-09 ]

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".

Generated at Thu Feb 08 07:50:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.