List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:January 11 2011 1:44pm
Subject:Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3248)
WL#5741
View as plain text  
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.

Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3248) WL#5741Jorgen Loland11 Jan
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3248)WL#5741Guilhem Bichot11 Jan
    • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3248)WL#5741Jorgen Loland14 Jan
      • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3248)WL#5741Guilhem Bichot14 Jan