Details
Major
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
To find table on which we should check NULL Item_direct_view_ref::check_null_ref gets the left most real table of the VIEW or derived table (get_real_join_table()) and get unused table which probably do net update status correctly (if change order of the tables in the view or in the derived table bug will disappear).
The problem now how to find correct table in the all leaf tables of the view or of the derived table.
Above maybe wrong because mark_as_null_row was really called twice for both tables.
The problem is that reading first record (by index) in the left part of left join returns no more record (as if table is empty). Everything else goes from this.
mark_as_null_row applied to both tables under left join when sub_select drop flag only for one table
duplicate of MDEV-5107
Here is another test case, it's very similar to the initial one, but might be somewhat simpler as it doesn't require a subquery with UNION under IN:
CREATE TABLE t1 (a INT, b VARCHAR(1), INDEX(b,a));
INSERT INTO t1 VALUES (4,'p'),(1,'q'),(9,'w');
CREATE TABLE t2 (c VARCHAR(1), INDEX(c));
INSERT INTO t2 VALUES ('q'),('a');
CREATE ALGORITHM=MERGE VIEW v AS SELECT t1a.* FROM t1, t1 AS t1a;
SELECT * FROM t2 LEFT JOIN v ON ( c=b AND a IN ( 1,6 ) );
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v AS SELECT t1a.* FROM t1, t1 AS t1a;
SELECT * FROM t2 LEFT JOIN v ON ( c=b AND a IN ( 1,6 ) );