3242 Jorgen Loland 2010-12-21
WL#4800:
Revert print_quick() and friends to how they are in
next-mr-opt-backporting, i.e., remove optimizer tracing from
this function since tracing of quick is performed in
TABLE_READ_PLAN::trace_basic_info() instead.
@ WL4800_TODO.txt
Update todo-list
@ sql/opt_range.cc
Revert print_quick() and friends to how they are in next-mr-opt-backporting.
modified:
WL4800_TODO.txt
sql/opt_range.cc
3241 Guilhem Bichot 2010-12-19 [merge]
merge Jorgen's changes
modified:
WL4800_TODO.txt
3240 Guilhem Bichot 2010-12-19
applying review comments. Fix for build failures.
modified:
WL4800_TODO.txt
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/optimizer_trace2.result
mysql-test/t/optimizer_trace2.test
sql/opt_range.cc
sql/opt_trace.h
sql/opt_trace2server.cc
sql/sql_parse.cc
sql/sql_prepare.cc
3239 Guilhem Bichot 2010-12-03
review comments. Fix for a bug with views (testcase will come later,
part of bigger unfinished work).
modified:
sql/opt_trace.h
sql/sql_view.cc
3238 Guilhem Bichot 2010-11-26
making use of sql_command_flags as advised by Davi
modified:
sql/opt_trace2server.cc
sql/sql_class.h
sql/sql_parse.cc
3237 Guilhem Bichot 2010-11-26
Limit optimizer tracing to a certain set of SQL commands (safer).
All other changes in this patch follow from this goal.
@ mysql-test/include/optimizer_trace.inc
* Marks in queries (as /**/ comments )to facilitate understanding results
(exactly identical queries make it hard).
* Comments explaining why results now differ between non-ps and --ps-protocol:
now in ps-protocol, PREPARE and EXECUTE both generate one trace. This wasn't
the case before: this is because PREPARE and EXECUTE, in mysqltest, are
sent via COM_STMT_(PREPARE|EXECUTE), which are handled in dispatch_command():
** COM_STMT_PREPARE never goes through mysql_execute_command() so the
preparation was not traced (now that we trace in check_prepared_statement(),
it is traced)
** COM_STMT_EXECUTE executes the prepared command with mysql_execute_command()
so the execution was traced and is still traced.
@ mysql-test/r/optimizer_trace_no_prot.result
SET is not traced anymore. PREPARE CALL neither (thus we see the trace
of the statement before PREPARE CALL, which is a SELECT).
@ mysql-test/r/optimizer_trace_ps_prot.result
Now that PREPARE SELECT and EXECUTE SELECT
@ mysql-test/t/optimizer_trace2.test
Now that CALL is not traced anymore, the testcase needs to use
a top-statement which is traced (SELECT STORED_FUNC() instead of CALL),
in order to keep the test's intention.
@ sql/opt_trace.h
Updating comments: EXECUTE is not traced anymore, it's the executed statement
which is (one level below, in terms of stack frames...).
@ sql/opt_trace2server.cc
- let opt_trace_start() limit tracing to certain SQL commands
- it sounds like the similar check done in opt_trace_print_expanded_query()
would become unnecessary, but alas no, because sometimes we open tables
before executing the command (see comments).
@ sql/sql_parse.cc
The check on CF_DIAGNOSTIC_STMT is not needed, as opt_trace_start()
now checks sql_command.
@ sql/sql_prepare.cc
To have "PREPARE <command>" be traced only if <command> is in a certain set,
we need to know <command>, which is possible only in check_prepared_statement();
so we:
- don't turn tracing on in mysql_execute_command() when command is SQLCOM_PREPARE
(too early, not enough info)
- check the _to-be-prepared_ command in check_prepared_statement() and decide then.
modified:
mysql-test/include/optimizer_trace.inc
mysql-test/r/optimizer_trace2.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/t/optimizer_trace2.test
sql/opt_trace.h
sql/opt_trace2server.cc
sql/sql_parse.cc
sql/sql_prepare.cc
3236 Guilhem Bichot 2010-11-26
Fix for a bug: if a sub-statement shouldn't be traced but is inside a traced
top-statement, the sub-statement's trace structures would be included
inside the top-statement's trace instead of being invisible.
@ mysql-test/r/optimizer_trace2.result
Without the code fix, the trace of CALL would contain the trace
of "select TRACE+NULL..." even though the latter one uses
OPTIMIZER_TRACE (which shouldn't be traced).
@ sql/opt_trace2server.cc
Fix for the bug. The "allocated_here" part is for another bug (which
could happen if running out of memory): we should free opt_trace
only if we allocated it in this frame.
added:
mysql-test/r/optimizer_trace2.result
mysql-test/t/optimizer_trace2.test
modified:
sql/opt_trace2server.cc
3235 Guilhem Bichot 2010-11-24
Implementing some TODOs:
- renamed add_str*() and reviewed all calls of it
- fixed "database"/"table" printouts in the trace (more uniform now)
- test trace when the view/table/column/index' name contains \n and \t
- added add_null()
Misc: removed redundant "constant_tables" array. Fixed bug in detecting
duplicate consecutive keys in a trace object.
@ WL4800_TODO.txt
deleting TODOs, adding others agreed during reviews
@ mysql-test/r/optimizer_trace_no_prot.result
"constant_tables" removed
@ mysql-test/r/optimizer_trace_ps_prot.result
"constant_tables" removed
@ mysql-test/r/optimizer_trace_range.result
escaping is done on the range now, gives correct trace
@ mysql-test/t/optimizer_trace_charset.test
* to check the validity of a trace, better get it in utf8 (that's what
Python's json module wants) => "set names utf8"
* test of \n and \t in view/table/column/index name
@ sql/opt_range.cc
add_alnum/add_utf8. add_null.
@ sql/opt_trace.cc
add_alnum/add_utf8. strcmp() was not detecting the case where
the key is identical to the previous key but longer than sizeof(previous_key)
(different length of previous_key and key made the equality false).
@ sql/opt_trace.h
add_str() renamed to add_alnum(). add_str_escaped() renamed to add_utf8().
add_null() is added.
A macro to avoid repeating this more than 10 times:
trace_object.add_utf8("database", ...->s->db.str, ...->s->db.length)
.add_utf8("table", ...->alias).
@ sql/opt_trace2server.cc
add_alnum/add_utf8
@ sql/sql_delete.cc
add_alnum/add_utf8.
@ sql/sql_select.cc
add_alnum/add_utf8. There were different sources used for finding the
database and name of a table, here we standardize on TABLE::TABLE_SHARE::db
and TABLE::alias. 'alias' is better than 'table_name' because in
a query which uses the same table twice, the 'alias' is more unique.
The old code also had a problem: sometimes it used the database
of the first table of the join (s->join->tables_list->db) which
may be wrong in a cross-database join.
The "constant_tables" array is removed because it is redundant: constant
tables are already listed in "records_estimation" with a mention of
"const" or "system". Thus a big code block is re-indented.
@ sql/sql_test.cc
add_alnum/add_utf8.
@ sql/sql_update.cc
add_alnum/add_utf8.
@ sql/sql_view.cc
add_alnum/add_utf8. "database" instead of "db"; "view" instead of "name";
this is to be symmetric of the "database"/"table" pair found in
traces in sql_select.cc
@ unittest/gunit/opt_trace-t.cc
add_alnum/add_utf8.
modified:
WL4800_TODO.txt
mysql-test/r/optimizer_trace_charset.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/r/optimizer_trace_range.result
mysql-test/t/optimizer_trace_charset.test
sql/opt_range.cc
sql/opt_trace.cc
sql/opt_trace.h
sql/opt_trace2server.cc
sql/sql_delete.cc
sql/sql_select.cc
sql/sql_test.cc
sql/sql_update.cc
sql/sql_view.cc
unittest/gunit/opt_trace-t.cc
3234 Guilhem Bichot 2010-11-23 [merge]
merge of WL#5594 "tracing for range optimizer" into tree of WL#4800 "optimizer trace"
removed:
mysql-test/suite/ndb/r/optimizer_trace_range_opt_bug.result
mysql-test/suite/ndb/t/optimizer_trace_range_opt_bug.test
added:
mysql-test/r/optimizer_trace_range.result
mysql-test/t/optimizer_trace_range.test
modified:
WL4800_TODO.txt
mysql-test/include/optimizer_trace.inc
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/optimizer_trace_charset.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result
mysql-test/t/optimizer_trace_charset.test
sql/handler.cc
sql/opt_range.cc
sql/opt_range.h
sql/opt_trace.cc
sql/opt_trace.h
sql/opt_trace2server.cc
sql/sql_delete.cc
sql/sql_help.cc
sql/sql_select.cc
sql/sql_update.cc
sql/sys_vars.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt 2010-11-17 12:27:39 +0000
+++ b/WL4800_TODO.txt 2010-12-21 07:34:33 +0000
@@ -1,19 +1,11 @@
Short-term TODO list to remember for WL#4800
============================================
-avoid strlen() when appending constant strings.
-
void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose)
doesn't use "verbose".
-convert obscure names like range_select quick_condition_rows (etc?) to
-user-friendly descriptive self-explanatory names.
-
see all added @todo
-About the prototype tracing of the range optimizer: either understand
-and improve it or delete it (Jorgen is working on it).
-
My command to update the HTML Doxygen is:
rsync -avz /m/doxygen/mysql-wl4800 [URL]:public_html
@@ -30,17 +22,40 @@ changes? any little change? maybe).
Coding style:
Make sure long code lines are wrapped.
-Sort out any charset-related problems (security-wise, for example: can
-I find a proper combination of client/server charsets so that creating
-the trace makes the server do wrong?). Review how escaping is done in
-opt_trace.cc.
+Misc security issues as discussed already.
Review comments left:
- in doc, mention DUMPFILE instead of OUTFILE
- in changelog, mention /*select#*/; in doc, mention use of this (to
decrypt what "id" is about in EXPLAIN).
- more checks for non-unique keys?
-- investigate/test whether or not we need special case for
- MYSQL_TYPE_BIT in print_key2
-- revert print_quick() and dbug_dump() to how they are in
- opt_backporting
+
+Try to revert opt_range.cc and sql_test.cc as much as possible. Jorgen
+working on it.
+
+merge latest opt-backporting with fix for uninitialized keyuse, then
+fix all crashes of --opt-trace-protocol
+
+try to avoid the call to get_current_struct in opt_range.cc
+
+solve the problems of "one trace per row scanned" ("range checked for
+each record" and subqueries); one example is at the end of
+http://lists.mysql.com/commits/124599
+
+check save_in_field_no_warnings() in opt_range.cc: do we need to
+disable tracing there? see optimizer_trace_no_prot.result. Jorgen has
+maybe looked at this in his in-review subquery tracing patch.
+
+Push already written patches for
+- when view is merged, its WHERE clause is not seen in expanded query
+(solution: don't print expanded query in open_tables() but in
+JOIN::prepare(), when the clause has finally been merged)
+- some LEFT JOIN trigcond conditions may not be shown
+
+The optimizer may have second thoughts about which access method to
+use for a table. This should be traced. See example query (1) where
+the trace says that ref-access is best but we change our mind and use
+range access anyway.
+(1)CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+ INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+ SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
=== modified file 'WL4800_validate_json.py'
--- a/WL4800_validate_json.py 2010-10-05 10:29:43 +0000
+++ b/WL4800_validate_json.py 2010-11-15 21:05:48 +0000
@@ -42,8 +42,16 @@ def check(trace, first_trace_line):
parsed = json.loads(s)
except:
print "parse error at line", first_trace_line
- print sys.exc_info()
- print s
+ error = str(sys.exc_info())
+ print error
+ # if there is a character position specified, put a mark ('&')
+ # in front of this character
+ matchobj = re.search(r"ValueError\('Invalid control character at: line \d+ column \d+ \(char (\d+)\)'", error)
+ if matchobj:
+ first_error_char = int(matchobj.group(1))
+ print s[:first_error_char] + "&" + s[first_error_char:]
+ else:
+ print s
retcode = 1
print
return
=== modified file 'mysql-test/include/optimizer_trace.inc'
--- a/mysql-test/include/optimizer_trace.inc 2010-11-11 13:25:53 +0000
+++ b/mysql-test/include/optimizer_trace.inc 2010-11-26 16:16:31 +0000
@@ -59,13 +59,13 @@ set optimizer_trace="one_line=off,end_ma
EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
select * from information_schema.OPTIMIZER_TRACE;
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
select * from information_schema.OPTIMIZER_TRACE;
set @@session.optimizer_trace="enabled=off";
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
+SELECT /* bug if you see this*/ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
# should not see last statement but previous
-select * from information_schema.OPTIMIZER_TRACE;
+select QUERY from information_schema.OPTIMIZER_TRACE;
set @@session.optimizer_trace="enabled=on";
@@ -82,12 +82,12 @@ select * from information_schema.OPTIMIZ
select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
select length(@trace);
# The trace above has length:
-# - >8400 in normal mode
-# - <8400 in ps-protocol mode (because IN->EXISTS is done at PREPARE
+# - >9000 in normal mode
+# - <9000 in ps-protocol mode (because IN->EXISTS is done at PREPARE
# and we trace only EXECUTE)
# - So in normal mode, the lines below verify truncation,
# whereas in ps-protocol mode they verify non-truncation.
-set optimizer_trace_max_mem_size=8400;
+set optimizer_trace_max_mem_size=9000;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
@@ -156,9 +156,6 @@ select @@optimizer_trace_features;
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
select * from information_schema.OPTIMIZER_TRACE;
-set @@optimizer_trace_features="greedy_search=on,misc=off";
-explain select * from t1,t2;
-select * from information_schema.OPTIMIZER_TRACE;
set @@optimizer_trace_features=default;
set @@session.optimizer_prune_level=default;
drop table t1, t2;
@@ -332,6 +329,11 @@ select 2;
select 3;
select 4;
select 5;
+# In normal mode, each SELECT is a standalone statement and generates
+# one trace: so 3rd and 4th trace means 3rd and 4th SELECT.
+# In ps-protocol mode, each SELECT is two statements: PREPARE and
+# EXECUTE; each of the two creates one trace; so 3rd and 4th trace
+# means 2nd SELECT (preparation) and 2nd SELECT again (execution).
select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=-2,optimizer_trace_limit=2;
# should be empty
@@ -389,14 +391,23 @@ select s, f1() from t2 order by s desc|
select * from information_schema.OPTIMIZER_TRACE|
select * from t6 where d in (select f1() from t2 where s="c")|
select * from information_schema.OPTIMIZER_TRACE|
-# Want to see the top and invoked sub-statements:
-set optimizer_trace_offset=-4, optimizer_trace_limit=4|
+# Want to see the top and invoked sub-statements; this means 7 traces:
+# 1 top statement + two executions of f1() (there is one
+# execution inside the range optimizer and one "normal" execution);
+# 3 sub-statements in the stored function: 1+2*3=7.
+set optimizer_trace_offset=-7, optimizer_trace_limit=7|
select @@optimizer_trace_offset, @@optimizer_trace_limit|
select * from t6 where d in (select f1() from t2 where s="c")|
select * from information_schema.OPTIMIZER_TRACE|
# Want to see the DELETE (invoked sub-statement):
set optimizer_trace_offset=2, optimizer_trace_limit=1|
select * from t6 where d in (select f1() from t2 where s="c")|
+# In normal mode, we have traces for the top SELECT, then the
+# function's INSERT then the function's DELETE, so DELETE is the
+# third trace, which we see.
+# In ps-protocol mode, we have PREPARE and EXECUTE for the top SELECT
+# (two traces); execution will generate the function's INSERT and
+# DELETE; so INSERT is the third trace, which we see.
select * from information_schema.OPTIMIZER_TRACE|
set optimizer_trace_offset=default, optimizer_trace_limit=default|
@@ -424,7 +435,8 @@ delimiter ;|
# PREPARE/EXECUTE/EXECUTE
prepare stmt from 'call p1(?)';
-select * from information_schema.OPTIMIZER_TRACE;
+# PREPARE CALL is not traced, we should see previous statement:
+select QUERY from information_schema.OPTIMIZER_TRACE;
set @param="c";
execute stmt using @param;
select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
@@ -501,6 +513,14 @@ select 1 union select 2;
set optimizer_switch='default,index_merge=on,index_merge=off,default';
select @@optimizer_switch;
+# Test for long query (1070 chars)
+--disable_query_log
+--disable_result_log
+select "abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def" as col;
+--enable_result_log
+--enable_query_log
+select * from information_schema.OPTIMIZER_TRACE;
+
drop table t1,t2;
DROP TABLE t5,t6;
set optimizer_trace=default;
=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result 2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result 2010-12-19 14:24:03 +0000
@@ -418,8 +418,8 @@ The following options may be given as th
Enables/disables tracing of selected features of the
Optimizer:
optimizer_trace_features=option=val[,option=val...],
- where option is one of {misc, greedy_search,
- range_optimizer} and val is one of {on, off, default}
+ where option is one of {greedy_search, range_optimizer}
+ and val is one of {on, off, default}
--optimizer-trace-limit=#
Maximum number of shown optimizer traces
--optimizer-trace-max-mem-size=#
@@ -871,7 +871,7 @@ optimizer-prune-level 1
optimizer-search-depth 62
optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
optimizer-trace
-optimizer-trace-features misc=on,greedy_search=on,range_optimizer=on
+optimizer-trace-features greedy_search=on,range_optimizer=on
optimizer-trace-limit 1
optimizer-trace-max-mem-size 16384
optimizer-trace-offset -1
=== added file 'mysql-test/r/optimizer_trace2.result'
--- a/mysql-test/r/optimizer_trace2.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/optimizer_trace2.result 2010-12-19 14:24:03 +0000
@@ -0,0 +1,171 @@
+set optimizer_trace="enabled=on,end_marker=on";
+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;
+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;
+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": [
+ {
+ "expanded_query": "/* select#1 */ select cast(`a` as unsigned) AS `cast(a as unsigned)` from `test`.`t1` where (`a` > 0x0c)"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`a` > 0x0c)",
+ "after_equality_propagation": "(`test`.`t1`.`a` > 0x0c)",
+ "after_constant_propagation": "(`test`.`t1`.`a` > 0x0c)",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`a` > 0x0c)"
+ } /* 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 */,
+ "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": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`a` > 0x0c)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop table t1;
=== modified file 'mysql-test/r/optimizer_trace_charset.result'
--- a/mysql-test/r/optimizer_trace_charset.result 2010-11-22 09:59:17 +0000
+++ b/mysql-test/r/optimizer_trace_charset.result 2010-11-24 18:54:26 +0000
@@ -29,9 +29,6 @@ explain extended select '��� from t1
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t1"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -78,9 +75,6 @@ explain extended select 'ÁÂÃÄÅ' fro
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t1"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -115,14 +109,15 @@ col
select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query:=QUERY)+NULL (@trace:=TRACE)+NULL
NULL NULL
+set names utf8;
select @query, @trace;
@query @trace
-select * from v1 where v1.col = '��� {
+select * from v1 where v1.col = 'ÁÂÃÄÅ' {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"materialized": true
} /* view */
@@ -142,9 +137,6 @@ select * from v1 where v1.col = '���
"select#": 2,
"steps": [
{
- "constant_tables": [
- "t1"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -194,9 +186,6 @@ select * from v1 where v1.col = '���
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "v1"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -225,15 +214,20 @@ select * from v1 where v1.col = '���
}
drop table t1;
drop view v1;
+set names latin1;
create table t1(c char(4) primary key);
insert into t1 values ('aaa'), ('��');
select * from t1 where c < '���;
c
aaa
��
-select query, trace from information_schema.OPTIMIZER_TRACE;
-query trace
-select * from t1 where c < '��� {
+select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
+(@query:=QUERY)+NULL (@trace:=TRACE)+NULL
+NULL NULL
+set names utf8;
+select @query, @trace;
+@query @trace
+select * from t1 where c < 'ÁÂÃÄÅ' {
"steps": [
{
"expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where (`c` < '?????')"
@@ -263,8 +257,6 @@ select * from t1 where c < '��� {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -297,7 +289,7 @@ select * from t1 where c < ' "index": "PRIMARY",
"ranges": [
- "c <= ��"
+ "c <= ÁÂÃÄ"
] /* ranges */,
"index_only": true,
"records": 2,
@@ -365,4 +357,168 @@ select * from t1 where c < '��� {
] /* steps */
}
drop table t1;
+create table `t
+1 a`(`col
+1 a` int, index `index
+1 a` (`col
+1 a`));
+insert into `t
+1 a` values(2),(3);
+create view `v
+1 a` as select * from `t
+1 a`;
+select `col
+1 a` from `v
+1 a` where `col
+1 a` < 6;
+col
+1 a
+2
+3
+select query, trace from information_schema.OPTIMIZER_TRACE;
+query trace
+select `col
+1 a` from `v
+1 a` where `col
+1 a` < 6 {
+ "steps": [
+ {
+ "view": {
+ "database": "test",
+ "view": "v\n1\ta",
+ "in_select#": 1,
+ "merged": true
+ } /* view */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `col\n1\ta` AS `col\n1\ta` from (`test`.`t\n1\ta`) where (`col\n1\ta` < 6)"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t\n1\ta`.`col\n1\ta` < 6)",
+ "after_equality_propagation": "(`test`.`t\n1\ta`.`col\n1\ta` < 6)",
+ "after_constant_propagation": "(`test`.`t\n1\ta`.`col\n1\ta` < 6)",
+ "after_trivial_conditions_removal": "(`test`.`t\n1\ta`.`col\n1\ta` < 6)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t\n1\ta",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "index\n1\ta",
+ "usable": true,
+ "key_parts": [
+ "col\n1\ta"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "index\n1\ta",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "index\n1\ta",
+ "ranges": [
+ "NULL < col\n1\ta < 6"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "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": "t\n1\ta",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1,
+ "cost": 2.2034,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.2034,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t\n1\ta`.`col\n1\ta` < 6)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t\n1\ta",
+ "attached": "(`test`.`t\n1\ta`.`col\n1\ta` < 6)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t\n1\ta",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop table `t
+1 a`;
+drop view `v
+1 a`;
set optimizer_trace=default;
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2010-11-22 09:59:17 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2010-11-26 16:16:31 +0000
@@ -37,9 +37,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ],
"records_estimation": [
{
"database": "test",
@@ -86,8 +83,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
]
},
{
- "constant_tables": [
- ],
"records_estimation": [
{
"database": "test",
@@ -134,18 +129,18 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-50.3069
+50.1007
set optimizer_trace="one_line=on";
SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-6.0185
+6.0792
set optimizer_trace="one_line=off,end_marker=on";
EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
id select_type table type possible_keys key key_len ref rows Extra
@@ -177,9 +172,6 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -226,8 +218,6 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -271,12 +261,12 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
}
] /* steps */
} 0 0
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 {
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 {
"steps": [
{
"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
@@ -300,9 +290,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -349,8 +336,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -395,15 +380,12 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* steps */
} 0 0
set @@session.optimizer_trace="enabled=off";
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
+SELECT /* bug if you see this*/ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
NULL
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-set @@session.optimizer_trace="enabled=off" {
- "steps": [
- ] /* steps */
-} 0 0
+select QUERY from information_schema.OPTIMIZER_TRACE;
+QUERY
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5
set @@session.optimizer_trace="enabled=on";
SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
@@ -441,9 +423,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -490,8 +469,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -555,8 +532,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -610,8 +585,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -767,9 +740,6 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -846,8 +816,6 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -918,8 +886,8 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-9257
-set optimizer_trace_max_mem_size=8400;
+9086
+set optimizer_trace_max_mem_size=9000;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
1
@@ -927,7 +895,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)
-972
+208
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -935,7 +903,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8285 1 1
+8878 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)
@@ -1022,9 +990,6 @@ explain SELECT c FROM t5 where c+1 in (s
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1283,9 +1248,6 @@ explain SELECT c FROM t5 where c+1 in (s
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1340,8 +1302,6 @@ explain SELECT c FROM t5 where c+1 in (s
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1504,8 +1464,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1568,8 +1526,6 @@ explain extended select * from t1 where
"select#": 2,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1682,8 +1638,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1746,8 +1700,6 @@ explain extended select * from t1 where
"select#": 2,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1877,8 +1829,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1954,8 +1904,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2084,8 +2032,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2161,8 +2107,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2252,8 +2196,6 @@ explain select * from t1,t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2389,7 +2331,7 @@ explain select * from t1,t2 {
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-misc=on,greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -2414,8 +2356,6 @@ explain select * from t1,t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2474,14 +2414,6 @@ explain select * from t1,t2 {
}
] /* steps */
} 0 0
-set @@optimizer_trace_features="greedy_search=on,misc=off";
-explain select * from t1,t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain select * from t1,t2 0 0
set @@optimizer_trace_features=default;
set @@session.optimizer_prune_level=default;
drop table t1, t2;
@@ -2551,7 +2483,7 @@ where a1 in (select b1 from t2_16 where
"from": "IN (SELECT)",
"to": "materialization",
"chosen": false,
- "cause": "field types"
+ "cause": "field_types"
} /* transformation */
},
{
@@ -2586,8 +2518,6 @@ where a1 in (select b1 from t2_16 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2663,8 +2593,6 @@ where a1 in (select b1 from t2_16 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2803,8 +2731,6 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2935,8 +2861,6 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3038,8 +2962,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3115,8 +3037,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3186,8 +3106,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -3343,8 +3261,6 @@ concat(c1,'y') IN
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3423,8 +3339,6 @@ concat(c1,'y') IN
"select#": 3,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3487,8 +3401,6 @@ concat(c1,'y') IN
"select#": 2,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3576,10 +3488,6 @@ select * from t1,t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t1",
- "t2"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3709,11 +3617,6 @@ trace
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t3",
- "t1",
- "t2"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3840,11 +3743,6 @@ trace
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t3",
- "t1",
- "t2"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3955,8 +3853,6 @@ select * from t1 left join t2 on t2.a=50
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4122,8 +4018,6 @@ select * from t1 where (t1.a,t1.b) not i
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4207,8 +4101,6 @@ select * from t1 where (t1.a,t1.b) not i
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4313,8 +4205,6 @@ trace
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4646,8 +4536,6 @@ insert into t6 select * from t6 where d>
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4780,9 +4668,6 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4928,9 +4813,6 @@ delete t6 from t5, t6 where d>7000 {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5205,8 +5087,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5290,8 +5170,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5372,8 +5250,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5433,14 +5309,387 @@ select sum(data) into ret from t1 {
}
] /* steps */
} 0 0
-set optimizer_trace_offset=-4, optimizer_trace_limit=4|
+set optimizer_trace_offset=-7, optimizer_trace_limit=7|
select @@optimizer_trace_offset, @@optimizer_trace_limit|
@@optimizer_trace_offset @@optimizer_trace_limit
--4 4
+-7 7
select * from t6 where d in (select f1() from t2 where s="c")|
d
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+select * from t6 where d in (select f1() from t2 where s="c") {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`t6`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t6.d= `f1`()",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "3 <= d <= 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "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 */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "d",
+ "records": 1,
+ "cost": 3,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1,
+ "cost": 2.6076,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.6289,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 5.2289,
+ "records": 1,
+ "duplicate_tables_left": true,
+ "chosen": true
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "d",
+ "records": 1,
+ "cost": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 4,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0213,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.0213,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0213,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 3.0213,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 3.2212,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 3.6213,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` = `f1`())"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual"
+ }
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
select sum(data) into ret from t1 {
"steps": [
{
@@ -5458,8 +5707,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5560,8 +5807,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5689,8 +5934,6 @@ select d+1 into res from t6 where d= NAM
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5758,8 +6001,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5829,12 +6070,9 @@ c 3 3
3 100 200
3 100 200
prepare stmt from 'call p1(?)';
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-prepare stmt from 'call p1(?)' {
- "steps": [
- ] /* steps */
-} 0 0
+select QUERY from information_schema.OPTIMIZER_TRACE;
+QUERY
+select * from t2
set @param="c";
execute stmt using @param;
Warnings:
@@ -5876,8 +6114,6 @@ select @trace;
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6005,8 +6241,6 @@ select d into res from t6 where d in (se
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6305,8 +6539,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6407,8 +6639,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6468,10 +6698,6 @@ select sum(data) into ret from t1 {
}
] /* steps */
} 0 0
-SET optimizer_trace="enabled=off" {
- "steps": [
- ] /* steps */
-} 0 0
select @@optimizer_trace|
@@optimizer_trace
enabled=off,end_marker=on,one_line=off
@@ -6490,8 +6716,8 @@ explain select * from v1 where id="b" {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"merged": true
} /* view */
@@ -6524,8 +6750,6 @@ explain select * from v1 where id="b" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6592,8 +6816,8 @@ insert into v1 values("z", 100) {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"merged": true
} /* view */
@@ -6610,8 +6834,8 @@ delete from v1 where data=100 {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"merged": true
} /* view */
@@ -6621,7 +6845,7 @@ delete from v1 where data=100 {
},
{
"database": "test",
- "table": "v1",
+ "table": "t1",
"range_analysis": {
"table_scan": {
"records": 3,
@@ -6643,8 +6867,8 @@ explain select * from v1 where id="b" {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"materialized": true
} /* view */
@@ -6677,8 +6901,6 @@ explain select * from v1 where id="b" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6761,8 +6983,6 @@ explain select * from v1 where id="b" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -6860,8 +7080,6 @@ VARIABLE_NAME="optimizer_trace" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "information_schema",
@@ -6972,8 +7190,6 @@ select TRACE into dumpfile 'MYSQLTEST_VA
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ],
"records_estimation": [
{
"database": "",
@@ -7037,6 +7253,29 @@ ERROR 42000: Variable 'optimizer_switch'
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+select "abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def" as col {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 'abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def' AS `col`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
+ }
+ ]
+} 0 0
drop table t1,t2;
DROP TABLE t5,t6;
set optimizer_trace=default;
=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result 2010-11-22 09:59:17 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2010-11-26 16:16:31 +0000
@@ -37,9 +37,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ],
"records_estimation": [
{
"database": "test",
@@ -86,8 +83,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
]
},
{
- "constant_tables": [
- ],
"records_estimation": [
{
"database": "test",
@@ -134,18 +129,18 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-50.3069
+50.1007
set optimizer_trace="one_line=on";
SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-6.0185
+6.0792
set optimizer_trace="one_line=off,end_marker=on";
EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
id select_type table type possible_keys key key_len ref rows Extra
@@ -177,9 +172,6 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -226,8 +218,6 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -271,12 +261,12 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
}
] /* steps */
} 0 0
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 {
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 {
"steps": [
{
"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
@@ -300,9 +290,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -349,8 +336,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -395,15 +380,12 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* steps */
} 0 0
set @@session.optimizer_trace="enabled=off";
-SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
+SELECT /* bug if you see this*/ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
NULL
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-set @@session.optimizer_trace="enabled=off" {
- "steps": [
- ] /* steps */
-} 0 0
+select QUERY from information_schema.OPTIMIZER_TRACE;
+QUERY
+SELECT /* should be last */ (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5
set @@session.optimizer_trace="enabled=on";
SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
@@ -441,9 +423,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -490,8 +469,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -555,8 +532,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -610,8 +585,6 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -751,9 +724,6 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -830,8 +800,6 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -902,16 +870,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-8802
-set optimizer_trace_max_mem_size=8400;
+8631
+set optimizer_trace_max_mem_size=9000;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
-1
+0
SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-517
+0
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -919,12 +887,12 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8285 1 1
+8631 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)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100)
-1
+0
select instr(@trace, @trace2) = 1;
instr(@trace, @trace2) = 1
1
@@ -1006,9 +974,6 @@ explain SELECT c FROM t5 where c+1 in (s
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1267,9 +1232,6 @@ explain SELECT c FROM t5 where c+1 in (s
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1324,8 +1286,6 @@ explain SELECT c FROM t5 where c+1 in (s
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1488,8 +1448,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1552,8 +1510,6 @@ explain extended select * from t1 where
"select#": 2,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1666,8 +1622,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1730,8 +1684,6 @@ explain extended select * from t1 where
"select#": 2,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1861,8 +1813,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1938,8 +1888,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2068,8 +2016,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2145,8 +2091,6 @@ explain extended select * from t1 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2236,8 +2180,6 @@ explain select * from t1,t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2373,7 +2315,7 @@ explain select * from t1,t2 {
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-misc=on,greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -2398,8 +2340,6 @@ explain select * from t1,t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2458,14 +2398,6 @@ explain select * from t1,t2 {
}
] /* steps */
} 0 0
-set @@optimizer_trace_features="greedy_search=on,misc=off";
-explain select * from t1,t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain select * from t1,t2 0 0
set @@optimizer_trace_features=default;
set @@session.optimizer_prune_level=default;
drop table t1, t2;
@@ -2535,7 +2467,7 @@ where a1 in (select b1 from t2_16 where
"from": "IN (SELECT)",
"to": "materialization",
"chosen": false,
- "cause": "field types"
+ "cause": "field_types"
} /* transformation */
},
{
@@ -2570,8 +2502,6 @@ where a1 in (select b1 from t2_16 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2647,8 +2577,6 @@ where a1 in (select b1 from t2_16 where
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2787,8 +2715,6 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2919,8 +2845,6 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3022,8 +2946,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3099,8 +3021,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3170,8 +3090,6 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -3327,8 +3245,6 @@ concat(c1,'y') IN
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3407,8 +3323,6 @@ concat(c1,'y') IN
"select#": 3,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3471,8 +3385,6 @@ concat(c1,'y') IN
"select#": 2,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3560,10 +3472,6 @@ select * from t1,t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- "t1",
- "t2"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3693,11 +3601,6 @@ trace
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t3",
- "t1",
- "t2"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3824,11 +3727,6 @@ trace
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t3",
- "t1",
- "t2"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -3939,8 +3837,6 @@ select * from t1 left join t2 on t2.a=50
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4098,8 +3994,6 @@ select * from t1 where (t1.a,t1.b) not i
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4183,8 +4077,6 @@ select * from t1 where (t1.a,t1.b) not i
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4289,8 +4181,6 @@ trace
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4622,8 +4512,6 @@ insert into t6 select * from t6 where d>
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4756,9 +4644,6 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -4904,9 +4789,6 @@ delete t6 from t5, t6 where d>7000 {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- "t5"
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5033,10 +4915,10 @@ select 5;
5
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select 3 {
+select 2 {
"steps": [
{
- "expanded_query": "/* select#1 */ select 3 AS `3`"
+ "expanded_query": "/* select#1 */ select 2 AS `2`"
},
{
"join_preparation": {
@@ -5044,20 +4926,13 @@ select 3 {
"steps": [
] /* steps */
} /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- ] /* steps */
- } /* join_optimization */
}
] /* steps */
} 0 0
-select 4 {
+select 2 {
"steps": [
{
- "expanded_query": "/* select#1 */ select 4 AS `4`"
+ "expanded_query": "/* select#1 */ select 2 AS `2`"
},
{
"join_preparation": {
@@ -5095,10 +4970,10 @@ select 5;
5
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select 4 {
+select 5 {
"steps": [
{
- "expanded_query": "/* select#1 */ select 4 AS `4`"
+ "expanded_query": "/* select#1 */ select 5 AS `5`"
},
{
"join_preparation": {
@@ -5106,13 +4981,6 @@ select 4 {
"steps": [
] /* steps */
} /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- ] /* steps */
- } /* join_optimization */
}
] /* steps */
} 0 0
@@ -5181,8 +5049,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5266,8 +5132,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5348,8 +5212,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5409,14 +5271,387 @@ select sum(data) into ret from t1 {
}
] /* steps */
} 0 0
-set optimizer_trace_offset=-4, optimizer_trace_limit=4|
+set optimizer_trace_offset=-7, optimizer_trace_limit=7|
select @@optimizer_trace_offset, @@optimizer_trace_limit|
@@optimizer_trace_offset @@optimizer_trace_limit
--4 4
+-7 7
select * from t6 where d in (select f1() from t2 where s="c")|
d
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+select * from t6 where d in (select f1() from t2 where s="c") {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t6`.`d` AS `d` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`t6`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t6.d= `f1`()",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "3 <= d <= 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "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 */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "d",
+ "records": 1,
+ "cost": 3,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1,
+ "cost": 2.6076,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.6289,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 5.2289,
+ "records": 1,
+ "duplicate_tables_left": true,
+ "chosen": true
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "d",
+ "records": 1,
+ "cost": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 4,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0213,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.0213,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 3,
+ "cost": 2.0213,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 3.0213,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 3.2212,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 3.6213,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 3,
+ "cost": 2.0212,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` = `f1`())"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual"
+ }
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
select sum(data) into ret from t1 {
"steps": [
{
@@ -5434,8 +5669,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5536,8 +5769,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5602,20 +5833,10 @@ select * from t6 where d in (select f1()
d
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-delete from t1 where id="z" {
+insert into t1 values("z",0) {
"steps": [
{
- "expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
- },
- {
- "database": "test",
- "table": "t1",
- "range_analysis": {
- "table_scan": {
- "records": 3,
- "cost": 4.7154
- } /* table_scan */
- } /* range_analysis */
+ "expanded_query": "/* select#1 */ select from dual"
}
] /* steps */
} 0 0
@@ -5665,8 +5886,6 @@ select d+1 into res from t6 where d= NAM
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5734,8 +5953,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5805,12 +6022,9 @@ c 3 3
3 100 200
3 100 200
prepare stmt from 'call p1(?)';
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-prepare stmt from 'call p1(?)' {
- "steps": [
- ] /* steps */
-} 0 0
+select QUERY from information_schema.OPTIMIZER_TRACE;
+QUERY
+select * from t2
set @param="c";
execute stmt using @param;
Warnings:
@@ -5852,8 +6066,6 @@ select @trace;
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -5981,8 +6193,6 @@ select d into res from t6 where d in (se
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6281,8 +6491,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6383,8 +6591,6 @@ select sum(data) into ret from t1 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6444,10 +6650,6 @@ select sum(data) into ret from t1 {
}
] /* steps */
} 0 0
-SET optimizer_trace="enabled=off" {
- "steps": [
- ] /* steps */
-} 0 0
select @@optimizer_trace|
@@optimizer_trace
enabled=off,end_marker=on,one_line=off
@@ -6466,8 +6668,8 @@ explain select * from v1 where id="b" {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"merged": true
} /* view */
@@ -6500,8 +6702,6 @@ explain select * from v1 where id="b" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6581,7 +6781,7 @@ delete from v1 where data=100 {
},
{
"database": "test",
- "table": "v1",
+ "table": "t1",
"range_analysis": {
"table_scan": {
"records": 3,
@@ -6603,8 +6803,8 @@ explain select * from v1 where id="b" {
"steps": [
{
"view": {
- "db": "test",
- "name": "v1",
+ "database": "test",
+ "view": "v1",
"in_select#": 1,
"materialized": true
} /* view */
@@ -6637,8 +6837,6 @@ explain select * from v1 where id="b" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -6721,8 +6919,6 @@ explain select * from v1 where id="b" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "",
@@ -6820,8 +7016,6 @@ VARIABLE_NAME="optimizer_trace" {
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "information_schema",
@@ -6932,8 +7126,6 @@ select TRACE into dumpfile 'MYSQLTEST_VA
"select#": "fake",
"steps": [
{
- "constant_tables": [
- ],
"records_estimation": [
{
"database": "",
@@ -6997,6 +7189,29 @@ ERROR 42000: Variable 'optimizer_switch'
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+select "abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def" as col {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 'abc1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111def' AS `col`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ ]
+ }
+ }
+ ]
+} 0 0
drop table t1,t2;
DROP TABLE t5,t6;
set optimizer_trace=default;
=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result 2010-11-22 09:59:17 +0000
+++ b/mysql-test/r/optimizer_trace_range.result 2010-11-24 18:54:26 +0000
@@ -71,8 +71,6 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -253,8 +251,6 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -370,8 +366,6 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -576,8 +570,6 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -765,8 +757,6 @@ EXPLAIN SELECT DISTINCT key2 FROM t2 {
"select#": 1,
"steps": [
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -847,7 +837,7 @@ EXPLAIN SELECT DISTINCT key2 FROM t2 {
"best_group_range_summary": {
"type": "index_group",
"index": "i2_1",
- "group_attribute": "null",
+ "group_attribute": null,
"min_aggregate": false,
"max_aggregate": false,
"distinct_aggregate": false,
@@ -864,7 +854,7 @@ EXPLAIN SELECT DISTINCT key2 FROM t2 {
"range_access_plan": {
"type": "index_group",
"index": "i2_1",
- "group_attribute": "null",
+ "group_attribute": null,
"min_aggregate": false,
"max_aggregate": false,
"distinct_aggregate": false,
@@ -968,8 +958,6 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1203,8 +1191,6 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1390,8 +1376,6 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1644,8 +1628,6 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 <
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1782,8 +1764,6 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -1964,8 +1944,6 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2177,8 +2155,6 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2397,8 +2373,6 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
] /* ref-optimizer-key-uses */
},
{
- "constant_tables": [
- ] /* constant_tables */,
"records_estimation": [
{
"database": "test",
@@ -2431,9 +2405,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
{
"index": "cola",
"ranges": [
- "f
-o <= cola <= f
-o"
+ "f\no <= cola <= f\no"
] /* ranges */,
"index_only": false,
"records": 1,
@@ -2453,9 +2425,7 @@ o"
"index": "cola",
"records": 1,
"ranges": [
- "f
-o <= cola <= f
-o"
+ "f\no <= cola <= f\no"
] /* ranges */
} /* range_access_plan */,
"records_for_plan": 1,
=== removed file 'mysql-test/suite/ndb/r/optimizer_trace_range_opt_bug.result'
--- a/mysql-test/suite/ndb/r/optimizer_trace_range_opt_bug.result 2010-05-25 09:49:19 +0000
+++ b/mysql-test/suite/ndb/r/optimizer_trace_range_opt_bug.result 1970-01-01 00:00:00 +0000
@@ -1,1031 +0,0 @@
-create table t1 (
-uid int, fid int, index(uid)
-) engine=ndb;
-insert into t1 values
-(1,1), (1,2), (1,3), (1,4),
-(2,5), (2,6), (2,7), (2,8),
-(3,1), (3,2), (3,9);
-create table t2 (
-uid int primary key, name varchar(128), index(name)
-) engine=ndb;
-insert into t2 values
-(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
-(6, "F"), (7, "G"), (8, "H"), (9, "I");
-create table t3 (
-uid int, fid int, index(uid)
-) engine=ndb;
-insert into t3 values
-(1,1), (1,2), (1,3),(1,4),
-(2,5), (2,6), (2,7), (2,8),
-(3,1), (3,2), (3,9);
-create table t4 (
-uid int primary key, name varchar(128), index(name)
-) engine=ndb;
-insert into t4 values
-(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
-(6, "F"), (7, "G"), (8, "H"), (9, "I");
-select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid;
-uid
-1
-4
-2
-3
-set optimizer_join_cache_level=7;
-set optimizer_trace="enabled=on,end_marker=on";
-explain select name from t2, t1
-where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
-and t2.uid=t1.fid;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ref uid uid 5 const 10 Using where with pushed condition; Start temporary
-1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 1 Using where with pushed condition; End temporary
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY_ID TRACE
-0
-{
- "top_query": "explain select name from t2, t1
-where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
-and t2.uid=t1.fid",
- "join_preparation": {
- "join_preparation": {
- "subquery_strategy": {
- "subquery": "`t1`.`uid` in (select #2)",
- "maybe_semijoin_transformation": true
- } /* subquery_strategy */
- } /* join_preparation */
- } /* join_preparation */,
- "join_optimization": {
- "transformation": {
- "from": "subquery",
- "to": "semijoin",
- "transformed": [
- "`test`.`t1`.`uid` in (select #2)"
- ] /* transformed */
- } /* transformation */,
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`uid` = `test`.`t1`.`fid`) and (`test`.`t3`.`uid` = 1) and (`test`.`t4`.`uid` = `test`.`t3`.`fid`) and (`test`.`t1`.`uid` = `test`.`t4`.`uid`))",
- "after_equality_propagation": "(1 and multiple equal(`test`.`t2`.`uid`, `test`.`t1`.`fid`) and multiple equal(1, `test`.`t3`.`uid`) and multiple equal(`test`.`t4`.`uid`, `test`.`t3`.`fid`, `test`.`t1`.`uid`))",
- "after_constant_propagation": "(1 and multiple equal(`test`.`t2`.`uid`, `test`.`t1`.`fid`) and multiple equal(1, `test`.`t3`.`uid`) and multiple equal(`test`.`t4`.`uid`, `test`.`t3`.`fid`, `test`.`t1`.`uid`))",
- "after_trivial_conditions_removal": "(multiple equal(`test`.`t2`.`uid`, `test`.`t1`.`fid`) and multiple equal(1, `test`.`t3`.`uid`) and multiple equal(`test`.`t4`.`uid`, `test`.`t3`.`fid`, `test`.`t1`.`uid`))"
- } /* condition_processing */,
- "ref-optimizer": {
- "keyuse": {
- "condition": "t2.uid= `test`.`t1`.`fid`",
- "null_rejecting": true
- } /* keyuse */,
- "keyuse": {
- "condition": "t1.uid= `test`.`t4`.`uid`",
- "null_rejecting": false
- } /* keyuse */,
- "keyuse": {
- "condition": "t1.uid= `test`.`t3`.`fid`",
- "null_rejecting": true
- } /* keyuse */,
- "keyuse": {
- "condition": "t4.uid= `test`.`t3`.`fid`",
- "null_rejecting": true
- } /* keyuse */,
- "keyuse": {
- "condition": "t4.uid= `test`.`t1`.`uid`",
- "null_rejecting": true
- } /* keyuse */,
- "keyuse": {
- "condition": "t3.uid= 1",
- "null_rejecting": false
- } /* keyuse */
- } /* ref-optimizer */,
- "constant_table_detection": {
- "records_estimation": [
- {
- "table": "t2",
- "table_scan": {
- "records": 9,
- "cost": 9000
- } /* table_scan */
- },
- {
- "table": "t1",
- "table_scan": {
- "records": 11,
- "cost": 11000
- } /* table_scan */
- },
- {
- "table": "t4",
- "table_scan": {
- "records": 9,
- "cost": 9000
- } /* table_scan */
- },
- {
- "table": "t3",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- }
- ] /* records_estimation */
- } /* constant_table_detection */,
- "semijoin_table_pullout": {
- "pulled_out": {
- "table": "t4",
- "functionally_dependent": true
- } /* pulled_out */
- } /* semijoin_table_pullout */,
- "execution_plan_for_potential_materialization": {
- } /* execution_plan_for_potential_materialization */,
- "considered_execution_plans": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9000,
- "records_for_plan": 9,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t4",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9082.8,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 18083,
- "records_for_plan": 81,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 162,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 18245,
- "records_for_plan": 810,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 810,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 19055,
- "records_for_plan": 810,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 19500,
- "records": 81,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 81,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 18164,
- "records_for_plan": 81,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 162,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 18326,
- "records_for_plan": 810,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "cost": 18326,
- "records": 81,
- "chosen": true
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 18771,
- "records": 81,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 18,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9018,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t4",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "cost": 10,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9028,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 90,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9118,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 9167.5,
- "records": 9,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 90,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9108,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_heuristic": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 11101,
- "records": 11,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 20101,
- "records_for_plan": 99,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t4",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9000,
- "records_for_plan": 9,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9002.5,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 18002,
- "records_for_plan": 81,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- },
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 18,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9018,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9080.3,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 18098,
- "records_for_plan": 810,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 90,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9108,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 9157.5,
- "records": 9,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "cost": 9,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9166.5,
- "records_for_plan": 9,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 9166.5,
- "records": 9,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 9,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9009,
- "records_for_plan": 9,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 18,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9027,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "cost": 9027,
- "records": 9,
- "chosen": true
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 9076.5,
- "records": 9,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "cost": 9,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9036,
- "records_for_plan": 9,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 9085.5,
- "records": 9,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "cost": 9,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9018,
- "records_for_plan": 9,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 18,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9036,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "cost": 9036,
- "records": 9,
- "chosen": true
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 9085.5,
- "records": 9,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- }
- ] /* rest_of_plan */
- }
- ] /* rest_of_plan */
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t3",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 2,
- "records": 10,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2,
- "records_for_plan": 10,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t4",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "cost": 10,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 12,
- "records_for_plan": 10,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9008.9,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9020.9,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 10,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 22,
- "records_for_plan": 10,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 27.5,
- "records": 1,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "cost": 10,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 9000,
- "records": 9,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 37.5,
- "records_for_plan": 1,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 37.5,
- "records": 1,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 9006.2,
- "records": 9,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 9008.2,
- "records_for_plan": 90,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "cost": 10,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 12,
- "records_for_plan": 10,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_heuristic": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "uid",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 11000,
- "records": 11,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 11000,
- "records_for_plan": 11,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- }
- ] /* considered_execution_plans */,
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t4`.`uid` = `test`.`t3`.`fid`) and (`test`.`t1`.`uid` = `test`.`t3`.`fid`) and (`test`.`t3`.`uid` = 1) and (`test`.`t2`.`uid` = `test`.`t1`.`fid`))",
- "attached_conditions": [
- {
- "table": "t3",
- "attached": "(`test`.`t3`.`fid` is not null)"
- },
- {
- "table": "t4",
- "attached": "<null>"
- },
- {
- "table": "t1",
- "attached": "((`test`.`t1`.`uid` = `test`.`t3`.`fid`) and (`test`.`t1`.`fid` is not null))"
- },
- {
- "table": "t2",
- "attached": "<null>"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- } /* join_optimization */
-}
-select name from t2, t1
-where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
-and t2.uid=t1.fid;
-name
-A
-A
-B
-B
-C
-D
-E
-F
-G
-H
-I
-select name from t2, t1
-where t1.uid in (1,3,2,3,4)
-and t2.uid=t1.fid;
-name
-A
-A
-B
-B
-C
-D
-E
-F
-G
-H
-I
-set optimizer_join_cache_level=default;
-drop table t1,t2,t3,t4;
=== removed file 'mysql-test/suite/ndb/t/optimizer_trace_range_opt_bug.test'
--- a/mysql-test/suite/ndb/t/optimizer_trace_range_opt_bug.test 2010-05-25 09:49:19 +0000
+++ b/mysql-test/suite/ndb/t/optimizer_trace_range_opt_bug.test 1970-01-01 00:00:00 +0000
@@ -1,58 +0,0 @@
--- source include/have_ndb.inc
--- source include/not_embedded.inc
-
-# this comes from test for BUG#53305
-
-create table t1 (
- uid int, fid int, index(uid)
-) engine=ndb;
-insert into t1 values
- (1,1), (1,2), (1,3), (1,4),
- (2,5), (2,6), (2,7), (2,8),
- (3,1), (3,2), (3,9);
-
-create table t2 (
- uid int primary key, name varchar(128), index(name)
-) engine=ndb;
-insert into t2 values
- (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
- (6, "F"), (7, "G"), (8, "H"), (9, "I");
-
-create table t3 (
- uid int, fid int, index(uid)
-) engine=ndb;
-insert into t3 values
- (1,1), (1,2), (1,3),(1,4),
- (2,5), (2,6), (2,7), (2,8),
- (3,1), (3,2), (3,9);
-
-create table t4 (
- uid int primary key, name varchar(128), index(name)
-) engine=ndb;
-insert into t4 values
- (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
- (6, "F"), (7, "G"), (8, "H"), (9, "I");
-
-select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid;
-
-set optimizer_join_cache_level=7;
-
-set optimizer_trace="enabled=on,end_marker=on";
-explain select name from t2, t1
- where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
- and t2.uid=t1.fid;
-select * from information_schema.OPTIMIZER_TRACE;
-
---sorted_result
-select name from t2, t1
- where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
- and t2.uid=t1.fid;
-
---sorted_result
-select name from t2, t1
- where t1.uid in (1,3,2,3,4)
- and t2.uid=t1.fid;
-
-set optimizer_join_cache_level=default;
-
-drop table t1,t2,t3,t4;
=== modified file 'mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result 2010-11-17 12:27:39 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result 2010-11-23 10:21:53 +0000
@@ -1,45 +1,45 @@
SET @start_global_value = @@global.optimizer_trace_features;
SELECT @start_global_value;
@start_global_value
-misc=on,greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-misc=on,greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-misc=on,greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on
show global variables like 'optimizer_trace_features';
Variable_name Value
-optimizer_trace_features misc=on,greedy_search=on,range_optimizer=on
+optimizer_trace_features greedy_search=on,range_optimizer=on
show session variables like 'optimizer_trace_features';
Variable_name Value
-optimizer_trace_features misc=on,greedy_search=on,range_optimizer=on
+optimizer_trace_features greedy_search=on,range_optimizer=on
select * from information_schema.global_variables where variable_name='optimizer_trace_features';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES misc=on,greedy_search=on,range_optimizer=on
+OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on
select * from information_schema.session_variables where variable_name='optimizer_trace_features';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES misc=on,greedy_search=on,range_optimizer=on
+OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on
set global optimizer_trace_features=2;
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-misc=off,greedy_search=on,range_optimizer=off
+greedy_search=off,range_optimizer=on
set session optimizer_trace_features=2;
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-misc=off,greedy_search=on,range_optimizer=off
+greedy_search=off,range_optimizer=on
set global optimizer_trace_features=0;
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-misc=off,greedy_search=off,range_optimizer=off
+greedy_search=off,range_optimizer=off
set session optimizer_trace_features=0;
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-misc=off,greedy_search=off,range_optimizer=off
+greedy_search=off,range_optimizer=off
set session optimizer_trace_features=default;
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-misc=off,greedy_search=off,range_optimizer=off
+greedy_search=off,range_optimizer=off
set global optimizer_trace_features=1.1;
ERROR 42000: Incorrect argument type to variable 'optimizer_trace_features'
set global optimizer_trace_features=1e1;
@@ -49,4 +49,4 @@ ERROR 42000: Variable 'optimizer_trace_f
SET @@global.optimizer_trace_features = @start_global_value;
SELECT @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-misc=on,greedy_search=on,range_optimizer=on
+greedy_search=on,range_optimizer=on
=== added file 'mysql-test/t/optimizer_trace2.test'
--- a/mysql-test/t/optimizer_trace2.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace2.test 2010-12-19 14:24:03 +0000
@@ -0,0 +1,49 @@
+# Continuation of tests for optimizer trace
+
+--source include/have_optimizer_trace.inc
+
+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;
+delimiter |;
+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")|
+--echo
+# we should not see the trace of "select TRACE+NULL..."
+# because tracing is disabled when OPTIMIZER_TRACE table is used.
+# "distinct" is because in ps-protocol mode, we get 'select f1("c")'
+# twice (one PREPARE, one EXECUTE), and we must have a single result
+# file for normal and ps-protocol mode.
+select distinct QUERY from information_schema.OPTIMIZER_TRACE|
+delimiter ;|
+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";
+select locate("\"view\": \"v1\"", TRACE) != 0
+from information_schema.OPTIMIZER_TRACE;
+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';
+# Note that in the trace we get either 0x0c or 12
+select TRACE from information_schema.OPTIMIZER_TRACE;
+drop table t1;
=== modified file 'mysql-test/t/optimizer_trace_charset.test'
--- a/mysql-test/t/optimizer_trace_charset.test 2010-10-22 08:31:43 +0000
+++ b/mysql-test/t/optimizer_trace_charset.test 2010-11-24 18:54:26 +0000
@@ -13,6 +13,9 @@ if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL
set @@session.optimizer_trace="enabled=on,end_marker=on";
create table t1(a int);
insert into t1 values(1);
+
+# test non-utf8 chars in the SELECT list
+
set names latin1;
explain extended select '��� from t1 limit 1;
# In the trace and EXPLAIN EXTENDED's warning we see symptoms of BUG#57341
@@ -24,19 +27,48 @@ select @query, @trace;
set names utf8;
select @query, @trace;
+# test non-utf8 chars in a view's definition
+
set names latin1;
create view v1 as select '��� as col from t1 limit 1;
select * from v1 where v1.col = '���;
select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
+set names utf8;
select @query, @trace;
drop table t1;
drop view v1;
+# test non-ASCII chars in a range
+
+set names latin1;
create table t1(c char(4) primary key);
insert into t1 values ('aaa'), ('��');
select * from t1 where c < '���;
-select query, trace from information_schema.OPTIMIZER_TRACE;
+select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
+set names utf8;
+select @query, @trace;
drop table t1;
+# test non-ASCII chars in an identifier
+create table `t
+1 a`(`col
+1 a` int, index `index
+1 a` (`col
+1 a`));
+insert into `t
+1 a` values(2),(3);
+create view `v
+1 a` as select * from `t
+1 a`;
+select `col
+1 a` from `v
+1 a` where `col
+1 a` < 6;
+select query, trace from information_schema.OPTIMIZER_TRACE;
+drop table `t
+1 a`;
+drop view `v
+1 a`;
+
set optimizer_trace=default;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2010-11-22 13:22:03 +0000
+++ b/sql/opt_range.cc 2010-12-21 07:34:33 +0000
@@ -118,10 +118,6 @@
#include <m_ctype.h>
#include "sql_select.h"
#include "opt_trace.h"
-/**
- All tracing done is here is still SergeyP's prototype and not understood.
- @todo fix this before release
-*/
#ifndef EXTRA_DEBUG
#define test_rb_tree(A,B) {}
@@ -796,17 +792,13 @@ TABLE_READ_PLAN *get_best_disjunct_quick
static
TRP_GROUP_MIN_MAX *get_best_group_min_max(PARAM *param, SEL_TREE *tree,
double read_time);
-#ifndef DBUG_OFF
+#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
static void print_sel_tree(PARAM *param, SEL_TREE *tree, key_map *tree_map,
const char *msg);
static void print_ror_scans_arr(TABLE *table, const char *msg,
struct st_ror_scan_info **start,
struct st_ror_scan_info **end);
-#endif
-#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
-#if 0
static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg);
-#endif
static void append_range(String *out,
const KEY_PART_INFO *key_parts,
const uchar *min_key, const uchar *max_key,
@@ -1967,53 +1959,6 @@ public:
Opt_trace_object *trace_object) const = 0;
};
-/**
- Print a key into a stream
-
- @param[out] out String the key is appended to
- @param[in] key_part Index components description
- @param[in] key Key tuple
- @param[in] used_length Key tuple length
-*/
-static void
-print_key_value(String *out, const KEY_PART_INFO *key_part, const uchar *key)
-{
- String tmp;
- uint store_length;
- TABLE *table= key_part->field->table;
- my_bitmap_map *old_sets[2];
-
- dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
-
- Field *field= key_part->field;
- store_length= key_part->store_length;
-
- if (field->real_maybe_null())
- {
- /*
- Byte 0 of key is the null-byte. If set, key is NULL.
- Otherwise, print the key value starting immediately after the
- null-byte
- */
- if (*key)
- {
- out->append(STRING_WITH_LEN("NULL"));
- goto restore_col_map;
- }
- key++; // Skip null byte
- store_length--;
- }
- field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void) field->val_int_as_str(&tmp, 1); // may change tmp's charset
- else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
-
-restore_col_map:
- dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
-}
-
/*
Plan for a QUICK_RANGE_SELECT scan.
TRP_RANGE::make_quick ignores retrieve_full_rows parameter because
@@ -2058,9 +2003,8 @@ public:
const KEY cur_key= param->table->key_info[keynr_in_table];
const KEY_PART_INFO *key_part= cur_key.key_part;
- trace_object->add_str("type", "range_scan").
- add_str("index", cur_key.name, strlen(cur_key.name)).
- add("records", records);
+ trace_object->add_alnum("type", "range_scan").
+ add_utf8("index", cur_key.name).add("records", records);
Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
for (const SEL_ARG *current= key;
@@ -2078,7 +2022,7 @@ public:
part->min_value, part->max_value,
part->min_flag | part->max_flag);
}
- trace_range.add_str(range_info.ptr(), range_info.length());
+ trace_range.add_utf8(range_info.ptr(), range_info.length());
}
#endif
}
@@ -2128,7 +2072,7 @@ public:
Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace_object->add_str("type", "index_roworder_intersect").
+ trace_object->add_alnum("type", "index_roworder_intersect").
add("records", records).
add("cost", read_cost).
add("covering", is_covering).
@@ -2144,9 +2088,8 @@ public:
const KEY_PART_INFO *key_part= cur_key.key_part;
Opt_trace_object trace_isect_idx(trace_ctx);
- trace_isect_idx.add_str("type", "range_scan").
- add_str("index", cur_key.name, strlen(cur_key.name)).
- add("records", (*cur_scan)->records);
+ trace_isect_idx.add_alnum("type", "range_scan").
+ add_utf8("index", cur_key.name).add("records", (*cur_scan)->records);
Opt_trace_array trace_range(trace_ctx, "ranges");
for (const SEL_ARG *current= (*cur_scan)->sel_arg;
@@ -2164,7 +2107,7 @@ public:
part->min_value, part->max_value,
part->min_flag | part->max_flag);
}
- trace_range.add_str(range_info.ptr(), range_info.length());
+ trace_range.add_utf8(range_info.ptr(), range_info.length());
}
}
#endif
@@ -2192,7 +2135,7 @@ public:
Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace_object->add_str("type", "index_roworder_union");
+ trace_object->add_alnum("type", "index_roworder_union");
Opt_trace_array ota(param->thd->opt_trace, "union_of");
for (TABLE_READ_PLAN **current= first_ror;
current != last_ror;
@@ -2225,7 +2168,7 @@ public:
Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace_object->add_str("type", "index_merge");
+ trace_object->add_alnum("type", "index_merge");
Opt_trace_array ota(param->thd->opt_trace, "index_merge_of");
for (TRP_RANGE **current= range_scans;
current != range_scans_end;
@@ -2279,14 +2222,13 @@ public:
Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace_object->add_str("type", "index_group").
- add_str("index", index_info->name, strlen(index_info->name));
+ trace_object->add_alnum("type", "index_group").
+ add_utf8("index", index_info->name);
if (min_max_arg_part)
- trace_object->add_str("group_attribute",
- min_max_arg_part->field->field_name,
- strlen(min_max_arg_part->field->field_name));
+ trace_object->add_utf8("group_attribute",
+ min_max_arg_part->field->field_name);
else
- trace_object->add_str("group_attribute", "null");
+ trace_object->add_null("group_attribute");
trace_object->add("min_aggregate", have_min).
add("max_aggregate", have_max).
add("distinct_aggregate", have_agg_distinct).
@@ -2300,7 +2242,7 @@ public:
for (uint partno= 0; partno < used_key_parts; partno++)
{
const KEY_PART_INFO *cur_key_part= key_part + partno;
- trace_keyparts.add_str(cur_key_part->field->field_name);
+ trace_keyparts.add_utf8(cur_key_part->field->field_name);
}
}
Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
@@ -2319,7 +2261,7 @@ public:
part->min_value, part->max_value,
part->min_flag | part->max_flag);
}
- trace_range.add_str(range_info.ptr(), range_info.length());
+ trace_range.add_utf8(range_info.ptr(), range_info.length());
}
#endif
}
@@ -2558,19 +2500,18 @@ int SQL_SELECT::test_quick_select(THD *t
for (idx=0 ; idx < head->s->keys ; idx++, key_info++)
{
Opt_trace_object trace_idx_details(trace);
- trace_idx_details.add_str("index",
- key_info->name, strlen(key_info->name));
+ trace_idx_details.add_utf8("index", key_info->name);
KEY_PART_INFO *key_part_info;
if (!keys_to_use.is_set(idx))
{
trace_idx_details.add("usable", false).
- add_str("cause", "not_usable_by_conditions");
+ add_alnum("cause", "not_usable_by_conditions");
continue;
}
if (key_info->flags & HA_FULLTEXT)
{
trace_idx_details.add("usable", false).
- add_str("cause", "fulltext");
+ add_alnum("cause", "fulltext");
continue; // ToDo: ft-keys in non-ft ranges, if possible SerG
}
@@ -2592,8 +2533,7 @@ int SQL_SELECT::test_quick_select(THD *t
(key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW;
/* Only HA_PART_KEY_SEG is used */
key_parts->flag= (uint8) key_part_info->key_part_flag;
- trace_keypart.add_str(key_parts->field->field_name,
- strlen(key_parts->field->field_name));
+ trace_keypart.add_utf8(key_parts->field->field_name);
}
param.real_keynr[param.keys++]=idx;
}
@@ -2620,12 +2560,10 @@ int SQL_SELECT::test_quick_select(THD *t
Opt_trace_object trace_cov(trace,
"best_covering_index_scan",
Opt_trace_context::RANGE_OPTIMIZER);
- trace_cov.add_str("index", head->key_info[key_for_use].name,
- strlen(head->key_info[key_for_use].name)).
- add("cost", key_read_time).
- add("chosen", chosen);
+ trace_cov.add_utf8("index", head->key_info[key_for_use].name).
+ add("cost", key_read_time).add("chosen", chosen);
if (!chosen)
- trace_cov.add_str("cause", "cost");
+ trace_cov.add_alnum("cause", "cost");
}
TABLE_READ_PLAN *best_trp= NULL;
@@ -2652,7 +2590,7 @@ int SQL_SELECT::test_quick_select(THD *t
DBUG_ASSERT(tree->type == SEL_TREE::ALWAYS); //anything else possible?
trace_range.add("range_scan_possible", false);
if (tree->type == SEL_TREE::ALWAYS)
- trace_range.add_str("cause", "condition_always_true");
+ trace_range.add_alnum("cause", "condition_always_true");
tree= NULL;
}
@@ -2679,7 +2617,7 @@ int SQL_SELECT::test_quick_select(THD *t
best_read_time= best_trp->read_cost;
}
else
- grp_summary.add("chosen", false).add_str("cause", "cost");
+ grp_summary.add("chosen", false).add_alnum("cause", "cost");
}
if (tree)
@@ -2804,6 +2742,8 @@ free_mem:
thd->no_errors=0;
}
+ DBUG_EXECUTE("info", print_quick(quick, &needed_reg););
+
/*
Assume that if the user is using 'limit' we will only need to scan
limit rows if we are using a key
@@ -3164,7 +3104,7 @@ end:
void store_key_image_to_rec(Field *field, uchar *ptr, uint len)
{
- /* Do the same as print_key() does */
+ /* Do the same as print_key_value() does */
my_bitmap_map *old_map;
if (field->real_maybe_null())
@@ -4167,7 +4107,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
}
if (imerge_too_expensive)
{
- trace_idx.add("chosen", false).add_str("cause", "cost");
+ trace_idx.add("chosen", false).add_alnum("cause", "cost");
continue;
}
@@ -4185,8 +4125,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
non_cpk_scan_records += (*cur_child)->records;
trace_idx.
- add_str("index_to_merge", param->table->key_info[keynr_in_table].name,
- strlen(param->table->key_info[keynr_in_table].name)).
+ add_utf8("index_to_merge", param->table->key_info[keynr_in_table].name).
add("cumulated_cost", imerge_cost);
}
@@ -4205,8 +4144,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
*/
DBUG_PRINT("info", ("Sum of index_merge scans is more expensive than "
"full table scan, bailing out"));
- trace_best_disjunct.add("chosen", false).
- add_str("cause", "cost");
+ trace_best_disjunct.add("chosen", false).add_alnum("cause", "cost");
DBUG_RETURN(NULL);
}
@@ -4220,7 +4158,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
{
roru_read_plans= (TABLE_READ_PLAN**)range_scans;
trace_best_disjunct.add("use_roworder_union", true).
- add_str("cause", "always_cheaper_than_not_roworder_retrieval");
+ add_alnum("cause", "always_cheaper_than_not_roworder_retrieval");
goto skip_to_ror_scan;
}
@@ -4253,7 +4191,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
{
trace_best_disjunct.add("use_roworder_index_merge", true).
- add_str("cause", "cost");
+ add_alnum("cause", "cost");
goto build_ror_index_merge;
}
@@ -4944,7 +4882,7 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
{
if (tree->n_ror_scans < 2)
trace_ror.add("usable", false).
- add_str("cause", "too_few_roworder_scans");
+ add_alnum("cause", "too_few_roworder_scans");
else
trace_ror.add("usable", false).add("need_tracing", true);
DBUG_RETURN(NULL);
@@ -5025,12 +4963,12 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
{
Opt_trace_object trace_idx(param->thd->opt_trace);
char *idx_name= param->table->key_info[(*cur_ror_scan)->keynr].name;
- trace_idx.add_str("index", idx_name, strlen(idx_name));
+ trace_idx.add_utf8("index", idx_name);
/* S= S + first(R); R= R - first(R); */
if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
{
trace_idx.add("usable", false).
- add_str("cause", "does_not_reduce_cost_of_intersect");
+ add_alnum("cause", "does_not_reduce_cost_of_intersect");
cur_ror_scan++;
continue;
}
@@ -5089,13 +5027,13 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
}
else
trace_cpk.add("clustered_pk_added_to_intersect", false).
- add_str("cause", "cost");
+ add_alnum("cause", "cost");
}
else
{
trace_cpk.add("clustered_pk_added_to_intersect", false).
- add_str("cause", cpk_scan ?
- "roworder_is_covering" : "no_clustered_pk_index");
+ add_alnum("cause", cpk_scan ?
+ "roworder_is_covering" : "no_clustered_pk_index");
}
}
/* Ok, return ROR-intersect plan if we have found one */
@@ -5364,8 +5302,7 @@ static TRP_RANGE *get_key_scans_params(P
(bool) param->table->covering_keys.is_set(keynr);
Opt_trace_object trace_idx(param->thd->opt_trace);
- trace_idx.add_str("index", param->table->key_info[keynr].name,
- strlen(param->table->key_info[keynr].name));
+ trace_idx.add_utf8("index", param->table->key_info[keynr].name);
found_records= check_quick_select(param, idx, read_index_only, *key,
update_tbl_stats, &mrr_flags,
@@ -5394,7 +5331,7 @@ static TRP_RANGE *get_key_scans_params(P
best_buf_size= buf_size;
}
else
- trace_idx.add("chosen", false).add_str("cause", "cost");
+ trace_idx.add("chosen", false).add_alnum("cause", "cost");
}
}
@@ -6363,6 +6300,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
field->type() == MYSQL_TYPE_DATETIME))
field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
{
+ // Note that value may be a stored function call, executed here
err= value->save_in_field_no_warnings(field, 1);
}
if (err > 0)
@@ -6571,9 +6509,9 @@ end:
{
Opt_trace_object (param->thd->opt_trace, "impossible_condition",
Opt_trace_context::RANGE_OPTIMIZER).
- add_str("table", *field->table_name, strlen(*field->table_name)).
- add_str("field", field->field_name, strlen(field->field_name)).
- add_str("cause", impossible_cond_cause);
+ add_utf8("table", *field->table_name).
+ add_utf8("field", field->field_name).
+ add_alnum("cause", impossible_cond_cause);
}
param->thd->mem_root= alloc;
DBUG_RETURN(tree);
@@ -8269,7 +8207,7 @@ walk_up_n_right:
*/
Opt_trace_struct *trace_range=
seq->param->thd->opt_trace->get_current_struct();
- trace_range->add_str(key_range_trace.ptr(), key_range_trace.length());
+ trace_range->add_utf8(key_range_trace.ptr(), key_range_trace.length());
}
#endif
@@ -10266,8 +10204,7 @@ get_best_group_min_max(PARAM *param, SEL
cur_index_info++, cur_index++)
{
Opt_trace_object trace_idx(thd->opt_trace);
- trace_idx.add_str("index", cur_index_info->name,
- strlen(cur_index_info->name));
+ trace_idx.add_utf8("index", cur_index_info->name);
KEY_PART_INFO *cur_part;
KEY_PART_INFO *end_part; /* Last part for loops. */
/* Last index part. */
@@ -10581,7 +10518,7 @@ get_best_group_min_max(PARAM *param, SEL
next_index:
if (cause)
{
- trace_idx.add("usable", false).add_str("cause", cause);
+ trace_idx.add("usable", false).add_alnum("cause", cause);
cause= NULL;
}
}
@@ -10631,12 +10568,12 @@ get_best_group_min_max(PARAM *param, SEL
return_null_not_chosen_cause:
DBUG_ASSERT(cause);
- trace_group.add("chosen", false).add_str("cause", cause);
+ trace_group.add("chosen", false).add_alnum("cause", cause);
DBUG_RETURN(NULL);
return_null_not_usable_cause:
DBUG_ASSERT(cause);
- trace_group.add("usable", false).add_str("cause", cause);
+ trace_group.add("usable", false).add_alnum("cause", cause);
DBUG_RETURN(NULL);
}
@@ -12317,14 +12254,58 @@ static void print_ror_scans_arr(TABLE *t
#endif /* !DBUG_OFF */
+#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
+
/*****************************************************************************
-** Print a quick range for debugging
-** TODO:
-** This should be changed to use a String to store each row instead
-** of locking the DEBUG stream !
+** Append a range for a single keypart into a stream.
*****************************************************************************/
-#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
+/**
+ Print a key into a stream
+
+ @param[out] out String the key is appended to
+ @param[in] key_part Index components description
+ @param[in] key Key tuple
+ @param[in] used_length Key tuple length
+*/
+static void
+print_key_value(String *out, const KEY_PART_INFO *key_part, const uchar *key)
+{
+ String tmp;
+ uint store_length;
+ TABLE *table= key_part->field->table;
+ my_bitmap_map *old_sets[2];
+
+ dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+
+ Field *field= key_part->field;
+ store_length= key_part->store_length;
+
+ if (field->real_maybe_null())
+ {
+ /*
+ Byte 0 of key is the null-byte. If set, key is NULL.
+ Otherwise, print the key value starting immediately after the
+ null-byte
+ */
+ if (*key)
+ {
+ out->append(STRING_WITH_LEN("NULL"));
+ goto restore_col_map;
+ }
+ key++; // Skip null byte
+ store_length--;
+ }
+ field->set_key_image(key, key_part->length);
+ if (field->type() == MYSQL_TYPE_BIT)
+ (void) field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ else
+ field->val_str(&tmp); // may change tmp's charset
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
+
+restore_col_map:
+ dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+}
/**
Append range info for a key part to a string
@@ -12365,7 +12346,53 @@ static void append_range(String *out,
}
}
-#if 0
+/*****************************************************************************
+** Print a quick range for debugging
+** TODO:
+** This should be changed to use a String to store each row instead
+** of locking the DEBUG stream !
+*****************************************************************************/
+
+static void
+print_multiple_key_values(KEY_PART *key_part, const uchar *key,
+ uint used_length)
+{
+ char buff[1024];
+ const uchar *key_end= key+used_length;
+ String tmp(buff,sizeof(buff),&my_charset_bin);
+ uint store_length;
+ TABLE *table= key_part->field->table;
+ my_bitmap_map *old_sets[2];
+
+ dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+
+ for (; key < key_end; key+=store_length, key_part++)
+ {
+ Field *field= key_part->field;
+ store_length= key_part->store_length;
+
+ if (field->real_maybe_null())
+ {
+ if (*key)
+ {
+ fwrite("NULL",sizeof(char),4,DBUG_FILE);
+ continue;
+ }
+ key++; // Skip null byte
+ store_length--;
+ }
+ field->set_key_image(key, key_part->length);
+ if (field->type() == MYSQL_TYPE_BIT)
+ (void) field->val_int_as_str(&tmp, 1);
+ else
+ field->val_str(&tmp);
+ fwrite(tmp.ptr(),sizeof(char),tmp.length(),DBUG_FILE);
+ if (key+store_length < key_end)
+ fputc('/',DBUG_FILE);
+ }
+ dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+}
+
static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg)
{
char buf[MAX_KEY/8+1];
@@ -12390,88 +12417,92 @@ static void print_quick(QUICK_SELECT_I *
DBUG_UNLOCK_FILE;
DBUG_VOID_RETURN;
}
-#endif /* if 0*/
+
void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose)
{
- Opt_trace_context *out= current_thd->opt_trace;
- Opt_trace_object oto(out, "range_scan");
- oto.add_str("index", head->key_info[index].name,
- strlen(head->key_info[index].name)).
- add("key_length", max_used_key_length).
- add("used_keyparts", used_key_parts);
-
- Opt_trace_array trace_range(out, "ranges");
/* purecov: begin inspected */
- if (TRUE)
+ fprintf(DBUG_FILE, "%*squick range select, key %s, length: %d\n",
+ indent, "", head->key_info[index].name, max_used_key_length);
+
+ if (verbose)
{
QUICK_RANGE *range;
QUICK_RANGE **pr= (QUICK_RANGE**)ranges.buffer;
QUICK_RANGE **end_range= pr + ranges.elements;
for (; pr != end_range; ++pr)
{
- String range_info;
- range_info.set_charset(system_charset_info);
+ fprintf(DBUG_FILE, "%*s", indent + 2, "");
range= *pr;
- append_range(&range_info, head->key_info[index].key_part,
- range->min_key, range->max_key,
- range->flag);
- trace_range.add_str(range_info.ptr(), range_info.length());
+ if (!(range->flag & NO_MIN_RANGE))
+ {
+ print_multiple_key_values(key_parts, range->min_key,
+ range->min_length);
+ if (range->flag & NEAR_MIN)
+ fputs(" < ",DBUG_FILE);
+ else
+ fputs(" <= ",DBUG_FILE);
+ }
+ fputs("X",DBUG_FILE);
+
+ if (!(range->flag & NO_MAX_RANGE))
+ {
+ if (range->flag & NEAR_MAX)
+ fputs(" < ",DBUG_FILE);
+ else
+ fputs(" <= ",DBUG_FILE);
+ print_multiple_key_values(key_parts, range->max_key,
+ range->max_length);
+ }
+ fputs("\n",DBUG_FILE);
}
}
- /* purecov: end */
+ /* purecov: end */
}
void QUICK_INDEX_MERGE_SELECT::dbug_dump(int indent, bool verbose)
{
- Opt_trace_context *out= current_thd->opt_trace;
List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);
QUICK_RANGE_SELECT *quick;
- /* purecov: begin inspected */
- Opt_trace_array ota(out, "index_merge");
+ fprintf(DBUG_FILE, "%*squick index_merge select\n", indent, "");
+ fprintf(DBUG_FILE, "%*smerged scans {\n", indent, "");
while ((quick= it++))
- {
- Opt_trace_object wrapper(out);
quick->dbug_dump(indent+2, verbose);
- }
if (pk_quick_select)
{
- Opt_trace_object oto2(out, "clustered_pk_scan");
+ fprintf(DBUG_FILE, "%*sclustered PK quick:\n", indent, "");
pk_quick_select->dbug_dump(indent+2, verbose);
}
+ fprintf(DBUG_FILE, "%*s}\n", indent, "");
}
void QUICK_ROR_INTERSECT_SELECT::dbug_dump(int indent, bool verbose)
{
- Opt_trace_context *out= current_thd->opt_trace;
List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);
QUICK_RANGE_SELECT *quick;
- Opt_trace_array ota(out, "index_roworder_intersect");
+ fprintf(DBUG_FILE, "%*squick ROR-intersect select, %scovering\n",
+ indent, "", need_to_fetch_row? "":"non-");
+ fprintf(DBUG_FILE, "%*smerged scans {\n", indent, "");
while ((quick= it++))
- {
- Opt_trace_object wrapper(out);
quick->dbug_dump(indent+2, verbose);
- }
if (cpk_quick)
{
- Opt_trace_object oto2(out, "clustered_pk_scan");
+ fprintf(DBUG_FILE, "%*sclustered PK quick:\n", indent, "");
cpk_quick->dbug_dump(indent+2, verbose);
}
+ fprintf(DBUG_FILE, "%*s}\n", indent, "");
}
void QUICK_ROR_UNION_SELECT::dbug_dump(int indent, bool verbose)
{
- Opt_trace_context *out= current_thd->opt_trace;
List_iterator_fast<QUICK_SELECT_I> it(quick_selects);
QUICK_SELECT_I *quick;
- Opt_trace_array ota(out, "index_roworder_union");
+ fprintf(DBUG_FILE, "%*squick ROR-union select\n", indent, "");
+ fprintf(DBUG_FILE, "%*smerged scans {\n", indent, "");
while ((quick= it++))
- {
- Opt_trace_object wrapper(out);
quick->dbug_dump(indent+2, verbose);
- }
+ fprintf(DBUG_FILE, "%*s}\n", indent, "");
}
-
/*
Print quick select information to DBUG_FILE.
@@ -12494,14 +12525,9 @@ void QUICK_ROR_UNION_SELECT::dbug_dump(i
void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
{
- Opt_trace_context *out= current_thd->opt_trace;
- Opt_trace_object oto(out, "index_group");
- oto.add_str("index", index_info->name, strlen(index_info->name)).
- add("key_length", max_used_key_length);
-
fprintf(DBUG_FILE,
"%*squick_group_min_max_select: index %s (%d), length: %d\n",
- indent, "", index_info->name, index, max_used_key_length);
+ indent, "", index_info->name, index, max_used_key_length);
if (key_infix_len > 0)
{
fprintf(DBUG_FILE, "%*susing key_infix with length %d:\n",
@@ -12519,6 +12545,7 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_du
}
}
+
#endif /* !DBUG_OFF || OPTIMIZER_TRACE */
/*****************************************************************************
=== modified file 'sql/opt_trace.cc'
--- a/sql/opt_trace.cc 2010-11-11 13:25:53 +0000
+++ b/sql/opt_trace.cc 2010-11-24 18:54:26 +0000
@@ -25,6 +25,7 @@
#include "opt_trace.h"
#include "mysqld.h" // system_charset_info
#include "item.h" // Item
+#include "m_string.h" // _dig_vec_lower
#ifdef OPTIMIZER_TRACE
@@ -85,9 +86,9 @@ void Opt_trace_struct::do_construct(Opt_
if (stmt->current_struct != NULL)
{
if (key != NULL)
- stmt->current_struct->add_str(key, "...");
+ stmt->current_struct->add_alnum(key, "...");
else
- stmt->current_struct->add_str("...");
+ stmt->current_struct->add_alnum("...");
}
stmt->support_I_S= false; // disable tracing for this struct and children
}
@@ -107,24 +108,26 @@ void Opt_trace_struct::do_construct(Opt_
continuation. That will be visible in the OS_MALLOC_ERROR column.
*/
size_t new_size= alloced + increment;
- const size_t max_size= stmt->buffer.allowed_mem_size;
- if (new_size > max_size) // don't pre-allocate more than the limit
- new_size= max_size;
- const size_t overhead=
- 1 + (ALIGN_SIZE(1) - 1) // see start of String::realloc()
- /*
- Stay strictly below max_size or it would trigger truncation at
- next append(), which is potentially wrong for a "pre-emptive
- allocation" as we do here.
- */
- + 1;
- if (new_size >= overhead)
+ size_t max_size= stmt->buffer.allowed_mem_size;
+ /*
+ Determine a safety margin:
+ (A) String::realloc() adds at most ALIGN_SIZE(1) bytes to requested
+ length, so we need to decrement max_size by this amount, to be sure that
+ we don't allocate more than max_size
+ (B) We need to stay at least one byte under that max_size, or the next
+ append() would trigger up-front truncation, which is potentially wrong
+ for a "pre-emptive allocation" as we do here.
+ */
+ const size_t safety_margin= ALIGN_SIZE(1) /* (A) */ + 1 /* (B) */;
+ if (max_size >= safety_margin)
{
- new_size-= overhead;
+ max_size-= safety_margin;
+ if (new_size > max_size) // don't pre-allocate more than the limit
+ new_size= max_size;
if (new_size >= alloced) // never shrink string
stmt->buffer.realloc(new_size);
}
- }
+ }
}
if (likely(parent != NULL))
parent->add_struct(key);
@@ -154,7 +157,7 @@ void Opt_trace_struct::add_key_name(cons
Check that we're not having two identical consecutive keys in one
object; though the real restriction should not have 'consecutive'.
*/
- DBUG_ASSERT(strcmp(previous_key, key) != 0);
+ DBUG_ASSERT(strncmp(previous_key, key, sizeof(previous_key) - 1) != 0);
strncpy(previous_key, key, sizeof(previous_key) - 1);
previous_key[sizeof(previous_key) - 1]= 0;
#endif
@@ -378,12 +381,11 @@ const char *Opt_trace_context::flag_name
const char *Opt_trace_context::feature_names[]=
{
- "misc", "greedy_search", "range_optimizer", "default", NullS
+ "greedy_search", "range_optimizer", "default", NullS
};
const Opt_trace_context::feature_value Opt_trace_context::FEATURES_DEFAULT=
- Opt_trace_context::feature_value(Opt_trace_context::MISC |
- Opt_trace_context::GREEDY_SEARCH |
+ Opt_trace_context::feature_value(Opt_trace_context::GREEDY_SEARCH |
Opt_trace_context::RANGE_OPTIMIZER);
Opt_trace_context::Opt_trace_context(void):
@@ -539,7 +541,7 @@ bool Opt_trace_context::start(bool suppo
long offset_arg,
long limit_arg,
ulong max_mem_size_arg,
- feature_value features_arg)
+ ulonglong features_arg)
{
DBUG_ENTER("Opt_trace_context::start");
/*
@@ -578,7 +580,7 @@ bool Opt_trace_context::start(bool suppo
offset= offset_arg;
limit= limit_arg;
max_mem_size= max_mem_size_arg;
- features= features_arg;
+ features= feature_value(features_arg | MISC); // MISC always on
}
/*
Decide whether to-be-created trace should support I_S.
@@ -928,33 +930,31 @@ bool Opt_trace_stmt::Buffer::append_esca
#endif
{
rc= false;
- /*
- The JSON syntax mandates that those characters be escaped:
- \ " \b \f \n \r \t
- It also mentions escaping / , but Python's and Perl's json modules
- do not require it, and somewhere on Internet someone said JSON
- allows escaping of / but does not require it.
- */
const char *pstr, *pstr_end;
char buf[128] /* temporary output buffer*/, *pbuf= buf;
for (pstr= str, pstr_end= (str + length) ; pstr < pstr_end ; pstr++)
{
char esc;
const char c= *pstr;
+ /*
+ JSON syntax says that control characters must be escaped. Experience
+ confirms that this means ASCII 0->31 and " and \ . A few of
+ them are accepted with a short escaping syntax (using \ : like
+ \n) but for most of them, only \uXXXX works, where XXXX is an
+ hexadecimal value for the code point.
+ Rules also mention escaping / , but Python's and Perl's json modules
+ do not require it, and somewhere on Internet someone said JSON
+ allows escaping of / but does not require it.
+ */
switch (c)
{
+ // don't use \u when possible for common chars, \ is easier to read:
case '\\':
esc= '\\';
break;
case '"':
esc= '\"';
break;
- case '\b':
- esc= 'b';
- break;
- case '\f':
- esc= 'f';
- break;
case '\n':
esc= 'n';
break;
@@ -964,27 +964,44 @@ bool Opt_trace_stmt::Buffer::append_esca
case '\t':
esc= 't';
break;
- case '\0': // Python's 'json' module has a problem with unescaped NUL
- esc= '0';
- break;
- ///@todo should we escape ctrl-Z for Windows (see String::print())?
default:
- esc= 0; // other character, so no escaping needed
+ esc= 0;
break;
}
- if (pbuf > buf + (sizeof(buf) - 2))
- {
- /* no room in 'buf' for 2 chars, so flush it */
- rc|= String::append(buf, pbuf - buf);
- pbuf= buf; // back to buf's start
- }
- if (esc)
+ if (esc != 0) // escaping with backslash
{
*pbuf++= '\\';
*pbuf++= esc;
}
else
- *pbuf++= c;
+ {
+ uint ascii_code= (uint)c;
+ if (ascii_code < 32) // escaping with \u
+ {
+ *pbuf++= '\\';
+ *pbuf++= 'u';
+ *pbuf++= '0';
+ *pbuf++= '0';
+ if (ascii_code < 16)
+ {
+ *pbuf++= '0';
+ }
+ else
+ {
+ *pbuf++= '1';
+ ascii_code-= 16;
+ }
+ *pbuf++= _dig_vec_lower[ascii_code];
+ }
+ else
+ *pbuf++= c; // normal character, no escaping needed
+ }
+ if (pbuf > buf + (sizeof(buf) - 6))
+ {
+ /* no room in 'buf' for next char, so flush it */
+ rc|= String::append(buf, pbuf - buf);
+ pbuf= buf; // back to buf's start
+ }
}
/* flush any chars left in 'buf' */
rc|= String::append(buf, pbuf - buf);
=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h 2010-11-17 12:27:39 +0000
+++ b/sql/opt_trace.h 2010-12-19 14:24:03 +0000
@@ -21,7 +21,8 @@
class THD;
#include "my_base.h" // ha_rows
#include "sql_string.h" // String
-
+#include "sql_list.h" // because sql_cmd.h needs it
+#include "sql_cmd.h" // for enum_sql_command
/**
@file
@@ -368,7 +369,6 @@ public:
/**
Features' names for @@@@optimizer_trace_features variable of
@c sys_vars.cc:
- @li "misc" = anything unclassified
@li "greedy_search" = the greedy search for a plan
@li "range_optimizer" = the cost analysis of accessing data through
ranges in indices
@@ -377,15 +377,19 @@ public:
static const char *feature_names[];
/** Features' numeric values for @@@@optimizer_trace_features variable */
enum feature_value {
+ GREEDY_SEARCH= 1,
+ RANGE_OPTIMIZER= 2, ///@todo join cache, semijoin...
+ /*
+ if you add here, update feature_value of empty implementation
+ and FEATURES_DEFAULT!
+ */
/**
Anything unclassified, including the top object (thus, by "inheritance
from parent", disabling MISC makes an empty trace).
+ This feature cannot be disabled by the user; for this it is important
+ that it always has biggest flag; flag's value itself does not matter
*/
- MISC= 1,
- GREEDY_SEARCH= 2,
- RANGE_OPTIMIZER= 4 ///@todo join cache, semijoin...
- /* if you add here, update feature_value of empty implementation
- * and FEATURES_DEFAULT! */
+ MISC= 128
};
static const feature_value FEATURES_DEFAULT;
@@ -419,7 +423,7 @@ public:
*/
bool start(bool need_it_for_I_S, bool end_marker, bool one_line,
long offset, long limit, ulong max_mem_size,
- feature_value features);
+ ulonglong features);
/** Ends the current trace */
void end(void);
/** Returns whether there is a current trace */
@@ -773,9 +777,22 @@ public:
/**
Adds a value (of string type) to the structure. A key is specified, so it
- is adds the key/value pair (the structure must thus be an object).
+ adds the key/value pair (the structure must thus be an object).
+
+ There are two "add_*" variants to add a string value.
+ If the value is 0-terminated and each character
+ - is ASCII 7-bit
+ - has code >=32 and is neither '"' nor '\\'
+ then add_alnum() should be used. That should be the case for all fixed
+ strings like add_alnum("cause", "cost").
+ Otherwise, add_utf8() should be used; it accepts any UTF8-encoded character
+ in 'value' and will escape characters which JSON requires (and is thus
+ slower than add_alnum()). It should be used for all strings which we get
+ from the server's objects (indeed a table's name, a WHERE condition, may
+ contain "strange" characters).
+
@param key key
- @param value value, in ASCII
+ @param value value
@param val_length length of string 'value'
@return a reference to the structure, useful for chaining like this:
@verbatim add(x,y).add(z,t).add(u,v) @endverbatim
@@ -804,7 +821,7 @@ public:
unusually intensive on the optimizer and thus real-life cases should have
a smaller penalty. This will be benchmarked with the QA teams.
- String-related add() variants are named add_str():
+ String-related add() variants are named add_[something]():
- to avoid confusing the compiler between:
add(const char *value, size_t val_length) and
add(const char *key, ulonglong value)
@@ -814,50 +831,60 @@ public:
add(const char *key, double value) instead of
add(const char *value, size_t val_length).
*/
- Opt_trace_struct& add_str(const char *key,
- const char *value, size_t val_length)
+ Opt_trace_struct& add_alnum(const char *key,
+ const char *value)
{
if (likely(!started))
return *this;
- return do_add(key, value, val_length, false);
+ return do_add(key, value, strlen(value), false);
}
/**
- Like add() but for non-ASCII strings.
+ Adds a value (of string type) to the structure. No key is specified, so
+ it adds only the value (the structure must thus be an array).
+ @param value value
+ @return a reference to the structure
+ */
+ Opt_trace_struct& add_alnum(const char *value)
+ {
+ if (likely(!started))
+ return *this;
+ return do_add(NULL, value, strlen(value), false);
+ }
+ /**
+ Like add_alnum() but supports any UTF8 characters in 'value'.
Will "escape" 'value' to be JSON-compliant.
@param key
- @param value value, in UTF8
+ @param value value
@param val_length length of string 'value'
*/
- Opt_trace_struct& add_str_escaped(const char *key,
- const char *value, size_t val_length)
+ Opt_trace_struct& add_utf8(const char *key,
+ const char *value, size_t val_length)
{
if (likely(!started))
return *this;
return do_add(key, value, val_length, true);
}
- Opt_trace_struct& add_str(const char *key, const char *value)
+ /** Variant of add_utf8() for adding to an array (no key) */
+ Opt_trace_struct& add_utf8(const char *value, size_t val_length)
{
if (likely(!started))
return *this;
- return do_add(key, value, strlen(value), false);
+ return do_add(NULL, value, val_length, true);
}
- /**
- Adds a value (of string type) to the structure. No key is specified, so
- it adds only the value (the structure must thus be an array).
- @param value value, in ASCII
- @return a reference to the structure
- */
- Opt_trace_struct& add_str(const char *value, size_t val_length)
+ /** Variant of add_utf8() where 'value' is 0-terminated */
+ Opt_trace_struct& add_utf8(const char *key,
+ const char *value)
{
if (likely(!started))
return *this;
- return do_add(NULL, value, val_length, false);
+ return do_add(key, value, strlen(value), true);
}
- Opt_trace_struct& add_str(const char *value)
+ /** Variant of add_utf8() where 'value' is 0-terminated */
+ Opt_trace_struct& add_utf8(const char *value)
{
if (likely(!started))
return *this;
- return do_add(NULL, value, strlen(value), false);
+ return do_add(NULL, value, strlen(value), true);
}
/**
Add a value (of Item type) to the structure. The Item should be a
@@ -952,6 +979,12 @@ public:
return *this;
return do_add(NULL, value);
}
+ Opt_trace_struct& add_null(const char *key)
+ {
+ if (likely(!started))
+ return *this;
+ return do_add_null(key);
+ }
/**
The exception to RAII: this function is an explicit way to end a
structure before it goes out of scope. Don't use it unless RAII mandates
@@ -1083,7 +1116,7 @@ public:
/**
A JSON object (unordered set of key/value pairs).
- Defines only a constructor, all the rest in inherited from Opt_trace_struct.
+ Defines only a constructor, all the rest is inherited from Opt_trace_struct.
*/
class Opt_trace_object: public Opt_trace_struct
{
@@ -1119,7 +1152,8 @@ class Opt_trace_array: public Opt_trace_
{
public:
/**
- Constructs an array. @sa Opt_trace_object::Opt_trace_object.
+ Constructs an array. Key is specified, so the array is the value of a
+ key/value pair.
@param ctx context for this array
@param key key
*/
@@ -1127,6 +1161,11 @@ public:
Opt_trace_context::feature_value feature=
Opt_trace_context::MISC) :
Opt_trace_struct(ctx, false, key, feature) {}
+ /**
+ Constructs an array. No key is specified, so the array is just a value
+ (serves for adding the object to an array).
+ @param ctx context for this array
+ */
Opt_trace_array(Opt_trace_context *ctx,
Opt_trace_context::feature_value feature=
Opt_trace_context::MISC) :
@@ -1206,7 +1245,7 @@ private:
#define OPT_TRACE(trace, action) do { \
if (unlikely((trace) != NULL)) \
if (unlikely((trace)->is_started())) \
- (trace)->get_current_struct()->action; \
+ (trace)->get_current_struct()->action; \
} while (0)
@@ -1232,18 +1271,17 @@ struct TABLE_LIST;
Start tracing a THD's actions (generally at a statement's start).
@param thd the THD
@param tbl list of tables read/written by the statement.
+ @param sql_command SQL command being prepared or executed
@return whether this function decided to trace (and thus the corresponding
opt_trace_end() should end the trace).
@note if tracing was already started (by a top statement above the present
- sub-statement in the call chain), and this function decides to trace the
- sub-statement, it returns "true". For example in
- @verbatim EXECUTE prepared_stmt @endverbatim @c mysql_execute_command() is
- called as usual, and from inside it, @c mysql_execute_command() is
- called. The first call starts one trace, the second call starts a separate
- new trace; at the end of the second call the second trace is terminated; at
- the end of the first call the first trace is terminated.
+ sub-statement in the call chain), and this function decides to trace
+ (respectively not trace) the sub-statement, it returns "true"
+ (resp. false). Each sub-statement is responsible for ending the trace which it
+ has started.
*/
-bool opt_trace_start(THD *thd, const TABLE_LIST *tbl);
+bool opt_trace_start(THD *thd, const TABLE_LIST *tbl,
+ enum enum_sql_command sql_command);
/**
Stop tracing a THD's actions (generally at statement's end).
@@ -1309,8 +1347,12 @@ int make_optimizer_trace_table_for_show(
class Opt_trace_context
{
public:
- /* We need this one */
- enum feature_value { MISC= 1, GREEDY_SEARCH= 2, RANGE_OPTIMIZER= 4 };
+ /// We need this enum even if tracing is disabled
+ enum feature_value {
+ GREEDY_SEARCH= 1,
+ RANGE_OPTIMIZER= 2,
+ MISC= 128
+ };
};
/** Empty implementation used when optimizer trace is not compiled in */
@@ -1323,10 +1365,12 @@ public:
Opt_trace_object(Opt_trace_context *ctx,
Opt_trace_context::feature_value feature=
Opt_trace_context::MISC) {}
- Opt_trace_object& add_str(const char *key,
- const char *value, size_t val_length)
+ Opt_trace_object& add_alnum(const char *key, const char *value)
{ return *this; }
- Opt_trace_object& add_str(const char *key, const char *value)
+ Opt_trace_object& add_utf8(const char *key,
+ const char *value, size_t val_length)
+ { return *this; }
+ Opt_trace_object& add_utf8(const char *key, const char *value)
{ return *this; }
Opt_trace_object& add(const char *key, Item *item) { return *this; }
Opt_trace_object& add(const char *key, bool value) { return *this; }
@@ -1335,6 +1379,7 @@ public:
Opt_trace_object& add(const char *key, longlong value) { return *this; }
Opt_trace_object& add(const char *key, ulonglong value) { return *this; }
Opt_trace_object& add(const char *key, double value) { return *this; }
+ void end(void) {}
};
/** Empty implementation used when optimizer trace is not compiled in */
@@ -1347,9 +1392,11 @@ public:
Opt_trace_array(Opt_trace_context *ctx,
Opt_trace_context::feature_value feature=
Opt_trace_context::MISC) {}
- Opt_trace_array& add_str(const char *value, size_t val_length)
+ Opt_trace_array& add_alnum(const char *value) { return *this; }
+ Opt_trace_array& add_utf8(const char *value, size_t val_length)
+ { return *this; }
+ Opt_trace_array& add_utf8(const char *value)
{ return *this; }
- Opt_trace_array& add_str(const char *value) { return *this; }
Opt_trace_array& add(Item *item) { return *this; }
Opt_trace_array& add(bool value) { return *this; }
Opt_trace_array& add(int value) { return *this; }
@@ -1357,6 +1404,7 @@ public:
Opt_trace_array& add(longlong value) { return *this; }
Opt_trace_array& add(ulonglong value) { return *this; }
Opt_trace_array& add(double value) { return *this; }
+ void end(void) {}
};
/** Empty implementation used when optimizer trace is not compiled in */
@@ -1369,7 +1417,7 @@ public:
#define OPT_TRACE(trace, action) do {} while (0)
#define OPT_TRACE2(trace, action) do {} while (0)
-#define opt_trace_start(thd, tbl) (false)
+#define opt_trace_start(thd, tbl, sql_command) (false)
#define opt_trace_end(thd, started) do {} while (0)
#define opt_trace_print_expanded_query(thd) do {} while (0)
#define opt_trace_add_select_number(s, select_number) do {} while (0)
@@ -1396,10 +1444,18 @@ public:
@param to description of the after-transformation state
*/
#define OPT_TRACE_TRANSFO(trace,object_level0,object_level1,select_number,from,to) \
- Opt_trace_object object_level0(trace); \
- Opt_trace_object object_level1(trace, "transformation"); \
- opt_trace_add_select_number(&object_level1, select_number); \
- object_level1.add_str("from", from).add_str("to", to);
+ Opt_trace_object object_level0(trace); \
+ Opt_trace_object object_level1(trace, "transformation"); \
+ opt_trace_add_select_number(&object_level1, select_number); \
+ object_level1.add_alnum("from", from).add_alnum("to", to);
+
+/**
+ Helper to put the database/table name in the trace
+ @param t TABLE* pointer
+*/
+#define add_utf8_table(t) \
+ add_utf8("database", (t)->s->db.str, (t)->s->db.length). \
+ add_utf8("table", (t)->alias)
#if !defined(DBUG_OFF) && !defined(OPTIMIZER_TRACE)
@@ -1410,10 +1466,4 @@ public:
#error debug binaries must support optimizer trace
#endif
-/**
- The trace for range optimizer isn't mastered, and breaks.
- @todo fix this.
-*/
-#define NO_OPT_TRACE_FOR_RANGE_OPT 1
-
#endif /* OPT_TRACE_INCLUDED */
=== modified file 'sql/opt_trace2server.cc'
--- a/sql/opt_trace2server.cc 2010-10-21 15:22:32 +0000
+++ b/sql/opt_trace2server.cc 2010-12-19 14:24:03 +0000
@@ -29,7 +29,7 @@
#include "opt_trace.h"
#include "sql_show.h" // schema_table_stored_record()
-#include "sql_class.h" // THD
+#include "sql_parse.h" // sql_command_flags
#ifdef OPTIMIZER_TRACE
@@ -56,7 +56,39 @@ static bool list_has_optimizer_trace_tab
}
-bool opt_trace_start(THD *thd, const TABLE_LIST *tbl)
+/**
+ Whether a SQL command qualifies for optimizer tracing.
+ @param sql_command the command
+*/
+static inline bool sql_command_can_be_traced
+(enum enum_sql_command sql_command)
+{
+ /*
+ Tracing is limited to a few SQL commands only.
+
+ Reasons to not trace other commands:
+ - it reduces the range of potential unknown bugs and misuse
+ - they probably don't have anything interesting optimizer-related
+ - select_lex for them might be uninitialized and unprintable.
+ - SHOW WARNINGS would create an uninteresting trace and thus overwrite the
+ previous interesting one.
+
+ About prepared statements: note that we don't turn on tracing for
+ SQLCOM_PREPARE (respectively SQLCOM_EXECUTE), because we don't know yet what
+ command is being prepared (resp. executed). We turn tracing on later, if the
+ prepared (resp. executed) command is in the allowed set above, in
+ check_prepared_statement() (resp. mysql_execute_command() called by
+ Prepared_statement::execute()).
+ PREPARE SELECT is worth tracing as it does permanent query transformations.
+
+ Note that SELECT includes EXPLAIN.
+ */
+ return (sql_command_flags[sql_command] & CF_OPTIMIZER_TRACE);
+}
+
+
+bool opt_trace_start(THD *thd, const TABLE_LIST *tbl,
+ enum enum_sql_command sql_command)
{
DBUG_ENTER("opt_trace_start");
@@ -66,41 +98,67 @@ bool opt_trace_start(THD *thd, const TAB
* if we are using --debug (because the trace serves as a relay for it, for
optimizer debug printouts).
Additionally, we should not be tracing if:
- * we are already tracing (see function comment of opt_trace_start())
+ * command is not interesting (optimizer-wise)
* query involves I_S.OPTIMIZER_TRACE (we do not want to overwrite the
trace while reading it with SELECT).
*/
ulonglong var= thd->variables.optimizer_trace;
bool need_it_for_I_S= (var & Opt_trace_context::FLAG_ENABLED);
- bool need_it_for_dbug= false, ret= false;
+ bool need_it_for_dbug= false, allocated_here= false;
/* This will be triggered if --debug or --debug=d:opt_trace is used */
DBUG_EXECUTE("opt_trace", need_it_for_dbug= true;);
if (!(need_it_for_I_S || need_it_for_dbug))
- goto end;
+ goto disable;
+ if (!sql_command_can_be_traced(sql_command))
+ goto disable;
if (list_has_optimizer_trace_table(tbl))
- goto end;
+ goto disable;
/*
We don't allocate it in THD's MEM_ROOT as it must survive until a next
statement (SELECT) reads the trace.
*/
if (thd->opt_trace == NULL)
+ {
if ((thd->opt_trace= new Opt_trace_context()) == NULL)
- goto end;
+ goto disable;
+ allocated_here= true;
+ }
+start_trace:
if (thd->opt_trace->start(need_it_for_I_S,
(var & Opt_trace_context::FLAG_END_MARKER),
(var & Opt_trace_context::FLAG_ONE_LINE),
thd->variables.optimizer_trace_offset,
thd->variables.optimizer_trace_limit,
thd->variables.optimizer_trace_max_mem_size,
- Opt_trace_context::feature_value(thd->variables.optimizer_trace_features)))
+ thd->variables.optimizer_trace_features))
+ {
+ if (allocated_here)
+ {
+ delete thd->opt_trace;
+ thd->opt_trace= NULL;
+ }
+ goto disable;
+ }
+ DBUG_RETURN(true); // started all ok
+disable:
+ /*
+ No need to create a trace for this statement.
+ Exception: imagine there is a parent statement which has a trace, and we
+ don't create a trace for the child statement here. Then trace structures of
+ the child will be accidentally attached to the parent's trace (as
+ it is still 'current_stmt_in_gen', which constructors of Opt_trace_struct
+ will use); thus the child's trace will be visible (as a chunk of the
+ parent's trace). That would be incorrect.
+ To avoid this, we create a trace for the child but with I_S output disabled;
+ this changes 'current_stmt_in_gen', thus this child's trace structures
+ will be attached to the child's trace and not be visible.
+ */
+ if (need_it_for_I_S && thd->opt_trace != NULL && thd->opt_trace->is_started())
{
- delete thd->opt_trace;
- thd->opt_trace= NULL;
- goto end;
- }
- ret= true; // started all ok
-end:
- DBUG_RETURN(ret);
+ need_it_for_I_S= false;
+ goto start_trace;
+ }
+ DBUG_RETURN(false);
}
@@ -115,24 +173,23 @@ void opt_trace_end(THD *thd, bool starte
void opt_trace_print_expanded_query(THD *thd)
{
- Opt_trace_context *trace= thd->opt_trace;
+ Opt_trace_context * const trace= thd->opt_trace;
if (trace == NULL || !trace->is_started())
return;
- switch (thd->lex->sql_command)
- {
- case SQLCOM_SELECT:
- case SQLCOM_INSERT:
- case SQLCOM_INSERT_SELECT:
- case SQLCOM_REPLACE_SELECT:
- case SQLCOM_UPDATE:
- case SQLCOM_DELETE:
- case SQLCOM_UPDATE_MULTI:
- case SQLCOM_DELETE_MULTI:
- break;
- default:
- /* select_lex for other commands might be uninitialized, don't print */
+ /*
+ In "SELECT stored_func()" , execution of a
+ sub-statement of stored_func() may start by opening tables (that's the case
+ of DECLARE which opens tables (actually an empty list of tables) and does
+ not go through mysql_execute_command()). So we may arrive here,
+ print the "expanded query" of this sub-statement even before
+ opt_trace_start() has been called for it (it may never be called for it
+ at all). Thus trying to put the expanded query inside the parent statement's
+ trace, which would be logically wrong and possibly a syntax error.
+ thd->lex->sql_command is SQLCOM_END in this case. So we do a check of the
+ command:
+ */
+ if (!sql_command_can_be_traced(thd->lex->sql_command))
return;
- }
char buff[1024];
String str(buff,(uint32) sizeof(buff), system_charset_info);
str.length(0);
@@ -145,8 +202,7 @@ void opt_trace_print_expanded_query(THD
*/
thd->lex->unit.print(&str, enum_query_type(QT_EXPAND_VIEW_DEFINITION |
QT_SHOW_SELECT_NUMBER));
- Opt_trace_object(trace).add_str_escaped("expanded_query",
- str.ptr(), str.length());
+ Opt_trace_object(trace).add_utf8("expanded_query", str.ptr(), str.length());
}
@@ -156,7 +212,7 @@ void opt_trace_add_select_number(Opt_tra
if (unlikely(select_number >= INT_MAX))
{
/* clearer than any huge number */
- s->add_str("select#", "fake");
+ s->add_alnum("select#", "fake");
}
else
s->add("select#", select_number);
@@ -181,11 +237,10 @@ int fill_optimizer_trace_info(THD *thd,
const Opt_trace_info info= *it;
restore_record(table, s->default_values);
/*
- We will put the query, which is in character_set_client (except
- literals with introducers) into a column using
- character_set_client; this is better than UTF8 (see BUG#57306). If
- literals with introducers are used, which we cannot know (that would
- require looking at the parse tree), it may be wrong, some say.
+ We will put the query, which is in character_set_client, into a column
+ using character_set_client; this is better than UTF8 (see BUG#57306).
+ When literals with introducers are used, see "LiteralsWithIntroducers"
+ in this file.
*/
table->field[0]->store(info.query_ptr, info.query_length, info.query_charset);
table->field[1]->store(info.trace_ptr, info.trace_length, system_charset_info);
@@ -242,4 +297,34 @@ int make_optimizer_trace_table_for_show(
return 0;
}
+/*
+ LiteralsWithIntroducers :
+
+ They may be significantly altered; but this isn't specific to the optimizer
+ trace, it also happens with SHOW PROCESSLIST, and is deemed a not too
+ important problem.
+
+ Consider
+ mysql> set names latin1;
+ mysql> SELECT 'í', _cp850'í';
+ | í | Ý |
+ This sends the binary string:
+ SELECT <0xED>, _cp850<0xED>
+ to the server (í is 0xED in latin1).
+ Now we put this into OPTIMIZER_TRACE.QUERY, using latin1
+ (character_set_client), and the client has switched to utf8: we convert the
+ query from latin1 to utf8 when sending to client, which receives:
+ SELECT <0xC3><0xAD>, _cp850<0xC3><0xAD>
+ (í is <0xC3><0xAD> in utf8).
+ But <0xC3><0xAD> in _cp850 means a completely different character:
+ mysql> set names utf8;
+ mysql> SELECT 'í', _cp850'í';
+ | í | ├¡ |
+
+ If the client had originally issued
+ SELECT 'í', _cp850 0xED;
+ there would be no problem ('0', 'x', 'E', and 'D' are identical in latin1 and
+ utf8: they would be preserved during conversion).
+*/
+
#endif // OPTIMIZER_TRACE
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2010-09-27 09:26:39 +0000
+++ b/sql/sql_class.h 2010-11-26 17:29:04 +0000
@@ -3577,6 +3577,9 @@ public:
*/
#define CF_CAN_GENERATE_ROW_EVENTS (1U << 11)
+/** Identifies statements which may generate an optimizer trace */
+#define CF_OPTIMIZER_TRACE (1U << 12)
+
/* Bits in server_command_flags */
/**
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2010-11-22 09:59:17 +0000
+++ b/sql/sql_delete.cc 2010-11-24 18:54:26 +0000
@@ -193,9 +193,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
{ // Enter scope for optimizer trace wrapper
Opt_trace_object wrapper(thd->opt_trace);
- wrapper.add_str("database", table_list->db,
- strlen(table_list->db)).
- add_str("table", table_list->alias, strlen(table_list->alias));
+ wrapper.add_utf8_table(table);
if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
{
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-11-06 09:56:51 +0000
+++ b/sql/sql_parse.cc 2010-12-19 14:24:03 +0000
@@ -294,28 +294,38 @@ void init_update_queries(void)
sql_command_flags[SQLCOM_UPDATE]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
CF_PROTECT_AGAINST_GRL |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_UPDATE_MULTI]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
CF_PROTECT_AGAINST_GRL |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
+ // This is INSERT VALUES(...), can be VALUES(stored_func()) so we trace it
sql_command_flags[SQLCOM_INSERT]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
CF_PROTECT_AGAINST_GRL |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_INSERT_SELECT]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
CF_PROTECT_AGAINST_GRL |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_DELETE]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
CF_PROTECT_AGAINST_GRL |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_DELETE_MULTI]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
CF_PROTECT_AGAINST_GRL |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_REPLACE]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_REPLACE_SELECT]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_SELECT]= CF_REEXECUTION_FRAGILE |
- CF_CAN_GENERATE_ROW_EVENTS;
+ CF_CAN_GENERATE_ROW_EVENTS |
+ CF_OPTIMIZER_TRACE;
sql_command_flags[SQLCOM_SET_OPTION]= CF_REEXECUTION_FRAGILE | CF_AUTO_COMMIT_TRANS;
sql_command_flags[SQLCOM_DO]= CF_REEXECUTION_FRAGILE |
CF_CAN_GENERATE_ROW_EVENTS;
@@ -1884,7 +1894,6 @@ mysql_execute_command(THD *thd)
/* have table map for update for multi-update statement (BUG#37051) */
bool have_table_map_for_update= FALSE;
#endif
- bool started_optimizer_trace= false;
/* Saved variable value */
DBUG_ENTER("mysql_execute_command");
#ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -2058,19 +2067,11 @@ mysql_execute_command(THD *thd)
status_var_increment(thd->status_var.com_stat[lex->sql_command]);
- if ((sql_command_flags[lex->sql_command] & CF_DIAGNOSTIC_STMT) == 0)
- {
- if ((started_optimizer_trace= opt_trace_start(thd, all_tables)))
- opt_trace_set_query(thd->opt_trace, thd->query(), thd->query_length(),
- thd->variables.character_set_client);
- }
- else
- {
- /*
- We don't want SHOW WARNINGS to start a trace, it would overwrite the
- previous statement's trace.
- */
- }
+ const bool started_optimizer_trace= opt_trace_start(thd, all_tables,
+ lex->sql_command);
+ if (started_optimizer_trace)
+ opt_trace_set_query(thd->opt_trace, thd->query(), thd->query_length(),
+ thd->variables.character_set_client);
Opt_trace_object trace_command(thd->opt_trace);
Opt_trace_array trace_command_steps(thd->opt_trace, "steps");
=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc 2010-08-18 10:18:27 +0000
+++ b/sql/sql_prepare.cc 2010-12-19 14:24:03 +0000
@@ -1964,6 +1964,20 @@ static bool check_prepared_statement(Pre
if (tables)
thd->warning_info->opt_clear_warning_info(thd->query_id);
+ /*
+ For the optimizer trace, this is the symmetric, for statement preparation,
+ of what is done at statement execution (in mysql_execute_command()).
+ */
+ const bool started_optimizer_trace= opt_trace_start(thd, tables,
+ sql_command);
+ if (started_optimizer_trace)
+ opt_trace_set_query(thd->opt_trace, thd->query(), thd->query_length(),
+ thd->variables.character_set_client);
+
+
+ Opt_trace_object trace_command(thd->opt_trace);
+ Opt_trace_array trace_command_steps(thd->opt_trace, "steps");
+
switch (sql_command) {
case SQLCOM_REPLACE:
case SQLCOM_INSERT:
@@ -2006,7 +2020,8 @@ static bool check_prepared_statement(Pre
if (res == 2)
{
/* Statement and field info has already been sent */
- DBUG_RETURN(FALSE);
+ res= FALSE;
+ goto end;
}
break;
case SQLCOM_CREATE_TABLE:
@@ -2017,7 +2032,8 @@ static bool check_prepared_statement(Pre
if (lex->create_view_mode == VIEW_ALTER)
{
my_message(ER_UNSUPPORTED_PS, ER(ER_UNSUPPORTED_PS), MYF(0));
- goto error;
+ res= true;
+ goto end;
}
res= mysql_test_create_view(stmt);
break;
@@ -2090,15 +2106,19 @@ static bool check_prepared_statement(Pre
{
/* All other statements are not supported yet. */
my_message(ER_UNSUPPORTED_PS, ER(ER_UNSUPPORTED_PS), MYF(0));
- goto error;
+ res= true;
+ goto end;
}
break;
}
if (res == 0)
- DBUG_RETURN(stmt->is_sql_prepare() ?
- FALSE : (send_prep_stmt(stmt, 0) || thd->protocol->flush()));
-error:
- DBUG_RETURN(TRUE);
+ res= stmt->is_sql_prepare() ?
+ FALSE : (send_prep_stmt(stmt, 0) || thd->protocol->flush());
+end:
+ trace_command_steps.end();
+ trace_command.end(); // must be closed before trace is ended below
+ opt_trace_end(thd, started_optimizer_trace);
+ DBUG_RETURN(res);
}
/**
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-11-22 09:59:17 +0000
+++ b/sql/sql_select.cc 2010-11-24 18:54:26 +0000
@@ -167,8 +167,7 @@ end_update(JOIN *join, JOIN_TAB *join_ta
static enum_nested_loop_state
end_unique_update(JOIN *join, JOIN_TAB *join_tab, bool end_of_records);
-static int join_read_const_table(JOIN_TAB *tab, POSITION *pos,
- Opt_trace_array *trace_array);
+static int join_read_const_table(JOIN_TAB *tab, POSITION *pos);
static int join_read_system(JOIN_TAB *tab);
static int join_read_const(JOIN_TAB *tab);
static int join_read_key(JOIN_TAB *tab);
@@ -991,7 +990,7 @@ bool resolve_subquery(THD *thd, JOIN *jo
"IN (SELECT)", "materialization");
oto1.add("chosen", choice == 2);
if (types_problem)
- oto1.add_str("cause", "field types");
+ oto1.add_alnum("cause", "field_types");
}
}
@@ -2666,7 +2665,7 @@ setup_subq_exit:
DBUG_ASSERT(zero_result_cause != NULL);
trace_steps.end(); // because all steps are done
Opt_trace_object trace_empty_result(trace, "empty_result");
- trace_empty_result.add_str("cause", zero_result_cause);
+ trace_empty_result.add_alnum("cause", zero_result_cause);
/*
Even with zero matching rows, subqueries in the HAVING clause may
need to be evaluated if there are aggregate functions in the
@@ -4118,7 +4117,7 @@ skip_conversion:
(*subq)->get_select_lex()->select_number,
"IN (SELECT)", "semijoin");
if (outer_join_objection)
- oto1.add_str("cause", "outer_join");
+ oto1.add_alnum("cause", "outer_join");
oto1.add("chosen", false);
}
JOIN *child_join= (*subq)->unit->first_select()->join;
@@ -4350,9 +4349,7 @@ int pull_out_semijoin_tables(JOIN *join)
if (tbl->table->map & join->const_table_map)
{
pulled_tables |= tbl->table->map;
- Opt_trace_object(trace).
- add_str("database", tbl->db, strlen(tbl->db)).
- add_str("table", tbl->alias, strlen(tbl->alias)).
+ Opt_trace_object(trace).add_utf8_table(tbl->table).
add("constant", true);
}
}
@@ -4378,9 +4375,7 @@ int pull_out_semijoin_tables(JOIN *join)
{
pulled_a_table= TRUE;
pulled_tables |= tbl->table->map;
- Opt_trace_object(trace).
- add_str("database", tbl->db, strlen(tbl->db)).
- add_str("table", tbl->alias, strlen(tbl->alias)).
+ Opt_trace_object(trace).add_utf8_table(tbl->table).
add("functionally_dependent", true);
/*
Pulling a table out of uncorrelated subquery in general makes
@@ -4743,194 +4738,187 @@ make_join_statistics(JOIN *join, TABLE_L
{
Opt_trace_object trace_wrapper(trace);
+ for (POSITION *p_pos= join->positions, *p_end= p_pos + const_count;
+ p_pos < p_end ;
+ p_pos++)
+ {
+ int tmp;
+ s= p_pos->table;
+ s->type= JT_SYSTEM;
+ join->const_table_map|= s->table->map;
+ if ((tmp= join_read_const_table(s, p_pos)))
+ {
+ if (tmp > 0)
+ goto error; // Fatal error
+ }
+ else
+ {
+ found_const_table_map|= s->table->map;
+ s->table->pos_in_table_list->optimized_away= TRUE;
+ }
+ }
+
+ /* loop until no more const tables are found */
+ int ref_changed;
+ do
{
- Opt_trace_array trace_const_tables(trace, "constant_tables");
+ more_const_tables_found:
+ ref_changed = 0;
+ found_ref= 0;
- for (POSITION *p_pos= join->positions, *p_end= p_pos + const_count;
- p_pos < p_end ;
- p_pos++)
- {
- int tmp;
- s= p_pos->table;
- s->type= JT_SYSTEM;
- join->const_table_map|= s->table->map;
- if ((tmp= join_read_const_table(s, p_pos, &trace_const_tables)))
- {
- if (tmp > 0)
- goto error; // Fatal error
- }
- else
- {
- found_const_table_map|= s->table->map;
- s->table->pos_in_table_list->optimized_away= TRUE;
- }
- }
+ /*
+ We only have to loop from stat_vector + const_count as
+ set_position() will move all const_tables first in stat_vector
+ */
- /* loop until no more const tables are found */
- int ref_changed;
- do
+ for (JOIN_TAB **pos= stat_vector + const_count ; (s= *pos) ; pos++)
{
- more_const_tables_found:
- ref_changed = 0;
- found_ref= 0;
+ table= s->table;
/*
- We only have to loop from stat_vector + const_count as
- set_position() will move all const_tables first in stat_vector
+ If equi-join condition by a key is null rejecting and after a
+ substitution of a const table the key value happens to be null
+ then we can state that there are no matches for this equi-join.
*/
-
- for (JOIN_TAB **pos= stat_vector + const_count ; (s= *pos) ; pos++)
+ if ((keyuse= s->keyuse) && *s->on_expr_ref && !s->embedding_map)
{
- table= s->table;
-
/*
- If equi-join condition by a key is null rejecting and after a
- substitution of a const table the key value happens to be null
- then we can state that there are no matches for this equi-join.
+ When performing an outer join operation if there are no matching rows
+ for the single row of the outer table all the inner tables are to be
+ null complemented and thus considered as constant tables.
+ Here we apply this consideration to the case of outer join operations
+ with a single inner table only because the case with nested tables
+ would require a more thorough analysis.
+ TODO. Apply single row substitution to null complemented inner tables
+ for nested outer join operations.
*/
- if ((keyuse= s->keyuse) && *s->on_expr_ref && !s->embedding_map)
+ while (keyuse->table == table)
{
- /*
- When performing an outer join operation if there are no matching rows
- for the single row of the outer table all the inner tables are to be
- null complemented and thus considered as constant tables.
- Here we apply this consideration to the case of outer join operations
- with a single inner table only because the case with nested tables
- would require a more thorough analysis.
- TODO. Apply single row substitution to null complemented inner tables
- for nested outer join operations.
- */
- while (keyuse->table == table)
+ if (!(keyuse->val->used_tables() & ~join->const_table_map) &&
+ keyuse->val->is_null() && keyuse->null_rejecting)
{
- if (!(keyuse->val->used_tables() & ~join->const_table_map) &&
- keyuse->val->is_null() && keyuse->null_rejecting)
- {
- s->type= JT_CONST;
- mark_as_null_row(table);
- found_const_table_map|= table->map;
- join->const_table_map|= table->map;
- set_position(join, const_count++, s, (KEYUSE*) 0);
- goto more_const_tables_found;
- }
- keyuse++;
+ s->type= JT_CONST;
+ mark_as_null_row(table);
+ found_const_table_map|= table->map;
+ join->const_table_map|= table->map;
+ set_position(join, const_count++, s, (KEYUSE*) 0);
+ goto more_const_tables_found;
}
+ keyuse++;
}
+ }
- if (s->dependent) // If dependent on some table
- {
- // All dep. must be constants
- if (s->dependent & ~(join->const_table_map))
- continue;
- if (table->file->stats.records <= 1L &&
- (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
- !table->pos_in_table_list->in_outer_join_nest())
- { // system table
- int tmp= 0;
- s->type= JT_SYSTEM;
- join->const_table_map|= table->map;
- set_position(join, const_count++, s, (KEYUSE*) 0);
- if ((tmp= join_read_const_table(s, join->positions +
- const_count - 1,
- &trace_const_tables)))
- {
- if (tmp > 0)
- goto error; // Fatal error
- }
- else
- found_const_table_map|= table->map;
- continue;
+ if (s->dependent) // If dependent on some table
+ {
+ // All dep. must be constants
+ if (s->dependent & ~(join->const_table_map))
+ continue;
+ if (table->file->stats.records <= 1L &&
+ (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
+ !table->pos_in_table_list->in_outer_join_nest())
+ { // system table
+ int tmp= 0;
+ s->type= JT_SYSTEM;
+ join->const_table_map|= table->map;
+ set_position(join, const_count++, s, (KEYUSE*) 0);
+ if ((tmp= join_read_const_table(s, join->positions +
+ const_count - 1)))
+ {
+ if (tmp > 0)
+ goto error; // Fatal error
}
+ else
+ found_const_table_map|= table->map;
+ continue;
}
- /* check if table can be read by key or table only uses const refs */
- if ((keyuse= s->keyuse))
+ }
+ /* check if table can be read by key or table only uses const refs */
+ if ((keyuse= s->keyuse))
+ {
+ s->type= JT_REF;
+ while (keyuse->table == table)
{
- s->type= JT_REF;
- while (keyuse->table == table)
+ start_keyuse= keyuse;
+ key= keyuse->key;
+ s->keys.set_bit(key); // QQ: remove this ?
+
+ refs= 0;
+ key_map const_ref, eq_part;
+ do
{
- start_keyuse= keyuse;
- key= keyuse->key;
- s->keys.set_bit(key); // QQ: remove this ?
-
- refs= 0;
- key_map const_ref, eq_part;
- do
+ if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize)
{
- if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize)
- {
- if (!((~found_const_table_map) & keyuse->used_tables))
- const_ref.set_bit(keyuse->keypart);
- else
- refs|= keyuse->used_tables;
- eq_part.set_bit(keyuse->keypart);
- }
- keyuse++;
- } while (keyuse->table == table && keyuse->key == key);
+ if (!((~found_const_table_map) & keyuse->used_tables))
+ const_ref.set_bit(keyuse->keypart);
+ else
+ refs|= keyuse->used_tables;
+ eq_part.set_bit(keyuse->keypart);
+ }
+ keyuse++;
+ } while (keyuse->table == table && keyuse->key == key);
- /*
- TODO (low priority): currently we ignore the const tables that
- are within a semi-join nest which is within an outer join nest.
- The effect of this is that we don't do const substitution for
- such tables.
- */
- if (eq_part.is_prefix(table->key_info[key].key_parts) &&
- !table->fulltext_searched &&
- !table->pos_in_table_list->in_outer_join_nest())
+ /*
+ TODO (low priority): currently we ignore the const tables that
+ are within a semi-join nest which is within an outer join nest.
+ The effect of this is that we don't do const substitution for
+ such tables.
+ */
+ if (eq_part.is_prefix(table->key_info[key].key_parts) &&
+ !table->fulltext_searched &&
+ !table->pos_in_table_list->in_outer_join_nest())
+ {
+ if (table->key_info[key].flags & HA_NOSAME)
{
- if (table->key_info[key].flags & HA_NOSAME)
- {
- if (const_ref == eq_part)
- { // Found everything for ref.
- int tmp;
- ref_changed = 1;
- s->type= JT_CONST;
- join->const_table_map|= table->map;
- set_position(join, const_count++, s, start_keyuse);
- if (create_ref_for_key(join, s, start_keyuse,
- found_const_table_map))
- goto error;
- if ((tmp= join_read_const_table(s,
- join->positions +
- const_count - 1,
- &trace_const_tables)))
- {
- if (tmp > 0)
- goto error; // Fatal error
- }
- else
- found_const_table_map|= table->map;
- break;
+ if (const_ref == eq_part)
+ { // Found everything for ref.
+ int tmp;
+ ref_changed = 1;
+ s->type= JT_CONST;
+ join->const_table_map|= table->map;
+ set_position(join, const_count++, s, start_keyuse);
+ if (create_ref_for_key(join, s, start_keyuse,
+ found_const_table_map))
+ goto error;
+ if ((tmp= join_read_const_table(s,
+ join->positions +
+ const_count - 1)))
+ {
+ if (tmp > 0)
+ goto error; // Fatal error
}
else
- found_ref|= refs; // Table is const if all refs are const
+ found_const_table_map|= table->map;
+ break;
}
- else if (const_ref == eq_part)
- s->const_keys.set_bit(key);
+ else
+ found_ref|= refs; // Table is const if all refs are const
}
+ else if (const_ref == eq_part)
+ s->const_keys.set_bit(key);
}
}
}
- } while (join->const_table_map & found_ref && ref_changed);
+ }
+ } while (join->const_table_map & found_ref && ref_changed);
- /*
- Update info on indexes that can be used for search lookups as
- reading const tables may has added new sargable predicates.
- */
- if (const_count && sargables)
+ /*
+ Update info on indexes that can be used for search lookups as
+ reading const tables may has added new sargable predicates.
+ */
+ if (const_count && sargables)
+ {
+ for( ; sargables->field ; sargables++)
{
- for( ; sargables->field ; sargables++)
- {
- Field *field= sargables->field;
- JOIN_TAB *join_tab= field->table->reginfo.join_tab;
- key_map possible_keys= field->key_start;
- possible_keys.intersect(field->table->keys_in_use_for_query);
- bool is_const= 1;
- for (uint j= 0; j < sargables->num_values; j++)
- is_const&= sargables->arg_value[j]->const_item();
- if (is_const)
- join_tab[0].const_keys.merge(possible_keys);
- }
+ Field *field= sargables->field;
+ JOIN_TAB *join_tab= field->table->reginfo.join_tab;
+ key_map possible_keys= field->key_start;
+ possible_keys.intersect(field->table->keys_in_use_for_query);
+ bool is_const= 1;
+ for (uint j= 0; j < sargables->num_values; j++)
+ is_const&= sargables->arg_value[j]->const_item();
+ if (is_const)
+ join_tab[0].const_keys.merge(possible_keys);
}
-
}
{
@@ -4940,14 +4928,12 @@ make_join_statistics(JOIN *join, TABLE_L
for (s= stat ; s < stat_end ; s++)
{
Opt_trace_object trace_table(trace);
- trace_table.add_str("database",s->join->tables_list->db,
- strlen(s->join->tables_list->db)).
- add_str("table", s->table->alias, strlen(s->table->alias));
+ trace_table.add_utf8_table(s->table);
if (s->type == JT_SYSTEM || s->type == JT_CONST)
{
trace_table.add("records", 1).add("cost", 1);
- trace_table.add_str("table_type", (s->type == JT_SYSTEM) ?
- "system": "const");
+ trace_table.add_alnum("table_type", (s->type == JT_SYSTEM) ?
+ "system": "const");
/* Only one matching row */
s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
@@ -5012,7 +4998,7 @@ make_join_statistics(JOIN *join, TABLE_L
/* Generate empty row */
s->info= "Impossible ON condition";
trace_table.add("returning_empty_null_row", true).
- add_str("cause", "impossible_on_condition");
+ add_alnum("cause", "impossible_on_condition");
found_const_table_map|= s->table->map;
s->type= JT_CONST;
mark_as_null_row(s->table); // All fields are NULL
@@ -5020,7 +5006,7 @@ make_join_statistics(JOIN *join, TABLE_L
else
{
trace_table.add("records", 0).
- add_str("cause", "impossible_where_condition");
+ add_alnum("cause", "impossible_where_condition");
}
}
if (records != HA_POS_ERROR)
@@ -6564,6 +6550,7 @@ static void trace_indices_added_group_di
const key_map new_keys,
const char* cause)
{
+#ifdef OPTIMIZER_TRACE
if (likely(trace == NULL) || !trace->is_started())
return;
@@ -6576,9 +6563,10 @@ static void trace_indices_added_group_di
Opt_trace_array trace_key(trace,"keys");
for (uint j= 0 ; j < nbrkeys ; j++)
if (new_keys.is_set(j) && !existing_keys.is_set(j))
- trace_key.add_str(key_info[j].name, strlen(key_info[j].name));
+ trace_key.add_utf8(key_info[j].name);
}
- trace_summary.add_str("cause", cause);
+ trace_summary.add_alnum("cause", cause);
+#endif
}
@@ -7021,8 +7009,8 @@ best_access_path(JOIN *join,
DBUG_PRINT("info", ("Considering ref access on key %s",
keyuse->table->key_info[keyuse->key].name));
Opt_trace_object trace_access_idx(trace);
- trace_access_idx.add_str("access_type", "ref").
- add_str("index", keyinfo->name, strlen(keyinfo->name));
+ trace_access_idx.add_alnum("access_type", "ref").
+ add_utf8("index", keyinfo->name);
/*
True if we find some keys from the range optimizer that match more
@@ -7408,10 +7396,10 @@ best_access_path(JOIN *join,
*/
if (!(records >= s->found_records || best > s->read_time)) // (1)
{
- trace_access_scan.add_str("access_type", s->quick ? "range" : "scan");
+ trace_access_scan.add_alnum("access_type", s->quick ? "range" : "scan");
trace_access_scan.add("cost", s->read_time).
add("records", s->found_records).
- add_str("cause", "cost");
+ add_alnum("cause", "cost");
goto skip_table_scan;
}
@@ -7419,23 +7407,23 @@ best_access_path(JOIN *join,
if ((s->quick && best_key && s->quick->index == best_key->key && // (2)
best_max_key_part >= s->table->quick_key_parts[best_key->key])) // (2)
{
- trace_access_scan.add_str("access_type", "range").
- add_str("cause", "heuristic_index_cheaper");
+ trace_access_scan.add_alnum("access_type", "range").
+ add_alnum("cause", "heuristic_index_cheaper");
goto skip_table_scan;
}
if (((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)
! s->table->covering_keys.is_clear_all() && best_key && !s->quick))// (3)
{
- trace_access_scan.add_str("access_type", s->quick ? "range" : "scan").
- add_str("cause", "covering_index_better_than_full_scan");
+ trace_access_scan.add_alnum("access_type", s->quick ? "range" : "scan").
+ add_alnum("cause", "covering_index_better_than_full_scan");
goto skip_table_scan;
}
if ((s->table->force_index && best_key && !s->quick)) // (4)
{
- trace_access_scan.add_str("access_type", "scan").
- add_str("cause", "force_index");
+ trace_access_scan.add_alnum("access_type", "scan").
+ add_alnum("cause", "force_index");
goto skip_table_scan;
}
@@ -7468,7 +7456,7 @@ best_access_path(JOIN *join,
if (s->quick)
{
- trace_access_scan.add_str("access_type", "range");
+ trace_access_scan.add_alnum("access_type", "range");
/*
For each record we:
- read record range through 'quick'
@@ -7486,7 +7474,7 @@ best_access_path(JOIN *join,
}
else
{
- trace_access_scan.add_str("access_type", "scan");
+ trace_access_scan.add_alnum("access_type", "scan");
/* Estimate cost of reading table. */
if (s->table->force_index && !best_key)
tmp= s->table->file->read_time(s->ref.key, 1, s->records);
@@ -8319,10 +8307,7 @@ best_extension_by_limited_search(JOIN
double current_record_count, current_read_time;
POSITION *position= join->positions + idx;
Opt_trace_object trace_one_table(trace);
- trace_one_table.
- add_str("database", s->join->tables_list->db,
- strlen(s->join->tables_list->db)).
- add_str("table", s->table->alias, strlen(s->table->alias));
+ trace_one_table.add_utf8_table(s->table);
/* Find the best access method from 's' to the current partial plan */
POSITION loose_scan_pos;
@@ -8743,11 +8728,8 @@ static bool fix_semijoin_strategies_for_
Opt_trace_object oto0(trace);
Opt_trace_object oto1(trace,
"reconsidering_access_paths_for_semijoin");
- oto1.add_str("strategy", "MaterializationScan").
- add_str("database", join->tables_list->db,
- strlen(join->tables_list->db)).
- add_str("table", join->best_positions[i].table->table->alias,
- strlen(join->best_positions[i].table->table->alias));
+ oto1.add_alnum("strategy", "MaterializationScan").
+ add_utf8_table(join->best_positions[i].table->table);
best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE,
prefix_rec_count, join->best_positions + i, &dummy);
prefix_rec_count *= join->best_positions[i].records_read;
@@ -8783,11 +8765,8 @@ static bool fix_semijoin_strategies_for_
Opt_trace_object oto0(trace);
Opt_trace_object oto1(trace,
"reconsidering_access_paths_for_semijoin");
- oto1.add_str("strategy", "FirstMatch").
- add_str("database", join->tables_list->db,
- strlen(join->tables_list->db)).
- add_str("table", join->best_positions[idx].table->table->alias,
- strlen(join->best_positions[idx].table->table->alias));
+ oto1.add_alnum("strategy", "FirstMatch").
+ add_utf8_table(join->best_positions[idx].table->table);
best_access_path(join, join->best_positions[idx].table,
rem_tables, idx, TRUE /* no jbuf */,
record_count, join->best_positions + idx, &dummy);
@@ -8820,11 +8799,8 @@ static bool fix_semijoin_strategies_for_
Opt_trace_object oto0(trace);
Opt_trace_object oto1(trace,
"reconsidering_access_paths_for_semijoin");
- oto1.add_str("strategy", "LooseScan").
- add_str("database", join->tables_list->db,
- strlen(join->tables_list->db)).
- add_str("table", join->best_positions[idx].table->table->alias,
- strlen(join->best_positions[idx].table->table->alias));
+ oto1.add_alnum("strategy", "LooseScan").
+ add_utf8_table(join->best_positions[idx].table->table);
best_access_path(join, join->best_positions[idx].table,
rem_tables, idx, TRUE /* no jbuf */,
record_count, join->best_positions + idx,
@@ -9698,9 +9674,7 @@ static bool make_join_select(JOIN *join,
{
tab= join->join_tab+i;
Opt_trace_object trace_one_table(thd->opt_trace);
- trace_one_table.add_str("database", join->tables_list->db,
- strlen(join->tables_list->db)).
- add_str("table", tab->table->alias, strlen(tab->table->alias));
+ trace_one_table.add_utf8_table(tab->table);
/*
first_inner is the X in queries like:
SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X
@@ -11182,9 +11156,7 @@ make_join_readinfo(JOIN *join, ulonglong
pick_table_access_method (tab);
Opt_trace_object trace_refine_table(trace);
- trace_refine_table.add_str("database", join->tables_list->db,
- strlen(join->tables_list->db)).
- add_str("table", table->alias, strlen(table->alias));
+ trace_refine_table.add_utf8_table(table);
if (tab->loosescan_match_tab)
{
@@ -11311,8 +11283,8 @@ make_join_readinfo(JOIN *join, ulonglong
tab->select->quick->index != MAX_KEY && ! tab->table->key_read)
push_index_cond(tab, tab->select->quick->index, icp_other_tables_ok);
}
- trace_refine_table.add_str("scan_type",
- tab->type==JT_NEXT ? "index" : "table");
+ trace_refine_table.add_alnum("scan_type",
+ tab->type == JT_NEXT ? "index" : "table");
break;
case JT_FT:
break;
@@ -13843,7 +13815,7 @@ void optimize_wo_join_buffering(JOIN *jo
DBUG_ENTER("optimize_wo_join_buffering");
Opt_trace_object trace_recompute(trace, "recompute_best_access_paths");
- trace_recompute.add_str("cause", "join_buffering_not_possible");
+ trace_recompute.add_alnum("cause", "join_buffering_not_possible");
Opt_trace_array trace_tables(trace, "tables");
if (first_tab > join->const_tables)
@@ -13869,10 +13841,7 @@ void optimize_wo_join_buffering(JOIN *jo
{
/* Find the best access method that would not use join buffering */
Opt_trace_object trace_one_table(trace);
- trace_one_table.add_str("database", join->tables_list->db,
- strlen(join->tables_list->db));
- trace_one_table.add_str("table", rs->table->alias,
- strlen(rs->table->alias));
+ trace_one_table.add_utf8_table(rs->table);
best_access_path(join, rs, reopt_remaining_tables, i,
i < no_jbuf_before, inner_fanout * outer_fanout,
&pos, &loose_scan_pos);
@@ -14072,7 +14041,7 @@ void advance_sj_state(JOIN *join, table_
double reopt_cost, reopt_rec_count;
/* We use the same FirstLetterUpcase as in EXPLAIN */
Opt_trace_object trace_one_strategy(trace);
- trace_one_strategy.add_str("strategy", "FirstMatch");
+ trace_one_strategy.add_alnum("strategy", "FirstMatch");
optimize_wo_join_buffering(join, pos->first_firstmatch_table, idx,
remaining_tables, FALSE, idx,
&reopt_rec_count, &reopt_cost);
@@ -14143,7 +14112,7 @@ void advance_sj_state(JOIN *join, table_
/* Got a complete LooseScan range. Calculate its cost */
double reopt_cost, reopt_rec_count;
Opt_trace_object trace_one_strategy(trace);
- trace_one_strategy.add_str("strategy", "LooseScan");
+ trace_one_strategy.add_alnum("strategy", "LooseScan");
/*
The same problem as with FirstMatch - we need to save POSITIONs
somewhere but reserving space for all cases would require too
@@ -14220,8 +14189,8 @@ void advance_sj_state(JOIN *join, table_
emb_sj_nest->sj_inner_tables |
emb_sj_nest->nested_join->sj_depends_on;
pos->sjm_scan_last_inner= idx;
- Opt_trace_object(trace).add_str("strategy", "MaterializationScan").
- add_str("choice", "deferred");
+ Opt_trace_object(trace).add_alnum("strategy", "MaterializationScan").
+ add_alnum("choice", "deferred");
}
else if (sjm_strategy == SJ_OPT_MATERIALIZE_LOOKUP)
{
@@ -14245,7 +14214,7 @@ void advance_sj_state(JOIN *join, table_
prefix_rec_count * emb_sj_nest->nested_join->sjm.lookup_cost.total_cost();
Opt_trace_object trace_one_strategy(trace);
- trace_one_strategy.add_str("strategy", "MaterializationLookup").
+ trace_one_strategy.add_alnum("strategy", "MaterializationLookup").
add("cost", mat_read_time).add("records", prefix_rec_count).
add("duplicate_tables_left", pos->dups_producing_tables != 0);
if (mat_read_time < *current_read_time || pos->dups_producing_tables)
@@ -14278,7 +14247,7 @@ void advance_sj_state(JOIN *join, table_
int first_tab= pos->sjm_scan_last_inner + 1 - table_count;
Opt_trace_object trace_one_strategy(trace);
- trace_one_strategy.add_str("strategy", "MaterializationScan");
+ trace_one_strategy.add_alnum("strategy", "MaterializationScan");
/* Get the prefix cost */
if (first_tab == (int)join->const_tables)
@@ -14307,16 +14276,13 @@ void advance_sj_state(JOIN *join, table_
/* Need to re-run best-access-path as we prefix_rec_count has changed */
{
Opt_trace_object trace_recompute(trace, "recompute_best_access_paths");
- trace_recompute.add_str("cause", "costs_of_prefix_changed");
+ trace_recompute.add_alnum("cause", "costs_of_prefix_changed");
Opt_trace_array trace_tables(trace, "tables");
for (i= first_tab + table_count; i <= idx; i++)
{
Opt_trace_object trace_one_table(trace);
- trace_one_table.add_str("database", join->tables_list->db,
- strlen(join->tables_list->db)).
- add_str("table", join->positions[i].table->table->alias,
- strlen(join->positions[i].table->table->alias));
+ trace_one_table.add_utf8_table(join->positions[i].table->table);
best_access_path(join, join->positions[i].table, rem_tables, i, FALSE,
prefix_rec_count, &curpos, &dummy);
prefix_rec_count *= curpos.records_read;
@@ -14443,7 +14409,7 @@ void advance_sj_state(JOIN *join, table_
leave duplicate-producing tables not handled by any strategy.
*/
Opt_trace_object trace_one_strategy(trace);
- trace_one_strategy.add_str("strategy", "DuplicatesWeedout").
+ trace_one_strategy.add_alnum("strategy", "DuplicatesWeedout").
add("cost", dups_cost).
add("records", prefix_rec_count * sj_outer_fanout).
add("duplicate_tables_left", pos->dups_producing_tables != 0);
@@ -14597,7 +14563,7 @@ optimize_cond(JOIN *join, Item *conds, L
{
Opt_trace_object trace_wrapper(trace);
Opt_trace_object trace_cond(trace, "condition_processing");
- trace_cond.add_str("condition", build_equalities ? "WHERE" : "HAVING");
+ trace_cond.add_alnum("condition", build_equalities ? "WHERE" : "HAVING");
/*
Build all multiple equality predicates and eliminate equality
predicates that can be inferred from these multiple equalities.
@@ -18015,8 +17981,7 @@ int safe_index_read(JOIN_TAB *tab)
@retval <0 ??
*/
static int
-join_read_const_table(JOIN_TAB *tab, POSITION *pos,
- Opt_trace_array *trace_array)
+join_read_const_table(JOIN_TAB *tab, POSITION *pos)
{
int error;
DBUG_ENTER("join_read_const_table");
@@ -18025,7 +17990,6 @@ join_read_const_table(JOIN_TAB *tab, POS
table->null_row=0;
table->status=STATUS_NO_RECORD;
- trace_array->add_str(table->alias); /// @todo add info about findings below
if (tab->type == JT_SYSTEM)
{
if ((error=join_read_system(tab)))
=== modified file 'sql/sql_test.cc'
--- a/sql/sql_test.cc 2010-10-21 13:03:30 +0000
+++ b/sql/sql_test.cc 2010-11-24 18:54:26 +0000
@@ -268,7 +268,7 @@ static void print_keyuse(Opt_trace_conte
DBUG_UNLOCK_FILE;
//key_part_map keypart_map; --?? there can be several?
sprintf(buff2, "%s.%s= %s", keyuse->table->alias, fieldname, str.ptr());
- Opt_trace_object(trace).add_str_escaped("condition", buff2, strlen(buff2)).
+ Opt_trace_object(trace).add_utf8("condition", buff2).
add("null_rejecting", keyuse->null_rejecting);
}
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2010-11-22 09:59:17 +0000
+++ b/sql/sql_update.cc 2010-11-24 18:54:26 +0000
@@ -343,9 +343,7 @@ int mysql_update(THD *thd,
{ // Enter scope for optimizer trace wrapper
Opt_trace_object wrapper(thd->opt_trace);
- wrapper.add_str("database", table_list->db,
- strlen(table_list->db)).
- add_str("table", table_list->alias, strlen(table_list->alias));
+ wrapper.add_utf8_table(table);
if (error || !limit ||
(select && select->check_quick(thd, safe_update, limit)))
=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc 2010-10-21 13:03:30 +0000
+++ b/sql/sql_view.cc 2010-12-03 15:26:41 +0000
@@ -1141,8 +1141,9 @@ bool mysql_make_view(THD *thd, File_pars
Opt_trace_object trace_wrapper(thd->opt_trace);
Opt_trace_object trace_view(thd->opt_trace, "view");
- trace_view.add_str("db", table->db, table->db_length).
- add_str("name", table->table_name, table->table_name_length).
+ // When reading I_S.VIEWS, table->alias may be NULL
+ trace_view.add_utf8("database", table->db, table->db_length).
+ add_utf8("view", table->alias ? table->alias : table->table_name).
add("in_select#", old_lex->select_lex.select_number);
/*
=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc 2010-11-17 12:27:39 +0000
+++ b/sql/sys_vars.cc 2010-11-23 10:21:53 +0000
@@ -1425,11 +1425,15 @@ static Sys_var_flagset Sys_optimizer_tra
Opt_trace_context::flag_names,
DEFAULT(Opt_trace_context::FLAG_DEFAULT));
+/*
+ Note how "misc" is not here: it is not accessible to the user; disabling
+ "misc" disables the top object which makes an empty trace.
+*/
static Sys_var_flagset Sys_optimizer_trace_features(
"optimizer_trace_features",
"Enables/disables tracing of selected features of the Optimizer:"
" optimizer_trace_features=option=val[,option=val...], where option is one of"
- " {misc, greedy_search, range_optimizer}"
+ " {greedy_search, range_optimizer}"
" and val is one of {on, off, default}",
SESSION_VAR(optimizer_trace_features), CMD_LINE(REQUIRED_ARG),
Opt_trace_context::feature_names,
=== modified file 'unittest/gunit/opt_trace-t.cc'
--- a/unittest/gunit/opt_trace-t.cc 2010-10-21 20:24:51 +0000
+++ b/unittest/gunit/opt_trace-t.cc 2010-11-24 18:54:26 +0000
@@ -29,8 +29,41 @@
CHARSET_INFO *system_charset_info= &my_charset_utf8_general_ci;
/* ... end here. */
-const Opt_trace_context::feature_value all_features=
- Opt_trace_context::FEATURES_DEFAULT;
+const ulonglong all_features= Opt_trace_context::FEATURES_DEFAULT;
+
+
+/**
+ Checks compliance of a trace with JSON syntax rules.
+ This is a helper which has interest only when developing the test; once you
+ know that the produced trace is compliant and has expected content, just set
+ "expected" to it, add a comparison with "expected", and don't use this
+ function.
+ @param str pointer to trace
+ @param length trace's length
+*/
+void check_json_compliance(const char *str, size_t length)
+{
+#if 0
+ /*
+ Read from stdin, eliminate comments, parse as JSON. If invalid, an exception
+ is thrown by Python, uncaught, which produces a non-zero error code.
+ */
+ const char python_cmd[]=
+ "python -c \""
+ "import json, re, sys;"
+ "s= sys.stdin.read();"
+ "s= re.sub('/\\\\*[ A-Za-z_]* \\\\*/', '', s);"
+ "json.loads(s, 'utf-8')\"";
+ // Send the trace to this new process' stdin:
+ FILE *fd= popen(python_cmd, "w");
+ ASSERT_NE((void*)NULL, fd);
+ ASSERT_EQ((size_t)1, fwrite(str, length, 1, fd));
+ int rc= pclose(fd);
+ rc= WEXITSTATUS(rc);
+ ASSERT_EQ(0, rc);
+#endif
+}
+
/** Test empty trace */
TEST(Trace_content_test, empty)
@@ -55,6 +88,7 @@ TEST(Trace_content_test, empty)
const char expected[]= "{\n}";
ASSERT_STREQ(expected, info.trace_ptr);
ASSERT_EQ(sizeof(expected) - 1, info.trace_length);
+ check_json_compliance(info.trace_ptr, info.trace_length);
ASSERT_EQ((size_t)0, info.missing_bytes);
ASSERT_EQ(false, info.malloc_error);
/* Should be no more traces */
@@ -76,11 +110,11 @@ TEST(Trace_content_test, normal_usage)
ota.add(200.4);
{
Opt_trace_object oto1(&trace);
- oto1.add_str("one key", "one value").
+ oto1.add_alnum("one key", "one value").
add("another key", 100U);
}
Opt_trace_context *trace_ptr= &trace;
- OPT_TRACE(trace_ptr, add_str("one string element"));
+ OPT_TRACE(trace_ptr, add_alnum("one string element"));
ota.add(true);
}
oto.add("yet another key", -1000LL);
@@ -114,6 +148,7 @@ TEST(Trace_content_test, normal_usage)
"}";
ASSERT_STREQ(expected, info.trace_ptr);
ASSERT_EQ(sizeof(expected) - 1, info.trace_length);
+ check_json_compliance(info.trace_ptr, info.trace_length);
ASSERT_EQ((size_t)0, info.missing_bytes);
ASSERT_EQ(false, info.malloc_error);
it++;
@@ -137,10 +172,10 @@ TEST(Trace_content_test, tail)
ota.add(200.4);
{
Opt_trace_object oto1(&trace);
- oto1.add_str("one key", "one value").
+ oto1.add_utf8("one key", "one value", 7). // test explicit length
add("another key", 100LL);
}
- ota.add_str("one string element");
+ ota.add_utf8("one string element", 15); // test explicit length
ota.add(true);
}
oto.add("yet another key", -1000LL);
@@ -159,10 +194,10 @@ TEST(Trace_content_test, tail)
" \"one array\": [\n"
" 200.4,\n"
" {\n"
- " \"one key\": \"one value\",\n"
+ " \"one key\": \"one val\",\n"
" \"another key\": 100\n"
" },\n"
- " \"one string element\",\n"
+ " \"one string elem\",\n"
" true\n"
" ] /* one array */,\n"
" \"yet another key\": -1000,\n"
@@ -199,10 +234,10 @@ TEST(Trace_content_test, buggy_object)
{
Opt_trace_object oto1(&trace);
Opt_trace_struct::dbug_assert_on_syntax_error= false;
- oto1.add_str("one value"); // no key, which is wrong
+ oto1.add_alnum("one value"); // no key, which is wrong
Opt_trace_struct::dbug_assert_on_syntax_error= true;
}
- ota.add_str("one string element");
+ ota.add_alnum("one string element");
ota.add(true);
}
oto.add("yet another key", -1000LL);
@@ -301,7 +336,7 @@ TEST(Trace_content_test, disable_I_S)
ota.add(200.4);
{
Opt_trace_object oto1(&trace);
- oto1.add_str("one key", "one value").
+ oto1.add_alnum("one key", "one value").
add("another key", 100LL);
Opt_trace_disable_I_S otd(&trace, true);
oto1.add("a third key", false);
@@ -313,7 +348,7 @@ TEST(Trace_content_test, disable_I_S)
oto2.add("another key inside", 5LL);
}
Opt_trace_context *trace_ptr= &trace;
- OPT_TRACE(trace_ptr, add_str("one string element"));
+ OPT_TRACE(trace_ptr, add_alnum("one string element"));
ota.add(true);
}
Opt_trace_disable_I_S otd2(&trace, false); // don't disable
@@ -348,6 +383,7 @@ TEST(Trace_content_test, disable_I_S)
"}";
ASSERT_STREQ(expected, info.trace_ptr);
ASSERT_EQ(sizeof(expected) - 1, info.trace_length);
+ check_json_compliance(info.trace_ptr, info.trace_length);
ASSERT_EQ((size_t)0, info.missing_bytes);
ASSERT_EQ(false, info.malloc_error);
it++;
@@ -467,7 +503,7 @@ TEST(Trace_settings_test, max_mem_size)
Opt_trace_array ota(&trace, "one array");
for (int i= 0; i < 100; i++)
{
- ota.add_str("make it long");
+ ota.add_alnum("make it long");
}
}
trace.end();
@@ -503,14 +539,14 @@ TEST(Trace_settings_test, max_mem_size2)
/* make a "long" trace */
{
Opt_trace_object oto(&trace);
- oto.add_str("some key1", "make it long");
+ oto.add_alnum("some key1", "make it long");
}
trace.end();
/* A second similar trace */
ASSERT_EQ(false, trace.start(true, false, false, -2, 2, 21, all_features));
{
Opt_trace_object oto(&trace);
- oto.add_str("some key2", "make it long");
+ oto.add_alnum("some key2", "make it long");
}
trace.end();
Opt_trace_iterator it(&trace);
@@ -534,7 +570,7 @@ TEST(Trace_settings_test, max_mem_size2)
ASSERT_EQ(false, trace.start(true, false, false, -2, 2, 21, all_features));
{
Opt_trace_object oto(&trace);
- oto.add_str("some key3", "make it long");
+ oto.add_alnum("some key3", "make it long");
}
trace.end();
Opt_trace_iterator it2(&trace);
@@ -606,7 +642,7 @@ TEST(Trace_content_test, filtering_by_fe
ota.add(200.4);
{
Opt_trace_object oto1(&trace, Opt_trace_context::GREEDY_SEARCH);
- oto1.add_str("one key", "one value").
+ oto1.add_alnum("one key", "one value").
add("another key", 100LL);
Opt_trace_object oto2(&trace, "a fourth key",
Opt_trace_context::MISC);
@@ -647,8 +683,113 @@ TEST(Trace_content_test, filtering_by_fe
"}";
ASSERT_STREQ(expected, info.trace_ptr);
ASSERT_EQ(sizeof(expected) - 1, info.trace_length);
+ check_json_compliance(info.trace_ptr, info.trace_length);
+ ASSERT_EQ((size_t)0, info.missing_bytes);
+ ASSERT_EQ(false, info.malloc_error);
+ it++;
+ ASSERT_EQ(false, it != Opt_trace_iterator::end);
+}
+
+
+/** Test escaping of characters */
+TEST(Trace_content_test, escaping)
+{
+ Opt_trace_context trace;
+ ASSERT_EQ(false, trace.start(true, true, false, -1, 1, ULONG_MAX,
+ all_features));
+ // All ASCII 0-127 chars are valid UTF8 encodings
+ char all_chars[130];
+ for (uint c= 0; c < sizeof(all_chars) - 2 ; c++)
+ all_chars[c]= c;
+ // Now a character with a two-byte code in utf8: ä
+ all_chars[128]= 0xc3;
+ all_chars[129]= 0xa4;
+ // all_chars is used both as query...
+ trace.set_query(all_chars, sizeof(all_chars), system_charset_info);
+ {
+ Opt_trace_object oto(&trace);
+ // ... and inside the trace:
+ oto.add_utf8("somekey", all_chars, sizeof(all_chars));
+ }
+ trace.end();
+ Opt_trace_iterator it(&trace);
+ ASSERT_EQ(true, it != Opt_trace_iterator::end);
+ const Opt_trace_info info= *it;
+ // we get the trace escaped, JSON-compliant:
+ const char expected[]=
+ "{\n"
+ " \"somekey\": \"\\u0000\\u0001\\u0002\\u0003\\u0004\\u0005\\u0006\\u0007\\u0008\\t\\n\\u000b\\u000c\\r\\u000e\\u000f\\u0010\\u0011\\u0012\\u0013\\u0014\\u0015\\u0016\\u0017\\u0018\\u0019\\u001a\\u001b\\u001c\\u001d\\u001e\\u001f !\\\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\\\]^_`abcdefghijklmnopqrstuvwxyz{|}~ä\"\n"
+ "}";
+ ASSERT_STREQ(expected, info.trace_ptr);
+ ASSERT_EQ(sizeof(expected) - 1, info.trace_length);
+ check_json_compliance(info.trace_ptr, info.trace_length);
+ ASSERT_EQ((size_t)0, info.missing_bytes);
+ ASSERT_EQ(false, info.malloc_error);
+ ASSERT_EQ(sizeof(all_chars), info.query_length);
+ // we get the query unescaped, verbatim, not 0-terminated:
+ ASSERT_EQ(0, memcmp(all_chars, info.query_ptr, sizeof(all_chars)));
+ ASSERT_EQ(system_charset_info, info.query_charset);
+ it++;
+ ASSERT_EQ(false, it != Opt_trace_iterator::end);
+}
+
+
+/** Test how the system handles non-UTF8 characters, a violation of its API */
+TEST(Trace_content_test, non_utf8)
+{
+ Opt_trace_context trace;
+ ASSERT_EQ(false, trace.start(true, true, false, -1, 1, ULONG_MAX,
+ all_features));
+ /*
+ A string which starts with invalid utf8 (the four first bytes are éèÄà in
+ latin1).
+ In utf8, the following holds
+ - E0->EF can only be the start of a 3-byte sequence
+ - C2->DF 2-byte
+ - ASCII a single-byte sequence
+ Because the query was declared as latin1, the trace system takes the
+ precaution to search for and eliminate any non-utf8 characters in the trace
+ (which should not exist, as they violate the API as add_utf8()
+ requires utf8). We test this here.
+ Given the utf8 rules above, the string below is filtered like this:
+ - \xe9 is in E0-EF so is the start of 3-byte sequence, so \xe8 should be
+ the second byte, but \xe8 is also in E0-EF thus the start of a 3-byte
+ sequence: so we have invalid utf8 and \xe9 is replaced with '?', and we
+ restart from \xe8
+ - \xe8 is in E0-EF so start of 3-byte sequence, so \xc4 should be second
+ byte, but is in C2-DF so is start of 2-byte sequence: invalid, \xe8 is
+ replaced with '?'
+ - \xc4 is in C2-DF, so start of a 2-byte sequence, so \xe0 should be second
+ byte, but is in E0-EF so is start of 3-byte sequence: invalid, \xc4 is
+ replaced with '?'
+ - \xe0 is in E0-EF so start of 3-byte sequence so 'A' should be second, but
+ is ASCII so is single-byte sequence: invalid, \xe0 is replaced with '?'
+ - 'A' 'B' and 'C' are valid utf8 and preserved.
+ */
+ const char all_chars[]= "\xe9\xe8\xc4\xe0" "ABC";
+ // We declare a query in latin1
+ trace.set_query(all_chars, sizeof(all_chars), &my_charset_latin1);
+ {
+ Opt_trace_object oto(&trace);
+ oto.add_utf8("somekey", all_chars, sizeof(all_chars) - 1);
+ }
+ trace.end();
+ Opt_trace_iterator it(&trace);
+ ASSERT_EQ(true, it != Opt_trace_iterator::end);
+ const Opt_trace_info info= *it;
+ // This is UTF8 and thus JSON-compliant; ABC is present
+ const char expected[]=
+ "{\n"
+ " \"somekey\": \"????ABC\"\n"
+ "}";
+ ASSERT_STREQ(expected, info.trace_ptr);
+ ASSERT_EQ(sizeof(expected) - 1, info.trace_length);
+ check_json_compliance(info.trace_ptr, info.trace_length);
ASSERT_EQ((size_t)0, info.missing_bytes);
ASSERT_EQ(false, info.malloc_error);
+ ASSERT_EQ(sizeof(all_chars), info.query_length);
+ // we get the query unescaped, verbatim, not 0-terminated:
+ ASSERT_EQ(0, memcmp(all_chars, info.query_ptr, sizeof(all_chars)));
it++;
ASSERT_EQ(false, it != Opt_trace_iterator::end);
}
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3234 to 3242)WL#4800 | Jorgen Loland | 21 Dec |
