Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14515

ifnull result depends on number of rows in joined table.

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: 5.5.60
    • Labels:
      None
    • Environment:
      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

        1. add_row_ano.sql
          0.1 kB
        2. cre_tab_ano.sql
          2 kB
        3. del_ano.sql
          0.0 kB
        4. sel_ano.sql
          0.3 kB

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              steenb steen bartholdy
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: