From a1fd25c22bc27e58b802dd83ee48428913351180 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 18 Aug 2018 22:57:20 -0700 Subject: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows If the specification of a derived table contained a table value constructor then the optimizer incorrectly estimated the number of rows in the derived table. This happened because the optimizer did not take into account the number of rows in the constructor. The wrong estimate could lead to choosing inefficient execution plans. --- mysql-test/main/range.result | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test/main/range.result') diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index e2996b963bc..cbf9d5bb7d9 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1052,6 +1052,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1062,15 +1063,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', -- cgit v1.2.1 From 6b9dd66f0770077b2573de5d16671e7463f8c5d5 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 23 Aug 2018 19:30:26 +0300 Subject: Move the testcase for BUG#21282 to a file that includes have_debug.inc The testcase needs to set in_predicate_conversion_threshold which is only available in debug builds (this is subject to further discussion). --- mysql-test/main/range.result | 24 ------------------------ 1 file changed, 24 deletions(-) (limited to 'mysql-test/main/range.result') diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index cbf9d5bb7d9..052ea09d5ac 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1048,30 +1048,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 drop table t1,t2,t3; -create table t1 (a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, key(a)); -insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; -set in_predicate_conversion_threshold= 2000; -set @a="select * from t2 force index (a) where a NOT IN(0"; -select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; -count(*) -1000 -set @a=concat(@a, ')'); -insert into t2 values (11),(13),(15); -set @b= concat("explain ", @a); -prepare stmt1 from @b; -execute stmt1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index -prepare stmt1 from @a; -execute stmt1; -a -11 -13 -15 -set in_predicate_conversion_threshold= default; -drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', b int NOT NULL DEFAULT '0', -- cgit v1.2.1 From 07e4853c232726a639b30d4718bef9914ea42d24 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 7 Nov 2018 19:00:14 +0400 Subject: MDEV-17563 Different results using table or view when comparing values of time type MDEV-17625 Different warnings when comparing a garbage to DATETIME vs TIME - Splitting processes of data type conversion (to TIME/DATE,DATETIME) and warning generation. Warning are now only get collected during conversion (in an "int" variable), and are pushed in the very end of conversion (not in parallel). Warnings generated by the low level routines str_to_xxx() and number_to_xxx() can now be changed at the end, when TIME_FUZZY_DATES is applied, from "Invalid value" to "Truncated invalid value". Now "Illegal value" is issued only when the low level routine returned an error and TIME_FUZZY_DATES was not set. Otherwise, if the low level routine returned "false" (success), or if NULL was converted to a zero datetime by TIME_FUZZY_DATES, then "Truncated illegal value" is issued. This gives better warnings. - Methods Type_handler::Item_get_date() and Type_handler::Item_func_hybrid_field_type_get_date() now only convert and collect warning information, but do not push warnings. - Changing the return data type for Type_handler::Item_get_date() and Type_handler::Item_func_hybrid_field_type_get_date() from "bool" to "void". The conversion result (success vs error) can be checked by testing ltime->time_type. MYSQL_TIME_{NONE|ERROR} mean mean error, other values mean success. - Adding new wrapper methods Type_handler::Item_get_date_with_warn() and Type_handler::Item_func_hybrid_field_type_get_date_with_warn() to do conversion followed by raising warnings, and changing the code to call new Type_handler::***_with_warn() methods. - Adding a helper class Temporal::Status, a wrapper for MYSQL_TIME_STATUS with automatic initialization. - Adding a helper class Temporal::Warn, to collect warnings but without actually raising them. Moving a part of ErrConv into a separate class ErrBuff, and deriving both Temporal::Warn and ErrConv from ErrBuff. The ErrBuff part of Temporal::Warn is used to collect textual representation of the input data. - Adding a helper class Temporal::Warn_push. It's used to collect warning information during conversion, and automatically pushes warnings to the diagnostics area on its destructor time (in case of non-zero warning). - Moving more code from various functions inside class Temporal. - Adding more Temporal_hybrid constructors and protected Temporal methods make_from_xxx(), which convert and only collect warning information, but do not actually raise warnings. - Now the low level functions str_to_datetime() and str_to_time() always set status->warning if the return value is "true" (error). - Now the low level functions number_to_time() and number_to_datetime() set the "*was_cut" argument if the return value is "true" (error). - Adding a few DBUG_ASSERTs to make sure that str_to_xxx() and number_to_xxx() always set warnings on error. - Adding new warning flags MYSQL_TIME_WARN_EDOM and MYSQL_TIME_WARN_ZERO_DATE for the code symmetry. Before this change there was a special code path for (rc==true && was_cut==0) which was treated by Field_temporal::store_invalid_with_warning as "zero date violation". Now was_cut==0 always means that there are no any error/warnings/notes to be raised, not matter what rc is. - Using new Temporal_hybrid constructors in combination with Temporal::Warn_push inside str_to_datetime_with_warn(), double_to_datetime_with_warn(), int_to_datetime_with_warn(), Field::get_date(), Item::get_date_from_string(), and a few other places. - Removing methods Dec_ptr::to_datetime_with_warn(), Year::to_time_with_warn(), my_decimal::to_datetime_with_warn(), Dec_ptr::to_datetime_with_warn(). Fixing Sec6::to_time() and Sec6::to_datetime() to convert and only collect warnings, without raising warnings. Now warning raising functionality resides in Temporal::Warn_push. - Adding classes Longlong_hybrid_null and Double_null, to return both value and the "IS NULL" flag. Adding methods Item::to_double_null(), to_longlong_hybrid_null(), Item_func_hybrid_field_type::to_longlong_hybrid_null_op(), Item_func_hybrid_field_type::to_double_null_op(). Removing separate classes VInt and VInt_op, as they have been replaced by a single class Longlong_hybrid_null. - Adding a helper method Temporal::type_name_by_timestamp_type(), moving a part of make_truncated_value_warning() into it, and reusing in Temporal::Warn::push_conversion_warnings(). - Removing Item::make_zero_date() and Item_func_hybrid_field_type::make_zero_mysql_time(). They provided duplicate functionality. Now this code resides in Temporal::make_fuzzy_date(). The latter is now called for all Item types when data type conversion (to DATE/TIME/DATETIME) is involved, including Item_field and Item_direct_view_ref. This fixes MDEV-17563: Item_direct_view_ref now correctly converts NULL to a zero date when TIME_FUZZY_DATES says so. --- mysql-test/main/range.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test/main/range.result') diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 464c0bf031a..50de308045b 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; item started price Warnings: -Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' +Warning 1292 Truncated incorrect datetime value: '2005-12-01 24:00:00' SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; item started price A1 2005-11-01 08:00:00 1000.000 @@ -1113,7 +1113,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; item started price Warnings: -Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' +Warning 1292 Truncated incorrect datetime value: '2005-12-01 24:00:00' SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; item started price A1 2005-11-01 08:00:00 1000.000 @@ -1563,7 +1563,7 @@ str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' 1 Warnings: -Warning 1292 Incorrect datetime value: '' +Warning 1292 Truncated incorrect datetime value: '' SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND str_to_date('2007-20-00', '%Y-%m-%d') <= ''; str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND @@ -1578,7 +1578,7 @@ SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 1 Warnings: -Warning 1292 Incorrect datetime value: '' +Warning 1292 Truncated incorrect datetime value: '' SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' NULL -- cgit v1.2.1