List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:May 26 2011 4:24pm
Subject:[Resend] bzr commit into mysql-trunk branch (guilhem.bichot:3315)
Bug#12595210
View as plain text  
[This commit e-mail is a repeat.]

#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting-wl4800/ based on
revid:guilhem.bichot@stripped

 3315 Guilhem Bichot	2011-05-26
      fix for BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
     @ mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test
        test for bug; used to crash. BUG 12595688 was filed along the way.
     @ sql/item_subselect.cc
        when doing IN->EXISTS transformation of IN(select),
        if "select" contains some constant WHERE/HAVING clause we may evaluate it;
        this may evaluate a subquery (if the WHERE/HAVING has a subquery). This caused
        a syntax error like this:
                        {
                          "transformation": {
                            "select#": 2,
                            "from": "IN (SELECT)",
                            "to": "EXISTS (CORRELATED SELECT)",
                            "chosen": true** invalid JSON (missing key) ** ,
                            "?": {
                              "subselect_execution": {
                                "select#": 3,
        Where:
        "subselect_execution" and its outer unnamed object come from
join->conds->fix_fields()
        called in Item_in_subselect::row_value_in_to_exists_transformer()
        (transforming select#2 with IN->EXISTS, evaluating its WHERE clause along the
way,
        which means evaluating the constant select#3).
        The fix is to open a named object when about to evaluate the constant WHERE,
        so that subquery execution fits into it.
        Same for HAVING.
        Same for transformations of returning-single-column subqueries.

    modified:
      mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result
      mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result
      mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result
      mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result
      mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result
      mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test
      sql/item_subselect.cc
=== modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result	2011-05-20
13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result	2011-05-26
16:24:06 +0000
@@ -660,7 +660,9 @@ TRACE
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_having_conditions": [
+                    ] /* evaluating_constant_having_conditions */
                   } /* transformation */
                 }
               ] /* steps */

=== modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result	2011-05-20 13:15:22
+0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result	2011-05-26 16:24:06
+0000
@@ -1,3 +1,4 @@
+set optimizer_trace_max_mem_size=1048576;
 SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
 #
 # BUG#12430646 - SEL_ARG::LEFT AND RIGHT POINTERS INCORRECTLY 
@@ -31,3 +32,410 @@ AND t1.a = t2.a AND t1.c = t2.c;
 1
 1
 DROP TABLE t1, t2;
+#
+# BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
+#
+CREATE TABLE t1 (  
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (  
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (  
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE CC (
+pk INT NOT NULL AUTO_INCREMENT,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+PRIMARY KEY (`pk`)
+) ENGINE=InnoDB;
+CREATE VIEW view_CC AS SELECT * FROM CC;
+CREATE TABLE where_subselect_19379 SELECT
+(
+SELECT SUM(SQ1_alias1.pk) AS SQ1_field1
+FROM view_CC AS SQ1_alias1
+INNER JOIN t1 AS SQ1_alias2 ON  (SQ1_alias2.col_varchar_key =
+SQ1_alias1.col_varchar_key )
+) AS field1,
+alias1.col_varchar_nokey AS field2
+FROM (t2 AS alias1
+LEFT JOIN ( t2 AS alias2
+LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
+alias2.col_varchar_key )
+) ON (alias3.col_varchar_key = alias2.col_varchar_key)
+)
+WHERE  ( alias2.col_varchar_key IN (
+SELECT SQ2_alias1.col_varchar_nokey AS SQ2_field1
+FROM t3 AS SQ2_alias1
+WHERE SQ2_alias1.col_varchar_nokey <= alias1.col_varchar_key
+)
+);
+SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
+(SELECT
+(
+SELECT SUM(SQ1_alias1.pk) AS SQ1_field1
+FROM view_CC AS SQ1_alias1
+INNER JOIN t1 AS SQ1_alias2 ON  (SQ1_alias2.col_varchar_key =
+SQ1_alias1.col_varchar_key )
+) AS field1,
+alias1.col_varchar_nokey AS field2
+FROM (t2 AS alias1
+LEFT JOIN ( t2 AS alias2
+LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
+alias2.col_varchar_key )
+) ON (alias3.col_varchar_key = alias2.col_varchar_key)
+)
+WHERE  ( alias2.col_varchar_key IN (
+SELECT SQ2_alias1.col_varchar_nokey AS SQ2_field1
+FROM t3 AS SQ2_alias1
+WHERE SQ2_alias1.col_varchar_nokey <= alias1.col_varchar_key
+)
+));
+field1	field2
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
+(SELECT
+(
+SELECT SUM(SQ1_alias1.pk) AS SQ1_field1
+FROM view_CC AS SQ1_alias1
+INNER JOIN t1 AS SQ1_alias2 ON  (SQ1_alias2.col_varchar_key =
+SQ1_alias1.col_varchar_key )
+) AS field1,
+alias1.col_varchar_nokey AS field2
+FROM (t2 AS alias1
+LEFT JOIN ( t2 AS alias2
+LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
+alias2.col_varchar_key )
+) ON (alias3.col_varchar_key = alias2.col_varchar_key)
+)
+WHERE  ( alias2.col_varchar_key IN (
+SELECT SQ2_alias1.col_varchar_nokey AS SQ2_field1
+FROM t3 AS SQ2_alias1
+WHERE SQ2_alias1.col_varchar_nokey <= alias1.col_varchar_key
+)
+))	{
+  "steps": [
+    {
+      "view": {
+        "database": "test",
+        "view": "SQ1_alias1",
+        "in_select#": 1,
+        "select#": 5,
+        "merged": true
+      } /* view */
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_preparation": {
+                    "select#": 3,
+                    "steps": [
+                      {
+                        "expanded_query": "/* select#3 */ select sum(`test`.`CC`.`pk`) AS
`SQ1_field1` from ((`test`.`CC`) join `test`.`t1` `SQ1_alias2`
on((`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)))"
+                      }
+                    ] /* steps */
+                  } /* join_preparation */
+                },
+                {
+                  "join_preparation": {
+                    "select#": 4,
+                    "steps": [
+                      {
+                        "expanded_query": "/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`test`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`)"
+                      },
+                      {
+                        "transformation": {
+                          "select#": 4,
+                          "from": "IN (SELECT)",
+                          "to": "semijoin",
+                          "chosen": true
+                        } /* transformation */
+                      }
+                    ] /* steps */
+                  } /* join_preparation */
+                },
+                {
+                  "expanded_query": "/* select#2 */ select (/* select#3 */ select
sum(`test`.`CC`.`pk`) AS `SQ1_field1` from ((`test`.`CC`) join `test`.`t1` `SQ1_alias2`
on((`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)))) AS
`field1`,`test`.`alias1`.`col_varchar_nokey` AS `field2` from (`test`.`t2` `alias1` left
join (`test`.`t2` `alias2` left join `test`.`t2` `alias3`
on((`test`.`alias3`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`)))
on((`test`.`alias3`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`))) where
`test`.`alias2`.`col_varchar_key` in (/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`test`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`))"
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": false
+                  } /* transformation */
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "materialization",
+                    "chosen": false
+                  } /* transformation */
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "EXISTS (CORRELATED SELECT)",
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                      {
+                        "subselect_execution": {
+                          "select#": 3,
+                          "steps": [
+                            {
+                              "join_optimization": {
+                                "select#": 3,
+                                "steps": [
+                                  {
+                                    "condition_processing": {
+                                      "condition": "WHERE",
+                                      "original_condition":
"(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)",
+                                      "steps": [
+                                        {
+                                          "transformation": "equality_propagation",
+                                          "resulting_condition": "multiple
equal(`test`.`SQ1_alias2`.`col_varchar_key`, `test`.`CC`.`col_varchar_key`)"
+                                        },
+                                        {
+                                          "transformation": "constant_propagation",
+                                          "resulting_condition": "multiple
equal(`test`.`SQ1_alias2`.`col_varchar_key`, `test`.`CC`.`col_varchar_key`)"
+                                        },
+                                        {
+                                          "transformation": "trivial_condition_removal",
+                                          "resulting_condition": "multiple
equal(`test`.`SQ1_alias2`.`col_varchar_key`, `test`.`CC`.`col_varchar_key`)"
+                                        }
+                                      ] /* steps */
+                                    } /* condition_processing */
+                                  },
+                                  {
+                                    "ref_optimizer_key_uses": [
+                                    ] /* ref_optimizer_key_uses */
+                                  },
+                                  {
+                                    "records_estimation": [
+                                      {
+                                        "database": "test",
+                                        "table": "CC",
+                                        "table_scan": {
+                                          "records": 1,
+                                          "cost": 1
+                                        } /* table_scan */
+                                      },
+                                      {
+                                        "database": "test",
+                                        "table": "SQ1_alias2",
+                                        "table_scan": {
+                                          "records": 1,
+                                          "cost": 1
+                                        } /* table_scan */
+                                      }
+                                    ] /* records_estimation */
+                                  },
+                                  {
+                                    "considered_execution_plans": [
+                                      {
+                                        "database": "test",
+                                        "table": "CC",
+                                        "best_access_path": {
+                                          "considered_access_paths": [
+                                            {
+                                              "access_type": "scan",
+                                              "using_join_cache": true,
+                                              "records": 1,
+                                              "cost": 1,
+                                              "chosen": true
+                                            }
+                                          ] /* considered_access_paths */
+                                        } /* best_access_path */,
+                                        "cost_for_plan": 1.2,
+                                        "records_for_plan": 1,
+                                        "rest_of_plan": [
+                                          {
+                                            "database": "test",
+                                            "table": "SQ1_alias2",
+                                            "best_access_path": {
+                                              "considered_access_paths": [
+                                                {
+                                                  "access_type": "scan",
+                                                  "using_join_cache": true,
+                                                  "records": 1,
+                                                  "cost": 1.0001,
+                                                  "chosen": true
+                                                }
+                                              ] /* considered_access_paths */
+                                            } /* best_access_path */,
+                                            "cost_for_plan": 2.4001,
+                                            "records_for_plan": 1,
+                                            "chosen": true
+                                          }
+                                        ] /* rest_of_plan */
+                                      },
+                                      {
+                                        "database": "test",
+                                        "table": "SQ1_alias2",
+                                        "best_access_path": {
+                                          "considered_access_paths": [
+                                            {
+                                              "access_type": "scan",
+                                              "using_join_cache": true,
+                                              "records": 1,
+                                              "cost": 1,
+                                              "chosen": true
+                                            }
+                                          ] /* considered_access_paths */
+                                        } /* best_access_path */,
+                                        "cost_for_plan": 1.2,
+                                        "records_for_plan": 1,
+                                        "pruned_by_heuristic": true
+                                      }
+                                    ] /* considered_execution_plans */
+                                  },
+                                  {
+                                    "attaching_conditions_to_tables": {
+                                      "original_condition":
"(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)",
+                                      "attached_conditions_computation": [
+                                      ] /* attached_conditions_computation */,
+                                      "attached_conditions_summary": [
+                                        {
+                                          "database": "test",
+                                          "table": "CC",
+                                          "attached": null
+                                        },
+                                        {
+                                          "database": "test",
+                                          "table": "SQ1_alias2",
+                                          "attached":
"(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)"
+                                        }
+                                      ] /* attached_conditions_summary */
+                                    } /* attaching_conditions_to_tables */
+                                  },
+                                  {
+                                    "refine_plan": [
+                                      {
+                                        "database": "test",
+                                        "table": "CC",
+                                        "scan_type": "table"
+                                      },
+                                      {
+                                        "database": "test",
+                                        "table": "SQ1_alias2",
+                                        "scan_type": "table"
+                                      }
+                                    ] /* refine_plan */
+                                  }
+                                ] /* steps */
+                              } /* join_optimization */
+                            },
+                            {
+                              "join_execution": {
+                                "select#": 3,
+                                "steps": [
+                                ] /* steps */
+                              } /* join_execution */
+                            }
+                          ] /* steps */
+                        } /* subselect_execution */
+                      }
+                    ] /* evaluating_constant_where_conditions */,
+                    "evaluating_constant_having_conditions": [
+                      {
+                        "subselect_execution": {
+                          "select#": 3,
+                          "steps": [
+                          ] /* steps */
+                        } /* subselect_execution */
+                      }
+                    ] /* evaluating_constant_having_conditions */
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "expanded_query": "/* select#1 */ select
`test`.`where_subselect_19379`.`field1` AS
`field1`,`test`.`where_subselect_19379`.`field2` AS `field2` from
`test`.`where_subselect_19379` where
(not(<in_optimizer>((`test`.`where_subselect_19379`.`field1`,`test`.`where_subselect_19379`.`field2`),<exists>(/*
select#2 */ select (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`)) AS `field1`,`test`.`alias1`.`col_varchar_nokey` AS
`field2` from (`test`.`t2` `alias1` left join (`test`.`t2` `alias2` left join `test`.`t2`
`alias3` on((`test`.`alias3`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`)))
on((`test`.`alias3`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`))) where
(`test`.`alias2`.`col_varchar_key` in (/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`te!
 st`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`)) and
trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`field1`) = (/* select#3 */ select
sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join `test`.`t1` `SQ1_alias2`
where (`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`))) or
isnull((/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join
`test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`)))), true) and trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`field2`) =
`test`.`alias1`.`col_varchar_nokey`) or isnull(`test`.`alias1`.`col_varchar_nokey`)),
true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>((/* select#3
*/ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join `test`.`t1`
`SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_v!
 archar_key`))), true) and trigcond_if(outer_field_is_not_null, <is_not
_null_test>(`test`.`alias1`.`col_varchar_nokey`), true))))))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition":
"(not(<in_optimizer>((`test`.`where_subselect_19379`.`field1`,`test`.`where_subselect_19379`.`field2`),<exists>(/*
select#2 */ select (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`)) AS `field1`,`test`.`alias1`.`col_varchar_nokey` AS
`field2` from (`test`.`t2` `alias1` left join (`test`.`t2` `alias2` left join `test`.`t2`
`alias3` on((`test`.`alias3`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`)))
on((`test`.`alias3`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`))) where
(`test`.`alias2`.`col_varchar_key` in (/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`test`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`))
and trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`fie!
 ld1`) = (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC`
join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`test`.`CC`.`pk`)
AS `SQ1_field1` from `test`.`CC` join `test`.`t1` `SQ1_alias2` where
(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)))), true) and
trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`field2`) =
`test`.`alias1`.`col_varchar_nokey`) or isnull(`test`.`alias1`.`col_varchar_nokey`)),
true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>((/* select#3
*/ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join `test`.`t1`
`SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))), true) and trigcond_if(outer_field_is_not_null,
<is_not_null_test>(`test`.`alias1`.`col_varchar_nokey`), true))))))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "subselect_equality_propagation": [
+                  ] /* subselect_equality_propagation */,
+                  "resulting_condition":
"(not(<in_optimizer>((`test`.`where_subselect_19379`.`field1`,`test`.`where_subselect_19379`.`field2`),<exists>(/*
select#2 */ select (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`)) AS `field1`,`test`.`alias1`.`col_varchar_nokey` AS
`field2` from (`test`.`t2` `alias1` left join (`test`.`t2` `alias2` left join `test`.`t2`
`alias3` on((`test`.`alias3`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`)))
on((`test`.`alias3`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`))) where
(`test`.`alias2`.`col_varchar_key` in (/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`test`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`))
and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`where_subselect_19379`!
 .`field1`) = (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`test`.`CC`.`pk`)
AS `SQ1_field1` from `test`.`CC` join `test`.`t1` `SQ1_alias2` where
(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)))), true) and
trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`field2`) =
`test`.`alias1`.`col_varchar_nokey`) or isnull(`test`.`alias1`.`col_varchar_nokey`)),
true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>((/* select#3
*/ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join `test`.`t1`
`SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))), true) and trigcond_if(outer_field_is_not_null,
<is_not_null_test>(`test`.`alias1`.`col_varchar_nokey`), true))))))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "subselect_constant_propagation": [
+                  ] /* subselect_constant_propagation */,
+                  "resulting_condition":
"(not(<in_optimizer>((`test`.`where_subselect_19379`.`field1`,`test`.`where_subselect_19379`.`field2`),<exists>(/*
select#2 */ select (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`)) AS `field1`,`test`.`alias1`.`col_varchar_nokey` AS
`field2` from (`test`.`t2` `alias1` left join (`test`.`t2` `alias2` left join `test`.`t2`
`alias3` on((`test`.`alias3`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`)))
on((`test`.`alias3`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`))) where
(`test`.`alias2`.`col_varchar_key` in (/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`test`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`))
and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`where_subselect_19379`!
 .`field1`) = (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`test`.`CC`.`pk`)
AS `SQ1_field1` from `test`.`CC` join `test`.`t1` `SQ1_alias2` where
(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)))), true) and
trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`field2`) =
`test`.`alias1`.`col_varchar_nokey`) or isnull(`test`.`alias1`.`col_varchar_nokey`)),
true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>((/* select#3
*/ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join `test`.`t1`
`SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))), true) and trigcond_if(outer_field_is_not_null,
<is_not_null_test>(`test`.`alias1`.`col_varchar_nokey`), true))))))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "subselect_cond_removal": [
+                  ] /* subselect_cond_removal */,
+                  "resulting_condition":
"(not(<in_optimizer>((`test`.`where_subselect_19379`.`field1`,`test`.`where_subselect_19379`.`field2`),<exists>(/*
select#2 */ select (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`)) AS `field1`,`test`.`alias1`.`col_varchar_nokey` AS
`field2` from (`test`.`t2` `alias1` left join (`test`.`t2` `alias2` left join `test`.`t2`
`alias3` on((`test`.`alias3`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`)))
on((`test`.`alias3`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`))) where
(`test`.`alias2`.`col_varchar_key` in (/* select#4 */ select
`test`.`SQ2_alias1`.`col_varchar_nokey` AS `SQ2_field1` from `test`.`t3` `SQ2_alias1`
where (`test`.`SQ2_alias1`.`col_varchar_nokey` <= `test`.`alias1`.`col_varchar_key`))
and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`where_subselect_19379`!
 .`field1`) = (/* select#3 */ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from
`test`.`CC` join `test`.`t1` `SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`test`.`CC`.`pk`)
AS `SQ1_field1` from `test`.`CC` join `test`.`t1` `SQ1_alias2` where
(`test`.`SQ1_alias2`.`col_varchar_key` = `test`.`CC`.`col_varchar_key`)))), true) and
trigcond_if(outer_field_is_not_null,
((<cache>(`test`.`where_subselect_19379`.`field2`) =
`test`.`alias1`.`col_varchar_nokey`) or isnull(`test`.`alias1`.`col_varchar_nokey`)),
true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>((/* select#3
*/ select sum(`test`.`CC`.`pk`) AS `SQ1_field1` from `test`.`CC` join `test`.`t1`
`SQ1_alias2` where (`test`.`SQ1_alias2`.`col_varchar_key` =
`test`.`CC`.`col_varchar_key`))), true) and trigcond_if(outer_field_is_not_null,
<is_not_null_test>(`test`.`alias1`.`col_varchar_nokey`), true))))))"
+                }
+              ] /* steps */
+            } /* condition_processing */
+          },
+          {
+            "ref_optimizer_key_uses": [
+            ] /* ref_optimizer_key_uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "where_subselect_19379",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
+            ] /* records_estimation */
+          }
+        ] /* steps */,
+        "empty_result": {
+          "cause": "no matching row in const table"
+        } /* empty_result */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+drop table t1,t2,t3,CC,where_subselect_19379;
+drop view view_CC;

=== modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result	2011-05-20
13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result	2011-05-26
16:24:06 +0000
@@ -881,7 +881,9 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -915,7 +917,9 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                     "select#": 3,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -1170,7 +1174,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-13136
+13392
 set optimizer_trace_max_mem_size=12800;
 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
 length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -1179,7 +1183,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
 c
 select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from
information_schema.OPTIMIZER_TRACE;
 (@missing_bytes:=missing_bytes_beyond_max_mem_size)
-458
+713
 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from
information_schema.OPTIMIZER_TRACE;
 (@query2:=QUERY)+NULL	(@trace2:=TRACE)+NULL
 NULL	NULL
@@ -1187,7 +1191,7 @@ select length(@trace2),
 (length(@trace2) + @missing_bytes) = length(@trace),
 @query2 = @query;
 length(@trace2)	(length(@trace2) + @missing_bytes) = length(@trace)	@query2 = @query
-12678	1	1
+12679	1	1
 select length(@query2) + length(@trace2)
 between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
 length(@query2) + length(@trace2)
@@ -2224,7 +2228,9 @@ explain extended select * from t1 where 
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -2476,7 +2482,9 @@ explain extended select * from t1 where 
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -3029,7 +3037,9 @@ where a1 in (select b1 from t2_16 where 
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -3304,7 +3314,9 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
               "select#": 2,
               "from": "IN (SELECT)",
               "to": "EXISTS (CORRELATED SELECT)",
-              "chosen": true
+              "chosen": true,
+              "evaluating_constant_where_conditions": [
+              ] /* evaluating_constant_where_conditions */
             } /* transformation */
           },
           {
@@ -4844,7 +4856,11 @@ select * from t1 where (t1.a,t1.b) not i
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */,
+                    "evaluating_constant_having_conditions": [
+                    ] /* evaluating_constant_having_conditions */
                   } /* transformation */
                 }
               ] /* steps */

=== modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result	2011-05-20
13:15:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result	2011-05-26
16:24:06 +0000
@@ -2208,7 +2208,9 @@ explain extended select * from t1 where 
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -2460,7 +2462,9 @@ explain extended select * from t1 where 
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -3013,7 +3017,9 @@ where a1 in (select b1 from t2_16 where 
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
                   } /* transformation */
                 }
               ] /* steps */
@@ -3288,7 +3294,9 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
               "select#": 2,
               "from": "IN (SELECT)",
               "to": "EXISTS (CORRELATED SELECT)",
-              "chosen": true
+              "chosen": true,
+              "evaluating_constant_where_conditions": [
+              ] /* evaluating_constant_where_conditions */
             } /* transformation */
           },
           {

=== modified file
'mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result'
--- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result	2011-05-24
12:46:22 +0000
+++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result	2011-05-26
16:24:06 +0000
@@ -4041,7 +4041,9 @@ SELECT * from t1 where topic = all (SELE
                     "select#": 2,
                     "from": "IN (SELECT)",
                     "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
+                    "chosen": true,
+                    "evaluating_constant_having_conditions": [
+                    ] /* evaluating_constant_having_conditions */
                   } /* transformation */
                 }
               ] /* steps */

=== modified file 'mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test'
--- a/mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test	2011-05-20 13:15:22
+0000
+++ b/mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test	2011-05-26 16:24:06
+0000
@@ -1,6 +1,10 @@
 # Regressiontest for statements that failed with optimizer tracing enabled.
 
 --source include/have_optimizer_trace.inc
+
+let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
+eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
+
 SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
 
 --echo #
@@ -36,3 +40,75 @@ WHERE t1.d <> '1' AND t1.b > '1'
 AND t1.a = t2.a AND t1.c = t2.c;
 
 DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
+--echo #
+
+# This trace exhibits a non-empty
+# "evaluating_constant_where_conditions" object which is rare.
+
+CREATE TABLE t1 (  
+  pk INT NOT NULL AUTO_INCREMENT,
+  col_int_key INT DEFAULT NULL,
+  col_varchar_key VARCHAR(1) DEFAULT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+  
+CREATE TABLE t2 (  
+  pk INT NOT NULL AUTO_INCREMENT,
+  col_int_key INT DEFAULT NULL,
+  col_varchar_key VARCHAR(1) DEFAULT NULL,
+  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+CREATE TABLE t3 (  
+  pk INT NOT NULL AUTO_INCREMENT,
+  col_int_key INT DEFAULT NULL,
+  col_varchar_key VARCHAR(1) DEFAULT NULL,
+  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+CREATE TABLE CC (
+  pk INT NOT NULL AUTO_INCREMENT,
+  col_int_key INT DEFAULT NULL,
+  col_varchar_key VARCHAR(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=InnoDB;
+
+CREATE VIEW view_CC AS SELECT * FROM CC;
+
+let $query=
+SELECT
+  (
+  SELECT SUM(SQ1_alias1.pk) AS SQ1_field1
+  FROM view_CC AS SQ1_alias1
+    INNER JOIN t1 AS SQ1_alias2 ON  (SQ1_alias2.col_varchar_key =
+SQ1_alias1.col_varchar_key )
+  ) AS field1,
+  alias1.col_varchar_nokey AS field2
+FROM (t2 AS alias1
+  LEFT JOIN ( t2 AS alias2
+    LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
+alias2.col_varchar_key )
+  ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
+)
+WHERE  ( alias2.col_varchar_key IN (
+  SELECT SQ2_alias1.col_varchar_nokey AS SQ2_field1
+  FROM t3 AS SQ2_alias1
+  WHERE SQ2_alias1.col_varchar_nokey <= alias1.col_varchar_key
+  )
+);
+
+eval CREATE TABLE where_subselect_19379 $query;
+--disable_ps_protocol # because of BUG#12595688
+eval SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
+($query); 
+--enable_ps_protocol
+
+select * from information_schema.OPTIMIZER_TRACE;
+
+drop table t1,t2,t3,CC,where_subselect_19379;
+drop view view_CC;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-05-24 12:46:22 +0000
+++ b/sql/item_subselect.cc	2011-05-26 16:24:06 +0000
@@ -1336,6 +1336,8 @@ Item_in_subselect::single_value_in_to_ex
       we do not check join->having->fixed, because Item_and (from and_items)
       or comparison function (from func->create) can't be fixed after creation
     */
+    Opt_trace_array having_trace(&thd->opt_trace,
+                                 "evaluating_constant_having_conditions");
     tmp= join->having->fix_fields(thd, 0);
     select_lex->having_fix_field= 0;
     if (tmp)
@@ -1380,6 +1382,8 @@ Item_in_subselect::single_value_in_to_ex
           and_items) or comparison function (from func->create) can't be
           fixed after creation
         */
+        Opt_trace_array having_trace(&thd->opt_trace,
+                                     "evaluating_constant_having_conditions");
 	tmp= join->having->fix_fields(thd, 0);
         select_lex->having_fix_field= 0;
         if (tmp)
@@ -1416,6 +1420,8 @@ Item_in_subselect::single_value_in_to_ex
         we do not check join->conds->fixed, because Item_and can't be fixed
         after creation
       */
+      Opt_trace_array where_trace(&thd->opt_trace,
+                                  "evaluating_constant_where_conditions");
       if (join->conds->fix_fields(thd, 0))
 	DBUG_RETURN(RES_ERROR);
     }
@@ -1452,6 +1458,8 @@ Item_in_subselect::single_value_in_to_ex
           we do not check join->having->fixed, because comparison function
           (from func->create) can't be fixed after creation
         */
+        Opt_trace_array having_trace(&thd->opt_trace,
+                                     "evaluating_constant_having_conditions");
 	tmp= join->having->fix_fields(thd, 0);
         select_lex->having_fix_field= 0;
         if (tmp)
@@ -1749,6 +1757,8 @@ Item_in_subselect::row_value_in_to_exist
     */
     select_lex->where= join->conds= and_items(join->conds, where_item);
     select_lex->where->top_level_item();
+    Opt_trace_array where_trace(&thd->opt_trace,
+                                "evaluating_constant_where_conditions");
     if (join->conds->fix_fields(thd, 0))
       DBUG_RETURN(RES_ERROR);
   }
@@ -1765,6 +1775,8 @@ Item_in_subselect::row_value_in_to_exist
       argument (reference) to fix_fields()
     */
     select_lex->having_fix_field= 1;
+    Opt_trace_array having_trace(&thd->opt_trace,
+                                 "evaluating_constant_having_conditions");
     res= join->having->fix_fields(thd, 0);
     select_lex->having_fix_field= 0;
     if (res)


Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110526162406-hojlvqubkr0so96e.bundle
Thread
[Resend] bzr commit into mysql-trunk branch (guilhem.bichot:3315)Bug#12595210Guilhem Bichot26 May
  • Re: [Resend] bzr commit into mysql-trunk branch (guilhem.bichot:3315)Bug#12595210Jorgen Loland27 May