[MDEV-9744] session optimizer_use_condition_selectivity=5 causing SQL Error (1918): Encountered illegal value '' when converting to DECIMAL Created: 2016-03-16  Updated: 2018-04-02  Resolved: 2018-04-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.0.35

Type: Bug Priority: Minor
Reporter: Nickolay Ihalainen Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 1
Labels: None
Environment:

docker mariadb:latest container


Sprint: 10.2.13

 Description    

select version(),@@histogram_size, @@use_stat_tables, @@optimizer_use_condition_selectivity\G
 
                            version(): 10.1.12-MariaDB-1~jessie
 
                     @@histogram_size: 127
 
                    @@use_stat_tables: PREFERABLY
 
@@optimizer_use_condition_selectivity: 5

I'm using docker mariadb:latest container with 

histogram_size=127
 
use_stat_tables=preferably
 
optimizer_use_condition_selectivity=5
 
in [mysqld] section.


drop table if exists a;
 
create table a (id int(10),cost decimal(9,2)) engine=innodb;
 
ANALYZE TABLE a PERSISTENT FOR ALL;
 
drop table if exists b;
 
create temporary table b (id int);
 
insert into b (id) select id from a where cost > 0;

Last query producing warnings:

Query OK, 0 rows affected, 3 warnings (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 3
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+---------------------------------------------------------+
 
| Level   | Code | Message                                                 |
 
+---------+------+---------------------------------------------------------+
 
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
 
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
 
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |

Warnings are not generated for the sequence (select query added before insert select):

drop table if exists a;
 
create table a (id int(10),cost decimal(9,2)) engine=innodb;
 
ANALYZE TABLE a PERSISTENT FOR ALL;
 
drop table if exists b;
 
create temporary table b (id int);
 
select id from a where cost > 0;
 
insert into b (id) select id from a where cost > 0;

Also warnings are not generated normal (not temporary) table b.
set session optimizer_use_condition_selectivity=x; where x <= 2 also eliminates warnings.

 Comments   
Comment by Elena Stepanova [ 2016-03-16 ]

Thanks for the report.

MTR-like test case (same as above):

--source include/have_innodb.inc
 
 
 
set optimizer_use_condition_selectivity=3, use_stat_tables=preferably;
 
 
 
drop table if exists a, b;
 
create table a (id int(10),cost decimal(9,2)) engine=innodb;
 
ANALYZE TABLE a PERSISTENT FOR ALL;
 
 
 
create temporary table b (id int);
 
insert into b (id) select id from a where cost > 0;
 
 
 
drop table a;

Comment by Sergei Petrunia [ 2018-01-24 ]

varun, please check if it's reproducible anymore and investigate

Comment by Varun Gupta (Inactive) [ 2018-01-30 ] 

* thread #2, stop reason = breakpoint 2.1
 
  * frame #0: 0x0000000100210e43 mysqld`decimal_operation_results(result=8, value="", type="DECIMAL") at my_decimal.cc:45
 
    frame #1: 0x00000001001216e3 mysqld`check_result(mask=30, result=8) at my_decimal.h:193
 
    frame #2: 0x0000000100076a96 mysqld`binary2my_decimal(mask=30, bin="", d=0x000070000854cd10, prec=9, scale=2) at my_decimal.h:282
 
    frame #3: 0x000000010001bc78 mysqld`Field_new_decimal::val_decimal(this=0x0000000103245478, decimal_value=0x000070000854cd10) at field.cc:3335
 
    frame #4: 0x000000010001b93b mysqld`Field_new_decimal::val_real(this=0x0000000103245478) at field.cc:3315
 
    frame #5: 0x0000000100013353 mysqld`Field::pos_in_interval_val_real(this=0x0000000103242990, min=0x0000000103245478, max=0x0000000103245888) at field.cc:1124
 
    frame #6: 0x000000010003df30 mysqld`Field_num::pos_in_interval(this=0x0000000103242990, min=0x0000000103245478, max=0x0000000103245888) at field.h:1661
 
    frame #7: 0x00000001004adf7c mysqld`get_column_range_cardinality(field=0x0000000103242990, min_endp=0x000070000854cfa0, max_endp=0x0000000000000000, range_flag=6) at sql_statistics.cc:3832
 
    frame #8: 0x000000010023fca7 mysqld`records_in_column_ranges(param=0x000070000854d6a0, idx=0, tree=0x0000000103245ec8) at opt_range.cc:2848
 
    frame #9: 0x000000010023eee9 mysqld`calculate_cond_selectivity_for_table(thd=0x000000010418e870, table=0x0000000103241470, cond=0x0000000103202e40) at opt_range.cc:3085
 
    frame #10: 0x000000010042c0f1 mysqld`make_join_statistics(join=0x0000000103202a38, tables_list=0x0000000104192d78, keyuse_array=0x0000000103202d28) at sql_select.cc:4409
 
    frame #11: 0x00000001004238d4 mysqld`JOIN::optimize_inner(this=0x0000000103202a38) at sql_select.cc:1525
 
    frame #12: 0x0000000100421f13 mysqld`JOIN::optimize(this=0x0000000103202a38) at sql_select.cc:1101
 
    frame #13: 0x000000010041dbaf mysqld`mysql_select(thd=0x000000010418e870, tables=0x0000000103202010, wild_num=0, fields=0x0000000104192ca0, conds=0x00000001032027d0, og_num=0, order=0x0000000000000000, group=0x0000000000000000, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=3489925888, result=0x0000000103202990, unit=0x0000000104192440, select_lex=0x0000000104192b78) at sql_select.cc:3721
 
    frame #14: 0x000000010041d4b6 mysqld`handle_select(thd=0x000000010418e870, lex=0x0000000104192378, result=0x0000000103202990, setup_tables_done_option=1073741824) at sql_select.cc:361
 
    frame #15: 0x00000001003badad mysqld`mysql_execute_command(thd=0x000000010418e870) at sql_parse.cc:4525
 
    frame #16: 0x00000001003b20f8 mysqld`mysql_parse(thd=0x000000010418e870, rawbuf="insert into b (id) select id from a where cost > 9", length=50, parser_state=0x0000700008552e10, is_com_multi=false, is_next_command=false) at sql_parse.cc:7898
 
    frame #17: 0x00000001003ac01c mysqld`dispatch_command(command=COM_QUERY, thd=0x000000010418e870, packet="insert into b (id) select id from a where cost > 9", packet_length=50, is_com_multi=false, is_next_command=false) at sql_parse.cc:1805
 
    frame #18: 0x00000001003b049a mysqld`do_command(thd=0x000000010418e870) at sql_parse.cc:1359
 
    frame #19: 0x0000000100587741 mysqld`do_handle_one_connection(connect=0x0000000102c189b0) at sql_connect.cc:1335
 
    frame #20: 0x000000010058751a mysqld`::handle_one_connection(arg=0x0000000102c189b0) at sql_connect.cc:1241
 
    frame #21: 0x0000000100de4285 mysqld`::pfs_spawn_thread(arg=0x0000000102c1a4e0) at pfs.cc:1862
 
    frame #22: 0x00007fff6dc666c1 libsystem_pthread.dylib`_pthread_body + 340
 
    frame #23: 0x00007fff6dc6656d libsystem_pthread.dylib`_pthread_start + 377
 
    frame #24: 0x00007fff6dc65c5d libsystem_pthread.dylib`thread_start + 13

Comment by Sergei Petrunia [ 2018-01-30 ]

S,o, the warning is produced when EITS code tries to calculate condition selectivity here:

        min_mp_pos= field->pos_in_interval(col_stats->min_value,
 
                                           col_stats->max_value);

Going deeper in the stack: we are in

double Field::pos_in_interval_val_real(Field *min, Field *max)

we are on this line

  n= val_real() - min->val_real();

and we are computing min->val_real().

Comment by Sergei Petrunia [ 2018-01-30 ]

Going back up the stack and looking at column statistics:

(gdb) x/10c col_stats->min_value->ptr
 
  0x7ffff40bd38d:    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'
 
  0x7ffff40bd395:    0 '\000'    0 '\000'
 
(gdb) x/10c col_stats->max_value->ptr
 
  0x7ffff40bd39d:    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'
 
  0x7ffff40bd3a5:    0 '\000'    0 '\000'

Doesn't look like the value is present.

Note: I am using the testcase that was modified by Varun as follows: Before ANALYZE TABLE a PERSISTENT FOR ALL I ran insert into a(id,cost) VALUES ( 1, 9.4);, so I do have min and max value in the statistical table:

MariaDB [j2]> select *, hex(histogram) from mysql.column_stats where table_name='a' and db_name='j2'\G
 
*************************** 1. row ***************************
 
      db_name: j2
 
   table_name: a
 
  column_name: cost
 
    min_value: 9.40
 
    max_value: 9.40
 
  nulls_ratio: 0.0000
 
   avg_length: 5.0000
 
avg_frequency: 1.0000
 
    hist_size: 127
 
    hist_type: SINGLE_PREC_HB

Comment by Sergei Petrunia [ 2018-01-30 ]

An observation by varun: the query 

insert into b (id) select id from a where cost > 0;

produces warnings ONLY IF table `b` is a TEMPORARY table. If it is a regular table, no warnings are produced.

Comment by Sergei Petrunia [ 2018-01-30 ]

Debugging further: I can see that alloc_statistics_for_table_share is invoked for table a. But I don't see where mysql.column_stats is read.

Comment by Sergei Petrunia [ 2018-01-30 ]

... and the reason it is not read is this piece in statistics_for_tables_is_needed():

  /* 
    Do not read statistics for any query over non-user tables.
 
    If the query references some statistical tables, but not all 
    of them, reading the statistics may lead to a deadlock
 
  */ 
  for (TABLE_LIST *tl= tables; tl; tl= tl->next_global)
 
  {
 
    if (!tl->is_view_or_derived() && tl->table)
 
    {
 
      TABLE_SHARE *table_share= tl->table->s;
 
      if (table_share && 
          (table_share->table_category != TABLE_CATEGORY_USER ||
 
           table_share->tmp_table != NO_TMP_TABLE))
 
        return FALSE;
 
    }
 
  }

Let's denote this piece of code DONT-READ-STATS. So, for the optimizer the table looks like it has EITS statistiscs, while actually it does not.

This bug exposes two issues:

  • Issue #1: why does DONT-READ-STATS dont read EITS stats for an example like this. It should. It's perfectly reasonable to use a few temporary tables in analytics computations and one can expect EITS to work
  • Issue #2: If EITS data was allocated but then not read for some reason (because of DONT-READ-STATS or some other reason) , then the optimizer should not attempt to use bzero'ed buffers as EITS statistics! (A simple way to fix: add a"stats_loaded_sucessfully_flag", set it when we've loaded stats, let the optimizer use EITS data only if the flag is set)
Comment by Varun Gupta (Inactive) [ 2018-02-02 ] 

Continuing from the last comment, so in this case we have those 2 issues mentioned above
 
 
 
Now for issue 1 the approach we are using is that 
   "if the query explicitly uses any of EITS tables, then  do not load EITS statistics for any
 
     tables in the query"
 
This is mostly due to the reason of encountering a deadlock

Comment by Varun Gupta (Inactive) [ 2018-02-03 ]

http://lists.askmonty.org/pipermail/commits/2018-February/011954.html

Comment by Varun Gupta (Inactive) [ 2018-04-02 ]

Pushed to 10.0

Generated at Thu Feb 08 07:37:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.