Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
-
Red Hat Enterprise Linux Server release 7.4 (Maipo)
Description
sql in question :
select ifnull(min(true), false) asv from dual WHERE exists |
(select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i |
ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890'); |
Inner select ( select 1 ... ) returns empty result set.
When xepic_episode3 contains 24 rows of key '1234567890' then ifnull returns expected 0.
When adding one row to xepic_episode3 same key e.cpr, inner sql still returns empty result set, but ifnull returns 1
0 is expected, ( inner select : select 1 ... still returns empty result set )
when deleting the added row, ifnull returns correctly 0 again.
to show issue, run :
cre_tab_ano.sql – create to tables with data, testet with innodb
sel_ano.sql – expected return 1
add_row_ano.sql – add row
commit;
sel_ano.sql – unexpected returns 0
del_ano.sql – delete 25. row
commit;
sel_ano.sql – expected returns 1
===
in details running files :
MariaDB [camel]> source cre_tab_ano.sql
|
--------------
|
create table xintegration_mapping4 (fromvalue1 varchar(50), tovalue1 varchar(50))
|
--------------
|
|
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
--------------
|
insert into xintegration_mapping4 values ('1314', '1084')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xintegration_mapping4 values ('1330', '1084')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xintegration_mapping4 values ('1401', '1084')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xintegration_mapping4 values ('R580', '1084')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
create table xepic_episode3 (cpr varchar(50), rowno varchar(50), hospitalcode varchar(50))
|
--------------
|
|
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '1', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '2', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '3', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '4', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '5', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '6', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '7', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '8', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '9', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '10', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '11', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '12', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '13', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '14', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '15', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '16', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '17', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '18', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '19', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '20', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '21', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '22', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '23', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
--------------
|
insert into xepic_episode3 values ('1234567890', '24', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [camel]> source sel_ano.sql
|
--------------
|
select ifnull(min(true), false) asv from dual WHERE exists
|
(select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890')
|
--------------
|
|
|
+-----+
|
| asv |
|
+-----+
|
| 0 |
|
+-----+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [camel]> source add_row_ano.sql
|
--------------
|
insert into xepic_episode3 values ('1234567890', '25', '1330')
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [camel]> commit;
|
--------------
|
commit
|
--------------
|
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [camel]> source sel_ano.sql
|
--------------
|
select ifnull(min(true), false) asv from dual WHERE exists
|
(select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890')
|
--------------
|
|
|
+-----+
|
| asv |
|
+-----+
|
| 1 |
|
+-----+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [camel]> select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
-> ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890';
|
--------------
|
select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890'
|
--------------
|
|
|
Empty set (0.00 sec)
|
|
|
MariaDB [camel]> source del_ano.sql
|
--------------
|
delete from xepic_episode3
|
where rowno = '25'
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
|
|
MariaDB [camel]> commit;
|
--------------
|
commit
|
--------------
|
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [camel]> select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
-> ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890'
|
-> ;
|
--------------
|
select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890'
|
--------------
|
|
|
Empty set (0.00 sec)
|
|
|
MariaDB [camel]> source sel_ano.sql
|
--------------
|
select ifnull(min(true), false) asv from dual WHERE exists
|
(select 1 from xEpic_Episode3 e JOIN xIntegration_Mapping4 i
|
ON i.fromValue1 = e.hospitalCode and i.toValue1 = 'NoRowHasThisValueInToValue1' where e.cpr = '1234567890')
|
--------------
|
|
|
+-----+
|
| asv |
|
+-----+
|
| 0 |
|
+-----+
|
1 row in set (0.01 sec)
|
|
|
MariaDB [camel]>
|