[MDEV-14515] ifnull result depends on number of rows in joined table. Created: 2017-11-27  Updated: 2018-06-11  Resolved: 2018-04-17

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.60

Type: Bug Priority: Major
Reporter: steen bartholdy Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

Red Hat Enterprise Linux Server release 7.4 (Maipo)

Attachments: Text File add_row_ano.sql     Text File cre_tab_ano.sql     Text File del_ano.sql     Text File sel_ano.sql    

 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]>



 Comments   
Comment by Alice Sherepa [ 2017-11-28 ]

Thanks for the report!
The problem is reproducible on MariaDB 5.5-10.2

testcase:

create table t1 (i1 int, i2 int);
 
insert into t1 values (1314, 1084),(1330, 1084),(1401, 1084),(580, 1084);
 
 
 
create table t2 (cd int);
 
insert into t2 values (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330);
 
insert into t2 values (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330);
 
insert into t2 values (1330), (1330), (1330), (1330);
 
 
 
select  IF(NULL,0,min(1)) from dual  
where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
 
 
 
explain extended 
select  IF(NULL,0,min(1)) from dual  
where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
 
 
 
insert into t2 values (1330);
 
 
 
select  IF(NULL,0,min(1)) from dual  
where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
 
 
 
explain extended 
select  IF(NULL,0,min(1)) from dual  
where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);


select  IF(NULL,0,min(1)) from dual  
where 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 extended 
select  IF(NULL,0,min(1)) from dual  
where 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 0
 
 
 
insert into t2 values (1330);
 
Query OK, 1 row affected (0.04 sec)
 
 
 
select  IF(NULL,0,min(1)) from dual  
where 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 extended 
select  IF(NULL,0,min(1)) from dual  
where 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))

Comment by Igor Babaev [ 2018-04-17 ]

A fix for this bug was pushed into 5.5.
It should be merged upstream as it is.

Generated at Thu Feb 08 08:14:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.