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]>
|
Attachments
Activity
A fix for this bug was pushed into 5.5.
It should be merged upstream as it is.
Thanks for the report!
The problem is reproducible on MariaDB 5.5-10.2
testcase:
explain extendedexplain extendedselect IF(NULL,0,min(1)) from dualwhere exists (select 1 from t2 join t1 on t1.i1 = t2.cd and t1.i2 = 345);+-------------------+| IF(NULL,0,min(1)) |+-------------------+| NULL |+-------------------+1 row in set (0.00 sec)explain extendedselect IF(NULL,0,min(1)) from dualwhere exists (select 1 from t2 join t1 on t1.i1 = t2.cd and t1.i2 = 345);+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE || 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where || 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using where; Using join buffer (flat, BNL join) |+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+3 rows in set, 1 warning (0.00 sec)Note (Code 1003): select if(NULL,0,min(1)) AS `IF(NULL,0,min(1))` where 0insert into t2 values (1330);Query OK, 1 row affected (0.04 sec)select IF(NULL,0,min(1)) from dualwhere exists (select 1 from t2 join t1 on t1.i1 = t2.cd and t1.i2 = 345);+-------------------+| IF(NULL,0,min(1)) |+-------------------+| 1 |+-------------------+1 row in set (0.00 sec)explain extendedselect IF(NULL,0,min(1)) from dualwhere exists (select 1 from t2 join t1 on t1.i1 = t2.cd and t1.i2 = 345);+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where || 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 25 | 100.00 | Using where; Using join buffer (flat, BNL join) |+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+3 rows in set, 1 warning (0.00 sec)Note (Code 1003): select if(NULL,0,min(1)) AS `IF(NULL,0,min(1))` from DUAL where <in_optimizer>(1,exists(select 1 from `test`.`t2` join `test`.`t1` where `test`.`t2`.`cd` = `test`.`t1`.`i1` and `test`.`t1`.`i2` = 345))