bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3370)

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped

 3370 Ole John Aske	2010-11-16
      SPJ-scan-scan: Added handling and testcase for JT_SYSTEM optimized tables:
      
      A JT_SYSTEM table has already been read, or determined to contain no rows, by the optimizer.
      This table should therefore be excluded from pushability evaluation in ha_ndbcluster.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/abstract_query_plan.cc
      sql/abstract_query_plan.h
      sql/ha_ndbcluster.cc
      sql/sql_select.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-11-15 14:38:45 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-11-16 13:26:19 +0000
@@ -4519,6 +4519,25 @@ k	uq	k	uq
 9	1	3	NULL
 9	1	9	1
 drop table t;
+create table t (k int primary key, uq int) engine = ndb;
+insert into t values (1,3), (3,NULL), (6,9), (9,1);
+explain extended
+select * from t as a left join t as b 
+on a.k is null and a.uq=b.uq;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	b	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	4	50.00	
+Warnings:
+Note	1644	Table 'b' was const-table optimized, no runtime access required
+Note	1003	select `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,NULL AS `k`,NULL AS `uq` from `test`.`t` `a` left join `test`.`t` `b` on((isnull(`test`.`a`.`k`) and (`test`.`a`.`uq` = NULL)))
+select * from t as a left join t as b 
+on a.k is null and a.uq=b.uq;
+k	uq	k	uq
+1	3	NULL	NULL
+3	NULL	NULL	NULL
+6	9	NULL	NULL
+9	1	NULL	NULL
+drop table t;
 create table tc(
 a varchar(10) not null,
 b varchar(10),
@@ -4611,7 +4630,7 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2029
+2035
 pruned_scan_count
 8
 sorted_scan_count

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-11-15 14:38:45 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-11-16 13:26:19 +0000
@@ -2915,6 +2915,24 @@ select straight_join * from t as a join 
 
 drop table t;
 
+########
+# JT_SYSTEM testcase, 'a.k is null' is known 'false' ->
+# Join condition will always fail, and all 'left joins' can be NULL complemented wo/
+# even requiring to access left table (b) which becomes 'system' -> No pushed joins !
+########
+create table t (k int primary key, uq int) engine = ndb;
+insert into t values (1,3), (3,NULL), (6,9), (9,1);
+
+explain extended
+select * from t as a left join t as b 
+  on a.k is null and a.uq=b.uq;
+
+--sorted_result
+select * from t as a left join t as b 
+  on a.k is null and a.uq=b.uq;
+
+drop table t;
+
 #######
 # Test of varchar query parameteres.
 #######

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2010-11-15 14:38:45 +0000
+++ b/sql/abstract_query_plan.cc	2010-11-16 13:26:19 +0000
@@ -309,6 +309,11 @@ namespace AQP
     */
     switch (join_tab->type)
     {
+    case JT_SYSTEM:
+      DBUG_PRINT("info", ("Operation %d is const-optimized.", m_tab_no));
+      m_access_type= AT_FIXED;
+      break;
+
     case JT_EQ_REF:
     case JT_CONST:
       m_index_no= join_tab->ref.key;

=== modified file 'sql/abstract_query_plan.h'
--- a/sql/abstract_query_plan.h	2010-11-11 13:07:41 +0000
+++ b/sql/abstract_query_plan.h	2010-11-16 13:26:19 +0000
@@ -143,6 +143,8 @@ namespace AQP
   {
     /** For default initialization.*/
     AT_VOID,
+    /** Value has already been fetched / determined by optimizer.*/
+    AT_FIXED,
     /** Do a lookup of a single primary key.*/
     AT_PRIMARY_KEY,
     /** Do a lookup of a single unique index key.*/

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2010-11-15 11:08:25 +0000
+++ b/sql/ha_ndbcluster.cc	2010-11-16 13:26:19 +0000
@@ -874,6 +874,12 @@ ndb_pushed_builder_ctx::init_pushability
       DBUG_ASSERT(false);
       break;
 
+    case AQP::AT_FIXED:
+      EXPLAIN_NO_PUSH("Table '%s' was const-table optimized, no runtime access required",
+                      table_access->get_table()->alias);
+      m_tables[i].m_maybe_pushable= 0;
+      break;
+
     case AQP::AT_OTHER:
       EXPLAIN_NO_PUSH("Table '%s' is not pushable: %s",
                       table_access->get_table()->alias, 
@@ -7545,6 +7551,9 @@ int ha_ndbcluster::index_read_last(uchar
   This is actually (yet) never called for ndbcluster tables, as these table types
   does not set HA_STATS_RECORDS_IS_EXACT.
 
+  UPDATE: Might be called if the predicate contain '<column> IS NULL', and
+          <column> is defined as 'NOT NULL' (or is part of primary key)
+
   Implemented regardless of this as the default implememtation would break 
   any pushed joins as it calls ha_rnd_end() / ha_index_end() at end of execution.
   */

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-21 08:31:51 +0000
+++ b/sql/sql_select.cc	2010-11-16 13:26:19 +0000
@@ -11973,7 +11973,7 @@ join_read_system(JOIN_TAB *tab)
 					   table->s->primary_key)))
     {
       if (error != HA_ERR_END_OF_FILE)
-	return report_error(table, error);
+	DBUG_RETURN(report_error(table, error));
       mark_as_null_row(tab->table);
       empty_record(table);			// Make empty record
       DBUG_RETURN(-1);


Attachment: [text/bzr-bundle] bzr/[e-mail]