Mark constant 'null_tables' with table->const_table=1

This was done to make thing consistent. It gives the additional benefit
that EXPLAIN EXTENDED now treat null_tables like constant's and replaces
columns with NULL, in a similar way that it replaces columns with constants
for constant tables.

- Null tables are tables where all columns are always NULL. The most common
  NULL TABLE is a table used in a LEFT_JOIN that is never true.
- All result changes comes from replacing columns with NULL for null_tables.
- "Impossible where" is now also shows constants for const columns.
- Removed duplicated s->type= JT_CONST
- Reset found_const_table_map when JOIN is created (safety fix)

10 files changed, 15 insertions, 11 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 80aef096828..61886b2be98 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2284,7 +2284,7 @@ explain extended select * from t2 where (b > 25 and b < 15) or (a>55 and a<44);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 0
+Note	1003	select 44 AS `a`,15 AS `b`,NULL AS `c` from `test`.`t2` where 0
 drop table t1,t2;
 #
 # Start of 10.1 tests
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index b0672eed54e..f6c523f9900 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -2286,7 +2286,7 @@ explain extended select * from t2 where (b > 25 and b < 15) or (a>55 and a<44);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 0
+Note	1003	select 44 AS `a`,15 AS `b`,NULL AS `c` from `test`.`t2` where 0
 drop table t1,t2;
 #
 # Start of 10.1 tests
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index c7156ddae91..1c1758c83b8 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5482,7 +5482,7 @@ WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+Note	1003	select 1 AS `i1`,8 AS `j1`,NULL AS `i2` from `test`.`t2` where 0
 SELECT * FROM t1, t2 
 WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
 i1	j1	i2
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 92be057c62c..fdfb472a32e 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5493,7 +5493,7 @@ WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+Note	1003	select 1 AS `i1`,8 AS `j1`,NULL AS `i2` from `test`.`t2` where 0
 SELECT * FROM t1, t2 
 WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
 i1	j1	i2
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index c7156ddae91..1c1758c83b8 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5482,7 +5482,7 @@ WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+Note	1003	select 1 AS `i1`,8 AS `j1`,NULL AS `i2` from `test`.`t2` where 0
 SELECT * FROM t1, t2 
 WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
 i1	j1	i2
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 7400dff3958..cdb210ae2d9 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -784,7 +784,7 @@ select * from t1 where a < 1 and a > 7;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+Note	1003	select 7 AS `a` from `test`.`t1` where 0
 select * from t1 where a < 1 and a > 7;
 a
 drop table t1;
@@ -1508,7 +1508,7 @@ select * from t2 where col1 < 'b' and col1 > 'd';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where 0
+Note	1003	select 'd' AS `col1` from `test`.`t2` where 0
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 960a873c854..47d2e6fd554 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -791,7 +791,7 @@ select * from t1 where a < 1 and a > 7;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+Note	1003	select 7 AS `a` from `test`.`t1` where 0
 select * from t1 where a < 1 and a > 7;
 a
 drop table t1;
@@ -1518,7 +1518,7 @@ select * from t2 where col1 < 'b' and col1 > 'd';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where 0
+Note	1003	select 'd' AS `col1` from `test`.`t2` where 0
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc
index 390afc575ca..ef9b07cca47 100644
--- a/sql/opt_table_elimination.cc
+++ b/sql/opt_table_elimination.cc
@@ -1810,6 +1810,7 @@ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl)
     {
       DBUG_PRINT("info", ("Eliminated table %s", table->alias.c_ptr()));
       tab->type= JT_CONST;
+      tab->table->const_table= 1;
       join->eliminated_tables |= table->map;
       join->const_table_map|= table->map;
       set_position(join, join->const_tables++, tab, (KEYUSE*)0);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1a29dcb62ab..fb8698033e1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4198,6 +4198,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
               keyuse->val->is_null() && keyuse->null_rejecting)
           {
             s->type= JT_CONST;
+            s->table->const_table= 1;
             mark_as_null_row(table);
             found_const_table_map|= table->map;
 	    join->const_table_map|= table->map;
@@ -4303,6 +4304,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 	        s->type= JT_CONST;
 	        join->const_table_map|=table->map;
 	        set_position(join,const_count++,s,start_keyuse);
+                /* create_ref_for_key will set s->table->const_table */
 	        if (create_ref_for_key(join, s, start_keyuse, FALSE,
 				       found_const_table_map))
                   goto error;
@@ -4508,12 +4510,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 	join->const_table_map|= s->table->map;
 	set_position(join,const_count++,s,(KEYUSE*) 0);
 	s->type= JT_CONST;
+        s->table->const_table= 1;
 	if (*s->on_expr_ref)
 	{
 	  /* Generate empty row */
 	  s->info= ET_IMPOSSIBLE_ON_CONDITION;
 	  found_const_table_map|= s->table->map;
-	  s->type= JT_CONST;
 	  mark_as_null_row(s->table);		// All fields are NULL
 	}
       }
@@ -19533,6 +19535,7 @@ join_read_system(JOIN_TAB *tab)
     {
       if (error != HA_ERR_END_OF_FILE)
 	return report_error(table, error);
+      table->const_table= 1;
       mark_as_null_row(tab->table);
       empty_record(table);			// Make empty record
       return -1;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index c1e5a9f95ce..95e9e943c26 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1436,7 +1436,7 @@ public:
     table_count= 0;
     top_join_tab_count= 0;
     const_tables= 0;
-    const_table_map= 0;
+    const_table_map= found_const_table_map= 0;
     aggr_tables= 0;
     eliminated_tables= 0;
     join_list= 0;