[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
