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

Select on a view return unexpected result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.1.29, 10.0, 5.5
    • Fix Version/s: 5.5
    • Labels:
      None
    • Environment:
      Linux 4.16.0 Debian x86_64 GNU/Linux

      Description

      Test case:

      create table t1 (id int primary key);
      create table t2 (id int primary key);
      insert t1 values (1),(2),(3);
      insert t2 values (1);
      create view t1_view as select a.id from t1 a join t2 order by a.id;
       
      MariaDB [tmp]> select count(*) from t1_view where id not in (select id from t1);
      +----------+
      | count(*) |
      +----------+
      |        3 |
      +----------+
      1 row in set (0.00 sec)
       
      MariaDB [tmp]> select count(*) from t1_view a where not exists (select id from t1 b where a.id=b.id);
      +----------+
      | count(*) |
      +----------+
      |        3 |
      +----------+
      1 row in set (0.00 sec)
      

      These results should be 0. The following runs correctly.

      MariaDB [tmp]> select count(*) from t1_view where 0+id not in (select id from t1);
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.00 sec)
       
      MariaDB [tmp]> select count(*) from t1_view a where not exists (select id from t1 b where 0+a.id=b.id);
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            sycxyc sycxyc
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: