Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
None
-
docker mariadb:latest container
-
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.
Attachments
Activity
... 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)
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
|
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.