| Bug #101889 | Potentially wrong results | ||
|---|---|---|---|
| Submitted: | 7 Dec 2020 9:45 | Modified: | 7 Dec 2020 11:43 |
| Reporter: | xiaoyang chen | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Dec 2020 10:58]
MySQL Verification Team
Hello! Thank you for the report and test case. Could you please confirm with 8.0.22 build? I'm not seeing any issues with 8.0.22 build. Thank you! -- ./mtr bug101889 --nocheck-testcases Logging: ./mtr bug101889 --nocheck-testcases MySQL Version 8.0.22 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/home/umshastr/work/binaries/ga/mysql-8.0.22/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CALL mtr.add_suppression("==[0-9]*== Warning: set address range perms: large range"); set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; set @old_opt_switch=@@optimizer_switch; set optimizer_switch='subquery_materialization_cost_based=off'; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; create table t11 (a int NOT NULL, b int, primary key (a)); create table t12 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t11 values (0, 10),(1, 11),(2, 12); insert into t12 values (33, 10),(22, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row select * from t11; a b 0 10 1 11 2 12 drop table t11, t12, t2; [ 50%] main.bug101889 [ pass ] 157 [100%] shutdown_report [ pass ] ------------------------------------------------------------------------------ The servers were restarted 0 times The servers were reinitialized 0 times Spent 0.157 of 46 seconds executing testcases Completed: All 2 tests were successful. regards, Umesh
[7 Dec 2020 11:43]
xiaoyang chen
Yes, I have carefully checked the modified code and found that some special cases has been forgotten.
Description: The following mtr produces error result. ``` --source include/not_tsan.inc --source include/force_myisam_default.inc --source include/have_myisam.inc --source include/no_valgrind_without_big.inc CALL mtr.add_suppression("==[0-9]*== Warning: set address range perms: large range"); set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; set @old_opt_switch=@@optimizer_switch; set optimizer_switch='subquery_materialization_cost_based=off'; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; create table t11 (a int NOT NULL, b int, primary key (a)); create table t12 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t11 values (0, 10),(1, 11),(2, 12); insert into t12 values (33, 10),(22, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); -- error ER_SUBQUERY_NO_1_ROW delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); select * from t11; drop table t11, t12, t2; ``` The results: ``` CALL mtr.add_suppression("==[0-9]*== Warning: set address range perms: large range"); set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; set @old_opt_switch=@@optimizer_switch; set optimizer_switch='subquery_materialization_cost_based=off'; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; create table t11 (a int NOT NULL, b int, primary key (a)); create table t12 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t11 values (0, 10),(1, 11),(2, 12); insert into t12 values (33, 10),(22, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row select * from t11; a b 1 11 2 12 drop table t11, t12, t2; ``` However, the query "delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2)" should not delete the row "(0, 10)" as it executes failed. How to repeat: Following the above mtr test, one can re-produce the results.