#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:jorgen.loland@stripped
3230 Jorgen Loland 2010-10-22 [merge]
Merge wl4800 -> local branch
removed:
mysql-test/r/bug42620.result
mysql-test/t/bug42620.test
modified:
mysql-test/include/optimizer_trace.inc
mysql-test/r/archive_gis.result
mysql-test/r/func_encrypt.result
mysql-test/r/mysqld--help-win.result
mysql-test/r/named_pipe.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/r/shm.result
mysql-test/r/ssl.result
mysql-test/r/ssl_compress.result
mysql-test/suite/binlog/r/binlog_stm_blackhole.result
mysql-test/suite/funcs_1/r/is_columns_is.result
mysql-test/suite/funcs_1/r/is_tables_is.result
=== modified file 'mysql-test/include/optimizer_trace.inc'
--- a/mysql-test/include/optimizer_trace.inc 2010-10-22 08:31:43 +0000
+++ b/mysql-test/include/optimizer_trace.inc 2010-10-22 13:28:18 +0000
@@ -30,13 +30,13 @@ set @@session.optimizer_trace="enabled=o
select * from information_schema.OPTIMIZER_TRACE;
-CREATE TABLE C (c int);
-INSERT INTO C VALUES (NULL);
+CREATE TABLE t5 (c int);
+INSERT INTO t5 VALUES (NULL);
-CREATE TABLE D (d int , KEY (d));
-INSERT INTO D VALUES (NULL),(NULL);
+CREATE TABLE t6 (d int , KEY (d));
+INSERT INTO t6 VALUES (NULL),(NULL);
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
# some float values might not be identical on all platforms, we will see.
select * from information_schema.OPTIMIZER_TRACE;
@@ -46,7 +46,7 @@ select (1-length(replace(TRACE, " ", "")
# now all on one line
set optimizer_trace="one_line=on";
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
select * from information_schema.OPTIMIZER_TRACE;
# check frequency of spaces
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
@@ -56,25 +56,25 @@ select (1-length(replace(TRACE, " ", "")
set optimizer_trace="one_line=off,end_marker=on";
# trace should be produced by EXPLAIN too
-EXPLAIN SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+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 D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (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 D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (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;
set @@session.optimizer_trace="enabled=on";
# union in subquery
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
select * from information_schema.OPTIMIZER_TRACE;
# union in subquery in WHERE clause
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null));
+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 * from information_schema.OPTIMIZER_TRACE;
# Test max_mem_size: re-run same query with lower max_mem_size:
@@ -89,7 +89,7 @@ select length(@trace);
# whereas in ps-protocol mode they verify non-truncation.
set optimizer_trace_max_mem_size=8400;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null));
+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;
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
select length(@trace2),
@@ -114,13 +114,13 @@ eval set optimizer_trace_max_mem_size=$D
# semijoin
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null);
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
select * from information_schema.OPTIMIZER_TRACE;
# subquery materialization
set optimizer_switch="semijoin=off";
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null);
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
select * from information_schema.OPTIMIZER_TRACE;
set optimizer_switch=default;
@@ -303,22 +303,22 @@ drop table t1,t2;
# test non-SELECT statements
-update D set d=5 where D is NULL;
+update t6 set d=5 where d is NULL;
select * from information_schema.OPTIMIZER_TRACE;
-delete from D where d=5;
+delete from t6 where d=5;
select * from information_schema.OPTIMIZER_TRACE;
-insert into D values(6),(7),(8);
+insert into t6 values(6),(7),(8);
select * from information_schema.OPTIMIZER_TRACE;
-insert into D select * from D where D>7;
+insert into t6 select * from t6 where d>7;
select * from information_schema.OPTIMIZER_TRACE;
-update C,D set D.d=D.d+C.c+4-C.c-4 where D>7000;
+update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000;
select * from information_schema.OPTIMIZER_TRACE;
-delete D from C,D where D>7000;
+delete t6 from t5, t6 where d>7000;
select * from information_schema.OPTIMIZER_TRACE;
# Test optimizer_trace_offset/limit for consecutive
@@ -387,16 +387,16 @@ select f1()|
select * from information_schema.OPTIMIZER_TRACE|
select s, f1() from t2 order by s desc|
select * from information_schema.OPTIMIZER_TRACE|
-select * from D where d in (select f1() from t2 where s="c")|
+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|
select @@optimizer_trace_offset, @@optimizer_trace_limit|
-select * from D where d in (select f1() from t2 where s="c")|
+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 D where d in (select f1() from t2 where s="c")|
+select * from t6 where d in (select f1() from t2 where s="c")|
select * from information_schema.OPTIMIZER_TRACE|
set optimizer_trace_offset=default, optimizer_trace_limit=default|
@@ -405,8 +405,8 @@ set optimizer_trace_offset=default, opti
create procedure p1(arg char(1))
begin
declare res int;
- select d into res from D where d in (select f1() from t2 where s=arg);
- select d+1 into res from D where d=res+1;
+ select d into res from t6 where d in (select f1() from t2 where s=arg);
+ select d+1 into res from t6 where d=res+1;
end|
call p1("c")|
select * from information_schema.OPTIMIZER_TRACE|
@@ -416,7 +416,7 @@ create trigger trg1 before insert on t2
begin
set new.s=f1();
end|
-insert into t2 select d,100,200 from D where d is not null|
+insert into t2 select d,100,200 from t6 where d is not null|
select * from information_schema.OPTIMIZER_TRACE|
select * from t2|
@@ -446,11 +446,11 @@ begin
# want to see all of SELECT below
set optimizer_trace_offset=0, optimizer_trace_limit=100;
set optimizer_trace="enabled=on";
- select d into res from D where d in (select f1() from t2 where s=arg);
+ select d into res from t6 where d in (select f1() from t2 where s=arg);
set optimizer_trace="enabled=off"; # and not more
insert into optt select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=default, optimizer_trace_limit=default;
- select d+1 into res from D where d=res+1;
+ select d+1 into res from t6 where d=res+1;
end|
call p1("c")|
select * from optt|
@@ -497,5 +497,5 @@ select 1 union select 2;
--remove_file $file
drop table t1,t2;
-DROP TABLE C,D;
+DROP TABLE t5,t6;
set optimizer_trace=default;
=== modified file 'mysql-test/r/archive_gis.result'
--- a/mysql-test/r/archive_gis.result 2010-06-09 14:16:33 +0000
+++ b/mysql-test/r/archive_gis.result 2010-10-22 09:05:11 +0000
@@ -236,7 +236,7 @@ explain extended select Dimension(g), Ge
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
Warnings:
-Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`
+Note 1003 /* select#1 */ select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`
SELECT fid, X(g) FROM gis_point ORDER by fid;
fid X(g)
101 10
@@ -253,7 +253,7 @@ explain extended select X(g),Y(g) FROM g
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
Warnings:
-Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
+Note 1003 /* select#1 */ select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
fid AsText(StartPoint(g))
105 POINT(0 0)
@@ -288,7 +288,7 @@ explain extended select AsText(StartPoin
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
+Note 1003 /* select#1 */ select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
fid AsText(Centroid(g))
108 POINT(15 15)
@@ -318,7 +318,7 @@ explain extended select AsText(Centroid(
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
+Note 1003 /* select#1 */ select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
fid IsClosed(g)
114 0
@@ -357,7 +357,7 @@ explain extended SELECT fid, NumGeometri
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
+Note 1003 /* select#1 */ select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
fid AsText(GeometryN(g, 2))
111 POINT(10 10)
@@ -385,7 +385,7 @@ explain extended SELECT fid, AsText(Geom
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
+Note 1003 /* select#1 */ select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
@@ -405,7 +405,7 @@ id select_type table type possible_keys
1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, regular buffers)
Warnings:
-Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
+Note 1003 /* select#1 */ select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
CREATE TABLE t1 (
a INTEGER PRIMARY KEY AUTO_INCREMENT,
=== removed file 'mysql-test/r/bug42620.result'
--- a/mysql-test/r/bug42620.result 2010-10-09 15:04:30 +0000
+++ b/mysql-test/r/bug42620.result 1970-01-01 00:00:00 +0000
@@ -1,1109 +0,0 @@
-set @@optimizer_trace="enabled=on,end_marker=on";
-firstmatch (default, slow)
-explain extended select /*firstmatch*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY ot range expr_key expr_key 4 NULL 726 100.00 Using where; Using index
-1 PRIMARY it ALL NULL NULL NULL NULL 320000 100.00 Using where; FirstMatch(ot)
-Warnings:
-Note 1003 /* select#1 */ select count(`test`.`ot`.`expr_key`) AS `count(expr_key)` from `test`.`ot` semi join (`test`.`it`) where ((`test`.`it`.`expr_nokey` = `test`.`ot`.`expr_key`) and (`test`.`ot`.`expr_key` < 100000000))
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain extended select /*firstmatch*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000 {
- "steps": [
- {
- "expanded_query": "/* select#1 */ select count(`expr_key`) AS `count(expr_key)` from `test`.`ot` where (`expr_key` in (/* select#2 */ select `expr_nokey` from `test`.`it`) and (`ot`.`expr_key` < 100000000))"
- },
- {
- "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`.`ot`.`expr_key` < 100000000) and (`test`.`ot`.`expr_key` = `test`.`it`.`expr_nokey`))",
- "after_equality_propagation": "(1 and (`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))",
- "after_constant_propagation": "(1 and (`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))",
- "after_trivial_conditions_removal": "((`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- {
- "condition": "ot.expr_key= `test`.`it`.`expr_nokey`",
- "null_rejecting": false
- }
- ] /* ref-optimizer-key-uses */
- },
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "ot",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- },
- {
- "table": "it",
- "table_scan": {
- "records": 320000,
- "cost": 1798
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "pulled_out_semijoin_tables": [
- ] /* pulled_out_semijoin_tables */
- },
- {
- "execution_plan_for_potential_materialization": {
- "steps": [
- {
- "considered_execution_plans": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* considered_execution_plans */
- }
- ] /* steps */
- } /* execution_plan_for_potential_materialization */
- },
- {
- "considered_execution_plans": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "expr_key",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 161.64,
- "records": 726,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 161.64,
- "records_for_plan": 726,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1838.7,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2000.4,
- "records_for_plan": 2.32e8,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "recompute_best_access_paths": {
- "cause": "join_buffering_not_possible",
- "tables": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1838.7,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recompute_best_access_paths */,
- "cost": 2000.4,
- "records": 726,
- "chosen": true
- },
- {
- "strategy": "MaterializationLookup",
- "cost": 322687,
- "records": 726,
- "duplicate_tables_left": false,
- "chosen": false
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 3.7e12,
- "records": 726,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- {
- "strategy": "MaterializationScan",
- "choice": "deferred"
- }
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- }
- ] /* considered_execution_plans */
- },
- {
- "reconsidering_access_paths_for_semijoin": {
- "strategy": "FirstMatch",
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1.31e6,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- } /* reconsidering_access_paths_for_semijoin */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`it`.`expr_nokey` = `test`.`ot`.`expr_key`) and (`test`.`ot`.`expr_key` < 100000000))",
- "attached_conditions": [
- {
- "table": "ot",
- "attached": "(`test`.`ot`.`expr_key` < 100000000)"
- },
- {
- "table": "it",
- "attached": "(`test`.`it`.`expr_nokey` = `test`.`ot`.`expr_key`)"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
-set optimizer_switch="default,firstmatch=off";
-SJ-mat (better)
-explain extended select /*SJ-mat*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY ot range expr_key expr_key 4 NULL 726 100.00 Using where; Using index
-1 PRIMARY it ALL NULL NULL NULL NULL 320000 100.00 Materialize
-Warnings:
-Note 1003 /* select#1 */ select count(`test`.`ot`.`expr_key`) AS `count(expr_key)` from `test`.`ot` semi join (`test`.`it`) where ((`test`.`it`.`expr_nokey` = `test`.`ot`.`expr_key`) and (`test`.`ot`.`expr_key` < 100000000))
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain extended select /*SJ-mat*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000 {
- "steps": [
- {
- "expanded_query": "/* select#1 */ select count(`expr_key`) AS `count(expr_key)` from `test`.`ot` where (`expr_key` in (/* select#2 */ select `expr_nokey` from `test`.`it`) and (`ot`.`expr_key` < 100000000))"
- },
- {
- "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`.`ot`.`expr_key` < 100000000) and (`test`.`ot`.`expr_key` = `test`.`it`.`expr_nokey`))",
- "after_equality_propagation": "(1 and (`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))",
- "after_constant_propagation": "(1 and (`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))",
- "after_trivial_conditions_removal": "((`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- {
- "condition": "ot.expr_key= `test`.`it`.`expr_nokey`",
- "null_rejecting": false
- }
- ] /* ref-optimizer-key-uses */
- },
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "ot",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- },
- {
- "table": "it",
- "table_scan": {
- "records": 320000,
- "cost": 1798
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "pulled_out_semijoin_tables": [
- ] /* pulled_out_semijoin_tables */
- },
- {
- "execution_plan_for_potential_materialization": {
- "steps": [
- {
- "considered_execution_plans": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* considered_execution_plans */
- }
- ] /* steps */
- } /* execution_plan_for_potential_materialization */
- },
- {
- "considered_execution_plans": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "expr_key",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 161.64,
- "records": 726,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 161.64,
- "records_for_plan": 726,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1838.7,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2000.4,
- "records_for_plan": 2.32e8,
- "semijoin_strategy_choice": [
- {
- "strategy": "MaterializationLookup",
- "cost": 322687,
- "records": 726,
- "duplicate_tables_left": true,
- "chosen": true
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 3.7e12,
- "records": 726,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- {
- "strategy": "MaterializationScan",
- "choice": "deferred"
- }
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "expr_key",
- "cost": 320000,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 321799,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- {
- "strategy": "MaterializationScan",
- "recompute_best_access_paths": {
- "cause": "costs_of_prefix_changed",
- "tables": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "expr_key",
- "cost": 320000,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recompute_best_access_paths */,
- "cost": 961799,
- "records": 320000,
- "duplicate_tables_left": true,
- "chosen": true
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 5.12e9,
- "records": 1,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "pruned_by_cost": true
- }
- ] /* rest_of_plan */
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`it`.`expr_nokey` = `test`.`ot`.`expr_key`) and (`test`.`ot`.`expr_key` < 100000000))",
- "attached_conditions": [
- {
- "table": "ot",
- "attached": "(`test`.`ot`.`expr_key` < 100000000)"
- },
- {
- "table": "it",
- "attached": null
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
-set optimizer_switch="default,materialization=off,firstmatch=off";
-Duplicate Weed-out (best)
-explain extended select /*weedout*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY it ALL NULL NULL NULL NULL 320000 100.00 Using where; Start temporary
-1 PRIMARY ot ref expr_key expr_key 4 test.it.expr_nokey 1 100.00 Using index; End temporary
-Warnings:
-Note 1003 /* select#1 */ select count(`test`.`ot`.`expr_key`) AS `count(expr_key)` from `test`.`ot` semi join (`test`.`it`) where ((`test`.`ot`.`expr_key` = `test`.`it`.`expr_nokey`) and (`test`.`it`.`expr_nokey` < 100000000))
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain extended select /*weedout*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000 {
- "steps": [
- {
- "expanded_query": "/* select#1 */ select count(`expr_key`) AS `count(expr_key)` from `test`.`ot` where (`expr_key` in (/* select#2 */ select `expr_nokey` from `test`.`it`) and (`ot`.`expr_key` < 100000000))"
- },
- {
- "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`.`ot`.`expr_key` < 100000000) and (`test`.`ot`.`expr_key` = `test`.`it`.`expr_nokey`))",
- "after_equality_propagation": "(1 and (`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))",
- "after_constant_propagation": "(1 and (`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))",
- "after_trivial_conditions_removal": "((`test`.`ot`.`expr_key` < 100000000) and multiple equal(`test`.`ot`.`expr_key`, `test`.`it`.`expr_nokey`))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- {
- "condition": "ot.expr_key= `test`.`it`.`expr_nokey`",
- "null_rejecting": false
- }
- ] /* ref-optimizer-key-uses */
- },
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "ot",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- },
- {
- "table": "it",
- "table_scan": {
- "records": 320000,
- "cost": 1798
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "pulled_out_semijoin_tables": [
- ] /* pulled_out_semijoin_tables */
- },
- {
- "considered_execution_plans": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "expr_key",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "table scan",
- "cost": 161.64,
- "records": 726,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 161.64,
- "records_for_plan": 726,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1838.7,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2000.4,
- "records_for_plan": 2.32e8,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 3.7e12,
- "records": 726,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "expr_key",
- "cost": 320000,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "heuristic_index_must_be_cheaper": true,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 321799,
- "records_for_plan": 320000,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 5.12e9,
- "records": 1,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`ot`.`expr_key` = `test`.`it`.`expr_nokey`) and (`test`.`it`.`expr_nokey` < 100000000))",
- "attached_conditions": [
- {
- "table": "it",
- "attached": "(`test`.`it`.`expr_nokey` < 100000000)"
- },
- {
- "table": "ot",
- "attached": null
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
-set optimizer_switch="default,materialization=on,semijoin=off";
-Subq mater (like SJ-mat)
-explain extended select /*subq-mater*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY ot range expr_key expr_key 4 NULL 726 100.00 Using where; Using index
-2 SUBQUERY it ALL NULL NULL NULL NULL 320000 100.00
-Warnings:
-Note 1003 /* select#1 */ select count(`test`.`ot`.`expr_key`) AS `count(expr_key)` from `test`.`ot` where (<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in ( <materialize> (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it` ), <primary_index_lookup>(`test`.`ot`.`expr_key` in <temporary table> on distinct_key where ((`test`.`ot`.`expr_key` = `materialized subselect`.`expr_nokey`))))) and (`test`.`ot`.`expr_key` < 100000000))
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain extended select /*subq-mater*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000 {
- "steps": [
- {
- "expanded_query": "/* select#1 */ select count(`expr_key`) AS `count(expr_key)` from `test`.`ot` where (`expr_key` in (/* select#2 */ select `expr_nokey` from `test`.`it`) and (`ot`.`expr_key` < 100000000))"
- },
- {
- "join_preparation": {
- "select#": 1,
- "steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": false
- } /* transformation */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it`)) and (`test`.`ot`.`expr_key` < 100000000))",
- "after_equality_propagation": "(<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it`)) and (`test`.`ot`.`expr_key` < 100000000))",
- "after_constant_propagation": "(<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it`)) and (`test`.`ot`.`expr_key` < 100000000))",
- "after_trivial_conditions_removal": "(<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it`)) and (`test`.`ot`.`expr_key` < 100000000))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- ] /* ref-optimizer-key-uses */
- },
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "ot",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 161.64,
- "records": 726,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 161.64,
- "records_for_plan": 726,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it`)) and (`test`.`ot`.`expr_key` < 100000000))",
- "attached_conditions": [
- {
- "table": "ot",
- "attached": "(<in_optimizer>(`test`.`ot`.`expr_key`,`test`.`ot`.`expr_key` in (/* select#2 */ select `test`.`it`.`expr_nokey` from `test`.`it`)) and (`test`.`ot`.`expr_key` < 100000000))"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "it",
- "table_scan": {
- "records": 320000,
- "cost": 1798
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
- {
- "table": "it",
- "attached": null
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
-set optimizer_switch="default,materialization=off,semijoin=off";
-5.1 plan (slow); "*DEPENDENT* SUBQUERY" because IN->EXISTS
-makes it correlated (injects ot.expr_key inside subquery)
-explain extended select /*5.1, IN->EXISTS*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY ot range expr_key expr_key 4 NULL 726 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY it ALL NULL NULL NULL NULL 320000 100.00 Using where
-Warnings:
-Note 1003 /* select#1 */ select count(`test`.`ot`.`expr_key`) AS `count(expr_key)` from `test`.`ot` where (<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))
-select * from information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain extended select /*5.1, IN->EXISTS*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000 {
- "steps": [
- {
- "expanded_query": "/* select#1 */ select count(`expr_key`) AS `count(expr_key)` from `test`.`ot` where (`expr_key` in (/* select#2 */ select `expr_nokey` from `test`.`it`) and (`ot`.`expr_key` < 100000000))"
- },
- {
- "join_preparation": {
- "select#": 1,
- "steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": false
- } /* transformation */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "chosen": false
- } /* transformation */
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "EXISTS (CORRELATED SELECT)",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))",
- "after_equality_propagation": "(<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))",
- "after_constant_propagation": "(<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))",
- "after_trivial_conditions_removal": "(<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- ] /* ref-optimizer-key-uses */
- },
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "ot",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "table": "ot",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 161.64,
- "records": 726,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 161.64,
- "records_for_plan": 726,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))",
- "attached_conditions": [
- {
- "table": "ot",
- "attached": "(<in_optimizer>(`test`.`ot`.`expr_key`,<exists>(/* select#2 */ select 1 from `test`.`it` where (<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`))) and (`test`.`ot`.`expr_key` < 100000000))"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`)",
- "after_equality_propagation": "(<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`)",
- "after_constant_propagation": "(<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`)",
- "after_trivial_conditions_removal": "(<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`)"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- ] /* ref-optimizer-key-uses */
- },
- {
- "constant_tables": [
- ] /* constant_tables */,
- "records_estimation": [
- {
- "table": "it",
- "table_scan": {
- "records": 320000,
- "cost": 1798
- } /* table_scan */
- }
- ] /* records_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "table": "it",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 1798.9,
- "records": 320000,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1798.9,
- "records_for_plan": 320000,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "(<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`)",
- "attached_conditions": [
- {
- "table": "it",
- "attached": "(<cache>(`test`.`ot`.`expr_key`) = `test`.`it`.`expr_nokey`)"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
-drop table it, ot;
=== modified file 'mysql-test/r/func_encrypt.result'
--- a/mysql-test/r/func_encrypt.result 2009-09-10 09:18:29 +0000
+++ b/mysql-test/r/func_encrypt.result 2010-10-22 09:05:11 +0000
@@ -182,7 +182,7 @@ explain extended select des_decrypt(des_
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select des_decrypt(des_encrypt('hello',4),'password2') AS `des_decrypt(des_encrypt("hello",4),'password2')`,des_decrypt(des_encrypt('hello','hidden')) AS `des_decrypt(des_encrypt("hello","hidden"))`
+Note 1003 /* select#1 */ select des_decrypt(des_encrypt('hello',4),'password2') AS `des_decrypt(des_encrypt("hello",4),'password2')`,des_decrypt(des_encrypt('hello','hidden')) AS `des_decrypt(des_encrypt("hello","hidden"))`
drop table if exists t1;
create table t1 (f1 smallint(6) default null, f2 mediumtext character set utf8)
engine=myisam default charset=latin1;
=== modified file 'mysql-test/r/mysqld--help-win.result'
--- a/mysql-test/r/mysqld--help-win.result 2010-09-24 07:19:35 +0000
+++ b/mysql-test/r/mysqld--help-win.result 2010-10-22 09:05:11 +0000
@@ -409,6 +409,24 @@ The following options may be given as th
loosescan, firstmatch, mrr, mrr_cost_based,
index_condition_pushdown} and val is one of {on, off,
default}
+ --optimizer-trace=name
+ Controls tracing of the Optimizer:
+ optimizer_trace=option=val[,option=val...], where option
+ is one of {enabled, end_marker, one_line} and val is one
+ of {on, off, default}
+ --optimizer-trace-features=name
+ Enables/disables tracing of selected features of the
+ Optimizer:
+ optimizer_trace_features=option=val[,option=val...],
+ where option is one of {misc, greedy_search} and val is
+ one of {on, off, default}
+ --optimizer-trace-limit=#
+ Maximum number of shown optimizer traces
+ --optimizer-trace-max-mem-size=#
+ maximum allowed cumulated size of remembered optimizer
+ traces
+ --optimizer-trace-offset=#
+ Offset of first optimizer trace to show; see manual
--performance-schema
Enable the performance schema.
--performance-schema-events-waits-history-long-size=#
@@ -857,6 +875,11 @@ optimizer-join-cache-level 1
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
+optimizer-trace-limit 1
+optimizer-trace-max-mem-size 16384
+optimizer-trace-offset -1
performance-schema FALSE
performance-schema-events-waits-history-long-size 10000
performance-schema-events-waits-history-size 10
=== modified file 'mysql-test/r/named_pipe.result'
--- a/mysql-test/r/named_pipe.result 2010-06-11 08:57:45 +0000
+++ b/mysql-test/r/named_pipe.result 2010-10-22 09:05:11 +0000
@@ -1505,7 +1505,7 @@ explain extended select count(*),min(fld
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
Warnings:
-Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
+Note 1003 /* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2010-10-22 08:31:43 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2010-10-22 13:28:18 +0000
@@ -4,19 +4,19 @@ set optimizer_trace_max_mem_size=1048576
set @@session.optimizer_trace="enabled=on";
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-CREATE TABLE C (c int);
-INSERT INTO C VALUES (NULL);
-CREATE TABLE D (d int , KEY (d));
-INSERT INTO D VALUES (NULL),(NULL);
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
+CREATE TABLE t5 (c int);
+INSERT INTO t5 VALUES (NULL);
+CREATE TABLE t6 (d int , KEY (d));
+INSERT INTO t6 VALUES (NULL),(NULL);
+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 D WHERE d = c) AS RESULT FROM C {
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {
"steps": [
{
- "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = `c`)) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -38,12 +38,12 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
],
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
@@ -67,16 +67,16 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = NULL)",
- "after_equality_propagation": "multiple equal(NULL, `test`.`D`.`d`)",
- "after_constant_propagation": "multiple equal(NULL, `test`.`D`.`d`)",
- "after_trivial_conditions_removal": "multiple equal(NULL, `test`.`D`.`d`)"
+ "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": "D.d= NULL",
+ "condition": "t6.d= NULL",
"null_rejecting": true
}
]
@@ -87,7 +87,7 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -108,7 +108,7 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
"chosen": true
},
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
},
@@ -130,29 +130,29 @@ SELECT (SELECT 1 FROM D 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.1160
+49.9339
set optimizer_trace="one_line=on";
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
+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 D WHERE d = c) AS RESULT FROM C {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = `c`)) AS `RESULT` from `test`.`C`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["C"],"records_estimation": [{"database": "test","table": "C","records": 1,"cost": 1,"cause": "system_table"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`D`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`D`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "D.d= NULL","null_rejecti
ng": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "D","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": "D","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": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"cause": "system_table"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"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","n
ull_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 (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-6.0729
+6.0281
set optimizer_trace="one_line=off,end_marker=on";
-EXPLAIN SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+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
-1 PRIMARY C system NULL NULL NULL NULL 1
+1 PRIMARY t5 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-EXPLAIN SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C {
+EXPLAIN SELECT (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`.`D` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -174,12 +174,12 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
@@ -203,16 +203,16 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -223,7 +223,7 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -244,7 +244,7 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
"chosen": true
} /* best_covering_index_scan */,
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
} /* impossible_condition */,
@@ -263,15 +263,15 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
}
] /* steps */
} 0 0
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (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 D WHERE d = ifnull(c,null)) AS RESULT FROM C {
+SELECT (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`.`D` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -293,12 +293,12 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
@@ -322,16 +322,16 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -342,7 +342,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -363,7 +363,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"chosen": true
} /* best_covering_index_scan */,
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
} /* impossible_condition */,
@@ -383,7 +383,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* steps */
} 0 0
set @@session.optimizer_trace="enabled=off";
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
@@ -393,15 +393,15 @@ set @@session.optimizer_trace="enabled=o
] /* steps */
} 0 0
set @@session.optimizer_trace="enabled=on";
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+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
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C {
+SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 {
"steps": [
{
- "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = ifnull(`c`,NULL)) union /* select#3 */ select 2 from `test`.`D` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -430,12 +430,12 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
@@ -459,16 +459,16 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -479,7 +479,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -500,7 +500,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"chosen": true
} /* best_covering_index_scan */,
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
} /* impossible_condition */,
@@ -524,16 +524,16 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -544,7 +544,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -565,7 +565,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"chosen": true
} /* best_covering_index_scan */,
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
} /* impossible_condition */,
@@ -646,14 +646,14 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
}
] /* steps */
} 0 0
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null));
+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 * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) {
+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)) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`C` where 5 in (/* select#2 */ select 1 from `test`.`D` where (`d` = ifnull(`c`,NULL)) union /* select#3 */ select 2 from `test`.`D` where (`d` = ifnull(`c`,NULL)))"
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t5` where 5 in (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`d` = ifnull(`c`,NULL)))"
},
{
"join_preparation": {
@@ -731,10 +731,10 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))",
- "after_equality_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))",
- "after_constant_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))",
- "after_trivial_conditions_removal": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))"
+ "original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "after_equality_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "after_constant_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "after_trivial_conditions_removal": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
} /* condition_processing */
},
{
@@ -743,12 +743,12 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
@@ -762,9 +762,9 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
- "after_equality_propagation": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
- "after_constant_propagation": "((`test`.`D`.`d` = NULL) and (<cache>(5) = 1))",
+ "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
+ "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
+ "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 1))",
"after_trivial_conditions_removal": null
} /* condition_processing */
}
@@ -781,9 +781,9 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
- "after_equality_propagation": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
- "after_constant_propagation": "((`test`.`D`.`d` = NULL) and (<cache>(5) = 2))",
+ "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
+ "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
+ "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 2))",
"after_trivial_conditions_removal": null
} /* condition_processing */
}
@@ -884,16 +884,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-8953
+8988
set optimizer_trace_max_mem_size=8400;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
1
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null));
+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)
-671
+702
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -901,7 +901,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8282 1 1
+8286 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)
@@ -925,16 +925,16 @@ select * from information_schema.OPTIMIZ
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
381 0
set optimizer_trace_max_mem_size=1048576;
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null);
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY C system NULL NULL NULL NULL 1
-1 PRIMARY D ref d d 5 const 1 Using where; Using index; FirstMatch(C)
+1 PRIMARY t5 system NULL NULL NULL NULL 1
+1 PRIMARY t6 ref d d 5 const 1 Using where; Using index; FirstMatch(t5)
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null) {
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`C` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`D` where isnull(`d`))"
+ "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`t5` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`t6` where isnull(`d`))"
},
{
"join_preparation": {
@@ -973,35 +973,35 @@ explain SELECT c FROM C where c+1 in (se
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(1 and isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))",
- "after_equality_propagation": "(1 and isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))",
- "after_constant_propagation": "(1 and isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))",
- "after_trivial_conditions_removal": "(isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))"
+ "original_condition": "(1 and isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))",
+ "after_equality_propagation": "(1 and isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))",
+ "after_constant_propagation": "(1 and isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))",
+ "after_trivial_conditions_removal": "(isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= NULL",
+ "condition": "t6.d= NULL",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
},
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -1060,7 +1060,7 @@ explain SELECT c FROM C where c+1 in (se
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -1112,7 +1112,7 @@ explain SELECT c FROM C where c+1 in (se
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -1166,12 +1166,12 @@ explain SELECT c FROM C where c+1 in (se
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(isnull(`test`.`D`.`d`) and ((NULL + 1) = (`test`.`D`.`d` + 1)))",
+ "original_condition": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "(isnull(`test`.`D`.`d`) and ((NULL + 1) = (`test`.`D`.`d` + 1)))"
+ "table": "t6",
+ "attached": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -1182,16 +1182,16 @@ explain SELECT c FROM C where c+1 in (se
] /* steps */
} 0 0
set optimizer_switch="semijoin=off";
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null);
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY C system NULL NULL NULL NULL 1
-2 SUBQUERY D ref d d 5 const 1 Using where; Using index
+1 PRIMARY t5 system NULL NULL NULL NULL 1
+2 SUBQUERY t6 ref d d 5 const 1 Using where; Using index
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null) {
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`C` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`D` where isnull(`d`))"
+ "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`t5` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`t6` where isnull(`d`))"
},
{
"join_preparation": {
@@ -1230,10 +1230,10 @@ explain SELECT c FROM C where c+1 in (se
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
- "after_equality_propagation": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
- "after_constant_propagation": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
- "after_trivial_conditions_removal": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))"
+ "original_condition": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "after_equality_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "after_constant_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "after_trivial_conditions_removal": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))"
} /* condition_processing */
},
{
@@ -1242,12 +1242,12 @@ explain SELECT c FROM C where c+1 in (se
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
@@ -1256,7 +1256,7 @@ explain SELECT c FROM C where c+1 in (se
},
{
"attaching_conditions_to_tables": {
- "original_condition": "<in_optimizer>((NULL + 1),(NULL + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
+ "original_condition": "<in_optimizer>((NULL + 1),(NULL + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
"attached_conditions": [
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -1279,16 +1279,16 @@ explain SELECT c FROM C where c+1 in (se
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "isnull(`test`.`D`.`d`)",
- "after_equality_propagation": "isnull(`test`.`D`.`d`)",
- "after_constant_propagation": "isnull(`test`.`D`.`d`)",
- "after_trivial_conditions_removal": "isnull(`test`.`D`.`d`)"
+ "original_condition": "isnull(`test`.`t6`.`d`)",
+ "after_equality_propagation": "isnull(`test`.`t6`.`d`)",
+ "after_constant_propagation": "isnull(`test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "isnull(`test`.`t6`.`d`)"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= NULL",
+ "condition": "t6.d= NULL",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -1299,7 +1299,7 @@ explain SELECT c FROM C where c+1 in (se
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 2,
@@ -1351,7 +1351,7 @@ explain SELECT c FROM C where c+1 in (se
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -1378,12 +1378,12 @@ explain SELECT c FROM C where c+1 in (se
},
{
"attaching_conditions_to_tables": {
- "original_condition": "isnull(`test`.`D`.`d`)",
+ "original_condition": "isnull(`test`.`t6`.`d`)",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "isnull(`test`.`D`.`d`)"
+ "table": "t6",
+ "attached": "isnull(`test`.`t6`.`d`)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -4178,13 +4178,13 @@ trace
] /* steps */
}
drop table t1,t2;
-update D set d=5 where D is NULL;
+update t6 set d=5 where d is NULL;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-update D set d=5 where D is NULL {
+update t6 set d=5 where d is NULL {
"steps": [
{
- "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`D` where isnull(`D`)"
+ "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where isnull(`d`)"
},
{
"range_analysis": {
@@ -4241,10 +4241,10 @@ update D set d=5 where D is NULL {
}
] /* steps */
} 0 0
-delete from D where d=5;
+delete from t6 where d=5;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-delete from D where d=5 {
+delete from t6 where d=5 {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`d` = 5)"
@@ -4300,23 +4300,23 @@ delete from D where d=5 {
}
] /* steps */
} 0 0
-insert into D values(6),(7),(8);
+insert into t6 values(6),(7),(8);
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-insert into D values(6),(7),(8) {
+insert into t6 values(6),(7),(8) {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual"
}
] /* steps */
} 0 0
-insert into D select * from D where D>7;
+insert into t6 select * from t6 where d>7;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-insert into D select * from D where D>7 {
+insert into t6 select * from t6 where d>7 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`D` where (`D` > 7)"
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t6` where (`d` > 7)"
},
{
"join_preparation": {
@@ -4332,10 +4332,10 @@ insert into D select * from D where D>7
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` > 7)",
- "after_equality_propagation": "(`test`.`D`.`d` > 7)",
- "after_constant_propagation": "(`test`.`D`.`d` > 7)",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` > 7)"
+ "original_condition": "(`test`.`t6`.`d` > 7)",
+ "after_equality_propagation": "(`test`.`t6`.`d` > 7)",
+ "after_constant_propagation": "(`test`.`t6`.`d` > 7)",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` > 7)"
} /* condition_processing */
},
{
@@ -4348,7 +4348,7 @@ insert into D select * from D where D>7
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 3,
@@ -4400,7 +4400,7 @@ insert into D select * from D where D>7
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -4420,12 +4420,12 @@ insert into D select * from D where D>7
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`D`.`d` > 7)",
+ "original_condition": "(`test`.`t6`.`d` > 7)",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "(`test`.`D`.`d` > 7)"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` > 7)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -4435,13 +4435,13 @@ insert into D select * from D where D>7
}
] /* steps */
} 0 0
-update C,D set D.d=D.d+C.c+4-C.c-4 where D>7000;
+update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-update C,D set D.d=D.d+C.c+4-C.c-4 where D>7000 {
+update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `D`.`d` AS `d` from `test`.`C` join `test`.`D` where (`D` > 7000)"
+ "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `test`.`t5` join `test`.`t6` where (`d` > 7000)"
},
{
"join_preparation": {
@@ -4457,10 +4457,10 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` > 7000)",
- "after_equality_propagation": "(`test`.`D`.`d` > 7000)",
- "after_constant_propagation": "(`test`.`D`.`d` > 7000)",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` > 7000)"
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
+ "after_equality_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_constant_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` > 7000)"
} /* condition_processing */
},
{
@@ -4469,19 +4469,19 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
},
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 4,
@@ -4540,7 +4540,7 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -4559,12 +4559,12 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`D`.`d` > 7000)",
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "(`test`.`D`.`d` > 7000)"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` > 7000)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -4574,13 +4574,13 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
}
] /* steps */
} 0 0
-delete D from C,D where D>7000;
+delete t6 from t5, t6 where d>7000;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-delete D from C,D where D>7000 {
+delete t6 from t5, t6 where d>7000 {
"steps": [
{
- "expanded_query": "/* select#1 */ select NULL AS `NULL` from `test`.`C` join `test`.`D` where (`D` > 7000)"
+ "expanded_query": "/* select#1 */ select NULL AS `NULL` from `test`.`t5` join `test`.`t6` where (`d` > 7000)"
},
{
"join_preparation": {
@@ -4596,10 +4596,10 @@ delete D from C,D where D>7000 {
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` > 7000)",
- "after_equality_propagation": "(`test`.`D`.`d` > 7000)",
- "after_constant_propagation": "(`test`.`D`.`d` > 7000)",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` > 7000)"
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
+ "after_equality_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_constant_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` > 7000)"
} /* condition_processing */
},
{
@@ -4608,19 +4608,19 @@ delete D from C,D where D>7000 {
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "C",
+ "table": "t5",
"records": 1,
"cost": 1,
"cause": "system_table"
},
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 4,
@@ -4672,7 +4672,7 @@ delete D from C,D where D>7000 {
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -4692,12 +4692,12 @@ delete D from C,D where D>7000 {
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`D`.`d` > 7000)",
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "(`test`.`D`.`d` > 7000)"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` > 7000)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -5003,7 +5003,7 @@ select sum(data) into ret from t1 {
}
] /* steps */
} 0 0
-select * from D where d in (select f1() from t2 where s="c")|
+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
@@ -5080,14 +5080,14 @@ set optimizer_trace_offset=-4, optimizer
select @@optimizer_trace_offset, @@optimizer_trace_limit|
@@optimizer_trace_offset @@optimizer_trace_limit
-4 4
-select * from D where d in (select f1() from t2 where s="c")|
+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 D where d in (select f1() from t2 where s="c") {
+select * from t6 where d in (select f1() from t2 where s="c") {
"steps": [
{
- "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`D` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
},
{
"join_preparation": {
@@ -5126,16 +5126,16 @@ select * from D where d in (select f1()
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`D`.`d` = `f1`()))",
- "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ "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": "D.d= `f1`()",
+ "condition": "t6.d= `f1`()",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -5146,7 +5146,7 @@ select * from D where d in (select f1()
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 4,
@@ -5259,7 +5259,7 @@ select * from D where d in (select f1()
"rest_of_plan": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -5295,7 +5295,7 @@ select * from D where d in (select f1()
},
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -5402,17 +5402,17 @@ select * from D where d in (select f1()
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "(`test`.`D`.`d` = `f1`())"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` = `f1`())"
},
{
"database": "test",
"table": "t2",
- "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -5514,7 +5514,7 @@ select sum(data) into ret from t1 {
] /* steps */
} 0 0
set optimizer_trace_offset=2, optimizer_trace_limit=1|
-select * from D where d in (select f1() from t2 where s="c")|
+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
@@ -5537,18 +5537,18 @@ set optimizer_trace_offset=default, opti
create procedure p1(arg char(1))
begin
declare res int;
-select d into res from D where d in (select f1() from t2 where s=arg);
-select d+1 into res from D where d=res+1;
+select d into res from t6 where d in (select f1() from t2 where s=arg);
+select d+1 into res from t6 where d=res+1;
end|
call p1("c")|
Warnings:
Warning 1329 No data - zero rows fetched, selected, or processed
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select d+1 into res from D where d= NAME_CONST('res',NULL)+1 {
+select d+1 into res from t6 where d= NAME_CONST('res',NULL)+1 {
"steps": [
{
- "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`D` where (`d` = (res@1 + 1))"
+ "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`t6` where (`d` = (res@1 + 1))"
},
{
"join_preparation": {
@@ -5564,16 +5564,16 @@ select d+1 into res from D where d= NAME
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
- "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ "original_condition": "(`test`.`t6`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`t6`.`d`)"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= (res@1 + 1)",
+ "condition": "t6.d= (res@1 + 1)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -5584,7 +5584,7 @@ select d+1 into res from D where d= NAME
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 4,
@@ -5605,7 +5605,7 @@ select d+1 into res from D where d= NAME
"chosen": true
} /* best_covering_index_scan */,
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
} /* impossible_condition */,
@@ -5628,7 +5628,7 @@ create trigger trg1 before insert on t2
begin
set new.s=f1();
end|
-insert into t2 select d,100,200 from D where d is not null|
+insert into t2 select d,100,200 from t6 where d is not null|
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
select sum(data) into ret from t1 {
@@ -5726,7 +5726,7 @@ select @trace;
{
"steps": [
{
- "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`D` where (`d` = (res@1 + 1))"
+ "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`t6` where (`d` = (res@1 + 1))"
},
{
"join_preparation": {
@@ -5742,16 +5742,16 @@ select @trace;
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
- "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ "original_condition": "(`test`.`t6`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`t6`.`d`)"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= (res@1 + 1)",
+ "condition": "t6.d= (res@1 + 1)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -5762,7 +5762,7 @@ select @trace;
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 4,
@@ -5783,7 +5783,7 @@ select @trace;
"chosen": true
} /* best_covering_index_scan */,
"impossible_condition": {
- "table": "D",
+ "table": "t6",
"field": "d",
"cause": "comparison_with_null_always_false"
} /* impossible_condition */,
@@ -5818,21 +5818,21 @@ set optimizer_trace="enabled=off";
# want to see all of SELECT below
set optimizer_trace_offset=0, optimizer_trace_limit=100;
set optimizer_trace="enabled=on";
-select d into res from D where d in (select f1() from t2 where s=arg);
+select d into res from t6 where d in (select f1() from t2 where s=arg);
set optimizer_trace="enabled=off"; # and not more
insert into optt select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=default, optimizer_trace_limit=default;
-select d+1 into res from D where d=res+1;
+select d+1 into res from t6 where d=res+1;
end|
call p1("c")|
Warnings:
Warning 1329 No data - zero rows fetched, selected, or processed
select * from optt|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select d into res from D where d in (select f1() from t2 where s= NAME_CONST('arg',_latin1'c' COLLATE 'latin1_swedish_ci')) {
+select d into res from t6 where d in (select f1() from t2 where s= NAME_CONST('arg',_latin1'c' COLLATE 'latin1_swedish_ci')) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`D` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = arg@0))"
+ "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = arg@0))"
},
{
"join_preparation": {
@@ -5871,16 +5871,16 @@ select d into res from D where d in (sel
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
- "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`t6`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= `f1`()",
+ "condition": "t6.d= `f1`()",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -5891,7 +5891,7 @@ select d into res from D where d in (sel
"records_estimation": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"range_analysis": {
"table_scan": {
"records": 4,
@@ -5985,7 +5985,7 @@ select d into res from D where d in (sel
"considered_execution_plans": [
{
"database": "test",
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -6112,17 +6112,17 @@ select d into res from D where d in (sel
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
+ "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
"attached_conditions": [
{
"database": "test",
- "table": "D",
- "attached": "(`test`.`D`.`d` = `f1`())"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` = `f1`())"
},
{
"database": "test",
"table": "t2",
- "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
+ "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -6741,5 +6741,5 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
]
}drop table t1,t2;
-DROP TABLE C,D;
+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-10-21 15:22:32 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2010-10-22 09:05:11 +0000
@@ -3,19 +3,19 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM
set @@session.optimizer_trace="enabled=on";
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-CREATE TABLE C (c int);
-INSERT INTO C VALUES (NULL);
-CREATE TABLE D (d int , KEY (d));
-INSERT INTO D VALUES (NULL),(NULL);
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
+CREATE TABLE t5 (c int);
+INSERT INTO t5 VALUES (NULL);
+CREATE TABLE t6 (d int , KEY (d));
+INSERT INTO t6 VALUES (NULL),(NULL);
+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 D WHERE d = c) AS RESULT FROM C {
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {
"steps": [
{
- "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = `c`)) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -37,7 +37,7 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
],
"records_estimation": [
]
@@ -59,16 +59,16 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = NULL)",
- "after_equality_propagation": "multiple equal(NULL, `test`.`D`.`d`)",
- "after_constant_propagation": "multiple equal(NULL, `test`.`D`.`d`)",
- "after_trivial_conditions_removal": "multiple equal(NULL, `test`.`D`.`d`)"
+ "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": "D.d= NULL",
+ "condition": "t6.d= NULL",
"null_rejecting": true
}
]
@@ -78,7 +78,7 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
],
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
}
@@ -96,29 +96,29 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-45.3699
+45.1596
set optimizer_trace="one_line=on";
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
+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 D WHERE d = c) AS RESULT FROM C {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = `c`)) AS `RESULT` from `test`.`C`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["C"],"records_estimation": []},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`D`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`D`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "D.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"table": "D","more_r
ange_optimizer_trace": {"TODO?": "yes!"}}]}],"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": [{"constant_tables": ["t5"],"records_estimation": []},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"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": [{"table": "
t6","more_range_optimizer_trace": {"TODO?": "yes!"}}]}],"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.4238
+6.3688
set optimizer_trace="one_line=off,end_marker=on";
-EXPLAIN SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+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
-1 PRIMARY C system NULL NULL NULL NULL 1
+1 PRIMARY t5 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-EXPLAIN SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C {
+EXPLAIN SELECT (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`.`D` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -140,7 +140,7 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
] /* records_estimation */
@@ -162,16 +162,16 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -181,7 +181,7 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -196,15 +196,15 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
}
] /* steps */
} 0 0
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (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 D WHERE d = ifnull(c,null)) AS RESULT FROM C {
+SELECT (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`.`D` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -226,7 +226,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
] /* records_estimation */
@@ -248,16 +248,16 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -267,7 +267,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -283,7 +283,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* steps */
} 0 0
set @@session.optimizer_trace="enabled=off";
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
@@ -293,15 +293,15 @@ set @@session.optimizer_trace="enabled=o
] /* steps */
} 0 0
set @@session.optimizer_trace="enabled=on";
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
+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
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C {
+SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 {
"steps": [
{
- "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = ifnull(`c`,NULL)) union /* select#3 */ select 2 from `test`.`D` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`C`"
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = ifnull(`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`d` = ifnull(`c`,NULL))) AS `RESULT` from `test`.`t5`"
},
{
"join_preparation": {
@@ -330,7 +330,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"steps": [
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
] /* records_estimation */
@@ -352,16 +352,16 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -371,7 +371,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -391,16 +391,16 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_equality_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_constant_propagation": "(`test`.`D`.`d` = ifnull(NULL,NULL))",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` = ifnull(NULL,NULL))"
+ "original_condition": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_equality_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_constant_propagation": "(`test`.`t6`.`d` = ifnull(NULL,NULL))",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` = ifnull(NULL,NULL))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= ifnull(NULL,NULL)",
+ "condition": "t6.d= ifnull(NULL,NULL)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -410,7 +410,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -483,14 +483,14 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
}
] /* steps */
} 0 0
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null));
+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 * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null)) {
+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)) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `test`.`C`.`c` AS `c` from `test`.`C` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`d` = ifnull(`c`,NULL)) and (`<no matter>`.`<left expr>` = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`d` = ifnull(`c`,NULL)) and (`<no matter>`.`<left expr>` = 2))))"
+ "expanded_query": "/* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`d` = ifnull(`c`,NULL)) and (`<no matter>`.`<left expr>` = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`d` = ifnull(`c`,NULL)) and (`<no matter>`.`<left expr>` = 2))))"
},
{
"join_preparation": {
@@ -552,10 +552,10 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))",
- "after_equality_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))",
- "after_constant_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))",
- "after_trivial_conditions_removal": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`D` where ((`test`.`D`.`d` = ifnull(`test`.`C`.`c`,NULL)) and (<cache>(5) = 2))))"
+ "original_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "after_equality_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "after_constant_propagation": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))",
+ "after_trivial_conditions_removal": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
} /* condition_processing */
},
{
@@ -564,7 +564,7 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
] /* records_estimation */
@@ -576,9 +576,9 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
- "after_equality_propagation": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
- "after_constant_propagation": "((`test`.`D`.`d` = NULL) and (<cache>(5) = 1))",
+ "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
+ "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 1))",
+ "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 1))",
"after_trivial_conditions_removal": null
} /* condition_processing */
}
@@ -595,9 +595,9 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
- "after_equality_propagation": "((`test`.`D`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
- "after_constant_propagation": "((`test`.`D`.`d` = NULL) and (<cache>(5) = 2))",
+ "original_condition": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
+ "after_equality_propagation": "((`test`.`t6`.`d` = ifnull(NULL,NULL)) and (<cache>(5) = 2))",
+ "after_constant_propagation": "((`test`.`t6`.`d` = NULL) and (<cache>(5) = 2))",
"after_trivial_conditions_removal": null
} /* condition_processing */
}
@@ -694,12 +694,12 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-8138
+8173
set optimizer_trace_max_mem_size=8400;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
0
-SELECT * FROM C WHERE 5 IN (SELECT 1 FROM D WHERE d = ifnull(c,null) UNION SELECT 2 FROM D WHERE d = ifnull(c,null));
+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)
@@ -711,7 +711,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8138 1 1
+8173 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)
@@ -735,16 +735,16 @@ select * from information_schema.OPTIMIZ
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
381 0
set optimizer_trace_max_mem_size=default;
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null);
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY C system NULL NULL NULL NULL 1
-1 PRIMARY D ref d d 5 const 1 Using where; Using index; FirstMatch(C)
+1 PRIMARY t5 system NULL NULL NULL NULL 1
+1 PRIMARY t6 ref d d 5 const 1 Using where; Using index; FirstMatch(t5)
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null) {
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`C` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`D` where isnull(`d`))"
+ "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`t5` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`t6` where isnull(`d`))"
},
{
"join_preparation": {
@@ -783,27 +783,27 @@ explain SELECT c FROM C where c+1 in (se
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(1 and isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))",
- "after_equality_propagation": "(1 and isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))",
- "after_constant_propagation": "(1 and isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))",
- "after_trivial_conditions_removal": "(isnull(`test`.`D`.`d`) and ((`test`.`C`.`c` + 1) = (`test`.`D`.`d` + 1)))"
+ "original_condition": "(1 and isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))",
+ "after_equality_propagation": "(1 and isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))",
+ "after_constant_propagation": "(1 and isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))",
+ "after_trivial_conditions_removal": "(isnull(`test`.`t6`.`d`) and ((`test`.`t5`.`c` + 1) = (`test`.`t6`.`d` + 1)))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= NULL",
+ "condition": "t6.d= NULL",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -820,7 +820,7 @@ explain SELECT c FROM C where c+1 in (se
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -870,7 +870,7 @@ explain SELECT c FROM C where c+1 in (se
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -923,11 +923,11 @@ explain SELECT c FROM C where c+1 in (se
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(isnull(`test`.`D`.`d`) and ((NULL + 1) = (`test`.`D`.`d` + 1)))",
+ "original_condition": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))",
"attached_conditions": [
{
- "table": "D",
- "attached": "(isnull(`test`.`D`.`d`) and ((NULL + 1) = (`test`.`D`.`d` + 1)))"
+ "table": "t6",
+ "attached": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -938,16 +938,16 @@ explain SELECT c FROM C where c+1 in (se
] /* steps */
} 0 0
set optimizer_switch="semijoin=off";
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null);
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY C system NULL NULL NULL NULL 1
-2 SUBQUERY D ref d d 5 const 1 Using where; Using index
+1 PRIMARY t5 system NULL NULL NULL NULL 1
+2 SUBQUERY t6 ref d d 5 const 1 Using where; Using index
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-explain SELECT c FROM C where c+1 in (select d+1 from D where d is null) {
+explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`C` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`D` where isnull(`d`))"
+ "expanded_query": "/* select#1 */ select `c` AS `c` from `test`.`t5` where (`c` + 1) in (/* select#2 */ select (`d` + 1) from `test`.`t6` where isnull(`d`))"
},
{
"join_preparation": {
@@ -986,10 +986,10 @@ explain SELECT c FROM C where c+1 in (se
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
- "after_equality_propagation": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
- "after_constant_propagation": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
- "after_trivial_conditions_removal": "<in_optimizer>((`test`.`C`.`c` + 1),(`test`.`C`.`c` + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))"
+ "original_condition": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "after_equality_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "after_constant_propagation": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
+ "after_trivial_conditions_removal": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))"
} /* condition_processing */
},
{
@@ -998,14 +998,14 @@ explain SELECT c FROM C where c+1 in (se
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
] /* records_estimation */
},
{
"attaching_conditions_to_tables": {
- "original_condition": "<in_optimizer>((NULL + 1),(NULL + 1) in (/* select#2 */ select (`test`.`D`.`d` + 1) from `test`.`D` where isnull(`test`.`D`.`d`)))",
+ "original_condition": "<in_optimizer>((NULL + 1),(NULL + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))",
"attached_conditions": [
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -1028,16 +1028,16 @@ explain SELECT c FROM C where c+1 in (se
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "isnull(`test`.`D`.`d`)",
- "after_equality_propagation": "isnull(`test`.`D`.`d`)",
- "after_constant_propagation": "isnull(`test`.`D`.`d`)",
- "after_trivial_conditions_removal": "isnull(`test`.`D`.`d`)"
+ "original_condition": "isnull(`test`.`t6`.`d`)",
+ "after_equality_propagation": "isnull(`test`.`t6`.`d`)",
+ "after_constant_propagation": "isnull(`test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "isnull(`test`.`t6`.`d`)"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= NULL",
+ "condition": "t6.d= NULL",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -1047,7 +1047,7 @@ explain SELECT c FROM C where c+1 in (se
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -1057,7 +1057,7 @@ explain SELECT c FROM C where c+1 in (se
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -1083,11 +1083,11 @@ explain SELECT c FROM C where c+1 in (se
},
{
"attaching_conditions_to_tables": {
- "original_condition": "isnull(`test`.`D`.`d`)",
+ "original_condition": "isnull(`test`.`t6`.`d`)",
"attached_conditions": [
{
- "table": "D",
- "attached": "isnull(`test`.`D`.`d`)"
+ "table": "t6",
+ "attached": "isnull(`test`.`t6`.`d`)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -3689,43 +3689,43 @@ trace
] /* steps */
}
drop table t1,t2;
-update D set d=5 where D is NULL;
+update t6 set d=5 where d is NULL;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-update D set d=5 where D is NULL {
+update t6 set d=5 where d is NULL {
"steps": [
{
- "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`D` where isnull(`D`)"
+ "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where isnull(`d`)"
}
] /* steps */
} 0 0
-delete from D where d=5;
+delete from t6 where d=5;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-delete from D where d=5 {
+delete from t6 where d=5 {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`d` = 5)"
}
] /* steps */
} 0 0
-insert into D values(6),(7),(8);
+insert into t6 values(6),(7),(8);
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-insert into D values(6),(7),(8) {
+insert into t6 values(6),(7),(8) {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual"
}
] /* steps */
} 0 0
-insert into D select * from D where D>7;
+insert into t6 select * from t6 where d>7;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-insert into D select * from D where D>7 {
+insert into t6 select * from t6 where d>7 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `test`.`D`.`d` AS `d` from `test`.`D` where (`D` > 7)"
+ "expanded_query": "/* select#1 */ select `test`.`t6`.`d` AS `d` from `test`.`t6` where (`d` > 7)"
},
{
"join_preparation": {
@@ -3741,10 +3741,10 @@ insert into D select * from D where D>7
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` > 7)",
- "after_equality_propagation": "(`test`.`D`.`d` > 7)",
- "after_constant_propagation": "(`test`.`D`.`d` > 7)",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` > 7)"
+ "original_condition": "(`test`.`t6`.`d` > 7)",
+ "after_equality_propagation": "(`test`.`t6`.`d` > 7)",
+ "after_constant_propagation": "(`test`.`t6`.`d` > 7)",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` > 7)"
} /* condition_processing */
},
{
@@ -3756,7 +3756,7 @@ insert into D select * from D where D>7
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -3766,7 +3766,7 @@ insert into D select * from D where D>7
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -3785,11 +3785,11 @@ insert into D select * from D where D>7
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`D`.`d` > 7)",
+ "original_condition": "(`test`.`t6`.`d` > 7)",
"attached_conditions": [
{
- "table": "D",
- "attached": "(`test`.`D`.`d` > 7)"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` > 7)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -3799,13 +3799,13 @@ insert into D select * from D where D>7
}
] /* steps */
} 0 0
-update C,D set D.d=D.d+C.c+4-C.c-4 where D>7000;
+update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-update C,D set D.d=D.d+C.c+4-C.c-4 where D>7000 {
+update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-4 where d>7000 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `D`.`d` AS `d` from `test`.`C` join `test`.`D` where (`D` > 7000)"
+ "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `test`.`t5` join `test`.`t6` where (`d` > 7000)"
},
{
"join_preparation": {
@@ -3821,10 +3821,10 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` > 7000)",
- "after_equality_propagation": "(`test`.`D`.`d` > 7000)",
- "after_constant_propagation": "(`test`.`D`.`d` > 7000)",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` > 7000)"
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
+ "after_equality_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_constant_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` > 7000)"
} /* condition_processing */
},
{
@@ -3833,11 +3833,11 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -3847,7 +3847,7 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -3866,11 +3866,11 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`D`.`d` > 7000)",
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions": [
{
- "table": "D",
- "attached": "(`test`.`D`.`d` > 7000)"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` > 7000)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -3880,13 +3880,13 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
}
] /* steps */
} 0 0
-delete D from C,D where D>7000;
+delete t6 from t5, t6 where d>7000;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-delete D from C,D where D>7000 {
+delete t6 from t5, t6 where d>7000 {
"steps": [
{
- "expanded_query": "/* select#1 */ select NULL AS `NULL` from `test`.`C` join `test`.`D` where (`D` > 7000)"
+ "expanded_query": "/* select#1 */ select NULL AS `NULL` from `test`.`t5` join `test`.`t6` where (`d` > 7000)"
},
{
"join_preparation": {
@@ -3902,10 +3902,10 @@ delete D from C,D where D>7000 {
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` > 7000)",
- "after_equality_propagation": "(`test`.`D`.`d` > 7000)",
- "after_constant_propagation": "(`test`.`D`.`d` > 7000)",
- "after_trivial_conditions_removal": "(`test`.`D`.`d` > 7000)"
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
+ "after_equality_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_constant_propagation": "(`test`.`t6`.`d` > 7000)",
+ "after_trivial_conditions_removal": "(`test`.`t6`.`d` > 7000)"
} /* condition_processing */
},
{
@@ -3914,11 +3914,11 @@ delete D from C,D where D>7000 {
},
{
"constant_tables": [
- "C"
+ "t5"
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -3928,7 +3928,7 @@ delete D from C,D where D>7000 {
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -3947,11 +3947,11 @@ delete D from C,D where D>7000 {
},
{
"attaching_conditions_to_tables": {
- "original_condition": "(`test`.`D`.`d` > 7000)",
+ "original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions": [
{
- "table": "D",
- "attached": "(`test`.`D`.`d` > 7000)"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` > 7000)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -4249,7 +4249,7 @@ select sum(data) into ret from t1 {
}
] /* steps */
} 0 0
-select * from D where d in (select f1() from t2 where s="c")|
+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
@@ -4322,14 +4322,14 @@ set optimizer_trace_offset=-4, optimizer
select @@optimizer_trace_offset, @@optimizer_trace_limit|
@@optimizer_trace_offset @@optimizer_trace_limit
-4 4
-select * from D where d in (select f1() from t2 where s="c")|
+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 D where d in (select f1() from t2 where s="c") {
+select * from t6 where d in (select f1() from t2 where s="c") {
"steps": [
{
- "expanded_query": "/* select#1 */ select `test`.`D`.`d` AS `d` from `test`.`D` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+ "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": {
@@ -4368,16 +4368,16 @@ select * from D where d in (select f1()
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`D`.`d` = `f1`()))",
- "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ "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": "D.d= `f1`()",
+ "condition": "t6.d= `f1`()",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -4387,7 +4387,7 @@ select * from D where d in (select f1()
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -4453,7 +4453,7 @@ select * from D where d in (select f1()
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -4487,7 +4487,7 @@ select * from D where d in (select f1()
] /* rest_of_plan */
},
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -4588,15 +4588,15 @@ select * from D where d in (select f1()
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
"attached_conditions": [
{
- "table": "D",
- "attached": "(`test`.`D`.`d` = `f1`())"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` = `f1`())"
},
{
"table": "t2",
- "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -4686,7 +4686,7 @@ select sum(data) into ret from t1 {
] /* steps */
} 0 0
set optimizer_trace_offset=2, optimizer_trace_limit=1|
-select * from D where d in (select f1() from t2 where s="c")|
+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
@@ -4701,18 +4701,18 @@ set optimizer_trace_offset=default, opti
create procedure p1(arg char(1))
begin
declare res int;
-select d into res from D where d in (select f1() from t2 where s=arg);
-select d+1 into res from D where d=res+1;
+select d into res from t6 where d in (select f1() from t2 where s=arg);
+select d+1 into res from t6 where d=res+1;
end|
call p1("c")|
Warnings:
Warning 1329 No data - zero rows fetched, selected, or processed
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select d+1 into res from D where d= NAME_CONST('res',NULL)+1 {
+select d+1 into res from t6 where d= NAME_CONST('res',NULL)+1 {
"steps": [
{
- "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`D` where (`d` = (res@1 + 1))"
+ "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`t6` where (`d` = (res@1 + 1))"
},
{
"join_preparation": {
@@ -4728,16 +4728,16 @@ select d+1 into res from D where d= NAME
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
- "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ "original_condition": "(`test`.`t6`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`t6`.`d`)"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= (res@1 + 1)",
+ "condition": "t6.d= (res@1 + 1)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -4747,7 +4747,7 @@ select d+1 into res from D where d= NAME
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -4766,7 +4766,7 @@ create trigger trg1 before insert on t2
begin
set new.s=f1();
end|
-insert into t2 select d,100,200 from D where d is not null|
+insert into t2 select d,100,200 from t6 where d is not null|
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
select sum(data) into ret from t1 {
@@ -4860,7 +4860,7 @@ select @trace;
{
"steps": [
{
- "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`D` where (`d` = (res@1 + 1))"
+ "expanded_query": "/* select#1 */ select (`d` + 1) AS `d+1` from `test`.`t6` where (`d` = (res@1 + 1))"
},
{
"join_preparation": {
@@ -4876,16 +4876,16 @@ select @trace;
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
- "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
- "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ "original_condition": "(`test`.`t6`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`t6`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`t6`.`d`)"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= (res@1 + 1)",
+ "condition": "t6.d= (res@1 + 1)",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -4895,7 +4895,7 @@ select @trace;
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -4926,21 +4926,21 @@ set optimizer_trace="enabled=off";
# want to see all of SELECT below
set optimizer_trace_offset=0, optimizer_trace_limit=100;
set optimizer_trace="enabled=on";
-select d into res from D where d in (select f1() from t2 where s=arg);
+select d into res from t6 where d in (select f1() from t2 where s=arg);
set optimizer_trace="enabled=off"; # and not more
insert into optt select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=default, optimizer_trace_limit=default;
-select d+1 into res from D where d=res+1;
+select d+1 into res from t6 where d=res+1;
end|
call p1("c")|
Warnings:
Warning 1329 No data - zero rows fetched, selected, or processed
select * from optt|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select d into res from D where d in (select f1() from t2 where s= NAME_CONST('arg',_latin1'c' COLLATE 'latin1_swedish_ci')) {
+select d into res from t6 where d in (select f1() from t2 where s= NAME_CONST('arg',_latin1'c' COLLATE 'latin1_swedish_ci')) {
"steps": [
{
- "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`D` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = arg@0))"
+ "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = arg@0))"
},
{
"join_preparation": {
@@ -4979,16 +4979,16 @@ select d into res from D where d in (sel
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
- "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
- "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`t6`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
{
- "condition": "D.d= `f1`()",
+ "condition": "t6.d= `f1`()",
"null_rejecting": false
}
] /* ref-optimizer-key-uses */
@@ -4998,7 +4998,7 @@ select d into res from D where d in (sel
] /* constant_tables */,
"records_estimation": [
{
- "table": "D",
+ "table": "t6",
"more_range_optimizer_trace": {
"TODO?": "yes!"
} /* more_range_optimizer_trace */
@@ -5047,7 +5047,7 @@ select d into res from D where d in (sel
{
"considered_execution_plans": [
{
- "table": "D",
+ "table": "t6",
"best_access_path": {
"considered_access_paths": [
{
@@ -5166,15 +5166,15 @@ select d into res from D where d in (sel
},
{
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
+ "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
"attached_conditions": [
{
- "table": "D",
- "attached": "(`test`.`D`.`d` = `f1`())"
+ "table": "t6",
+ "attached": "(`test`.`t6`.`d` = `f1`())"
},
{
"table": "t2",
- "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
+ "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
@@ -5737,5 +5737,5 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
]
}drop table t1,t2;
-DROP TABLE C,D;
+DROP TABLE t5,t6;
set optimizer_trace=default;
=== modified file 'mysql-test/r/shm.result'
--- a/mysql-test/r/shm.result 2010-06-11 08:57:45 +0000
+++ b/mysql-test/r/shm.result 2010-10-22 09:05:11 +0000
@@ -1505,7 +1505,7 @@ explain extended select count(*),min(fld
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
Warnings:
-Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
+Note 1003 /* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
=== modified file 'mysql-test/r/ssl.result'
--- a/mysql-test/r/ssl.result 2010-06-09 14:16:33 +0000
+++ b/mysql-test/r/ssl.result 2010-10-22 09:05:11 +0000
@@ -1508,7 +1508,7 @@ explain extended select count(*),min(fld
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
Warnings:
-Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
+Note 1003 /* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
=== modified file 'mysql-test/r/ssl_compress.result'
--- a/mysql-test/r/ssl_compress.result 2010-06-09 14:16:33 +0000
+++ b/mysql-test/r/ssl_compress.result 2010-10-22 09:05:11 +0000
@@ -1511,7 +1511,7 @@ explain extended select count(*),min(fld
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
Warnings:
-Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
+Note 1003 /* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_blackhole.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result 2010-08-20 02:59:58 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result 2010-10-22 09:05:11 +0000
@@ -74,7 +74,7 @@ explain extended select * from t1 where
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
a b
Full-text indexes are called collections
=== modified file 'mysql-test/suite/funcs_1/r/is_columns_is.result'
--- a/mysql-test/suite/funcs_1/r/is_columns_is.result 2010-07-16 15:04:39 +0000
+++ b/mysql-test/suite/funcs_1/r/is_columns_is.result 2010-10-22 09:05:11 +0000
@@ -125,6 +125,10 @@ def information_schema KEY_COLUMN_USAGE
def information_schema KEY_COLUMN_USAGE TABLE_CATALOG 4 NO varchar 512 1536 NULL NULL utf8 utf8_general_ci varchar(512) select
def information_schema KEY_COLUMN_USAGE TABLE_NAME 6 NO varchar 64 192 NULL NULL utf8 utf8_general_ci varchar(64) select
def information_schema KEY_COLUMN_USAGE TABLE_SCHEMA 5 NO varchar 64 192 NULL NULL utf8 utf8_general_ci varchar(64) select
+def information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE 3 0 NO int NULL NULL 10 0 NULL NULL int(20) select
+def information_schema OPTIMIZER_TRACE OS_MALLOC_ERROR 4 0 NO tinyint NULL NULL 3 0 NULL NULL tinyint(1) select
+def information_schema OPTIMIZER_TRACE QUERY 1 NULL NO longtext 4294967295 4294967295 NULL NULL utf8 utf8_general_ci longtext select
+def information_schema OPTIMIZER_TRACE TRACE 2 NULL NO longtext 4294967295 4294967295 NULL NULL utf8 utf8_general_ci longtext select
def information_schema PARAMETERS CHARACTER_MAXIMUM_LENGTH 8 NULL YES int NULL NULL 10 0 NULL NULL int(21) select
def information_schema PARAMETERS CHARACTER_OCTET_LENGTH 9 NULL YES int NULL NULL 10 0 NULL NULL int(21) select
def information_schema PARAMETERS CHARACTER_SET_NAME 12 NULL YES varchar 64 192 NULL NULL utf8 utf8_general_ci varchar(64) select
@@ -373,6 +377,7 @@ COL_CML DATA_TYPE CHARACTER_SET_NAME COL
NULL bigint NULL NULL
NULL datetime NULL NULL
NULL int NULL NULL
+NULL tinyint NULL NULL
--> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values
--> are 0, which is intended behavior, and the result of 0 / 0 IS NULL
SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
@@ -512,6 +517,10 @@ NULL information_schema KEY_COLUMN_USAGE
3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_COLUMN_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
+1.0000 information_schema OPTIMIZER_TRACE QUERY longtext 4294967295 4294967295 utf8 utf8_general_ci longtext
+1.0000 information_schema OPTIMIZER_TRACE TRACE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext
+NULL information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE int NULL NULL NULL NULL int(20)
+NULL information_schema OPTIMIZER_TRACE OS_MALLOC_ERROR tinyint NULL NULL NULL NULL tinyint(1)
3.0000 information_schema PARAMETERS SPECIFIC_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512)
3.0000 information_schema PARAMETERS SPECIFIC_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema PARAMETERS SPECIFIC_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
=== modified file 'mysql-test/suite/funcs_1/r/is_tables_is.result'
--- a/mysql-test/suite/funcs_1/r/is_tables_is.result 2010-01-15 11:42:15 +0000
+++ b/mysql-test/suite/funcs_1/r/is_tables_is.result 2010-10-22 09:05:11 +0000
@@ -268,6 +268,29 @@ user_comment
Separator -----------------------------------------------------
TABLE_CATALOG def
TABLE_SCHEMA information_schema
+TABLE_NAME OPTIMIZER_TRACE
+TABLE_TYPE SYSTEM VIEW
+ENGINE MyISAM
+VERSION 10
+ROW_FORMAT Dynamic
+TABLE_ROWS #TBLR#
+AVG_ROW_LENGTH #ARL#
+DATA_LENGTH #DL#
+MAX_DATA_LENGTH #MDL#
+INDEX_LENGTH #IL#
+DATA_FREE #DF#
+AUTO_INCREMENT NULL
+CREATE_TIME #CRT#
+UPDATE_TIME #UT#
+CHECK_TIME #CT#
+TABLE_COLLATION utf8_general_ci
+CHECKSUM NULL
+CREATE_OPTIONS #CO#
+TABLE_COMMENT #TC#
+user_comment
+Separator -----------------------------------------------------
+TABLE_CATALOG def
+TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
TABLE_TYPE SYSTEM VIEW
ENGINE MyISAM
@@ -952,6 +975,29 @@ user_comment
Separator -----------------------------------------------------
TABLE_CATALOG def
TABLE_SCHEMA information_schema
+TABLE_NAME OPTIMIZER_TRACE
+TABLE_TYPE SYSTEM VIEW
+ENGINE MyISAM
+VERSION 10
+ROW_FORMAT Dynamic
+TABLE_ROWS #TBLR#
+AVG_ROW_LENGTH #ARL#
+DATA_LENGTH #DL#
+MAX_DATA_LENGTH #MDL#
+INDEX_LENGTH #IL#
+DATA_FREE #DF#
+AUTO_INCREMENT NULL
+CREATE_TIME #CRT#
+UPDATE_TIME #UT#
+CHECK_TIME #CT#
+TABLE_COLLATION utf8_general_ci
+CHECKSUM NULL
+CREATE_OPTIONS #CO#
+TABLE_COMMENT #TC#
+user_comment
+Separator -----------------------------------------------------
+TABLE_CATALOG def
+TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
TABLE_TYPE SYSTEM VIEW
ENGINE MyISAM
=== removed file 'mysql-test/t/bug42620.test'
--- a/mysql-test/t/bug42620.test 2010-09-18 16:25:43 +0000
+++ b/mysql-test/t/bug42620.test 1970-01-01 00:00:00 +0000
@@ -1,46 +0,0 @@
---source include/have_optimizer_trace.inc
-
-# Tables created with
-#perl datagen.pl --generate --ot-rows=10000 --fanout=32 --load-data --database=test --user=root --socket=/tmp/mysql.sock
-
-#! don't leave this in the test
-let $MYSQLD_DATADIR= `select @@datadir`;
-system /bin/cp /m/42620/test/* $MYSQLD_DATADIR/test;
-
-if (0)
-{
-select count(*) from ot; # 10000
-select count(distinct ot.expr_key) from ot; # all are distinct
-select count(*) from ot where ot.expr_key<100000000; # 10%
-select count(*) from it; # 320000
-select count(distinct it.expr_nokey) from it; # all are distinct
-select count(*) from it where it.expr_nokey<100000000; # 10%
-}
-
-set @@optimizer_trace="enabled=on,end_marker=on";
---echo firstmatch (default, slow)
-explain extended select /*firstmatch*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-select * from information_schema.OPTIMIZER_TRACE;
-
-set optimizer_switch="default,firstmatch=off";
---echo SJ-mat (better)
-explain extended select /*SJ-mat*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-select * from information_schema.OPTIMIZER_TRACE;
-
-set optimizer_switch="default,materialization=off,firstmatch=off";
---echo Duplicate Weed-out (best)
-explain extended select /*weedout*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-select * from information_schema.OPTIMIZER_TRACE;
-
-set optimizer_switch="default,materialization=on,semijoin=off";
---echo Subq mater (like SJ-mat)
-explain extended select /*subq-mater*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-select * from information_schema.OPTIMIZER_TRACE;
-
-set optimizer_switch="default,materialization=off,semijoin=off";
---echo 5.1 plan (slow); "*DEPENDENT* SUBQUERY" because IN->EXISTS
---echo makes it correlated (injects ot.expr_key inside subquery)
-explain extended select /*5.1, IN->EXISTS*/ count(expr_key) from ot where expr_key in ( SELECT expr_nokey FROM it ) and ot.expr_key<100000000;
-select * from information_schema.OPTIMIZER_TRACE;
-drop table it, ot;
-
No bundle (reason: revision is a merge).
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3230) | Jorgen Loland | 22 Oct |
