[MDEV-9671] Wrong result upon select from a view with a FROM subquery Created: 2016-03-02  Updated: 2016-03-03  Resolved: 2016-03-03

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

a6c0c01cec43731df3c58b4fdb68d3ac18e6c699

Issue Links:
Blocks
blocks MDEV-3944 Allow derived tables in VIEWS Closed
Relates
relates to MDEV-9664 Test MDEV-3944 (Allow derived tables ... Closed
Sprint: 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.



 Comments   
Comment by Oleksandr Byelkin [ 2016-03-03 ]

revision-id: f072d1672a067da09e7a5dc18d2c227992f0d672 (mariadb-10.1.8-125-gf072d16)
parent(s): a6c0c01cec43731df3c58b4fdb68d3ac18e6c699
committer: Oleksandr Byelkin
timestamp: 2016-03-03 17:42:15 +0100
message:

MDEV-9671 Wrong result upon select from a view with a FROM subquery

do not take marker OIN_TYPE_OUTER as a LEFT JOIN on print

—

Generated at Thu Feb 08 07:36:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.