SELECT statement after UPDATE doesn't work

[avodaniel]

In mysql-test/t/update.test there is a test for update statement over versioned table. Unfortunately, the test doesn't work. Command:

SELECT x, y FROM t1;

Returns both historical and up-to-date data. When the same commands are run from client then everything works.

[midenok]

drop table t1; CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;  INSERT INTO t1(x, y) VALUES (1, 1000);  UPDATE t1 SET y = y + 1; SELECT * FROM t1;

Sys_end is not updated:

+------+------+----------------------------+----------------------------+
| x    | y    | Sys_start                  | Sys_end                    |
+------+------+----------------------------+----------------------------+
|    1 | 1000 | 2016-09-09 14:22:07.549334 | 2038-01-19 03:14:07.000000 |
|    1 | 1001 | 2016-09-09 14:22:07.553363 | 2038-01-19 03:14:07.000000 |
+------+------+----------------------------+----------------------------+

[midenok]

sql_insert.cc:787:

1. bogus restore_record() in insert_rec_for_system_versioning();
2. duplicate table->get_row_end_field()->set_time().

[midenok]

   1590 int insert_rec_for_system_versioning(TABLE *table, ha_rows *inserted)
   1591 {
   1592   DBUG_ASSERT(table->is_with_system_versioning());
   1593   restore_record(table,record[1]);
   1594
   1595   // Set Sys_end to now()
   1596   if (table->get_row_end_field()->set_time())
   1597   {
   1598     return 1;
   1599   }
   1600
   1601   const int error= table->file->ha_write_row(table->record[0]);
   1602   if (!error)
   1603     ++*inserted;
   1604
   1605   return error;
   1606 }

This ha_write_row is not working correctly for MyISAM storage engine (default-storage-engine=myisam), it duplicates current record no matter what table->record[0] contains. Do we need MyISAM storage for system versioned tables?

[midenok]

Insert into MyISAM:

#0  handler::ha_write_row (this=0x7feab78a8088, buf=0x7feab781e988 "\371\001") at /home/midenok/src/mariadb/hagrid/src/sql/handler.cc:5879
#1  0x00000000006704fc in write_record (thd=0x7feab7816070, table=0x7feab78c8870, info=0x7feae10a5e20) at /home/midenok/src/mariadb/hagrid/src/sql/sql_insert.cc:1949
#2  0x000000000066dbb9 in mysql_insert (thd=0x7feab7816070, table_list=0x7feab785e180, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=0x0) at /home/midenok/src/mariadb/hagrid/src/sql/sql_insert.cc:998
#3  0x0000000000693bab in mysql_execute_command (thd=0x7feab7816070) at /home/midenok/src/mariadb/hagrid/src/sql/sql_parse.cc:4289
#4  0x000000000069f205 in mysql_parse (thd=0x7feab7816070, rawbuf=0x7feab785e088 "INSERT INTO t1(x, y) VALUES (1, 1000)", length=0x25, parser_state=0x7feae10a6f70, is_com_multi=0x0, is_next_command=0x0) at /home/midenok/src/mariadb/hagrid/src/sql/sql_parse.cc:7789

Insert into InnoDB:

#0  handler::ha_write_row (this=0x7feab78a8088, buf=0x7feab781e988 "\371\001") at /home/midenok/src/mariadb/hagrid/src/sql/handler.cc:5879
#1  0x00000000006704fc in write_record (thd=0x7feab7816070, table=0x7feab78c8870, info=0x7feae10a5e20) at /home/midenok/src/mariadb/hagrid/src/sql/sql_insert.cc:1949
#2  0x000000000066dbb9 in mysql_insert (thd=0x7feab7816070, table_list=0x7feab785e180, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=0x0) at /home/midenok/src/mariadb/hagrid/src/sql/sql_insert.cc:998
#3  0x0000000000693bab in mysql_execute_command (thd=0x7feab7816070) at /home/midenok/src/mariadb/hagrid/src/sql/sql_parse.cc:4289
#4  0x000000000069f205 in mysql_parse (thd=0x7feab7816070, rawbuf=0x7feab785e088 "INSERT INTO t1(x, y) VALUES (1, 1000)", length=0x25, parser_state=0x7feae10a6f70, is_com_multi=0x0, is_next_command=0x0) at /home/midenok/src/mariadb/hagrid/src/sql/sql_parse.cc:7789

SQL-level insert is identical.

[midenok]

Double insert:

+------+------+----------------------------+----------------------------+
| x    | y    | Sys_start                  | Sys_end                    |
+------+------+----------------------------+----------------------------+
|    1 | 1000 | 2016-09-10 09:06:30.398396 | 2038-01-19 03:14:07.000000 |
|    1 |  232 | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00.000000 |
|    1 |  232 | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00.000000 |
+------+------+----------------------------+----------------------------+

Result: no records updated, 2 new records are inserted.

Double insert, then update:

+------+------+----------------------------+----------------------------+
| x    | y    | Sys_start                  | Sys_end                    |
+------+------+----------------------------+----------------------------+
|    1 | 1000 | 2016-09-10 09:04:33.227327 | 2038-01-19 03:14:07.000000 |
|    1 |  232 | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00.000000 |
|    1 | 1001 | 2016-09-10 09:04:33.227327 | 2038-01-19 03:14:07.000000 |
+------+------+----------------------------+----------------------------+

Result: 2 records inserted, update performed on inserted record.