Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
difference between 5.5. and 5.3 is that in sub_select() join_tab->last_inner set for 5.5
Above happened because outer_join of the temporary table become JOIN_TYPE_LEFT
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;
table->pos_in_table_list->outer_join in join_read_const_table() fot t2 differ on second execution
The problem is in JOIN_TYPE_OUTER
The problem is that JOIN_TYPE_OUTER set on every execution but simplifying joins (outer to inner) ony once.
sent for review. I think it should be fixed in 5.3 even without test suite.
.. 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)
0 caused by Item_sum_sum::add call on second execution where argument is not NULL.