#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem.bichot@stripped
3305 Guilhem Bichot 2011-05-13
fix for BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT:
we had two consecutive calls to SQL_SELECT::test_quick_select(),
thus two keys:
"range_analysis": ...,
"range_analysis": ...
which is not valid. Fixed by wrapping the second range_analysis in an
object.
@ mysql-test/include/optimizer_trace2.inc
1) test was wrongly restoring max trace size to default instead of 1MB
2) test for bug
@ mysql-test/r/optimizer_trace2_no_prot.result
We see the "without_ON_clause".
@ sql/sql_derived.cc
I did a bit of debugging after the previous revision...
@ sql/sql_select.cc
When we re-do range analysis without the ON clause, we say it
by creating a surrounding object. This avoids bad JSON.
modified:
mysql-test/include/optimizer_trace2.inc
mysql-test/r/optimizer_trace2_no_prot.result
mysql-test/r/optimizer_trace2_ps_prot.result
sql/sql_derived.cc
sql/sql_select.cc
=== modified file 'mysql-test/include/optimizer_trace2.inc'
--- a/mysql-test/include/optimizer_trace2.inc 2011-05-13 09:09:34 +0000
+++ b/mysql-test/include/optimizer_trace2.inc 2011-05-13 14:02:31 +0000
@@ -2,6 +2,9 @@
--source include/have_optimizer_trace.inc
+let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
+eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
+
set optimizer_trace="enabled=on,end_marker=on";
--echo # check that if a sub-statement should not be traced,
@@ -225,6 +228,7 @@ begin
end|
delimiter ;|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
+set @old_max=@@optimizer_trace_max_mem_size;
set optimizer_trace_max_mem_size=20000;
call p1();
# SET @a=(SELECT) is not traced because part of SET
@@ -232,7 +236,7 @@ call p1();
select * from information_schema.OPTIMIZER_TRACE;
select * from t1;
select @a,@b;
-set optimizer_trace_max_mem_size=default;
+set optimizer_trace_max_mem_size=@old_max;
drop procedure p1;
drop table t1;
@@ -367,3 +371,45 @@ FROM (
DROP TABLE t1;
DROP TABLE t2;
+
+--echo
+--echo BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT
+--echo
+
+CREATE TABLE t1 (
+ col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+ col_int_key INT,
+ KEY col_int_key (col_int_key)
+);
+
+CREATE TABLE t2 (
+ col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+ col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
+ col_int_key INT,
+ KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
+ KEY col_int_key (col_int_key)
+);
+
+INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
+
+CREATE TABLE t3 (
+ col_int_key INT,
+ col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
+ col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+ KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
+ KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
+);
+
+INSERT INTO t3 VALUES (0,'s','it');
+INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
+
+SELECT table2.col_int_key
+FROM t3 AS table1
+ LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
+ LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
+table3.col_varchar_10_utf8_key
+;
+
+select * from information_schema.optimizer_trace;
+
+DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/optimizer_trace2_no_prot.result'
--- a/mysql-test/r/optimizer_trace2_no_prot.result 2011-05-13 09:09:34 +0000
+++ b/mysql-test/r/optimizer_trace2_no_prot.result 2011-05-13 14:02:31 +0000
@@ -1,3 +1,4 @@
+set optimizer_trace_max_mem_size=1048576;
set optimizer_trace="enabled=on,end_marker=on";
# check that if a sub-statement should not be traced,
# it is not traced even if inside a traced top statement
@@ -1262,6 +1263,7 @@ else set @b=3;
end case;
end|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
+set @old_max=@@optimizer_trace_max_mem_size;
set optimizer_trace_max_mem_size=20000;
call p1();
select * from information_schema.OPTIMIZER_TRACE;
@@ -1518,7 +1520,7 @@ a
select @a,@b;
@a @b
3 2
-set optimizer_trace_max_mem_size=default;
+set optimizer_trace_max_mem_size=@old_max;
drop procedure p1;
drop table t1;
@@ -2006,3 +2008,246 @@ NULL
6
DROP TABLE t1;
DROP TABLE t2;
+
+BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT
+
+CREATE TABLE t1 (
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+col_int_key INT,
+KEY col_int_key (col_int_key)
+);
+CREATE TABLE t2 (
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
+col_int_key INT,
+KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
+CREATE TABLE t3 (
+col_int_key INT,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
+KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
+);
+INSERT INTO t3 VALUES (0,'s','it');
+INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
+SELECT table2.col_int_key
+FROM t3 AS table1
+LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
+LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
+table3.col_varchar_10_utf8_key
+;
+col_int_key
+NULL
+NULL
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT table2.col_int_key
+FROM t3 AS table1
+LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
+LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
+table3.col_varchar_10_utf8_key {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`table2`.`col_int_key` AS `col_int_key` from ((`test`.`t3` `table1` left join `test`.`t1` `table2` on((`test`.`table1`.`col_int_key` < `test`.`table2`.`col_int_key`))) left join `test`.`t2` `table3` on((convert(`test`.`table2`.`col_varchar_10_latin1_key` using utf8) >= `test`.`table3`.`col_varchar_10_utf8_key`)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "table1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "table2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "table3",
+ "table_scan": {
+ "records": 1,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "table1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0107,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.4107,
+ "records_for_plan": 2,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "table3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 1,
+ "cost": 4.0098,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 6.8205,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "1",
+ "attached_conditions_computation": [
+ {
+ "database": "test",
+ "table": "table3",
+ "rechecking_index_usage": {
+ "range_analysis": {
+ "table_scan": {
+ "records": 1,
+ "cost": 4.3049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "col_varchar_10_utf8_key",
+ "usable": true,
+ "key_parts": [
+ "col_varchar_10_utf8_key"
+ ] /* key_parts */
+ },
+ {
+ "index": "col_int_key",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "col_varchar_10_utf8_key",
+ "cost": 1.2,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "without_ON_clause": {
+ "range_analysis": {
+ "table_scan": {
+ "records": 1,
+ "cost": 4.3049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "col_varchar_10_utf8_key",
+ "usable": true,
+ "key_parts": [
+ "col_varchar_10_utf8_key"
+ ] /* key_parts */
+ },
+ {
+ "index": "col_int_key",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "col_varchar_10_utf8_key",
+ "cost": 1.2,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ } /* without_ON_clause */
+ } /* rechecking_index_usage */
+ }
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "table1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "table3",
+ "attached": "trigcond_if(is_not_null_compl(table3), (convert(NULL using utf8) >= `test`.`table3`.`col_varchar_10_utf8_key`), true)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "table1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "table3",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/optimizer_trace2_ps_prot.result'
--- a/mysql-test/r/optimizer_trace2_ps_prot.result 2011-05-13 09:09:34 +0000
+++ b/mysql-test/r/optimizer_trace2_ps_prot.result 2011-05-13 14:02:31 +0000
@@ -1,3 +1,4 @@
+set optimizer_trace_max_mem_size=1048576;
set optimizer_trace="enabled=on,end_marker=on";
# check that if a sub-statement should not be traced,
# it is not traced even if inside a traced top statement
@@ -1282,6 +1283,7 @@ else set @b=3;
end case;
end|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
+set @old_max=@@optimizer_trace_max_mem_size;
set optimizer_trace_max_mem_size=20000;
call p1();
select * from information_schema.OPTIMIZER_TRACE;
@@ -1538,7 +1540,7 @@ a
select @a,@b;
@a @b
3 2
-set optimizer_trace_max_mem_size=default;
+set optimizer_trace_max_mem_size=@old_max;
drop procedure p1;
drop table t1;
@@ -2026,3 +2028,246 @@ NULL
6
DROP TABLE t1;
DROP TABLE t2;
+
+BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT
+
+CREATE TABLE t1 (
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+col_int_key INT,
+KEY col_int_key (col_int_key)
+);
+CREATE TABLE t2 (
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
+col_int_key INT,
+KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
+CREATE TABLE t3 (
+col_int_key INT,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
+KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
+);
+INSERT INTO t3 VALUES (0,'s','it');
+INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
+SELECT table2.col_int_key
+FROM t3 AS table1
+LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
+LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
+table3.col_varchar_10_utf8_key
+;
+col_int_key
+NULL
+NULL
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT table2.col_int_key
+FROM t3 AS table1
+LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
+LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
+table3.col_varchar_10_utf8_key {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`table2`.`col_int_key` AS `col_int_key` from ((`test`.`t3` `table1` left join `test`.`t1` `table2` on((`test`.`table1`.`col_int_key` < `test`.`table2`.`col_int_key`))) left join `test`.`t2` `table3` on((convert(`test`.`table2`.`col_varchar_10_latin1_key` using utf8) >= `test`.`table3`.`col_varchar_10_utf8_key`)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "table1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "table2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "table3",
+ "table_scan": {
+ "records": 1,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "table1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0107,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.4107,
+ "records_for_plan": 2,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "table3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 1,
+ "cost": 4.0098,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 6.8205,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "1",
+ "attached_conditions_computation": [
+ {
+ "database": "test",
+ "table": "table3",
+ "rechecking_index_usage": {
+ "range_analysis": {
+ "table_scan": {
+ "records": 1,
+ "cost": 4.3049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "col_varchar_10_utf8_key",
+ "usable": true,
+ "key_parts": [
+ "col_varchar_10_utf8_key"
+ ] /* key_parts */
+ },
+ {
+ "index": "col_int_key",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "col_varchar_10_utf8_key",
+ "cost": 1.2,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "without_ON_clause": {
+ "range_analysis": {
+ "table_scan": {
+ "records": 1,
+ "cost": 4.3049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "col_varchar_10_utf8_key",
+ "usable": true,
+ "key_parts": [
+ "col_varchar_10_utf8_key"
+ ] /* key_parts */
+ },
+ {
+ "index": "col_int_key",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "col_varchar_10_utf8_key",
+ "cost": 1.2,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ } /* without_ON_clause */
+ } /* rechecking_index_usage */
+ }
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "table1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "table3",
+ "attached": "trigcond_if(is_not_null_compl(table3), (convert(NULL using utf8) >= `test`.`table3`.`col_varchar_10_utf8_key`), true)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "table1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "table3",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1,t2,t3;
=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc 2011-03-08 19:14:42 +0000
+++ b/sql/sql_derived.cc 2011-05-13 14:02:31 +0000
@@ -267,6 +267,7 @@ bool mysql_derived_filling(THD *thd, LEX
TABLE *table= orig_table_list->table;
SELECT_LEX_UNIT *unit= orig_table_list->derived;
bool res= FALSE;
+ DBUG_ENTER("mysql_derived_filling");
/*check that table creation pass without problem and it is derived table */
if (table && unit)
@@ -311,7 +312,7 @@ bool mysql_derived_filling(THD *thd, LEX
}
lex->current_select= save_current_select;
}
- return res;
+ DBUG_RETURN(res);
}
@@ -321,8 +322,9 @@ bool mysql_derived_filling(THD *thd, LEX
bool mysql_derived_cleanup(THD *thd, LEX *lex, TABLE_LIST *derived)
{
+ DBUG_ENTER("mysql_derived_cleanup");
SELECT_LEX_UNIT *unit= derived->derived;
if (unit)
unit->cleanup();
- return false;
+ DBUG_RETURN(false);
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-05-13 09:09:34 +0000
+++ b/sql/sql_select.cc 2011-05-13 14:02:31 +0000
@@ -10267,8 +10267,10 @@ static bool make_join_select(JOIN *join,
we have to check isn't it only "impossible ON" instead
*/
sel->cond=orig_cond;
- if (!*tab->on_expr_ref ||
- sel->test_quick_select(thd, tab->keys,
+ if (!*tab->on_expr_ref)
+ DBUG_RETURN(1); // Impossible WHERE
+ Opt_trace_object trace_without_on(trace, "without_ON_clause");
+ if (sel->test_quick_select(thd, tab->keys,
used_tables & ~ current_map,
(join->select_options &
OPTION_FOUND_ROWS ?
Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110513140231-bgqe5splbpsklvb2.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (guilhem.bichot:3305) Bug#12552262 | Guilhem Bichot | 13 May |
