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.
Bug
Minor
