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

Select on a view return unexpected result

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.1.29, 10.2, 10.3
    • 5.5
    • None
    • 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

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

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.