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: 5.5, 10.0, 10.1, 10.2, 10.3, 10.1.29
    • 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: