[Commits] de02bfd373a8bb668633a7bd50dfdd64864863ce The code that pushed conditions into derived did not take into account that the list of equal items in an Item_equal object may contain items with type() == REF_ITEM.

Igor Babaev igor at askmonty.org
Tue Sep 6 08:10:50 EEST 2016


commit de02bfd373a8bb668633a7bd50dfdd64864863ce
Author: Igor Babaev <igor at askmonty.org>
Commit: Igor Babaev <igor at askmonty.org>

    The code that pushed conditions into derived did not
    take into account that the list of equal items in
    an Item_equal object may contain items with type() == REF_ITEM.
---
 mysql-test/r/derived_view.result |   57 ++++++++++++++++++++++++++++++++------
 mysql-test/t/derived_view.test   |    4 +--
 sql/item.cc                      |   18 ++++++------
 3 files changed, 58 insertions(+), 21 deletions(-)

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 963fcf8..fb3509f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -513,8 +513,6 @@ f1	f11
 3	3
 5	5
 mat in merged derived join mat in merged derived
-set @save_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='condition_pushdown_for_derived=off';
 explain extended  select * from 
 (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
 join 
@@ -526,7 +524,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11`,`tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` join (select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `t1`.`f1`) `tt` where ((`tt`.`f1` = `tt`.`f1`) and (`tt`.`f1` > 2) and (`tt`.`f1` > 2))
+Note	1003	select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11`,`tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` > 2)) group by `test`.`t1`.`f1`) `tt` join (select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where ((`t1`.`f1` < 7) and (`t1`.`f1` > 2) and (`t1`.`f1` > 2)) group by `t1`.`f1`) `tt` where ((`tt`.`f1` = `tt`.`f1`) and (`tt`.`f1` > 2) and (`tt`.`f1` > 2))
 explain format=json  select * from 
 (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
 join 
@@ -553,7 +551,7 @@ EXPLAIN
                 "access_type": "ALL",
                 "rows": 11,
                 "filtered": 100,
-                "attached_condition": "(t1.f1 < 7)"
+                "attached_condition": "((t1.f1 < 7) and (t1.f1 > 2) and (t1.f1 > 2))"
               }
             }
           }
@@ -581,7 +579,7 @@ EXPLAIN
                 "access_type": "ALL",
                 "rows": 11,
                 "filtered": 100,
-                "attached_condition": "(t1.f1 < 7)"
+                "attached_condition": "((t1.f1 < 7) and (t1.f1 > 2) and (t1.f1 > 2))"
               }
             }
           }
@@ -606,11 +604,10 @@ Handler_read_key	2
 Handler_read_last	0
 Handler_read_next	2
 Handler_read_prev	0
-Handler_read_rnd	8
+Handler_read_rnd	4
 Handler_read_rnd_deleted	0
-Handler_read_rnd_next	39
+Handler_read_rnd_next	33
 flush status;
-set optimizer_switch=@save_optimizer_switch;
 merged in merged derived join merged in merged derived
 explain extended  select * from 
 (select * from 
@@ -857,9 +854,51 @@ explain extended select * from v6 join v7 on f2=f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
 1	PRIMARY	<derived5>	ref	key0	key0	5	test.t2.f2	2	100.00	
-5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using temporary; Using filesort
+5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` < 7) and (`test`.`t2`.`f2` in (2,3)))
+explain format=json select * from v6 join v7 on f2=f1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 11,
+      "filtered": 100,
+      "attached_condition": "((t2.f2 < 7) and (t2.f2 in (2,3)) and (t2.f2 is not null))"
+    },
+    "table": {
+      "table_name": "<derived5>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["f1"],
+      "ref": ["test.t2.f2"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 5,
+          "filesort": {
+            "sort_key": "t1.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(t1.f1 in (2,3))"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 select * from v6 join v7 on f2=f1;
 f2	f22	f1	f11
 3	3	3	3
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 8f7bdc0..e94ab70 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -143,8 +143,6 @@ select * from (select * from
   (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz;
 
 --echo mat in merged derived join mat in merged derived
-set @save_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='condition_pushdown_for_derived=off';
 explain extended  select * from 
  (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
 join 
@@ -164,7 +162,6 @@ join
  on x.f1 = z.f1;
 show status like 'Handler_read%';
 flush status;
-set optimizer_switch=@save_optimizer_switch;
 
 --echo merged in merged derived join merged in merged derived
 explain extended  select * from 
@@ -232,6 +229,7 @@ select * from (select * from v7 group by 1) tt;
 
 --echo join of above two
 explain extended select * from v6 join v7 on f2=f1;
+explain format=json select * from v6 join v7 on f2=f1;
 select * from v6 join v7 on f2=f1;
 
 --echo test two keys
diff --git a/sql/item.cc b/sql/item.cc
index 0ba922e..4544c24 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6800,12 +6800,12 @@ void Item_field::update_null_value()
     Item *item;
     while ((item=li++))
     {
-      if (item->used_tables() == map && item->type() == FIELD_ITEM)
+      if (item->used_tables() == map && item->real_item()->type() == FIELD_ITEM)
       {
 	Item_ref *rf= 
 	  new (thd->mem_root) Item_ref(thd, &sl->context, 
 				       NullS, NullS,
-				     ((Item_field*) item)->field_name);
+			      ((Item_field*) (item->real_item()))->field_name);
 	if (!rf)
 	  return 0;
 	return rf;
@@ -6836,11 +6836,11 @@ void Item_field::update_null_value()
     Item *item;
     while ((item=it++))
     {
-      if (item->used_tables() == map && item->type() == FIELD_ITEM)
+      if (item->used_tables() == map && item->real_item()->type() == FIELD_ITEM)
       {   
-	Item_field *field_item= (Item_field *) item;
+	Item_field *field_item= (Item_field *) (item->real_item());
 	li.rewind();
-        uint field_no= ((Item_field*) this)->field->field_index;
+        uint field_no= field_item->field->field_index;
         for (uint i= 0; i <= field_no; i++)
           producing_item= li++;
         return producing_item->build_clone(thd, thd->mem_root);
@@ -6873,9 +6873,9 @@ void Item_field::update_null_value()
     Item *item;
     while ((item=it++))
     {
-      if (item->used_tables() == map && item->type() == FIELD_ITEM)
+      if (item->used_tables() == map && item->real_item()->type() == FIELD_ITEM)
       {   
-	Item_field *field_item= (Item_field *) item;
+	Item_field *field_item= (Item_field *) (item->real_item());
 	li.rewind();
         while ((field=li++))
         {
@@ -10147,12 +10147,12 @@ bool Item_field::exclusive_dependence_on_grouping_fields_processor(void *arg)
     Item *item;
     while ((item=it++))
     {
-      if (item->used_tables() == map && item->type() == FIELD_ITEM)
+      if (item->used_tables() == map && item->real_item()->type() == FIELD_ITEM)
       {
 	li.rewind();
         while ((field=li++))
         {
-	  if (((Item_field *)item)->field == field->tmp_field)
+	  if (((Item_field *)(item->real_item()))->field == field->tmp_field)
 	    return false;
 	}
       }


More information about the commits mailing list