Description:
Query statement results with subqueries are unstable
How to repeat:
create database test_db;
use test_db;
create table `cs_rec_cardactivelog` (
`formnum` varchar(32)
);
insert into `cs_rec_cardactivelog` values ('20014403205816');
insert into `cs_rec_cardactivelog` values ('20021356099700');
create table `om_wl_log` (
`worklogid` decimal(14,0) not null,
`workid` decimal(14,0) not null
);
insert into `om_wl_log` values (20021356105936,20021356099700);
mysql> table cs_rec_cardactivelog;
+----------------+
| formnum |
+----------------+
| 20014403205816 |
| 20021356099700 |
+----------------+
2 rows in set (0.00 sec)
mysql> table om_wl_log;
+----------------+----------------+
| worklogid | workid |
+----------------+----------------+
| 20021356105936 | 20021356099700 |
+----------------+----------------+
1 row in set (0.00 sec)
execute sql:
mysql> select * from cs_rec_cardactivelog c, om_wl_log l where l.worklogid = ( select case when exists (select 1 from om_wl_log where workid =
c.formnum) then (select worklogid from om_wl_log) end max_worklogid from dual);
+----------------+----------------+----------------+
| formnum | worklogid | workid |
+----------------+----------------+----------------+
| 20014403205816 | 20021356105936 | 20021356099700 |
| 20021356099700 | 20021356105936 | 20021356099700 |
+----------------+----------------+----------------+
2 rows in set (0.01 sec)
Reconnect to MySQL Server and execute SQL above again:
mysql> use test_db; select * from cs_rec_cardactivelog c, om_wl_log l where l.worklogid = ( select case when exists (select 1 from om_wl_log where workid =
c.formnum) then (select worklogid from om_wl_log) end max_worklogid from dual );
Empty set (0.00 sec)
Reconnect to MySQL Server several times and then execute the above SQL, and it will reappear
Suggested fix:
Expected results：
The query result is not empty
Description: Query statement results with subqueries are unstable How to repeat: create database test_db; use test_db; create table `cs_rec_cardactivelog` ( `formnum` varchar(32) ); insert into `cs_rec_cardactivelog` values ('20014403205816'); insert into `cs_rec_cardactivelog` values ('20021356099700'); create table `om_wl_log` ( `worklogid` decimal(14,0) not null, `workid` decimal(14,0) not null ); insert into `om_wl_log` values (20021356105936,20021356099700); mysql> table cs_rec_cardactivelog; +----------------+ | formnum | +----------------+ | 20014403205816 | | 20021356099700 | +----------------+ 2 rows in set (0.00 sec) mysql> table om_wl_log; +----------------+----------------+ | worklogid | workid | +----------------+----------------+ | 20021356105936 | 20021356099700 | +----------------+----------------+ 1 row in set (0.00 sec) execute sql: mysql> select * from cs_rec_cardactivelog c, om_wl_log l where l.worklogid = ( select case when exists (select 1 from om_wl_log where workid = c.formnum) then (select worklogid from om_wl_log) end max_worklogid from dual); +----------------+----------------+----------------+ | formnum | worklogid | workid | +----------------+----------------+----------------+ | 20014403205816 | 20021356105936 | 20021356099700 | | 20021356099700 | 20021356105936 | 20021356099700 | +----------------+----------------+----------------+ 2 rows in set (0.01 sec) Reconnect to MySQL Server and execute SQL above again: mysql> use test_db; select * from cs_rec_cardactivelog c, om_wl_log l where l.worklogid = ( select case when exists (select 1 from om_wl_log where workid = c.formnum) then (select worklogid from om_wl_log) end max_worklogid from dual ); Empty set (0.00 sec) Reconnect to MySQL Server several times and then execute the above SQL, and it will reappear Suggested fix: Expected results： The query result is not empty