From a7fb5aecfd527c6b9274db02dcec69daf06c97a3 Mon Sep 17 00:00:00 2001 From: Chaithra Gopalareddy Date: Fri, 20 Nov 2015 12:30:15 +0530 Subject: Bug#19941403: FATAL_SIGNAL(SIG 6) IN BUILD_EQUAL_ITEMS_FOR_COND | IN SQL/SQL_OPTIMIZER.CC:1657 Problem: At the end of first execution select_lex->prep_where is pointing to a runtime created object (temporary table field). As a result server exits trying to access a invalid pointer during second execution. Analysis: While optimizing the join conditions for the query, after the permanent transformation, optimizer makes a copy of the new where conditions in select_lex->prep_where. "prep_where" is what is used as the "where condition" for the query at the start of execution. W.r.t the query in question, "where" condition is actually pointing to a field in the temporary table. As a result, for the second execution the pointer is no more valid resulting in server exit. Fix: At the end of the first execution, select_lex->where will have the original item of the where condition. Make prep_where the new place where the original item of select->where has to be rolled back. Fixed in 5.7 with the wl#7082 - Move permanent transformations from JOIN::optimize to JOIN::prepare Patch for 5.5 includes the following backports from 5.6: Bugfix for Bug12603141 - This makes the first execute statement in the testcase pass in 5.5 However it was noted later in in Bug16163596 that the above bugfix needed to be modified. Although Bug16163596 is reproducible only with changes done for Bug12582849, we have decided include the fix. Considering that Bug12582849 is related to Bug12603141, the fix is also included here. However this results in Bug16317817, Bug16317685, Bug16739050. So fix for the above three bugs is also part of this patch. --- sql/item_cmpfunc.h | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'sql/item_cmpfunc.h') diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 115d6db300d..00c7bccbfb8 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1514,7 +1514,7 @@ public: friend int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, COND **conds); void top_level_item() { abort_on_null=1; } - void copy_andor_arguments(THD *thd, Item_cond *item); + void copy_andor_arguments(THD *thd, Item_cond *item, bool real_items= false); bool walk(Item_processor processor, bool walk_subquery, uchar *arg); Item *transform(Item_transformer transformer, uchar *arg); void traverse_cond(Cond_traverser, void *arg, traverse_order order); @@ -1689,11 +1689,11 @@ public: const char *func_name() const { return "and"; } table_map not_null_tables() const { return abort_on_null ? not_null_tables_cache: and_tables_cache; } - Item* copy_andor_structure(THD *thd) + Item* copy_andor_structure(THD *thd, bool real_items) { Item_cond_and *item; if ((item= new Item_cond_and(thd, this))) - item->copy_andor_arguments(thd, this); + item->copy_andor_arguments(thd, this, real_items); return item; } Item *neg_transformer(THD *thd); @@ -1719,11 +1719,11 @@ public: longlong val_int(); const char *func_name() const { return "or"; } table_map not_null_tables() const { return and_tables_cache; } - Item* copy_andor_structure(THD *thd) + Item* copy_andor_structure(THD *thd, bool real_items) { Item_cond_or *item; if ((item= new Item_cond_or(thd, this))) - item->copy_andor_arguments(thd, this); + item->copy_andor_arguments(thd, this, real_items); return item; } Item *neg_transformer(THD *thd); -- cgit v1.2.1 From ce40ccaf24af2fe395f541cb1079256de8727ccd Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 28 Jan 2016 13:58:39 +0400 Subject: MDEV-9181 (NULLIF(count(table.col)), 0) gives wrong result on 10.1.x Wrapping args[0] and args[2] into an Item_cache for aggregate functions. --- sql/item_cmpfunc.h | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) (limited to 'sql/item_cmpfunc.h') diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index b7c51cd5178..16f8a247999 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -995,10 +995,13 @@ class Item_func_nullif :public Item_func_hybrid_field_type - Item_field::propagate_equal_fields(ANY_SUBST) for the left "a" - Item_field::propagate_equal_fields(IDENTITY_SUBST) for the right "a" */ + Item_cache *m_cache; + int compare(); public: // Put "a" to args[0] for comparison and to args[2] for the returned value. Item_func_nullif(THD *thd, Item *a, Item *b): - Item_func_hybrid_field_type(thd, a, b, a) + Item_func_hybrid_field_type(thd, a, b, a), + m_cache(NULL) {} bool date_op(MYSQL_TIME *ltime, uint fuzzydate); double real_op(); @@ -1009,6 +1012,9 @@ public: uint decimal_precision() const { return args[2]->decimal_precision(); } const char *func_name() const { return "nullif"; } void print(String *str, enum_query_type query_type); + void split_sum_func(THD *thd, Item **ref_pointer_array, List &fields, + uint flags); + void update_used_tables(); table_map not_null_tables() const { return 0; } bool is_null(); Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) -- cgit v1.2.1 From ff93b77fd62bdb708e2b2b34f4e2202c12e727c4 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 3 Mar 2016 18:44:10 +0100 Subject: MDEV-9641 MDEV-9644 NULLIF assertions * only copy args[0] to args[2] after fix_fields (when all item substitutions have already happened) * change QT_ITEM_FUNC_NULLIF_TO_CASE (that allows to print NULLIF as CASE) to QT_ITEM_ORIGINAL_FUNC_NULLIF (that prohibits it). So that NULLIF-to-CASE is allowed by default and only disabled explicitly for SHOW VIEW|FUNCTION|PROCEDURE and mysql_make_view. By default it is allowed (in particular in error messages and debug output, that can happen anytime before or after optimizer). --- sql/item_cmpfunc.h | 11 +++++++++-- 1 file changed, 9 insertions(+), 2 deletions(-) (limited to 'sql/item_cmpfunc.h') diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 39d9aa67819..f17167b5140 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -998,11 +998,18 @@ class Item_func_nullif :public Item_func_hybrid_field_type Item_cache *m_cache; int compare(); public: - // Put "a" to args[0] for comparison and to args[2] for the returned value. + /* + Here we pass three arguments to the parent constructor, as NULLIF + is a three-argument function, it needs two copies of the first argument + (see above). But fix_fields() will be confused if we try to prepare the + same Item twice (if args[0]==args[2]), so we hide the third argument + (decrementing arg_count) and copy args[2]=args[0] again after fix_fields(). + See also Item_func_nullif::fix_length_and_dec(). + */ Item_func_nullif(THD *thd, Item *a, Item *b): Item_func_hybrid_field_type(thd, a, b, a), m_cache(NULL) - {} + { arg_count--; } bool date_op(MYSQL_TIME *ltime, uint fuzzydate); double real_op(); longlong int_op(); -- cgit v1.2.1 From b25373beb52af6de43a70a0883918a0d2fd60c53 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 18 Mar 2016 17:50:18 +0400 Subject: MDEV-9653 Assertion `length || !scale' failed in uint my_decimal_length_to_precision(uint, uint, bool) MDEV-9752 Wrong data type for COALEASCE(?,1) in prepared statements --- sql/item_cmpfunc.h | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) (limited to 'sql/item_cmpfunc.h') diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index f17167b5140..cd19ba72c07 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -902,7 +902,11 @@ public: String *str_op(String *); my_decimal *decimal_op(my_decimal *); bool date_op(MYSQL_TIME *ltime,uint fuzzydate); - void fix_length_and_dec(); + void fix_length_and_dec() + { + set_handler_by_field_type(agg_field_type(args, arg_count, true)); + fix_attributes(args, arg_count); + } const char *func_name() const { return "coalesce"; } table_map not_null_tables() const { return 0; } }; @@ -915,13 +919,18 @@ public: */ class Item_func_case_abbreviation2 :public Item_func_hybrid_field_type { +protected: + void fix_length_and_dec2(Item **items) + { + set_handler_by_field_type(agg_field_type(items, 2, true)); + fix_attributes(items, 2); + } + uint decimal_precision2(Item **args) const; public: Item_func_case_abbreviation2(THD *thd, Item *a, Item *b): Item_func_hybrid_field_type(thd, a, b) { } Item_func_case_abbreviation2(THD *thd, Item *a, Item *b, Item *c): Item_func_hybrid_field_type(thd, a, b, c) { } - void fix_length_and_dec2(Item **args); - uint decimal_precision2(Item **args) const; }; @@ -1454,8 +1463,6 @@ public: Item *find_item(String *str); CHARSET_INFO *compare_collation() const { return cmp_collation.collation; } void cleanup(); - void agg_str_lengths(Item *arg); - void agg_num_lengths(Item *arg); Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond); }; -- cgit v1.2.1 From 94768542115289272996f6450b7d3cafa75dcead Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 21 Mar 2016 11:21:44 +0400 Subject: MDEV-9369 IN operator with ( num, NULL ) gives inconsistent result Based on this commit into MySQL-5.7: > commit 8e51b845aafc8b4cdebd763c8aebda262ac2d4cd > Author: Guilhem Bichot > Date: Mon Nov 4 15:44:55 2013 +0100 > > Bug#13944462 'NULL IN (XX)' RETURNS WRONG RESULTS --- sql/item_cmpfunc.h | 86 +++++++++++++++++++++++++++++++++++++----------------- 1 file changed, 59 insertions(+), 27 deletions(-) (limited to 'sql/item_cmpfunc.h') diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index cd19ba72c07..39f2cf5590d 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -796,6 +796,7 @@ public: public: inline void negate() { negated= !negated; } inline void top_level_item() { pred_level= 1; } + bool is_top_level_item() const { return pred_level; } Item *neg_transformer(THD *thd) { negated= !negated; @@ -1075,7 +1076,7 @@ public: { my_qsort2(base,used_count,size,compare,(void*)collation); } - int find(Item *item); + bool find(Item *item); /* Create an instance of Item_{type} (e.g. Item_decimal) constant object @@ -1243,6 +1244,10 @@ public: cmp_item() { cmp_charset= &my_charset_bin; } virtual ~cmp_item() {} virtual void store_value(Item *item)= 0; + /** + @returns result (TRUE, FALSE or UNKNOWN) of + "stored argument's value <> item's value" + */ virtual int cmp(Item *item)= 0; // for optimized IN with row virtual int compare(cmp_item *item)= 0; @@ -1255,7 +1260,14 @@ public: } }; -class cmp_item_string :public cmp_item +/// cmp_item which stores a scalar (i.e. non-ROW). +class cmp_item_scalar : public cmp_item +{ +protected: + bool m_null_value; ///< If stored value is NULL +}; + +class cmp_item_string : public cmp_item_scalar { protected: String *value_res; @@ -1281,14 +1293,20 @@ public: void store_value(Item *item) { value_res= item->val_str(&value); + m_null_value= item->null_value; } int cmp(Item *arg) { char buff[STRING_BUFFER_USUAL_SIZE]; - String tmp(buff, sizeof(buff), cmp_charset), *res; - res= arg->val_str(&tmp); - return (value_res ? (res ? sortcmp(value_res, res, cmp_charset) : 1) : - (res ? -1 : 0)); + String tmp(buff, sizeof(buff), cmp_charset), *res= arg->val_str(&tmp); + if (m_null_value || arg->null_value) + return UNKNOWN; + if (value_res && res) + return sortcmp(value_res, res, cmp_charset) != 0; + else if (!value_res && !res) + return FALSE; + else + return TRUE; } int compare(cmp_item *ci) { @@ -1303,7 +1321,7 @@ public: } }; -class cmp_item_int :public cmp_item +class cmp_item_int : public cmp_item_scalar { longlong value; public: @@ -1311,10 +1329,12 @@ public: void store_value(Item *item) { value= item->val_int(); + m_null_value= item->null_value; } int cmp(Item *arg) { - return value != arg->val_int(); + const bool rc= value != arg->val_int(); + return (m_null_value || arg->null_value) ? UNKNOWN : rc; } int compare(cmp_item *ci) { @@ -1330,7 +1350,7 @@ public: If the left item is a constant one then its value is cached in the lval_cache variable. */ -class cmp_item_datetime :public cmp_item +class cmp_item_datetime : public cmp_item_scalar { longlong value; public: @@ -1348,7 +1368,7 @@ public: cmp_item *make_same(); }; -class cmp_item_real :public cmp_item +class cmp_item_real : public cmp_item_scalar { double value; public: @@ -1356,10 +1376,12 @@ public: void store_value(Item *item) { value= item->val_real(); + m_null_value= item->null_value; } int cmp(Item *arg) { - return value != arg->val_real(); + const bool rc= value != arg->val_real(); + return (m_null_value || arg->null_value) ? UNKNOWN : rc; } int compare(cmp_item *ci) { @@ -1370,7 +1392,7 @@ public: }; -class cmp_item_decimal :public cmp_item +class cmp_item_decimal : public cmp_item_scalar { my_decimal value; public: @@ -1397,12 +1419,13 @@ public: void store_value(Item *item) { value_res= item->val_str(&value); + m_null_value= item->null_value; } int cmp(Item *item) { // Should never be called - DBUG_ASSERT(0); - return 1; + DBUG_ASSERT(false); + return TRUE; } int compare(cmp_item *ci) { @@ -1467,33 +1490,43 @@ public: }; /* - The Item_func_in class implements the in_expr IN(values_list) function. + The Item_func_in class implements + in_expr IN () + and + in_expr NOT IN () The current implementation distinguishes 2 cases: - 1) all items in the value_list are constants and have the same + 1) all items in are constants and have the same result type. This case is handled by in_vector class. - 2) items in the value_list have different result types or there is some - non-constant items. - In this case Item_func_in employs several cmp_item objects to performs - comparisons of in_expr and an item from the values_list. One cmp_item + 2) otherwise Item_func_in employs several cmp_item objects to perform + comparisons of in_expr and an item from . One cmp_item object for each result type. Different result types are collected in the fix_length_and_dec() member function by means of collect_cmp_types() function. */ class Item_func_in :public Item_func_opt_neg { + /** + Usable if is made only of constants. Returns true if one + of these constants contains a NULL. Example: + IN ( (-5, (12,NULL)), ... ). + */ + bool list_contains_null(); protected: SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value); public: - /* - an array of values when the right hand arguments of IN - are all SQL constant and there are no nulls - */ + /// An array of values, created when the bisection lookup method is used in_vector *array; + /** + If there is some NULL among , during a val_int() call; for + example + IN ( (1,(3,'col')), ... ), where 'col' is a column which evaluates to + NULL. + */ bool have_null; - /* - true when all arguments of the IN clause are of compatible types + /** + true when all arguments of the IN list are of compatible types and can be used safely as comparisons for key conditions */ bool arg_types_compatible; @@ -1547,7 +1580,6 @@ public: virtual void print(String *str, enum_query_type query_type); enum Functype functype() const { return IN_FUNC; } const char *func_name() const { return " IN "; } - bool nulls_in_row(); bool eval_not_null_tables(uchar *opt_arg); void fix_after_pullout(st_select_lex *new_parent, Item **ref); bool count_sargable_conds(uchar *arg); -- cgit v1.2.1