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

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

    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

              • Assignee:
                varun Varun Gupta
                Reporter:
                varun Varun Gupta
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: