[MDEV-5369] Wrong result (0 instead of NULL) on 2nd execution of PS with LEFT JOIN, TEMPTABLE view Created: 2013-12-01  Updated: 2014-01-29  Resolved: 2014-01-29

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.34, 10.0.6
Fix Version/s: 5.5.35, 10.0.8

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

Issue Links:
Relates

 Description   

The problem appeared on the 5.5 tree with the following revision:

revno: 3922 [merge]
 
revision-id: sanja@askmonty.org-20130925141613-wyi3pdd0viulaxtd
 
parent: wlad@montyprogram.com-20130924175251-2wvjo1i166cacwam
 
parent: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn
 
committer: sanja@askmonty.org
 
branch nick: work-maria-5.5-merge-5.3
 
timestamp: Wed 2013-09-25 17:16:13 +0300
 
message:
 
  merge 5.3 -> 5.5
 
    ------------------------------------------------------------
 
    revno: 2502.567.143
 
    revision-id: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn
 
    parent: bar@mnogosearch.org-20130916120355-atoza9gj16selqtp
 
    committer: sanja@askmonty.org
 
    branch nick: work-maria-5.3-MDEV-5039
 
    timestamp: Wed 2013-09-25 15:30:13 +0300
 
    message:
 
      MDEV-5039: incorrect Item_func_regex::update_used_tables()
 
      
      Other fix of maybe_null problem and revert of revno: 3608 "MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery from a MERGE view."

However it doesn't seem to be reproducible on 5.3 tree after the merged revision.

Test case:

CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (0),(8);
 
 
 
CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
 
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
 
 
 
SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
 
 
 
PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
 
EXECUTE stmt; 
EXECUTE stmt;
 
DEALLOCATE PREPARE stmt;
 
 
 
DROP VIEW v2;
 
DROP TABLE t1, t2;

Actual result:

SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
 
SUM(pk)
 
NULL
 
PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
 
EXECUTE stmt;
 
SUM(pk)
 
NULL
 
EXECUTE stmt;
 
SUM(pk)
 
0

Expected result:

SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
 
SUM(pk)
 
NULL
 
PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
 
EXECUTE stmt;
 
SUM(pk)
 
NULL
 
EXECUTE stmt;
 
SUM(pk)
 
NULL



 Comments   
Comment by Oleksandr Byelkin [ 2013-12-11 ]

0 caused by Item_sum_sum::add call on second execution where argument is not NULL.

Comment by Oleksandr Byelkin [ 2013-12-11 ]

difference between 5.5. and 5.3 is that in sub_select() join_tab->last_inner set for 5.5

Comment by Oleksandr Byelkin [ 2013-12-11 ]

Above happened because outer_join of the temporary table become JOIN_TYPE_LEFT

Comment by Oleksandr Byelkin [ 2013-12-11 ]

it is different optimisations on different executions:
PREPARE stmt FROM "explain SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
2 DERIVED t2 system NULL NULL NULL NULL 0 const row not found
DEALLOCATE PREPARE stmt;

Comment by Oleksandr Byelkin [ 2014-01-22 ]

table->pos_in_table_list->outer_join in join_read_const_table() fot t2 differ on second execution

Comment by Oleksandr Byelkin [ 2014-01-22 ]

The problem is in JOIN_TYPE_OUTER

Comment by Oleksandr Byelkin [ 2014-01-22 ]

The problem is that JOIN_TYPE_OUTER set on every execution but simplifying joins (outer to inner) ony once.

Comment by Oleksandr Byelkin [ 2014-01-22 ]

sent for review. I think it should be fixed in 5.3 even without test suite.

Comment by Sergei Petrunia [ 2014-01-28 ]

.. Patch approved. When I run the testcase on the latest 5.3, the bug is reproducible. (It is not reproducible if 5.3 is earlier than the fix for MDEV-5039)

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