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

Wrong result upon select from a view with a FROM subquery

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • N/A
    • Views
    • None
    • a6c0c01cec43731df3c58b4fdb68d3ac18e6c699
    • 10.2.0-7

    Description

      DROP VIEW IF EXISTS v1;
      DROP TABLE IF EXISTS t1, t2, t3;
       
      CREATE TABLE t1 (i INT);
      INSERT INTO t1 VALUES (3),(2);
       
      CREATE TABLE t2 (j INT);
      INSERT INTO t2 VALUES (8),(3),(3);
       
      CREATE TABLE t3 (k INT);
      INSERT INTO t3 VALUES (1),(8);
       
      SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j )  ;
       
      CREATE OR REPLACE VIEW v1 AS 
      SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j )  ;
       
      SELECT * FROM v1; 
      

      Results

      MariaDB [test]> SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j )  ;
      +------+------+
      | i    | j    |
      +------+------+
      |    3 | NULL |
      |    2 | NULL |
      +------+------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE OR REPLACE VIEW v1 AS 
          -> SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j )  ;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> SELECT * FROM v1; 
      +------+------+
      | i    | j    |
      +------+------+
      |    3 |    3 |
      |    3 |    3 |
      |    2 | NULL |
      +------+------+
      3 rows in set (0.00 sec)
      

      SELECT produces a correct result, view does not.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.