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

LP:823189 - Wrong result with NOT IN + RIGHT JOIN + views

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      The following query:

      SELECT * FROM v1, t2
      WHERE t2.a NOT IN (
              SELECT t3.b
              FROM t3
              RIGHT JOIN t4 ON ( t4.d = t3.d )
              WHERE t4.d >= v1.d
      );

      returns 2 rows even though the same query using base table instead of a view v1 returns no rows. PostgreSQL also returns no rows in both cases.

      test case:

      CREATE TABLE t1 ( d varchar(32) , e int );
      INSERT INTO t1 VALUES ('y',0),('w',0);

      CREATE TABLE t2 ( a int , b int , c int , d varchar(1), e varchar(1));
      INSERT INTO t2 VALUES (10,8,7,'b','b');

      CREATE TABLE t3 ( a int , b int , c int , d varchar(1), e varchar(1));

      CREATE TABLE t4 ( d varchar(32) , e int );
      INSERT INTO t4 VALUES ('y',0),('w',0);

      CREATE VIEW v1 AS SELECT * FROM t1;

      SELECT * FROM v1, t2
      WHERE t2.a NOT IN (
              SELECT t3.b
              FROM t3
              RIGHT JOIN t4 ON ( t4.d = t3.d )
              WHERE t4.d >= v1.d
      );

      Repeatable in maria-5.3, maria-5.2, mysql-5.5

      Attachments

        Activity

          People

            igor Igor Babaev
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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