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

Incorrect results when using BNLH join instead of BNL join with views

    XMLWordPrintable

Details

    Description

      Here is the test case

      the dataset:

      CREATE TABLE t1 (c1 text, c2 int);
      INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
      CREATE TABLE t2 (c1 text, c2 int);
      INSERT INTO t2 VALUES ('b',2), ('c',3);
      CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
      

      Now i run the query

      MariaDB [test]> SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
      +------+------+------+------+
      | c1   | c2   | c1   | c2   |
      +------+------+------+------+
      | c    |    3 | c    |    3 |
      | c    |    3 | c    |    3 |
      +------+------+------+------+
      2 rows in set (0.006 sec)
       
      MariaDB [test]> set @@join_cache_level=4;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
      +------+------+------+------+
      | c1   | c2   | c1   | c2   |
      +------+------+------+------+
      | a    |    1 | b    |    2 |
      | a    |    1 | c    |    3 |
      | c    |    3 | b    |    2 |
      | c    |    3 | c    |    3 |
      | g    |    7 | b    |    2 |
      | g    |    7 | c    |    3 |
      | d    |    4 | b    |    2 |
      | d    |    4 | c    |    3 |
      | c    |    3 | b    |    2 |
      | c    |    3 | c    |    3 |
      +------+------+------+------+
      10 rows in set (0.005 sec)
      

      So we see here that raising the value of join_cache_level from 2 to 4 gives incorrect result

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.