Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.4, 5.5.33, 5.3.12
-
None
-
None
Description
The problem appeared on 5.3 tree with the following revision:
revno: 3574
|
revision-id: sanja@montyprogram.com-20120905202358-r2qds8sj178cbjkn
|
committer: sanja@montyprogram.com
|
timestamp: Wed 2012-09-05 23:23:58 +0300
|
message:
|
MDEV-486 LP BUG#1010116 fix.
|
|
|
Link view/derived table fields to a real table to check turning the table record to null row.
|
|
|
Item_direct_view_ref wrapper now checks if table is turned to null row.
|
CREATE TABLE t1 (state VARCHAR(32), INDEX(state)); |
INSERT INTO t1 VALUES ('Indiana'),('Vermont'); |
|
|
CREATE TABLE t2 (state VARCHAR(32)); |
INSERT INTO t2 VALUES ('Hawaii'),('Oregon'),('Vermont'); |
|
|
CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* FROM t2, t1; |
|
|
SELECT * FROM t1 AS outer_t1 LEFT JOIN v1 AS joined_t1 |
ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION SELECT 'Florida' ) ); |
Actual result:
state state
|
Indiana NULL
|
Vermont NULL
|
Expected result:
state state
|
Indiana NULL
|
Vermont Vermont
|
Vermont Vermont
|
Vermont Vermont
|
Also reproducible with a subquery instead of the view, in this case derived_merge=on is required.
With a TEMPTABLE view, the result is correct.
With a SELECT subquery and derived_merge=off, the result is correct.
MariaDB 5.2 and MySQL 5.6 return the correct result.