Bug #115942 The delete operation performed in an uncommitted transaction affects the query r

Description:
The delete operation performed in an uncommitted transaction affects the query result.
1.execute a query

2.delete in an uncommited transaction

3.execute the query again

How to repeat:
1.create table and insert data

create database sqltester;
use sqltester
source sqltester_for_parallel_with_index.2k_dp_210col.index_desc.sql
source sqltester_for_parallel_with_index_view_part_tab.sql

2.execute query
select C_MIDDLE from t10 join t2 on(EXISTS(select binary_col from t0)) right join(select c0 from common_t0 join tx_cmplx_002 right join yy_player_t0  on 0 where EXISTS(select coin from t1 where EXISTS(select C_VARCHARTWN from t0_sub_union))) subq_2 on(c0) group by 1 order by 1 desc;

3.Enable the transaction and delete table data finally rollback it.
begin;delete from sqltester.t0;select sleep(10);rollback;

4.execute the query again
select C_MIDDLE from t10 join t2 on(EXISTS(select binary_col from t0)) right join(select c0 from common_t0 join tx_cmplx_002 right join yy_player_t0  on 0 where EXISTS(select coin from t1 where EXISTS(select C_VARCHARTWN from t0_sub_union))) subq_2 on(c0) group by 1 order by 1 desc;

Here is my execution:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.37    |
+-----------+
1 row in set (0.00 sec)

mysql> select C_MIDDLE from t10 join t2 on(EXISTS(select binary_col from t0)) right join(select c0 from common_t0 join tx_cmplx_002 right join yy_player_t0  on 0 where EXISTS(select coin from t1 where EXISTS(select C_VARCHARTWN from t0_sub_union))) subq_2 on(c0) group by 1 order by 1 desc;
Empty set (0.04 sec)

mysql> begin;delete from sqltester.t0;select sleep(10);rollback;
Query OK, 0 rows affected (0.00 sec)

Query OK, 510 rows affected (0.07 sec)

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> select C_MIDDLE from t10 join t2 on(EXISTS(select binary_col from t0)) right join(select c0 from common_t0 join tx_cmplx_002 right join yy_player_t0  on 0 where EXISTS(select coin from t1 where EXISTS(select C_VARCHARTWN from t0_sub_union))) subq_2 on(c0) group by 1 order by 1 desc;
+----------+
| C_MIDDLE |
+----------+
| NULL     |
+----------+
1 row in set (0.01 sec)

Hi,

What transaction isolation are you running as this will not be a bug in many cases. 

Please check https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

The transaction isolation is the default value which is REPEATABLE-READ
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

Hi Alice,

Can you please upload your my.cnf and also result of

show global variables;

Thanks

my.cnf is set as follows:

[client]
port=3306
#socket=/data/mysql/mysql_8035/mysql.sock
socket=/data/mysql/sql/mysql.sock
[mysqld]
user=root
datadir=/data/mysql/sql/data
basedir=/data/mysql/sql
log-error=/data/mysql/sql/data/error.log
innodb_buffer_pool_size=128G
#innodb_slice_replicas_num=3
innodb_adaptive_hash_index=OFF
#skip-character-set-client-handshake
tmpdir=/data/mysql/tmp
default_authentication_plugin=mysql_native_password
max_connections=10000
max_connect_errors=10000
#bind-address=0.0.0.0
port=3306
socket=/data/mysql/sql/mysql.sock
mysqlx_socket = /data/mysql/sql/mysqlx.sock
mysqlx_port = 33065
core-file
innodb_buffer_pool_in_core_file=ON
character_set_server=utf8mb4
#sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
sql_mode=''
completion_type=NO_CHAIN
event_scheduler=ON 
explicit_defaults_for_timestamp=ON
transaction_isolation=REPEATABLE-READ
autocommit=ON
innodb_strict_mode=OFFcollation_server=utf8mb4_0900_ai_cisecure_file_priv=
log_timestamps = SYSTEM
server-id=1
log-error-verbosity=2disabled_storage_engines='CSV,MyISAM,MEMORY,FEDERATED,ARCHIVE,PERFORMANCE_SCHEMA,MRG_MYISAM,BLACKHOLE'
###binlog######log_bin = ON
sync_binlog = 1
#gtid_mode=ON
#enforce_gtid_consistency=ON
#binlog_order_commits = OFF
#max_binlog_size=536870912
binlog_expire_logs_seconds = 604800

#innodb_log_buffer_size = 51539607552
#plugin-load = ";threadpool=threadpool.so"
#threadpool_size = 16
#thread_stack = 731728
thread_stack = 286720
general_log = OFF
#innodb_max_undo_log_size = 10G
#innodb_undo_tablespaces = 10
innodb_rollback_segments = 1
#innodb_purge_rseg_truncate_frequency = 128
max_allowed_packet = 10737418240
net_read_timeout = 600
net_write_timeout = 600
join_buffer_size = 10737418240
sort_buffer_size = 10737418240
secure_file_priv=''
skip-name-resolve
#optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=on,materialization=off,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=off,derived_merge_no_subquery_check=on'

#######pq#############
#optimizer_switch="offset_pushdown=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,derived_merge_no_subquery_check=off,gen_col_partition_prune=off,partial_result_cache=off"

lower_case_table_names = 0