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:
None 
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
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E2 (Low)

[11 Nov 2010 11:01] Ole John Aske
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.
[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.