Hello again,
This is fine, I just have curiosity questions:
Jorgen Loland a écrit, Le 11.01.2011 12:56:
> #At
> file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/
> based on revid:jorgen.loland@stripped
>
> 3248 Jorgen Loland 2011-01-11
> WL#5741: Add optimizer tracing to subqueries
>
> Tests only
>
> added:
> mysql-test/r/optimizer_trace_subquery.result
> mysql-test/t/optimizer_trace_subquery.test
> === added file 'mysql-test/r/optimizer_trace_subquery.result'
> --- a/mysql-test/r/optimizer_trace_subquery.result 1970-01-01 00:00:00 +0000
> +++ b/mysql-test/r/optimizer_trace_subquery.result 2011-01-11 11:56:02 +0000
> +CREATE TABLE t1 (a FLOAT(5,4) zerofill);
> +CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
> +SELECT t1.a
> +FROM t1
> +WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
> +t1.a= (SELECT a FROM t2 LIMIT 1) ;
> +a
> +
> +SELECT * FROM information_schema.OPTIMIZER_TRACE;
> +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
> +SELECT t1.a
> +FROM t1
> +WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
> +t1.a= (SELECT a FROM t2 LIMIT 1) {
> + "steps": [
> + {
> + "join_preparation": {
> + "select#": 1,
> + "steps": [
> + {
> + "join_preparation": {
> + "select#": 2,
> + "steps": [
> + {
> + "expanded_query": "/* select#2 */ select `test`.`t2`.`b` from
> `test`.`t2` limit 1"
> + }
> + ] /* steps */
> + } /* join_preparation */
> + },
> + {
> + "join_preparation": {
> + "select#": 3,
> + "steps": [
> + {
> + "expanded_query": "/* select#3 */ select `test`.`t2`.`a` from
> `test`.`t2` limit 1"
> + }
> + ] /* steps */
> + } /* join_preparation */
> + },
> + {
> + "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a` from
> `test`.`t1` where ((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from
> `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a`
> from `test`.`t2` limit 1)))"
> + }
> + ] /* steps */
> + } /* join_preparation */
> + },
> + {
> + "join_optimization": {
> + "select#": 1,
> + "steps": [
> + {
> + "condition_processing": {
> + "condition": "WHERE",
> + "original_condition": "((`test`.`t1`.`a` = (/* select#2 */ select
> `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */
> select `test`.`t2`.`a` from `test`.`t2` limit 1)))",
> + "evaluate_subselect_cond_steps": [
> + {
> + "subselect_exec": {
> + "select#": 2,
> + "steps": [
> + {
> + "join_optimization": {
> + "select#": 2,
> + "steps": [
> + {
> + "records_estimation": [
> + {
> + "database": "test",
> + "table": "t2",
> + "records": 1,
Do you know why it says there is 1 record, though the table is empty?
> + "cost": 1,
> + "table_type": "system"
> + }
> + ] /* records_estimation */
> + }
> + ] /* steps */,
> + "empty_result": {
> + "cause": "no matching row in const table"
> + } /* empty_result */
> + } /* join_optimization */
> + },
> + {
> + "join_execution": {
> + "select#": 2,
> + "steps": [
> + ] /* steps */
> + } /* join_execution */
> + }
> + ] /* steps */
> + } /* subselect_exec */
> + }
> + ] /* evaluate_subselect_cond_steps */,
> + "after_equality_propagation": "((NULL <> (/* select#3 */ select
> `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL
> from `test`.`t2` limit 1), `test`.`t1`.`a`))",
I don't understand how we come to this new condition above, from
"original_condition":
* "NULL <> (select #3 etc)"
makes me think that t1.a was replaced by NULL (but why: t1 is empty?).
* in multiple equal((/* select#2 */ select NULL from `test`.`t2` limit
1), `test`.`t1`.`a`) t1.a was not replaced, and also t2.b was replaced
by NULL (which may be ok if the system noted that t2 is empty and thus
the subquery will return NULL, but then why didn't it replace the entire
subquery with NULL?).
This is not related to your patch, so if you don't know, I can research
it myself.
I also had comments about this test in a previous review.
