| Bug #58134 | Incorrectly condition pushdown inside subquery to NDB engine | ||
|---|---|---|---|
| Submitted: | 11 Nov 2010 11:01 | Modified: | 29 Jan 2011 23:14 |
| Reporter: | Ole John Aske | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.51-ndb-7.1.9 | OS: | Any |
| Assigned to: | Ole John Aske | CPU Architecture: | Any |
[18 Nov 2010 23:16]
Sveta Smirnova
Thank you for the report. Verified as described.
[19 Nov 2010 10:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124392 3474 Ole John Aske 2010-11-19 Fix for bug#58134, Incorrectly condition pushdown inside subquery to NDB engine. An incorrect 'table_map' containing both the table itself, and possible any outer-refs if this was the last table in the subquery, was presented to make_cond_for_table(). As a pushed condition is only able to refer column from the table the condition is pushed to, nothing else than columns from the table itself (tab->table->map) may be refered in the pushed condition constructed by 'push_cond= make_cond_for_table()'. Also fix a minor 'copy and paste' bug in a comment inside make_cond_for_table().
[19 Nov 2010 10:51]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124413 3374 Ole John Aske 2010-11-19 SPJ-scan-scan: Cherry picked proposed fix for bug#58134: Incorrectly condition pushdown inside subquery to NDB engine into SPJ branch. Testblocker, prevented extensive RQG testing with subqueries.
[1 Dec 2010 19:40]
Ole John Aske
>>triage: It is not clear if this bug is NDB or relates to the optimizer push down that is >>planed for a future MySQL release - assuming the second setting to SRFEATURE This is about the 'engine_condition_pushdown' which has been in the telco releases since telco-6.3 (-> Several years). So, this bug *is* in production code and may affect current customers using the NDB storage engine. Request retriage as I think SRFEATURE is incorrect.
[6 Jan 2011 15:55]
Roy Lyseng
This is a fix for Engine Condition Pushdown for the NDB. The risk for the server is about zero, as this concerns the Cluster product only. Asking for 5.5 MRU tag, as this will ease port of Cluster to 5.5.
[13 Jan 2011 8:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128599 3235 Ole John Aske 2011-01-13 Fix for bug#58134: 'Incorrectly condition pushdown inside subquery to NDB engine' An incorrect 'table_map' containing both the table itself, and possible any outer-refs if this was the last table in the subquery, was presented to make_cond_for_table(). As a pushed condition is only able to refer column from the table the condition is pushed to, nothing else than columns from the table itself (tab->table->map) may be refered in the pushed condition constructed by 'push_cond= make_cond_for_table()'. Also fix a minor 'copy and paste' bug in a comment inside make_cond_for_table(). No testcase is possible on mainbranch as the NDB engine is not available (yet) on mysql >= 5.5
[13 Jan 2011 9:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128602 3496 Ole John Aske 2011-01-13 [merge] Merged fix for bug#58134 from mysql-5.5 -> mysql->trunk
[13 Jan 2011 9:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128608 3236 Ole John Aske 2011-01-13 Fix for bug#58134: 'Incorrectly condition pushdown inside subquery to NDB engine' An incorrect 'table_map' containing both the table itself, and possible any outer-refs if this was the last table in the subquery, was presented to make_cond_for_table(). As a pushed condition is only able to refer column from the table the condition is pushed to, nothing else than columns from the table itself (tab->table->map) may be refered in the pushed condition constructed by 'push_cond= make_cond_for_table()'. Also fix a minor 'copy and paste' bug in a comment inside make_cond_for_table(). No testcase is possible on mainbranch as the NDB engine is not available (yet) on mysql >= 5.5
[13 Jan 2011 9:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128609 3497 Ole John Aske 2011-01-13 [merge] Merged fix for bug#58134 from mysql-5.5 -> mysql->trunk
[13 Jan 2011 9:24]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:ole.john.aske@oracle.com-20110113092345-ck2wv0z48r6d1ylz) (version source revid:ole.john.aske@oracle.com-20110113092345-ck2wv0z48r6d1ylz) (merge vers: 5.6.2) (pib:24)
[13 Jan 2011 9:24]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:ole.john.aske@oracle.com-20110113092045-88ve30e8uh2bbrlo) (version source revid:ole.john.aske@oracle.com-20110113092045-88ve30e8uh2bbrlo) (merge vers: 5.5.9) (pib:24)
[13 Jan 2011 9:25]
Ole John Aske
Pushed to mysql-5.5 and mysql-trunk
[14 Jan 2011 10:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/128716 4107 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for bug#58134: 'Incorrectly condition pushdown inside subquery to NDB engine' An incorrect 'table_map' containing both the table itself, and possible any outer-refs if this was the last table in the subquery, was presented to make_cond_for_table(). As a pushed condition is only able to refer column from the table the condition is pushed to, nothing else than columns from the table itself (tab->table->map) may be refered in the pushed condition constructed by 'push_cond= make_cond_for_table()'. Also fix a minor 'copy and paste' bug in a comment in make_cond_for_table().
[14 Jan 2011 10:43]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:ole.john.aske@oracle.com-20110114104253-ojmo06ia9rjdvqkp) (version source revid:ole.john.aske@oracle.com-20110114104253-ojmo06ia9rjdvqkp) (merge vers: 5.1.51-ndb-7.0.21) (pib:24)
[22 Jan 2011 22:44]
Paul DuBois
Noted in 5.1.51-ndb-7.0.21, 5.5.9, 5.6.2 changelogs. Condition pushdown optimization could push down conditions with incorrect column references.
Description: The optimizer seems to incorrectly analyze which tables a condition refers inside a subquery condition. This cause conditions to be pushed which does not even refer columns from the table they are pushed onto. In the subquery condition from 'How to repeat', the condition 'subq.i=3 and subq.i=t.i' is first (correctly) const transformed into: 'subq.i=3 and subq.i=t.i' ==use 'subq.i=3'==> 'subq.i=3 and 3=t.i' As reported from 'explain extended' it then push the entire condition into table 'subq' even if it refers table 't' which is incorrect.: Using where with pushed condition: ((`test`.`subq`.`i` = 3) and (3 = `test`.`t`.`i`)) This seems to be related to 'subq' being an alias for t. However, even if both 'subq' and 't' refers the same physical table, 'subq' is another instance of table t and pushing the entire condition is incorrect. If we change the testcase to make 'subq' a separate table, the conditions is correctly analyzed to be non pushable: .................... create table t (pk int, i int) engine = ndb; insert into t values (1,3), (3,6), (6,9), (9,1); create table subq (pk int, i int) engine = ndb; insert into subq values (1,3), (3,6), (6,9), (9,1); set engine_condition_pushdown=1; explain extended select * from t where exists (select * from subq where subq.i=3 and subq.i=t.i); ---- > No pushed condition is reported How to repeat: drop table t; create table t (pk int, i int) engine = ndb; insert into t values (1,3), (3,6), (6,9), (9,1); eset engine_condition_pushdown=1; xplain extended select * from t where exists (select * from t as subq where subq.i=3 and subq.i=t.i); +----+--------------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------------------------------------------------------------+ | 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 4 | 50.00 | Using where | | 2 | DEPENDENT SUBQUERY | subq | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where with pushed condition: ((`test`.`subq`.`i` = 3) and (3 = `test`.`t`.`i`)) | +----+--------------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------------------------------------------------------------+ Executing the same query with 'engine_condition_pushdown=1' also return an incorrect result.