maria-developers team mailing list archive

Thread
Date
Re: 346c8ab9533: MDEV-11975: SQLCOM_PREPARE of EXPLAIN & ANALYZE statement do not return correct metadata info

To: maria-developers@xxxxxxxxxxxxxxxxxxx
From: Sergei Golubchik <serg@xxxxxxxxxxx>
Date: Thu, 19 Apr 2018 11:31:00 +0200
Cc: sanja@xxxxxxxxxxx
In-reply-to: <5ad78195.1c69fb81.f66e.ffe7@mx.google.com>
User-agent: Mutt/1.7.2 (2016-11-26)
Hi, Oleksandr!

On Apr 18, Oleksandr Byelkin wrote:
> revision-id: 346c8ab9533a3de6a4cb348428402ffee2aa8da2 (mariadb-10.3.6-16-g346c8ab9533)
> parent(s): cff60be7fe159fdcb2517ce8441610ad512aa7d0
> author: Oleksandr Byelkin
> committer: Oleksandr Byelkin
> timestamp: 2018-04-18 19:34:12 +0200
> message:
> 
> MDEV-11975: SQLCOM_PREPARE of EXPLAIN & ANALYZE statement do not return correct metadata info
> 
> Added metadate info after prepare EXPLAIN/ANALYZE.
> 
> ---
>  mysql-test/main/mysql_client_test.result | 122 ++++++++++++
>  mysql-test/main/mysql_client_test.test   |   7 +-
>  sql/sql_class.cc                         |  18 +-
>  sql/sql_class.h                          |   3 +-
>  sql/sql_explain.cc                       |   3 +-
>  sql/sql_parse.cc                         |   5 +-
>  sql/sql_prepare.cc                       |  24 ++-
>  sql/sql_prepare.h                        |   2 +
>  tests/mysql_client_test.c                | 323 ++++++++++++++++++++++++++++++-
>  9 files changed, 493 insertions(+), 14 deletions(-)
> 
> diff --git a/mysql-test/main/mysql_client_test.result b/mysql-test/main/mysql_client_test.result
> index 83ef8d442b3..20385acfa00 100644
> --- a/mysql-test/main/mysql_client_test.result
> +++ b/mysql-test/main/mysql_client_test.result
> @@ -122,5 +122,127 @@ EOF
>  mysql_stmt_next_result(): 0; field_count: 0
>  # ------------------------------------
>  
> +# cat MYSQL_TMP_DIR/test_explain_meta.out.log
> +# ------------------------------------

okay, altought I'd rather add prepare metadata output to mysqltest.
Like, either print prepare metadata on --enable_metadata or introduce a new
command --enable_prepare_metadata

> +SELECT number of fields: 1
> +EXPALIN number of fields: 10
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +EXPALIN JSON number of fields: 1
> +  - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +ANALYZE number of fields: 13
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
> +  - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +ANALYZE JSON number of fields: 1
> +  - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +EXPALIN INSERT number of fields: 10
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +EXPALIN JSON INSERT number of fields: 1
> +  - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +ANALYZE INSERT number of fields: 13
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
> +  - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +ANALYZE JSON INSERT number of fields: 1
> +  - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +EXPALIN UPDATE number of fields: 10
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +EXPALIN JSON UPDATE number of fields: 1
> +  - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +ANALYZE UPDATE number of fields: 13
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
> +  - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +ANALYZE JSON UPDATE number of fields: 1
> +  - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +EXPALIN DELETE number of fields: 10
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +EXPALIN JSON DELETE number of fields: 1
> +  - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +ANALYZE DELETE number of fields: 13
> +  - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0
> +  - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39
> +  - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 3: name: 'type'/''; table: ''/''; db: ''; catalog: 'def'; length: 30; max_length: 0; type: 253; decimals: 39
> +  - 4: name: 'possible_keys'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
> +  - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
> +  - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
> +  - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
> +  - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
> +  - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
> +  - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
> +ANALYZE JSON DELETE number of fields: 1
> +  - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
> +# ------------------------------------
> +
> +
>  SET @@global.general_log= @old_general_log;
>  SET @@global.slow_query_log= @old_slow_query_log;
> diff --git a/sql/sql_class.cc b/sql/sql_class.cc
> index 96485ed15ec..349ec576264 100644
> --- a/sql/sql_class.cc
> +++ b/sql/sql_class.cc
> @@ -2659,18 +2660,27 @@ CHANGED_TABLE_LIST* THD::changed_table_dup(const char *key, size_t key_length)
>  }
>  
>  
> -int THD::send_explain_fields(select_result *result, uint8 explain_flags, bool is_analyze)
> +int THD::send_explain_fields(select_result *result,
> +                             uint8 explain_flags,
> +                             bool is_analyze,
> +                             Prepared_statement *stmt)
>  {
>    List<Item> field_list;
> +  int rc;
>    if (lex->explain_json)
>      make_explain_json_field_list(field_list, is_analyze);
>    else
>      make_explain_field_list(field_list, explain_flags, is_analyze);
>  
>    result->prepare(field_list, NULL);
> -  return (result->send_result_set_metadata(field_list,
> -                                           Protocol::SEND_NUM_ROWS | 
> -                                           Protocol::SEND_EOF));
> +  if (stmt)
> +    rc= send_prep_stmt(stmt, result->field_count(field_list)) ||
> +        result->send_result_set_metadata(field_list, Protocol::SEND_EOF);
> +  else
> +    rc= result->send_result_set_metadata(field_list,
> +                                         Protocol::SEND_NUM_ROWS |
> +                                         Protocol::SEND_EOF);
> +  return(rc);
>  }

dunno, I'd rather keep prepare functionality in sql_prepare.cc.
Like,

void THD::prepare_explain_fields(select_result *result, List<Item> *field_list,
                                 uint8 explain_flags, bool is_analyze)
{
   if (lex->explain_json)
     make_explain_json_field_list(field_list, is_analyze);
   else
     make_explain_field_list(field_list, explain_flags, is_analyze);
   result->prepare(field_list, NULL);
}

int THD::send_explain_fields(select_result *result, uint8 explain_flags, bool is_analyze)
{
  List<Item> field_list;
  prepare_explain_fields(result, field_list, explain_flags, is_analyze);
  return result->send_result_set_metadata(field_list,
                                          Protocol::SEND_NUM_ROWS |
                                          Protocol::SEND_EOF);
}

and in sql_prepare.cc you do

  List<Item> field_list;
  thd->prepare_explain_fields(result, field_list, explain_flags, is_analyze);
  return send_prep_stmt(stmt, result->field_count(field_list)) ||
         result->send_result_set_metadata(field_list, Protocol::SEND_EOF);

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx
Follow ups

Re: 346c8ab9533: MDEV-11975: SQLCOM_PREPARE of EXPLAIN & ANALYZE statement do not return correct metadata info
From: Oleksandr Byelkin, 2018-04-20