Bug #95936 insert into ... select does not work with repeatable read

Description:
Under REPEATABLE-READ, "insert into ... select" retrieves extra records inserted by other threads.

How to repeat:
mysql> create table ta(id int);
Query OK, 0 rows affected (0.45 sec)

mysql> create table tb(id int);
Query OK, 0 rows affected (0.28 sec)

mysql> insert into ta values (1),(3);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set global tx_isolation=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ta;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

-- Open a new connection
mysql> insert into ta values (2);
Query OK, 1 row affected (0.05 sec)

-- back to the first connection
mysql> select * from ta;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> insert into tb select * from ta;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb;
+------+
| id   |
+------+
|    1 |
|    3 |
|    2 |
+------+
3 rows in set (0.00 sec)

Hi,

Thanks for the test case, reproduced as described.

bogdan

mysql [localhost:5726] {msandbox} (test) >  create table ta(id int);
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5726] {msandbox} (test) > create table tb(id int);
Query OK, 0 rows affected (0.02 sec)

mysql [localhost:5726] {msandbox} (test) > insert into ta values (1),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost:5726] {msandbox} (test) >  set global tx_isolation=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:5726] {msandbox} (test) > show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                              |
+---------+------+----------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead |
+---------+------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5726] {msandbox} (test) >  set session tx_isolation=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:5726] {msandbox} (test) > show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                              |
+---------+------+----------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead |
+---------+------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5726] {msandbox} (test) >  begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5726] {msandbox} (test) > select * from ta;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql [localhost:5726] {msandbox} (test) > select * from ta;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql [localhost:5726] {msandbox} (test) > 
mysql [localhost:5726] {msandbox} (test) > insert into tb select * from ta;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql [localhost:5726] {msandbox} (test) > select * from tb;
+------+
| id   |
+------+
|    1 |
|    3 |
|    2 |
+------+
3 rows in set (0.00 sec)

mysql [localhost:5726] {msandbox} (test) > 

-----

mysql [localhost:5726] {msandbox} ((none)) > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost:5726] {msandbox} (test) > show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql [localhost:5726] {msandbox} (test) > insert into ta values (2);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5726] {msandbox} (test) >

this seems to be similar as the issue as described in 

https://forums.mysql.com/read.php?22,678692,678692#msg-678692
(Repeatable Read update sees new value from another session)

That is, the UPDATE WHERE clause seemed to be seeing the lastest committed vs the isolated version of a record.