3263 Guilhem Bichot 2011-01-21
show traces in optimizer_trace2.test. Implies to have one version in normal mode
and one version in ps-protocol mode, due to IN->EXISTS (which is done
when preparing the statement).
added:
mysql-test/r/optimizer_trace2_ps_prot.result
mysql-test/t/optimizer_trace2_no_prot.test
mysql-test/t/optimizer_trace2_ps_prot.test
renamed:
mysql-test/r/optimizer_trace2.result => mysql-test/r/optimizer_trace2_no_prot.result
mysql-test/t/optimizer_trace2.test => mysql-test/include/optimizer_trace2.inc
modified:
mysql-test/r/optimizer_trace2_no_prot.result
mysql-test/include/optimizer_trace2.inc
3262 Guilhem Bichot 2011-01-21
Fix for optimizer trace bug (in fact, the same as EXPLAIN EXTENDED had: BUG 28728):
crash when printing the expanded_query of a query containing a materialized
FROM-clause derived table.
@ include/my_sys.h
In debug builds, make TRASH() overwrite memory so that it will segfault
if dereferenced (we used to have this in safemalloc builds but they're gone).
This is useful as without it, the bug (dereferencing a pointer in memory
freed by free_root()) was not always repeatable (it maybe depended on
whether the freed memory had already been reallocated+rewritten or not).
@ mysql-test/t/optimizer_trace2.test
test for bug
@ mysys/my_alloc.c
In free_root(), when we free() a block, TRASH() it first.
@ sql/opt_trace.cc
work around for BUG 57928 fixed in trunk but not yet merged
in feature tree.
@ sql/sql_select.cc
extend the fix of EXPLAIN EXTENDED to apply to the optimizer trace
modified:
WL4800_TODO.txt
include/my_sys.h
mysql-test/r/optimizer_trace2.result
mysql-test/t/optimizer_trace2.test
mysys/my_alloc.c
sql/opt_trace.cc
sql/sql_select.cc
3261 Guilhem Bichot 2011-01-20
Fix for bug: query is not ready for printing until we have done
fix_fields() on its HAVING clause.
modified:
WL4800_TODO.txt
mysql-test/r/optimizer_trace2.result
mysql-test/t/optimizer_trace2.test
sql/sql_select.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt 2011-01-20 21:13:56 +0000
+++ b/WL4800_TODO.txt 2011-01-21 15:11:00 +0000
@@ -49,6 +49,3 @@ should the debug binary really assert(0)
good idea at the customer's? On the other hand, how to make sure a
developer notices a syntax error when running tests?
sql_print_warning() is an idea.
-
-Fix crashes: in test profiling.test with --valgrind, +- --ps-protocol,
-with --opt-trace-protocol; see BUG#28728.
=== modified file 'include/my_sys.h'
--- a/include/my_sys.h 2010-08-06 08:54:01 +0000
+++ b/include/my_sys.h 2011-01-21 15:11:00 +0000
@@ -162,7 +162,13 @@ extern void *my_memdup(const void *from,
extern char *my_strdup(const char *from,myf MyFlags);
extern char *my_strndup(const char *from, size_t length,
myf MyFlags);
-#define TRASH(A,B) do{MEM_CHECK_ADDRESSABLE(A,B);MEM_UNDEFINED(A,B);} while (0)
+#ifndef DBUG_OFF
+/* Put bad content in memory to be sure it will segfault if dereferenced */
+#define TRASH_SUB(A,B) do{memset(A,0x8F,B);} while (0);
+#else
+#define TRASH_SUB(A,B)
+#endif
+#define TRASH(A,B) do{TRASH_SUB(A,B); MEM_CHECK_ADDRESSABLE(A,B);MEM_UNDEFINED(A,B);} while (0)
#if defined(ENABLED_DEBUG_SYNC)
extern void (*debug_sync_C_callback_ptr)(const char *, size_t);
#define DEBUG_SYNC_C(_sync_point_name_) do { \
=== renamed file 'mysql-test/t/optimizer_trace2.test' => 'mysql-test/include/optimizer_trace2.inc'
--- a/mysql-test/t/optimizer_trace2.test 2011-01-20 21:13:56 +0000
+++ b/mysql-test/include/optimizer_trace2.inc 2011-01-21 15:22:42 +0000
@@ -4,9 +4,10 @@
set optimizer_trace="enabled=on,end_marker=on";
-# check that if a sub-statement should not be traced,
-# it is not traced even if inside a traced top statement
+--echo # check that if a sub-statement should not be traced,
+--echo # it is not traced even if inside a traced top statement
+--echo
set optimizer_trace_offset=0, optimizer_trace_limit=100;
delimiter |;
create function f1(arg char(1)) returns int
@@ -30,7 +31,9 @@ delimiter ;|
set optimizer_trace_offset=default, optimizer_trace_limit=default;
drop function f1;
-# check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
+--echo
+--echo # check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
+--echo
create table t1(a int, b int);
create view v1 as select a from t1;
select VIEW_DEFINITION from information_schema.VIEWS
@@ -40,7 +43,9 @@ from information_schema.OPTIMIZER_TRACE;
drop table t1;
drop view v1;
-# check for readable display of BIT values
+--echo
+--echo # check for readable display of BIT values
+--echo
create table t1 (a bit(5), key(a));
insert into t1 values(b'00000'),(b'01101');
select cast(a as unsigned) from t1 where a > b'01100';
@@ -48,7 +53,9 @@ select cast(a as unsigned) from t1 where
select TRACE from information_schema.OPTIMIZER_TRACE;
drop table t1;
-# check that trace lists all pushed down ON conditions
+--echo
+--echo # check that trace lists all pushed down ON conditions
+--echo
create table t1 (i int not null);
insert into t1 values (0), (2),(3),(4);
create table t2 (i int not null);
@@ -68,7 +75,10 @@ select * from
select TRACE from information_schema.OPTIMIZER_TRACE;
drop table t1,t2,t3;
-# test of printing HAVING condition in ps-protocol (used to crash)
+--echo
+--echo # test of tracing a query with an HAVING condition, in
+--echo # ps-protocol, does not crash
+--echo
# Comes from having.test
CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
@@ -84,5 +94,17 @@ SELECT t1.f2 FROM t1
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
ORDER BY f2;
+select TRACE from information_schema.OPTIMIZER_TRACE;
DROP TABLES t1,t2,t3;
+
+--echo
+--echo # Test that tracing a query with a materialized FROM-clause
+--echo # derived table using a GROUP BY, does not crash
+--echo
+# Comes from profiling.test
+create table t1 (a int, b int);
+insert into t1 values (1,1), (2,null), (3, 4);
+select max(x) from (select sum(a) as x from t1 group by b) as teeone;
+select TRACE from information_schema.OPTIMIZER_TRACE;
+drop table t1;
=== renamed file 'mysql-test/r/optimizer_trace2.result' => 'mysql-test/r/optimizer_trace2_no_prot.result'
--- a/mysql-test/r/optimizer_trace2.result 2011-01-20 21:13:56 +0000
+++ b/mysql-test/r/optimizer_trace2_no_prot.result 2011-01-21 15:22:42 +0000
@@ -1,4 +1,7 @@
set optimizer_trace="enabled=on,end_marker=on";
+# check that if a sub-statement should not be traced,
+# it is not traced even if inside a traced top statement
+
set optimizer_trace_offset=0, optimizer_trace_limit=100;
create function f1(arg char(1)) returns int
begin
@@ -20,6 +23,9 @@ select 1 into res from dual
select 2 into res from dual
set optimizer_trace_offset=default, optimizer_trace_limit=default;
drop function f1;
+
+# check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
+
create table t1(a int, b int);
create view v1 as select a from t1;
select VIEW_DEFINITION from information_schema.VIEWS
@@ -32,6 +38,9 @@ locate("\"view\": \"v1\"", TRACE) != 0
1
drop table t1;
drop view v1;
+
+# check for readable display of BIT values
+
create table t1 (a bit(5), key(a));
insert into t1 values(b'00000'),(b'01101');
select cast(a as unsigned) from t1 where a > b'01100';
@@ -191,6 +200,9 @@ TRACE
] /* steps */
}
drop table t1;
+
+# check that trace lists all pushed down ON conditions
+
create table t1 (i int not null);
insert into t1 values (0), (2),(3),(4);
create table t2 (i int not null);
@@ -393,6 +405,10 @@ TRACE
] /* steps */
}
drop table t1,t2,t3;
+
+# test of tracing a query with an HAVING condition, in
+# ps-protocol, does not crash
+
CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
INSERT INTO t1 VALUES (16,'f');
INSERT INTO t1 VALUES (16,'f');
@@ -410,4 +426,547 @@ f
f
f
f
+select TRACE from information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1`"
+ },
+ {
+ "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
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`f2` AS `f2` from (`test`.`t1` straight_join (`test`.`t2` join `test`.`t3` on((`test`.`t3`.`f2` = `test`.`t2`.`f2`))) on((`test`.`t3`.`f2` = `test`.`t2`.`f2`))) having (not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`))))))) order by `test`.`t1`.`f2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t3`.`f2` = `test`.`t2`.`f2`) and (`test`.`t3`.`f2` = `test`.`t2`.`f2`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t3`.`f2`, `test`.`t2`.`f2`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t3`.`f2`, `test`.`t2`.`f2`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(`test`.`t3`.`f2`, `test`.`t2`.`f2`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))))))",
+ "steps": [
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0098,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0098,
+ "records_for_plan": 2,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0099,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0196,
+ "records_for_plan": 4,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0098,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0098,
+ "records_for_plan": 2,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`f2` = 'f')",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(`test`.`t2`.`f2` = 'f')"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(((<cache>(<cache>('v')) = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>(<cache>('i')) = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))",
+ "steps": [
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(((<cache>(<cache>('v')) = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>(<cache>('i')) = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(((<cache>(<cache>('v')) = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>(<cache>('i')) = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0098,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0098,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+}
DROP TABLES t1,t2,t3;
+
+# Test that tracing a query with a materialized FROM-clause
+# derived table using a GROUP BY, does not crash
+
+create table t1 (a int, b int);
+insert into t1 values (1,1), (2,null), (3, 4);
+select max(x) from (select sum(a) as x from t1 group by b) as teeone;
+max(x)
+3
+select TRACE from information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select sum(`test`.`t1`.`a`) AS `x` from `test`.`t1` group by `test`.`t1`.`b`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0066,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0066,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select max(`teeone`.`x`) AS `max(x)` from (/* select#2 */ select sum(`test`.`t1`.`a`) AS `x` from `test`.`t1` group by `test`.`t1`.`b`) `teeone`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "teeone",
+ "table_scan": {
+ "records": 3,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "",
+ "table": "teeone",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 10.15,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.15,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "",
+ "table": "teeone",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "teeone",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+}
+drop table t1;
=== added file 'mysql-test/r/optimizer_trace2_ps_prot.result'
--- a/mysql-test/r/optimizer_trace2_ps_prot.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/optimizer_trace2_ps_prot.result 2011-01-21 15:22:42 +0000
@@ -0,0 +1,964 @@
+set optimizer_trace="enabled=on,end_marker=on";
+# check that if a sub-statement should not be traced,
+# it is not traced even if inside a traced top statement
+
+set optimizer_trace_offset=0, optimizer_trace_limit=100;
+create function f1(arg char(1)) returns int
+begin
+declare res int;
+declare dummy varchar(1);
+select 1 into res from dual;
+select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
+select 2 into res from dual;
+return 3;
+end|
+select f1("c")|
+f1("c")
+3
+
+select distinct QUERY from information_schema.OPTIMIZER_TRACE|
+QUERY
+select f1("c")
+select 1 into res from dual
+select 2 into res from dual
+set optimizer_trace_offset=default, optimizer_trace_limit=default;
+drop function f1;
+
+# check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
+
+create table t1(a int, b int);
+create view v1 as select a from t1;
+select VIEW_DEFINITION from information_schema.VIEWS
+where TABLE_SCHEMA="test" and TABLE_NAME="v1";
+VIEW_DEFINITION
+select `test`.`t1`.`a` AS `a` from `test`.`t1`
+select locate("\"view\": \"v1\"", TRACE) != 0
+from information_schema.OPTIMIZER_TRACE;
+locate("\"view\": \"v1\"", TRACE) != 0
+1
+drop table t1;
+drop view v1;
+
+# check for readable display of BIT values
+
+create table t1 (a bit(5), key(a));
+insert into t1 values(b'00000'),(b'01101');
+select cast(a as unsigned) from t1 where a > b'01100';
+cast(a as unsigned)
+13
+select TRACE from information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select cast(`test`.`t1`.`a` as unsigned) AS `cast(a as unsigned)` from `test`.`t1` where (`test`.`t1`.`a` > 0x0c)"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`a` > 0x0c)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t1`.`a` > 0x0c)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t1`.`a` > 0x0c)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t1`.`a` > 0x0c)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "a",
+ "usable": true,
+ "key_parts": [
+ "a"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "a",
+ "cost": 1.4175,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "a",
+ "ranges": [
+ "12 < a"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0034,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0034,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`a` > 0x0c)",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`a` > 0x0c)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+}
+drop table t1;
+
+# check that trace lists all pushed down ON conditions
+
+create table t1 (i int not null);
+insert into t1 values (0), (2),(3),(4);
+create table t2 (i int not null);
+insert into t2 values (0),(1), (3),(4);
+create table t3 (i int not null);
+insert into t3 values (0),(1),(2), (4);
+select * from
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL
+;
+i i i
+2 NULL NULL
+select TRACE from information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t2`.`i` AS `i`,`test`.`t3`.`i` AS `i` from (`test`.`t1` left join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`i` = `test`.`t2`.`i`))) on((`test`.`t2`.`i` = `test`.`t1`.`i`))) where isnull(`test`.`t3`.`i`)"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "isnull(`test`.`t3`.`i`)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "isnull(`test`.`t3`.`i`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "isnull(`test`.`t3`.`i`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "isnull(`test`.`t3`.`i`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 4,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 4,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "table_scan": {
+ "records": 4,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 4,
+ "cost": 2.0068,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0068,
+ "records_for_plan": 4,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 4,
+ "cost": 8.0273,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.034,
+ "records_for_plan": 16,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 4,
+ "cost": 32.109,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 42.144,
+ "records_for_plan": 64,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "isnull(`test`.`t3`.`i`)",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "trigcond_if(is_not_null_compl(t2..t3), (`test`.`t2`.`i` = `test`.`t1`.`i`), true)"
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "attached": "((trigcond_if(found_match(t3), trigcond_if(found_match(t2..t3), isnull(`test`.`t3`.`i`), true), true) and trigcond_if(is_not_null_compl(t3), (`test`.`t2`.`i` = `test`.`t1`.`i`), true)) and trigcond_if(is_not_null_compl(t3), (`test`.`t3`.`i` = `test`.`t1`.`i`), true))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+}
+drop table t1,t2,t3;
+
+# test of tracing a query with an HAVING condition, in
+# ps-protocol, does not crash
+
+CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
+INSERT INTO t1 VALUES (16,'f');
+INSERT INTO t1 VALUES (16,'f');
+CREATE TABLE t2 (f1 INT, f2 VARCHAR(1));
+INSERT INTO t2 VALUES (13,'f');
+INSERT INTO t2 VALUES (20,'f');
+CREATE TABLE t3 (f1 INT, f2 VARCHAR(1));
+INSERT INTO t3 VALUES (7,'f');
+SELECT t1.f2 FROM t1
+STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
+HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
+ORDER BY f2;
+f2
+f
+f
+f
+f
+select TRACE from information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`f2` AS `f2` from (`test`.`t1` straight_join (`test`.`t2` join `test`.`t3` on((`test`.`t3`.`f2` = `test`.`t2`.`f2`))) on((`test`.`t3`.`f2` = `test`.`t2`.`f2`))) having (not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`))))))) order by `test`.`t1`.`f2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t3`.`f2` = `test`.`t2`.`f2`) and (`test`.`t3`.`f2` = `test`.`t2`.`f2`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t3`.`f2`, `test`.`t2`.`f2`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t3`.`f2`, `test`.`t2`.`f2`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(`test`.`t3`.`f2`, `test`.`t2`.`f2`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))))))",
+ "steps": [
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `test`.`t1`.`f2`,min(`test`.`t1`.`f2`) from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0098,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0098,
+ "records_for_plan": 2,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0099,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0196,
+ "records_for_plan": 4,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0098,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0098,
+ "records_for_plan": 2,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`f2` = 'f')",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(`test`.`t2`.`f2` = 'f')"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(((<cache>(<cache>('v')) = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>(<cache>('i')) = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))",
+ "steps": [
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(((<cache>(<cache>('v')) = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>(<cache>('i')) = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(((<cache>(<cache>('v')) = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>(<cache>('i')) = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`)))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0098,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0098,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+}
+DROP TABLES t1,t2,t3;
+
+# Test that tracing a query with a materialized FROM-clause
+# derived table using a GROUP BY, does not crash
+
+create table t1 (a int, b int);
+insert into t1 values (1,1), (2,null), (3, 4);
+select max(x) from (select sum(a) as x from t1 group by b) as teeone;
+max(x)
+3
+select TRACE from information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select sum(`test`.`t1`.`a`) AS `x` from `test`.`t1` group by `test`.`t1`.`b`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0066,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0066,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select max(`teeone`.`x`) AS `max(x)` from (/* select#2 */ select sum(`test`.`t1`.`a`) AS `x` from `test`.`t1` group by `test`.`t1`.`b`) `teeone`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "teeone",
+ "table_scan": {
+ "records": 3,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "",
+ "table": "teeone",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 10.15,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.15,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "",
+ "table": "teeone",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "teeone",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+}
+drop table t1;
=== added file 'mysql-test/t/optimizer_trace2_no_prot.test'
--- a/mysql-test/t/optimizer_trace2_no_prot.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace2_no_prot.test 2011-01-21 15:22:42 +0000
@@ -0,0 +1,8 @@
+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
+ + $VIEW_PROTOCOL > 0`)
+{
+ --skip Need normal protocol
+}
+
+# The main testing script
+--source include/optimizer_trace2.inc
=== added file 'mysql-test/t/optimizer_trace2_ps_prot.test'
--- a/mysql-test/t/optimizer_trace2_ps_prot.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace2_ps_prot.test 2011-01-21 15:22:42 +0000
@@ -0,0 +1,8 @@
+if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0
+ OR $PS_PROTOCOL = 0`)
+{
+ --skip Need ps-protocol
+}
+
+# The main testing script
+--source include/optimizer_trace2.inc
=== modified file 'mysys/my_alloc.c'
--- a/mysys/my_alloc.c 2010-07-08 21:20:08 +0000
+++ b/mysys/my_alloc.c 2011-01-21 15:11:00 +0000
@@ -73,6 +73,9 @@ void init_alloc_root(MEM_ROOT *mem_root,
}
+#define TRASH_MEM(X) TRASH(((char*)(X) + ((X)->size-(X)->left)), (X)->left)
+
+
/*
SYNOPSIS
reset_root_defaults()
@@ -120,7 +123,11 @@ void reset_root_defaults(MEM_ROOT *mem_r
{
/* remove block from the list and free it */
*prev= mem->next;
- my_free(mem);
+ {
+ mem->left= mem->size;
+ TRASH_MEM(mem);
+ my_free(mem);
+ }
}
else
prev= &mem->next;
@@ -292,8 +299,6 @@ void *multi_alloc_root(MEM_ROOT *root, .
DBUG_RETURN((void*) start);
}
-#define TRASH_MEM(X) TRASH(((char*)(X) + ((X)->size-(X)->left)), (X)->left)
-
/* Mark all data in blocks free for reusage */
static inline void mark_blocks_free(MEM_ROOT* root)
@@ -362,13 +367,21 @@ void free_root(MEM_ROOT *root, myf MyFla
{
old=next; next= next->next ;
if (old != root->pre_alloc)
+ {
+ old->left= old->size;
+ TRASH_MEM(old);
my_free(old);
+ }
}
for (next=root->free ; next ;)
{
old=next; next= next->next;
if (old != root->pre_alloc)
+ {
+ old->left= old->size;
+ TRASH_MEM(old);
my_free(old);
+ }
}
root->used=root->free=0;
if (root->pre_alloc)
=== modified file 'sql/opt_trace.cc'
--- a/sql/opt_trace.cc 2011-01-14 15:53:48 +0000
+++ b/sql/opt_trace.cc 2011-01-21 15:11:00 +0000
@@ -292,7 +292,7 @@ Opt_trace_struct& Opt_trace_struct::do_a
add_key_name(key);
LEX_CSTRING readables[]= { { STRING_WITH_LEN("false") },
{ STRING_WITH_LEN("true") } };
- const LEX_CSTRING *readable= &readables[(int)val];
+ const LEX_CSTRING *readable= &readables[test(val)];
stmt->buffer.append(readable->str, readable->length);
return *this;
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-01-20 21:13:56 +0000
+++ b/sql/sql_select.cc 2011-01-21 15:11:00 +0000
@@ -3379,11 +3379,20 @@ JOIN::exec()
for a derived table which is always materialized.
We also need to do this when we have temp table(s).
Otherwise we would not be able to print the query correctly.
- */
- if (items0 && (thd->lex->describe & DESCRIBE_EXTENDED) &&
+ Same applies to the optimizer trace.
+ */
+ if (items0 && ((thd->lex->describe & DESCRIBE_EXTENDED)
+#ifdef OPTIMIZER_TRACE
+ || (thd->opt_trace != NULL &&
+ thd->opt_trace->is_started())
+#endif
+ ) &&
(select_lex->linkage == DERIVED_TABLE_TYPE ||
exec_tmp_table1 || exec_tmp_table2))
+ {
+ DBUG_PRINT("info", ("restoring ref array for EXPLAIN EXTENDED, opt trace"));
set_items_ref_array(items0);
+ }
DBUG_VOID_RETURN;
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-next-mr-bugfixing branch (guilhem.bichot:3261 to 3263) | Guilhem Bichot | 21 Jan |
