[MDEV-5034] Wrong result on LEFT JOIN with a SELECT SQ or a merge view, UNION in IN subquery Created: 2013-09-18  Updated: 2013-10-11  Resolved: 2013-10-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.33, 5.3.12
Fix Version/s: 5.5.34, 10.0.6, 5.3.13

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

Issue Links:
Relates

 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.

 Comments   
Comment by Elena Stepanova [ 2013-10-09 ]

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 ) );

Comment by Oleksandr Byelkin [ 2013-10-09 ]

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.

Comment by Oleksandr Byelkin [ 2013-10-09 ]

Above maybe wrong because mark_as_null_row was really called twice for both tables.

Comment by Oleksandr Byelkin [ 2013-10-10 ]

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.

Comment by Oleksandr Byelkin [ 2013-10-10 ]

mark_as_null_row applied to both tables under left join when sub_select drop flag only for one table

Comment by Oleksandr Byelkin [ 2013-10-10 ]

duplicate of MDEV-5107

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