Hi Varun, On Sat, Feb 03, 2018 at 06:37:21PM +0530, Varun wrote:
revision-id: f4f32ab71d5c96fbec87f8b6a5c057af2bc2398b (mariadb-10.2.5-413-gf4f32ab71d5) parent(s): 9390ff53fc39e34976cf051ce33649f9316bb8e6 author: Varun Gupta committer: Varun Gupta timestamp: 2018-02-03 18:24:49 +0530 message:
MDEV-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918): Encountered illegal value '' when converting to DECIMAL
The issue was that EITS data was allocated but then not read for some reason (one being to avoid a deadlock), then the optimizer was using these bzero'ed buffers as EITS statistics. This should not be allowed, we should use statistcs for a table only when we have successfully loaded/read the stats from the statistical tables.
--- mysql-test/r/statistics.result | 16 ++++++++++++++++ mysql-test/t/statistics.test | 19 +++++++++++++++++++ sql/sql_statistics.cc | 23 ++++++++++++++++------- 3 files changed, 51 insertions(+), 7 deletions(-)
diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index ffaaf09acc8..1486a8ab5e6 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1716,5 +1716,21 @@ b 0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.004,0.000,0.000,0.004,0.000,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.004,0.008,0.004,0.008,0.008,0.008,0.008,0.020,0.004,0.016,0.020,0.016,0.016,0.051,0.031,0.027,0.031,0.043,0.047,0.043,0.043,0.055,0.051,0.071,0.043,0.043,0.043,0.020,0.024,0.024,0.020,0.016,0.016,0.008,0.008,0.012,0.000 DROP TABLE t1; # +# MDEB-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918): Please change to "MDEV"
+# Encountered illegal value '' when converting to DECIMAL +# +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=3, use_stat_tables=preferably; +create table a (id int(10),cost decimal(9,2)) engine=innodb; +ANALYZE TABLE a PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.a analyze status Engine-independent statistics collected +test.a analyze status OK +create temporary table b (id int); +insert into b (id) select id from a where cost > 0; +drop table a,b; Please follow the convention and use table names t1,t2,...
Ok to push after this is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog