From 0f3735842f584412da6e119927ece5974cbc23f8 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Wed, 20 Sep 2017 23:58:26 +0000 Subject: MDEV-12583 : Bake the git hash into the binaries. Added version_source_revision server "variable", for the git revision. Also , mysql -V will show git revision. "make dist" will now pack source_revision.h into the source package. --- CMakeLists.txt | 17 +++++++++++++++++ client/mysql.cc | 6 +++--- cmake/make_dist.cmake.in | 3 +++ include/source_revision.h.in | 1 + sql/sys_vars.cc | 8 ++++++++ 5 files changed, 32 insertions(+), 3 deletions(-) create mode 100644 include/source_revision.h.in diff --git a/CMakeLists.txt b/CMakeLists.txt index 3c638cb2d41..a0b498b27e3 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -432,6 +432,23 @@ CONFIGURE_FILE(${CMAKE_SOURCE_DIR}/include/mysql_version.h.in ${CMAKE_BINARY_DIR}/include/mysql_version.h ) CONFIGURE_FILE(${CMAKE_SOURCE_DIR}/sql/sql_builtin.cc.in ${CMAKE_BINARY_DIR}/sql/sql_builtin.cc) + +FIND_PACKAGE(Git) +IF(GIT_EXECUTABLE) + EXECUTE_PROCESS( + COMMAND ${GIT_EXECUTABLE} rev-parse HEAD + WORKING_DIRECTORY ${PROJECT_SOURCE_DIR} + OUTPUT_VARIABLE OUT RESULT_VARIABLE RES) + IF(RES EQUAL 0) + STRING(REGEX REPLACE "\n$" "" SOURCE_REVISION "${OUT}") + ENDIF() +ENDIF() +IF(SOURCE_REVISION OR + (NOT EXISTS ${PROJECT_SOURCE_DIR}/include/source_revision.h)) + CONFIGURE_FILE(${PROJECT_SOURCE_DIR}/include/source_revision.h.in + ${PROJECT_BINARY_DIR}/include/source_revision.h ) +ENDIF() + CONFIGURE_FILE( ${CMAKE_SOURCE_DIR}/cmake/info_macros.cmake.in ${CMAKE_BINARY_DIR}/info_macros.cmake @ONLY) diff --git a/client/mysql.cc b/client/mysql.cc index f2e7b9150fa..10601e36f64 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -40,7 +40,7 @@ #include "my_readline.h" #include #include - +#include #if defined(USE_LIBEDIT_INTERFACE) && defined(HAVE_LOCALE_H) #include #endif @@ -1721,8 +1721,8 @@ static void usage(int version) my_progname, VER, MYSQL_SERVER_VERSION, SYSTEM_TYPE, MACHINE_TYPE, readline, rl_library_version); #else - printf("%s Ver %s Distrib %s, for %s (%s)\n", my_progname, VER, - MYSQL_SERVER_VERSION, SYSTEM_TYPE, MACHINE_TYPE); + printf("%s Ver %s Distrib %s, for %s (%s), source revision %s\n", my_progname, VER, + MYSQL_SERVER_VERSION, SYSTEM_TYPE, MACHINE_TYPE,SOURCE_REVISION); #endif if (version) diff --git a/cmake/make_dist.cmake.in b/cmake/make_dist.cmake.in index 3cc93d10fb8..6bd71cc7653 100644 --- a/cmake/make_dist.cmake.in +++ b/cmake/make_dist.cmake.in @@ -52,6 +52,9 @@ IF(GIT_EXECUTABLE) ENDIF() ENDIF() +CONFIGURE_FILE(${CMAKE_BINARY_DIR}/include/source_revision.h + ${PACKAGE_DIR}/include/source_revision.h COPYONLY) + IF(NOT GIT_EXECUTABLE) MESSAGE(STATUS "git not found or source dir is not a repo, use CPack") diff --git a/include/source_revision.h.in b/include/source_revision.h.in new file mode 100644 index 00000000000..5b657d0940e --- /dev/null +++ b/include/source_revision.h.in @@ -0,0 +1 @@ +#cmakedefine SOURCE_REVISION "@SOURCE_REVISION@" diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 8a800017c1e..c123d60ccb3 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3503,6 +3503,14 @@ static Sys_var_charptr Sys_version_compile_os( CMD_LINE_HELP_ONLY, IN_SYSTEM_CHARSET, DEFAULT(SYSTEM_TYPE)); +#include +static char *server_version_source_revision; +static Sys_var_charptr Sys_version_source_revision( + "version_source_revision", "´Revision of the MariaDB source code", + READ_ONLY GLOBAL_VAR(server_version_source_revision), + CMD_LINE_HELP_ONLY, + IN_SYSTEM_CHARSET, DEFAULT(SOURCE_REVISION)); + static char *guess_malloc_library() { if (strcmp(MALLOC_LIBRARY, "system") == 0) -- cgit v1.2.1 From c027717adb64a8b364d5a0bf1599377ef3b58ec8 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Thu, 21 Sep 2017 09:14:01 +0000 Subject: MDEV-12583 post-fix Fix description of version_source_revision --- sql/sys_vars.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index c123d60ccb3..bc5f68ce83d 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3506,7 +3506,7 @@ static Sys_var_charptr Sys_version_compile_os( #include static char *server_version_source_revision; static Sys_var_charptr Sys_version_source_revision( - "version_source_revision", "´Revision of the MariaDB source code", + "version_source_revision", "Source control revision id for MariaDB source code", READ_ONLY GLOBAL_VAR(server_version_source_revision), CMD_LINE_HELP_ONLY, IN_SYSTEM_CHARSET, DEFAULT(SOURCE_REVISION)); -- cgit v1.2.1 From e12390a3bd895c6a6ecb04806fc947d63b243e94 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 22 Sep 2017 10:48:34 +0400 Subject: MDEV-13864 (partial) Change Item_func_case to store the predicant in args[0] This is a preparatory step for MDEV-13864. It does not change behavior in any ways. It simply splits methods into smaller peaces. The intent of this separate patch is to make more readable the main patch for MDEV-13864 (which will actually move the predicant to args[0]). 1. Splitting fix_length_and_dec() into smaller pieces, adding: - bool aggregate_then_and_else_arguments(THD *thd); - bool aggregate_switch_and_when_arguments(THD *thd); 2. Splitting find_item() into smaller pieces, adding: - Item *find_item_searched(); - Item *find_item_simple(); 3. Splitting print() into smaller pieces, adding: - void print_when_then_arguments(String *str, enum_query_type query_type, Item **items, uint count); - void print_else_argument(String *str, enum_query_type query_type, Item *item) 4. Moving the maybe_null handling part related to ELSE from fix_length_and_dec() to fix_fields(), as in all other Item_func's. 5. Removing the unused String* argument from find_item(). 6. Moving find_item() from public to private, as it's not needed outside. --- sql/item_cmpfunc.cc | 252 ++++++++++++++++++++++++++++------------------------ sql/item_cmpfunc.h | 13 ++- 2 files changed, 146 insertions(+), 119 deletions(-) diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index e8cd2be2650..7b9604b46a8 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2856,26 +2856,25 @@ Item_func_case::Item_func_case(THD *thd, List &list, failed */ -Item *Item_func_case::find_item(String *str) + +Item *Item_func_case::find_item_searched() { - if (first_expr_num == -1) - { - for (uint i=0 ; i < ncases ; i+=2) - { - // No expression between CASE and the first WHEN - if (args[i]->val_bool()) - return args[i+1]; - continue; - } - } - else + uint count= arg_count / 2; + for (uint i= 0 ; i < count ; i++) { - /* Compare every WHEN argument with it and return the first match */ - uint idx; - if (!Predicant_to_list_comparator::cmp(this, &idx, NULL)) - return args[idx + 1]; + if (args[2 * i]->val_bool()) + return args[2 * i + 1]; } - // No, WHEN clauses all missed, return ELSE expression + return else_expr_num != -1 ? args[else_expr_num] : 0; +} + + +Item *Item_func_case::find_item_simple() +{ + /* Compare every WHEN argument with it and return the first match */ + uint idx; + if (!Predicant_to_list_comparator::cmp(this, &idx, NULL)) + return args[idx + 1]; return else_expr_num != -1 ? args[else_expr_num] : 0; } @@ -2884,7 +2883,7 @@ String *Item_func_case::str_op(String *str) { DBUG_ASSERT(fixed == 1); String *res; - Item *item=find_item(str); + Item *item= find_item(); if (!item) { @@ -2901,9 +2900,7 @@ String *Item_func_case::str_op(String *str) longlong Item_func_case::int_op() { DBUG_ASSERT(fixed == 1); - char buff[MAX_FIELD_WIDTH]; - String dummy_str(buff,sizeof(buff),default_charset()); - Item *item=find_item(&dummy_str); + Item *item= find_item(); longlong res; if (!item) @@ -2919,9 +2916,7 @@ longlong Item_func_case::int_op() double Item_func_case::real_op() { DBUG_ASSERT(fixed == 1); - char buff[MAX_FIELD_WIDTH]; - String dummy_str(buff,sizeof(buff),default_charset()); - Item *item=find_item(&dummy_str); + Item *item= find_item(); double res; if (!item) @@ -2938,9 +2933,7 @@ double Item_func_case::real_op() my_decimal *Item_func_case::decimal_op(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); - char buff[MAX_FIELD_WIDTH]; - String dummy_str(buff, sizeof(buff), default_charset()); - Item *item= find_item(&dummy_str); + Item *item= find_item(); my_decimal *res; if (!item) @@ -2958,9 +2951,7 @@ my_decimal *Item_func_case::decimal_op(my_decimal *decimal_value) bool Item_func_case::date_op(MYSQL_TIME *ltime, uint fuzzydate) { DBUG_ASSERT(fixed == 1); - char buff[MAX_FIELD_WIDTH]; - String dummy_str(buff, sizeof(buff), default_charset()); - Item *item= find_item(&dummy_str); + Item *item= find_item(); if (!item) return (null_value= true); return (null_value= item->get_date_with_conversion(ltime, fuzzydate)); @@ -2979,6 +2970,10 @@ bool Item_func_case::fix_fields(THD *thd, Item **ref) return TRUE; bool res= Item_func::fix_fields(thd, ref); + + if (else_expr_num == -1 || args[else_expr_num]->maybe_null) + maybe_null= 1; + /* Call check_stack_overrun after fix_fields to be sure that stack variable is not optimized away @@ -3032,30 +3027,38 @@ bool Item_func_case::prepare_predicant_and_values(THD *thd, uint *found_types) void Item_func_case::fix_length_and_dec() { - Item **agg= arg_buffer; - uint nagg; THD *thd= current_thd; - m_found_types= 0; - if (else_expr_num == -1 || args[else_expr_num]->maybe_null) - maybe_null= 1; + if (!aggregate_then_and_else_arguments(thd) && + first_expr_num != -1) + aggregate_switch_and_when_arguments(thd); +} - /* - Aggregate all THEN and ELSE expression types - and collations when string result - */ + +/* + Aggregate all THEN and ELSE expression types + and collations when string result + @param THD - current thd + @param offs - the offset of the leftmost THEN argument + @paran count - the number or THEN..ELSE pairs +*/ +bool Item_func_case::aggregate_then_and_else_arguments(THD *thd) +{ + Item **agg= arg_buffer; + uint nagg; + for (nagg= 0 ; nagg < ncases/2 ; nagg++) agg[nagg]= args[nagg*2+1]; - + if (else_expr_num != -1) agg[nagg++]= args[else_expr_num]; if (aggregate_for_result(func_name(), agg, nagg, true)) - return; + return true; if (fix_attributes(agg, nagg)) - return; + return true; /* Copy all modified THEN and ELSE items back to args[] array. @@ -3066,81 +3069,85 @@ void Item_func_case::fix_length_and_dec() if (else_expr_num != -1) change_item_tree_if_needed(thd, &args[else_expr_num], agg[nagg++]); + return false; +} - /* - Aggregate first expression and all WHEN expression types - and collations when string comparison - */ - if (first_expr_num != -1) - { - if (prepare_predicant_and_values(thd, &m_found_types)) - { - /* - If Predicant_to_list_comparator() fails to prepare components, - it must put an error into the diagnostics area. This is needed - to make fix_fields() catches such errors. - */ - DBUG_ASSERT(thd->is_error()); - return; - } - - agg[0]= args[first_expr_num]; +/* + Aggregate the predicant expression and all WHEN expression types + and collations when string comparison +*/ +bool Item_func_case::aggregate_switch_and_when_arguments(THD *thd) +{ + Item **agg= arg_buffer; + uint nagg; + if (prepare_predicant_and_values(thd, &m_found_types)) + { /* - As the first expression and WHEN expressions - are intermixed in args[] array THEN and ELSE items, - extract the first expression and all WHEN expressions into - a temporary array, to process them easier. + If Predicant_to_list_comparator() fails to prepare components, + it must put an error into the diagnostics area. This is needed + to make fix_fields() catches such errors. */ - for (nagg= 0; nagg < ncases/2 ; nagg++) - agg[nagg+1]= args[nagg*2]; - nagg++; - if (!(m_found_types= collect_cmp_types(agg, nagg))) - return; - - if (m_found_types & (1U << STRING_RESULT)) - { - /* - If we'll do string comparison, we also need to aggregate - character set and collation for first/WHEN items and - install converters for some of them to cmp_collation when necessary. - This is done because cmp_item compatators cannot compare - strings in two different character sets. - Some examples when we install converters: + DBUG_ASSERT(thd->is_error()); + return true; + } - 1. Converter installed for the first expression: + /* + As the predicant expression and WHEN expressions + are intermixed in args[] array THEN and ELSE items, + extract the first expression and all WHEN expressions into + a temporary array, to process them easier. + */ + agg[0]= args[first_expr_num]; // The predicant + for (nagg= 0; nagg < ncases/2 ; nagg++) + agg[nagg+1]= args[nagg*2]; + nagg++; + if (!(m_found_types= collect_cmp_types(agg, nagg))) + return true; - CASE latin1_item WHEN utf16_item THEN ... END + if (m_found_types & (1U << STRING_RESULT)) + { + /* + If we'll do string comparison, we also need to aggregate + character set and collation for first/WHEN items and + install converters for some of them to cmp_collation when necessary. + This is done because cmp_item compatators cannot compare + strings in two different character sets. + Some examples when we install converters: - is replaced to: + 1. Converter installed for the first expression: - CASE CONVERT(latin1_item USING utf16) WHEN utf16_item THEN ... END + CASE latin1_item WHEN utf16_item THEN ... END - 2. Converter installed for the left WHEN item: + is replaced to: - CASE utf16_item WHEN latin1_item THEN ... END + CASE CONVERT(latin1_item USING utf16) WHEN utf16_item THEN ... END - is replaced to: + 2. Converter installed for the left WHEN item: - CASE utf16_item WHEN CONVERT(latin1_item USING utf16) THEN ... END - */ - if (agg_arg_charsets_for_comparison(cmp_collation, agg, nagg)) - return; - /* - Now copy first expression and all WHEN expressions back to args[] - arrray, because some of the items might have been changed to converters - (e.g. Item_func_conv_charset, or Item_string for constants). - */ - change_item_tree_if_needed(thd, &args[first_expr_num], agg[0]); + CASE utf16_item WHEN latin1_item THEN ... END - for (nagg= 0; nagg < ncases / 2; nagg++) - change_item_tree_if_needed(thd, &args[nagg * 2], agg[nagg + 1]); + is replaced to: - } + CASE utf16_item WHEN CONVERT(latin1_item USING utf16) THEN ... END + */ + if (agg_arg_charsets_for_comparison(cmp_collation, agg, nagg)) + return true; + /* + Now copy first expression and all WHEN expressions back to args[] + arrray, because some of the items might have been changed to converters + (e.g. Item_func_conv_charset, or Item_string for constants). + */ + change_item_tree_if_needed(thd, &args[first_expr_num], agg[0]); - if (make_unique_cmp_items(thd, cmp_collation.collation)) - return; + for (nagg= 0; nagg < ncases / 2; nagg++) + change_item_tree_if_needed(thd, &args[nagg * 2], agg[nagg + 1]); } + + if (make_unique_cmp_items(thd, cmp_collation.collation)) + return true; + + return false; } @@ -3230,10 +3237,30 @@ Item* Item_func_case::propagate_equal_fields(THD *thd, const Context &ctx, COND_ } -/** - @todo - Fix this so that it prints the whole CASE expression -*/ +void Item_func_case::print_when_then_arguments(String *str, + enum_query_type query_type, + Item **items, uint count) +{ + for (uint i=0 ; i < count ; i++) + { + str->append(STRING_WITH_LEN("when ")); + items[i * 2]->print_parenthesised(str, query_type, precedence()); + str->append(STRING_WITH_LEN(" then ")); + items[i * 2 + 1]->print_parenthesised(str, query_type, precedence()); + str->append(' '); + } +} + + +void Item_func_case::print_else_argument(String *str, + enum_query_type query_type, + Item *item) +{ + str->append(STRING_WITH_LEN("else ")); + item->print_parenthesised(str, query_type, precedence()); + str->append(' '); +} + void Item_func_case::print(String *str, enum_query_type query_type) { @@ -3243,20 +3270,9 @@ void Item_func_case::print(String *str, enum_query_type query_type) args[first_expr_num]->print_parenthesised(str, query_type, precedence()); str->append(' '); } - for (uint i=0 ; i < ncases ; i+=2) - { - str->append(STRING_WITH_LEN("when ")); - args[i]->print_parenthesised(str, query_type, precedence()); - str->append(STRING_WITH_LEN(" then ")); - args[i+1]->print_parenthesised(str, query_type, precedence()); - str->append(' '); - } + print_when_then_arguments(str, query_type, &args[0], ncases / 2); if (else_expr_num != -1) - { - str->append(STRING_WITH_LEN("else ")); - args[else_expr_num]->print_parenthesised(str, query_type, precedence()); - str->append(' '); - } + print_else_argument(str, query_type, args[else_expr_num]); str->append(STRING_WITH_LEN("end")); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index c7272f1f2cc..147f23720ec 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2041,6 +2041,17 @@ class Item_func_case :public Item_func_case_expression, Item **arg_buffer; uint m_found_types; bool prepare_predicant_and_values(THD *thd, uint *found_types); + bool aggregate_then_and_else_arguments(THD *thd); + bool aggregate_switch_and_when_arguments(THD *thd); + Item *find_item_searched(); + Item *find_item_simple(); + Item *find_item() + { + return first_expr_num == -1 ? find_item_searched() : find_item_simple(); + } + void print_when_then_arguments(String *str, enum_query_type query_type, + Item **items, uint count); + void print_else_argument(String *str, enum_query_type query_type, Item *item); public: Item_func_case(THD *thd, List &list, Item *first_expr_arg, Item *else_expr_arg); @@ -2055,7 +2066,6 @@ public: const char *func_name() const { return "case"; } enum precedence precedence() const { return BETWEEN_PRECEDENCE; } virtual void print(String *str, enum_query_type query_type); - Item *find_item(String *str); CHARSET_INFO *compare_collation() const { return cmp_collation.collation; } void cleanup() { @@ -2081,6 +2091,7 @@ public: } }; + /* The Item_func_in class implements in_expr IN () -- cgit v1.2.1 From c39a744616d0d36d061b9895809a7c016dc43b5f Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 22 Sep 2017 12:45:34 +0400 Subject: MDEV-13864 (final) Change Item_func_case to store the predicant in args[0] --- mysql-test/r/case.result | 48 +++++++++++++ mysql-test/r/func_debug.result | 14 ++-- mysql-test/t/case.test | 28 ++++++++ sql/item_cmpfunc.cc | 154 +++++++++++++++++++++-------------------- sql/item_cmpfunc.h | 136 ++++++++++++++++++++++++------------ sql/sql_yacc.yy | 34 +++++---- sql/sql_yacc_ora.yy | 62 ++++++----------- 7 files changed, 294 insertions(+), 182 deletions(-) diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index 3214f52e765..ee2db76e517 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -447,3 +447,51 @@ EXECUTE stmt; good was_bad_now_good one one DEALLOCATE PREPARE stmt; +# +# MDEV-13864 Change Item_func_case to store the predicant in args[0] +# +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('b'),('c'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and (case 'a' when 'a' then `test`.`t1`.`a` else 'a' end) = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and (case 'a' when 'a' then 'a' else `test`.`t1`.`a` end) = 'a' +ALTER TABLE t1 MODIFY a VARBINARY(10); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' +DROP TABLE t1; diff --git a/mysql-test/r/func_debug.result b/mysql-test/r/func_debug.result index f55bfca80f5..f1453344e0e 100644 --- a/mysql-test/r/func_debug.result +++ b/mysql-test/r/func_debug.result @@ -1629,8 +1629,8 @@ WHEN -9223372036854775808 THEN 'one' c NULL Warnings: -Note 1105 DBUG: [0] arg=0 handler=0 (bigint) -Note 1105 DBUG: [1] arg=2 handler=1 (decimal) +Note 1105 DBUG: [0] arg=1 handler=0 (bigint) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) DROP TABLE t1; # # MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result @@ -1648,10 +1648,10 @@ CASE TIME'10:20:30' good was_bad_now_good one one Warnings: -Note 1105 DBUG: [0] arg=0 handler=0 (time) -Note 1105 DBUG: [1] arg=2 handler=0 (time) -Note 1105 DBUG: [0] arg=0 handler=0 (time) -Note 1105 DBUG: [1] arg=2 handler=0 (time) -Note 1105 DBUG: [2] arg=4 handler=2 (datetime) +Note 1105 DBUG: [0] arg=1 handler=0 (time) +Note 1105 DBUG: [1] arg=3 handler=0 (time) +Note 1105 DBUG: [0] arg=1 handler=0 (time) +Note 1105 DBUG: [1] arg=3 handler=0 (time) +Note 1105 DBUG: [2] arg=5 handler=2 (datetime) SET SESSION debug_dbug="-d,Predicant_to_list_comparator"; SET SESSION debug_dbug="-d,Item_func_in"; diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index 87f12cd9e38..52db7588855 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -330,3 +330,31 @@ PREPARE stmt FROM "SELECT EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; + +--echo # +--echo # MDEV-13864 Change Item_func_case to store the predicant in args[0] +--echo # + +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('b'),('c'); + +# should propagate the predicant and the WHEN arguments (they are in comparison and use ANY_SUBST) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; + +# should not propagate the THEN and the ELSE arguments (they are not in comparison and use IDENTITY_SUBST) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; + +ALTER TABLE t1 MODIFY a VARBINARY(10); + +# with VARBINARY it should propagate all arguments +# as IDENTITY_SUBST for VARBINARY allows substitution +# of even those arguments that are not in comparison +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE a WHEN 'a' THEN 'a' ELSE 'a' END='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN a THEN 'a' ELSE 'a' END='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN a ELSE 'a' END='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND CASE 'a' WHEN 'a' THEN 'a' ELSE a END='a'; + +DROP TABLE t1; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 7b9604b46a8..f3dafec0020 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2814,27 +2814,6 @@ Item_func_nullif::is_null() } -Item_func_case::Item_func_case(THD *thd, List &list, - Item *first_expr_arg, Item *else_expr_arg): - Item_func_case_expression(thd), - Predicant_to_list_comparator(thd, list.elements/*QQ*/), - first_expr_num(-1), else_expr_num(-1), - m_found_types(0) -{ - ncases= list.elements; - if (first_expr_arg) - { - first_expr_num= list.elements; - list.push_back(first_expr_arg, thd->mem_root); - } - if (else_expr_arg) - { - else_expr_num= list.elements; - list.push_back(else_expr_arg, thd->mem_root); - } - set_arguments(thd, list); -} - /** Find and return matching items for CASE or ELSE item if all compares are failed or NULL if ELSE item isn't defined. @@ -2856,26 +2835,27 @@ Item_func_case::Item_func_case(THD *thd, List &list, failed */ - -Item *Item_func_case::find_item_searched() +Item *Item_func_case_searched::find_item() { - uint count= arg_count / 2; + uint count= when_count(); for (uint i= 0 ; i < count ; i++) { if (args[2 * i]->val_bool()) return args[2 * i + 1]; } - return else_expr_num != -1 ? args[else_expr_num] : 0; + Item **pos= Item_func_case_searched::else_expr_addr(); + return pos ? pos[0] : 0; } -Item *Item_func_case::find_item_simple() +Item *Item_func_case_simple::find_item() { /* Compare every WHEN argument with it and return the first match */ uint idx; if (!Predicant_to_list_comparator::cmp(this, &idx, NULL)) return args[idx + 1]; - return else_expr_num != -1 ? args[else_expr_num] : 0; + Item **pos= Item_func_case_simple::else_expr_addr(); + return pos ? pos[0] : 0; } @@ -2966,12 +2946,13 @@ bool Item_func_case::fix_fields(THD *thd, Item **ref) */ uchar buff[MAX_FIELD_WIDTH*2+sizeof(String)*2+sizeof(String*)*2+sizeof(double)*2+sizeof(longlong)*2]; - if (!(arg_buffer= (Item**) thd->alloc(sizeof(Item*)*(ncases+1)))) + if (!(arg_buffer= (Item**) thd->alloc(sizeof(Item*)*(arg_count)))) return TRUE; bool res= Item_func::fix_fields(thd, ref); - if (else_expr_num == -1 || args[else_expr_num]->maybe_null) + Item **pos= else_expr_addr(); + if (!pos || pos[0]->maybe_null) maybe_null= 1; /* @@ -3006,15 +2987,17 @@ static void change_item_tree_if_needed(THD *thd, } -bool Item_func_case::prepare_predicant_and_values(THD *thd, uint *found_types) +bool Item_func_case_simple::prepare_predicant_and_values(THD *thd, + uint *found_types) { bool have_null= false; uint type_cnt; Type_handler_hybrid_field_type tmp; - add_predicant(this, (uint) first_expr_num); - for (uint i= 0 ; i < ncases / 2; i++) + uint ncases= when_count(); + add_predicant(this, 0); + for (uint i= 0 ; i < ncases; i++) { - if (add_value_skip_null("case..when", this, i * 2, &have_null)) + if (add_value_skip_null("case..when", this, i * 2 + 1, &have_null)) return true; } all_values_added(&tmp, &type_cnt, &m_found_types); @@ -3025,12 +3008,19 @@ bool Item_func_case::prepare_predicant_and_values(THD *thd, uint *found_types) } -void Item_func_case::fix_length_and_dec() +void Item_func_case_searched::fix_length_and_dec() { THD *thd= current_thd; - m_found_types= 0; - if (!aggregate_then_and_else_arguments(thd) && - first_expr_num != -1) + Item **else_ptr= Item_func_case_searched::else_expr_addr(); + aggregate_then_and_else_arguments(thd, &args[1], when_count(), else_ptr); +} + + +void Item_func_case_simple::fix_length_and_dec() +{ + THD *thd= current_thd; + Item **else_ptr= Item_func_case_simple::else_expr_addr(); + if (!aggregate_then_and_else_arguments(thd, &args[2], when_count(), else_ptr)) aggregate_switch_and_when_arguments(thd); } @@ -3039,20 +3029,25 @@ void Item_func_case::fix_length_and_dec() Aggregate all THEN and ELSE expression types and collations when string result - @param THD - current thd - @param offs - the offset of the leftmost THEN argument - @paran count - the number or THEN..ELSE pairs + @param THD - current thd + @param them_expr - the pointer to the leftmost THEN argument in args[] + @param count - the number or THEN..ELSE pairs + @param else_epxr - the pointer to the ELSE arguments in args[] + (or NULL is there is not ELSE) */ -bool Item_func_case::aggregate_then_and_else_arguments(THD *thd) +bool Item_func_case::aggregate_then_and_else_arguments(THD *thd, + Item **then_expr, + uint count, + Item **else_expr) { Item **agg= arg_buffer; uint nagg; - for (nagg= 0 ; nagg < ncases/2 ; nagg++) - agg[nagg]= args[nagg*2+1]; + for (nagg= 0 ; nagg < count ; nagg++) + agg[nagg]= then_expr[nagg * 2]; - if (else_expr_num != -1) - agg[nagg++]= args[else_expr_num]; + if (else_expr) + agg[nagg++]= *else_expr; if (aggregate_for_result(func_name(), agg, nagg, true)) return true; @@ -3061,14 +3056,14 @@ bool Item_func_case::aggregate_then_and_else_arguments(THD *thd) return true; /* - Copy all modified THEN and ELSE items back to args[] array. + Copy all modified THEN and ELSE items back to then_expr[] array. Some of the items might have been changed to Item_func_conv_charset. */ - for (nagg= 0 ; nagg < ncases / 2 ; nagg++) - change_item_tree_if_needed(thd, &args[nagg * 2 + 1], agg[nagg]); + for (nagg= 0 ; nagg < count ; nagg++) + change_item_tree_if_needed(thd, &then_expr[nagg * 2], agg[nagg]); - if (else_expr_num != -1) - change_item_tree_if_needed(thd, &args[else_expr_num], agg[nagg++]); + if (else_expr) + change_item_tree_if_needed(thd, else_expr, agg[nagg++]); return false; } @@ -3077,10 +3072,12 @@ bool Item_func_case::aggregate_then_and_else_arguments(THD *thd) Aggregate the predicant expression and all WHEN expression types and collations when string comparison */ -bool Item_func_case::aggregate_switch_and_when_arguments(THD *thd) +bool Item_func_case_simple::aggregate_switch_and_when_arguments(THD *thd) { Item **agg= arg_buffer; uint nagg; + uint ncases= when_count(); + m_found_types= 0; if (prepare_predicant_and_values(thd, &m_found_types)) { /* @@ -3098,9 +3095,9 @@ bool Item_func_case::aggregate_switch_and_when_arguments(THD *thd) extract the first expression and all WHEN expressions into a temporary array, to process them easier. */ - agg[0]= args[first_expr_num]; // The predicant - for (nagg= 0; nagg < ncases/2 ; nagg++) - agg[nagg+1]= args[nagg*2]; + agg[0]= args[0]; // The predicant + for (nagg= 0; nagg < ncases ; nagg++) + agg[nagg+1]= args[nagg * 2 + 1]; nagg++; if (!(m_found_types= collect_cmp_types(agg, nagg))) return true; @@ -3138,10 +3135,10 @@ bool Item_func_case::aggregate_switch_and_when_arguments(THD *thd) arrray, because some of the items might have been changed to converters (e.g. Item_func_conv_charset, or Item_string for constants). */ - change_item_tree_if_needed(thd, &args[first_expr_num], agg[0]); + change_item_tree_if_needed(thd, &args[0], agg[0]); - for (nagg= 0; nagg < ncases / 2; nagg++) - change_item_tree_if_needed(thd, &args[nagg * 2], agg[nagg + 1]); + for (nagg= 0; nagg < ncases; nagg++) + change_item_tree_if_needed(thd, &args[nagg * 2 + 1], agg[nagg + 1]); } if (make_unique_cmp_items(thd, cmp_collation.collation)) @@ -3151,17 +3148,13 @@ bool Item_func_case::aggregate_switch_and_when_arguments(THD *thd) } -Item* Item_func_case::propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) +Item* Item_func_case_simple::propagate_equal_fields(THD *thd, + const Context &ctx, + COND_EQUAL *cond) { const Type_handler *first_expr_cmp_handler; - if (first_expr_num == -1) - { - // None of the arguments are in a comparison context - Item_args::propagate_equal_fields(thd, Context_identity(), cond); - return this; - } - first_expr_cmp_handler= args[first_expr_num]->type_handler_for_comparison(); + first_expr_cmp_handler= args[0]->type_handler_for_comparison(); for (uint i= 0; i < arg_count; i++) { /* @@ -3171,7 +3164,7 @@ Item* Item_func_case::propagate_equal_fields(THD *thd, const Context &ctx, COND_ CASE ... THEN y1 ... THEN y2 ... THEN y3 ... ELSE y4 END */ Item *new_item= 0; - if ((int) i == first_expr_num) // Then CASE (the switch) argument + if (i == 0) // Then CASE (the switch) argument { /* Cannot replace the CASE (the switch) argument if @@ -3208,7 +3201,7 @@ Item* Item_func_case::propagate_equal_fields(THD *thd, const Context &ctx, COND_ cmp_collation.collation), cond); } - else if ((i % 2) == 0) // WHEN arguments + else if ((i % 2) == 1 && i != arg_count - 1) // WHEN arguments { /* These arguments are in comparison. @@ -3262,17 +3255,26 @@ void Item_func_case::print_else_argument(String *str, } -void Item_func_case::print(String *str, enum_query_type query_type) +void Item_func_case_searched::print(String *str, enum_query_type query_type) { + Item **pos; str->append(STRING_WITH_LEN("case ")); - if (first_expr_num != -1) - { - args[first_expr_num]->print_parenthesised(str, query_type, precedence()); - str->append(' '); - } - print_when_then_arguments(str, query_type, &args[0], ncases / 2); - if (else_expr_num != -1) - print_else_argument(str, query_type, args[else_expr_num]); + print_when_then_arguments(str, query_type, &args[0], when_count()); + if ((pos= Item_func_case_searched::else_expr_addr())) + print_else_argument(str, query_type, pos[0]); + str->append(STRING_WITH_LEN("end")); +} + + +void Item_func_case_simple::print(String *str, enum_query_type query_type) +{ + Item **pos; + str->append(STRING_WITH_LEN("case ")); + args[0]->print_parenthesised(str, query_type, precedence()); + str->append(' '); + print_when_then_arguments(str, query_type, &args[1], when_count()); + if ((pos= Item_func_case_simple::else_expr_addr())) + print_else_argument(str, query_type, pos[0]); str->append(STRING_WITH_LEN("end")); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 147f23720ec..ac992ee03cc 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2016,79 +2016,129 @@ public: /* The class Item_func_case is the CASE ... WHEN ... THEN ... END function implementation. - - When there is no expression between CASE and the first WHEN - (the CASE expression) then this function simple checks all WHEN expressions - one after another. When some WHEN expression evaluated to TRUE then the - value of the corresponding THEN expression is returned. - - When the CASE expression is specified then it is compared to each WHEN - expression individually. When an equal WHEN expression is found - corresponding THEN expression is returned. - In order to do correct comparisons several comparators are used. One for - each result type. Different result types that are used in particular - CASE ... END expression are collected in the fix_length_and_dec() member - function and only comparators for there result types are used. */ -class Item_func_case :public Item_func_case_expression, - public Predicant_to_list_comparator +class Item_func_case :public Item_func_case_expression { - int first_expr_num, else_expr_num; +protected: String tmp_value; - uint ncases; DTCollation cmp_collation; Item **arg_buffer; - uint m_found_types; - bool prepare_predicant_and_values(THD *thd, uint *found_types); - bool aggregate_then_and_else_arguments(THD *thd); - bool aggregate_switch_and_when_arguments(THD *thd); - Item *find_item_searched(); - Item *find_item_simple(); - Item *find_item() - { - return first_expr_num == -1 ? find_item_searched() : find_item_simple(); - } + bool aggregate_then_and_else_arguments(THD *thd, + Item **items, uint count, + Item **else_expr); + virtual Item **else_expr_addr() const= 0; + virtual Item *find_item()= 0; void print_when_then_arguments(String *str, enum_query_type query_type, Item **items, uint count); void print_else_argument(String *str, enum_query_type query_type, Item *item); public: - Item_func_case(THD *thd, List &list, Item *first_expr_arg, - Item *else_expr_arg); + Item_func_case(THD *thd, List &list) + :Item_func_case_expression(thd, list) + { } double real_op(); longlong int_op(); String *str_op(String *); my_decimal *decimal_op(my_decimal *); bool date_op(MYSQL_TIME *ltime, uint fuzzydate); bool fix_fields(THD *thd, Item **ref); - void fix_length_and_dec(); table_map not_null_tables() const { return 0; } const char *func_name() const { return "case"; } enum precedence precedence() const { return BETWEEN_PRECEDENCE; } - virtual void print(String *str, enum_query_type query_type); CHARSET_INFO *compare_collation() const { return cmp_collation.collation; } + bool need_parentheses_in_default() { return true; } + Item *build_clone(THD *thd, MEM_ROOT *mem_root) + { + Item_func_case *clone= (Item_func_case *) Item_func::build_clone(thd, mem_root); + if (clone) + clone->arg_buffer= 0; + return clone; + } +}; + + +/* + CASE WHEN cond THEN res [WHEN cond THEN res...] [ELSE res] END + + Searched CASE checks all WHEN expressions one after another. + When some WHEN expression evaluated to TRUE then the + value of the corresponding THEN expression is returned. +*/ +class Item_func_case_searched: public Item_func_case +{ + uint when_count() const { return arg_count / 2; } + bool with_else() const { return arg_count % 2; } + Item **else_expr_addr() const { return with_else() ? &args[arg_count - 1] : 0; } +public: + Item_func_case_searched(THD *thd, List &list) + :Item_func_case(thd, list) + { + DBUG_ASSERT(arg_count >= 2); + } + void print(String *str, enum_query_type query_type); + void fix_length_and_dec(); + Item *propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) + { + // None of the arguments are in a comparison context + Item_args::propagate_equal_fields(thd, Context_identity(), cond); + return this; + } + Item *find_item(); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy(thd, mem_root, this); } +}; + + +/* + CASE pred WHEN value THEN res [WHEN value THEN res...] [ELSE res] END + + When the predicant expression is specified then it is compared to each WHEN + expression individually. When an equal WHEN expression is found + the corresponding THEN expression is returned. + In order to do correct comparisons several comparators are used. One for + each result type. Different result types that are used in particular + CASE ... END expression are collected in the fix_length_and_dec() member + function and only comparators for there result types are used. +*/ +class Item_func_case_simple: public Item_func_case, + public Predicant_to_list_comparator +{ + uint m_found_types; + uint when_count() const { return (arg_count - 1) / 2; } + bool with_else() const { return arg_count % 2 == 0; } + Item **else_expr_addr() const { return with_else() ? &args[arg_count - 1] : 0; } + bool aggregate_switch_and_when_arguments(THD *thd); + bool prepare_predicant_and_values(THD *thd, uint *found_types); +public: + Item_func_case_simple(THD *thd, List &list) + :Item_func_case(thd, list), + Predicant_to_list_comparator(thd, arg_count), + m_found_types(0) + { + DBUG_ASSERT(arg_count >= 3); + } void cleanup() { - DBUG_ENTER("Item_func_case::cleanup"); + DBUG_ENTER("Item_func_case_simple::cleanup"); Item_func::cleanup(); Predicant_to_list_comparator::cleanup(); DBUG_VOID_RETURN; } - Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond); - bool need_parentheses_in_default() { return true; } - Item *get_copy(THD *thd, MEM_ROOT *mem_root) - { return get_item_copy(thd, mem_root, this); } + void print(String *str, enum_query_type query_type); + void fix_length_and_dec(); + Item *propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond); + Item *find_item(); Item *build_clone(THD *thd, MEM_ROOT *mem_root) { - Item_func_case *clone= (Item_func_case *) Item_func::build_clone(thd, mem_root); - if (clone) - { - clone->arg_buffer= 0; - if (clone->Predicant_to_list_comparator::init_clone(thd, ncases)) - return NULL; - } + Item_func_case_simple *clone= (Item_func_case_simple *) + Item_func_case::build_clone(thd, mem_root); + uint ncases= when_count(); + if (clone && clone->Predicant_to_list_comparator::init_clone(thd, ncases)) + return NULL; return clone; } + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy(thd, mem_root, this); } }; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index c47e881a82c..7e89ad91a17 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1716,7 +1716,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type literal text_literal insert_ident order_ident temporal_literal - simple_ident expr opt_expr opt_else sum_expr in_sum_expr + simple_ident expr sum_expr in_sum_expr variable variable_aux bool_pri predicate bit_expr parenthesized_expr table_wild simple_expr column_default_non_parenthesized_expr udf_expr @@ -1744,7 +1744,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); NUM_literal %type - expr_list opt_udf_expr_list udf_expr_list when_list + expr_list opt_udf_expr_list udf_expr_list when_list when_list_opt_else ident_list ident_list_arg opt_expr_list %type @@ -9419,10 +9419,15 @@ column_default_non_parenthesized_expr: if (!($$= $5.create_typecast_item(thd, $3, Lex->charset))) MYSQL_YYABORT; } - | CASE_SYM opt_expr when_list opt_else END + | CASE_SYM when_list_opt_else END { - $$= new (thd->mem_root) Item_func_case(thd, *$3, $2, $4); - if ($$ == NULL) + if (!($$= new(thd->mem_root) Item_func_case_searched(thd, *$2))) + MYSQL_YYABORT; + } + | CASE_SYM expr when_list_opt_else END + { + $3->push_front($2, thd->mem_root); + if (!($$= new (thd->mem_root) Item_func_case_simple(thd, *$3))) MYSQL_YYABORT; } | CONVERT_SYM '(' expr ',' cast_type ')' @@ -10844,16 +10849,6 @@ ident_list: } ; -opt_expr: - /* empty */ { $$= NULL; } - | expr { $$= $1; } - ; - -opt_else: - /* empty */ { $$= NULL; } - | ELSE expr { $$= $2; } - ; - when_list: WHEN_SYM expr THEN_SYM expr { @@ -10871,6 +10866,15 @@ when_list: } ; +when_list_opt_else: + when_list + | when_list ELSE expr + { + $1->push_back($3, thd->mem_root); + $$= $1; + } + ; + /* Equivalent to in the SQL:2003 standard. */ /* Warning - may return NULL in case of incomplete SELECT */ table_ref: diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 90d509dc775..a475914d369 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1133,7 +1133,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type literal text_literal insert_ident order_ident temporal_literal - simple_ident expr opt_expr opt_else sum_expr in_sum_expr + simple_ident expr sum_expr in_sum_expr variable variable_aux bool_pri predicate bit_expr parenthesized_expr table_wild simple_expr column_default_non_parenthesized_expr udf_expr @@ -1163,7 +1163,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); NUM_literal %type - expr_list opt_udf_expr_list udf_expr_list when_list + expr_list opt_udf_expr_list udf_expr_list when_list when_list_opt_else ident_list ident_list_arg opt_expr_list decode_when_list @@ -9440,10 +9440,15 @@ column_default_non_parenthesized_expr: if (!($$= $5.create_typecast_item(thd, $3, Lex->charset))) MYSQL_YYABORT; } - | CASE_SYM opt_expr when_list opt_else END + | CASE_SYM when_list_opt_else END { - $$= new (thd->mem_root) Item_func_case(thd, *$3, $2, $4); - if ($$ == NULL) + if (!($$= new(thd->mem_root) Item_func_case_searched(thd, *$2))) + MYSQL_YYABORT; + } + | CASE_SYM expr when_list_opt_else END + { + $3->push_front($2, thd->mem_root); + if (!($$= new (thd->mem_root) Item_func_case_simple(thd, *$3))) MYSQL_YYABORT; } | CONVERT_SYM '(' expr ',' cast_type ')' @@ -9459,32 +9464,8 @@ column_default_non_parenthesized_expr: } | DECODE_SYM '(' expr ',' decode_when_list ')' { - if (($5->elements % 2) == 0) - { - // No default expression - $$= new (thd->mem_root) Item_func_case(thd, *$5, $3, NULL); - } - else - { - /* - There is a default expression at the end of the list $5. - Create a new list without the default expression. - */ - List tmp; - List_iterator_fast it(*$5); - for (uint i= 0; i < $5->elements - 1; i++) // copy all but last - { - Item *item= it++; - tmp.push_back(item); - } - /* - Now the new list "tmp" contains only WHEN-THEN pairs, - The default expression is pointed by the iterator "it" - and will be returned by the next call for it++ below. - */ - $$= new (thd->mem_root) Item_func_case(thd, tmp, $3, it++); - } - if ($$ == NULL) + $5->push_front($3, thd->mem_root); + if (!($$= new (thd->mem_root) Item_func_case_simple(thd, *$5))) MYSQL_YYABORT; } | DEFAULT '(' simple_ident ')' @@ -10907,16 +10888,6 @@ ident_list: } ; -opt_expr: - /* empty */ { $$= NULL; } - | expr { $$= $1; } - ; - -opt_else: - /* empty */ { $$= NULL; } - | ELSE expr { $$= $2; } - ; - when_list: WHEN_SYM expr THEN_SYM expr { @@ -10935,6 +10906,15 @@ when_list: ; +when_list_opt_else: + when_list + | when_list ELSE expr + { + $1->push_back($3, thd->mem_root); + $$= $1; + } + ; + decode_when_list: expr ',' expr { -- cgit v1.2.1 From 884bd1d61b333f3f016269e8710c3fc4ce0469b4 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Sat, 23 Sep 2017 00:55:28 +0400 Subject: MDEV-13863 sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent --- .../suite/compat/oracle/r/func_decode.result | 146 ++++++++++++++++++++- mysql-test/suite/compat/oracle/t/func_decode.test | 77 +++++++++++ sql/item.h | 17 ++- sql/item_cmpfunc.cc | 57 +++++++- sql/item_cmpfunc.h | 90 ++++++++++++- sql/item_create.cc | 29 ++++ sql/sql_yacc_ora.yy | 2 +- 7 files changed, 406 insertions(+), 12 deletions(-) diff --git a/mysql-test/suite/compat/oracle/r/func_decode.result b/mysql-test/suite/compat/oracle/r/func_decode.result index c4bfb713e61..b49bad93627 100644 --- a/mysql-test/suite/compat/oracle/r/func_decode.result +++ b/mysql-test/suite/compat/oracle/r/func_decode.result @@ -28,6 +28,150 @@ EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select case 12 when 10 then 'x10' when 11 then 'x11' else 'def' end AS "DECODE(12,10,'x10',11,'x11','def')" +Note 1003 select decode_oracle(12,10,'x10',11,'x11','def') AS "DECODE(12,10,'x10',11,'x11','def')" CREATE TABLE decode (decode int); DROP TABLE decode; +# +# MDEV-13863 sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent +# +SELECT DECODE(10); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +SELECT DECODE(10,10); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +SELECT DECODE_ORACLE(10); +ERROR 42000: Incorrect parameter count in the call to native function 'DECODE_ORACLE' +SELECT DECODE_ORACLE(10,10); +ERROR 42000: Incorrect parameter count in the call to native function 'DECODE_ORACLE' +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select decode_oracle(12,10,'x10',11,'x11') AS "DECODE(12,10,'x10',11,'x11')" +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select decode_oracle(12,10,'x10',11,'x11','def') AS "DECODE(12,10,'x10',11,'x11','def')" +EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select decode_oracle(12,10,'x10',11,'x11') AS "DECODE_ORACLE(12,10,'x10',11,'x11')" +EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11','def'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select decode_oracle(12,10,'x10',11,'x11','def') AS "DECODE_ORACLE(12,10,'x10',11,'x11','def')" +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS +SELECT +DECODE(a,1,'x1',NULL,'xNULL') AS d1, +DECODE(a,1,'x1',NULL,'xNULL','xELSE') AS d2, +DECODE_ORACLE(a,1,'x1',NULL,'xNULL') AS d3, +DECODE_ORACLE(a,1,'x1',NULL,'xNULL','xELSE') AS d4 +FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE VIEW "v1" AS select decode_oracle("t1"."a",1,'x1',NULL,'xNULL') AS "d1",decode_oracle("t1"."a",1,'x1',NULL,'xNULL','xELSE') AS "d2",decode_oracle("t1"."a",1,'x1',NULL,'xNULL') AS "d3",decode_oracle("t1"."a",1,'x1',NULL,'xNULL','xELSE') AS "d4" from "t1" latin1 latin1_swedish_ci +DROP VIEW v1; +DROP TABLE t1; +SELECT DECODE(TIME'10:20:31','10:20:31','then1','10:20:32','then2','def'); +DECODE(TIME'10:20:31','10:20:31','then1','10:20:32','then2','def') +then1 +SELECT DECODE(TIME'10:20:32','10:20:31','then1','10:20:32','then2','def'); +DECODE(TIME'10:20:32','10:20:31','then1','10:20:32','then2','def') +then2 +SELECT DECODE(TIME'10:20:33','10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def'); +DECODE(TIME'10:20:33','10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def') +then3 +SELECT DECODE(NULL,TIME'10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def'); +DECODE(NULL,TIME'10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def') +then2NULL +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:31','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def'); +DECODE(TIMESTAMP'2001-01-01 10:20:31','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def') +then1 +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:32','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def'); +DECODE(TIMESTAMP'2001-01-01 10:20:32','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def') +then2 +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:33','2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def'); +DECODE(TIMESTAMP'2001-01-01 10:20:33','2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def') +then3 +SELECT DECODE(NULL,TIMESTAMP'2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def'); +DECODE(NULL,TIMESTAMP'2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def') +then2NULL +SELECT DECODE('w1','w1','then1','w2','then2','def'); +DECODE('w1','w1','then1','w2','then2','def') +then1 +SELECT DECODE('w2','w1','then1','w2','then2','def'); +DECODE('w2','w1','then1','w2','then2','def') +then2 +SELECT DECODE('w3','w1','then1',NULL,'then2NULL','w3','then3','def'); +DECODE('w3','w1','then1',NULL,'then2NULL','w3','then3','def') +then3 +SELECT DECODE(NULL,'w1','then1',NULL,'then2NULL','w3','then3','def'); +DECODE(NULL,'w1','then1',NULL,'then2NULL','w3','then3','def') +then2NULL +SELECT DECODE(1,1,'then1',2,'then2','def'); +DECODE(1,1,'then1',2,'then2','def') +then1 +SELECT DECODE(2,1,'then1',2,'then2','def'); +DECODE(2,1,'then1',2,'then2','def') +then2 +SELECT DECODE(3,1,'then1',NULL,'then2NULL',3,'then3','def'); +DECODE(3,1,'then1',NULL,'then2NULL',3,'then3','def') +then3 +SELECT DECODE(NULL,1,'then1',NULL,'then2NULL',3,'then3','def'); +DECODE(NULL,1,'then1',NULL,'then2NULL',3,'then3','def') +then2NULL +SELECT DECODE(CAST(NULL AS SIGNED),1,'then1',NULL,'then2NULL',3,'then3','def'); +DECODE(CAST(NULL AS SIGNED),1,'then1',NULL,'then2NULL',3,'then3','def') +then2NULL +SELECT DECODE(1.0,1.0,'then1',2.0,'then2','def'); +DECODE(1.0,1.0,'then1',2.0,'then2','def') +then1 +SELECT DECODE(2.0,1.0,'then1',2.0,'then2','def'); +DECODE(2.0,1.0,'then1',2.0,'then2','def') +then2 +SELECT DECODE(3.0,1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +DECODE(3.0,1.0,'then1',NULL,'then2NULL',3.0,'then3','def') +then3 +SELECT DECODE(NULL,1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +DECODE(NULL,1.0,'then1',NULL,'then2NULL',3.0,'then3','def') +then2NULL +SELECT DECODE(CAST(NULL AS DECIMAL),1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +DECODE(CAST(NULL AS DECIMAL),1.0,'then1',NULL,'then2NULL',3.0,'then3','def') +then2NULL +SELECT DECODE(1e0,1e0,'then1',2e0,'then2','def'); +DECODE(1e0,1e0,'then1',2e0,'then2','def') +then1 +SELECT DECODE(2e0,1e0,'then1',2e0,'then2','def'); +DECODE(2e0,1e0,'then1',2e0,'then2','def') +then2 +SELECT DECODE(3e0,1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +DECODE(3e0,1e0,'then1',NULL,'then2NULL',3e0,'then3','def') +then3 +SELECT DECODE(NULL,1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +DECODE(NULL,1e0,'then1',NULL,'then2NULL',3e0,'then3','def') +then2NULL +SELECT DECODE(CAST(NULL AS DOUBLE),1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +DECODE(CAST(NULL AS DOUBLE),1e0,'then1',NULL,'then2NULL',3e0,'then3','def') +then2NULL +SELECT DECODE(NULL,NULL,1,2) FROM DUAL; +DECODE(NULL,NULL,1,2) +1 +SELECT DECODE(NULL,10,10,NULL,1,2) FROM DUAL; +DECODE(NULL,10,10,NULL,1,2) +1 +SELECT DECODE_ORACLE(NULL,NULL,1,2) FROM DUAL; +DECODE_ORACLE(NULL,NULL,1,2) +1 +SELECT DECODE_ORACLE(NULL,10,10,NULL,1,2) FROM DUAL; +DECODE_ORACLE(NULL,10,10,NULL,1,2) +1 +CREATE OR REPLACE TABLE t1 (a VARCHAR(10) DEFAULT NULL); +INSERT INTO t1 VALUES (NULL),(1); +SELECT a, DECODE(a,NULL,1,2) FROM t1; +a DECODE(a,NULL,1,2) +NULL 1 +1 2 +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_decode.test b/mysql-test/suite/compat/oracle/t/func_decode.test index ae05cb2c3d1..1d49cdd2102 100644 --- a/mysql-test/suite/compat/oracle/t/func_decode.test +++ b/mysql-test/suite/compat/oracle/t/func_decode.test @@ -19,3 +19,80 @@ EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def'); CREATE TABLE decode (decode int); DROP TABLE decode; + + +--echo # +--echo # MDEV-13863 sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent +--echo # + +--error ER_PARSE_ERROR +SELECT DECODE(10); +--error ER_PARSE_ERROR +SELECT DECODE(10,10); + +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE_ORACLE(10); +--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT +SELECT DECODE_ORACLE(10,10); + + +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11'); +EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def'); +EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11'); +EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11','def'); + +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS + SELECT + DECODE(a,1,'x1',NULL,'xNULL') AS d1, + DECODE(a,1,'x1',NULL,'xNULL','xELSE') AS d2, + DECODE_ORACLE(a,1,'x1',NULL,'xNULL') AS d3, + DECODE_ORACLE(a,1,'x1',NULL,'xNULL','xELSE') AS d4 + FROM t1; +SHOW CREATE VIEW v1; +DROP VIEW v1; +DROP TABLE t1; + +SELECT DECODE(TIME'10:20:31','10:20:31','then1','10:20:32','then2','def'); +SELECT DECODE(TIME'10:20:32','10:20:31','then1','10:20:32','then2','def'); +SELECT DECODE(TIME'10:20:33','10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def'); +SELECT DECODE(NULL,TIME'10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def'); + +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:31','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def'); +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:32','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def'); +SELECT DECODE(TIMESTAMP'2001-01-01 10:20:33','2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def'); +SELECT DECODE(NULL,TIMESTAMP'2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def'); + +SELECT DECODE('w1','w1','then1','w2','then2','def'); +SELECT DECODE('w2','w1','then1','w2','then2','def'); +SELECT DECODE('w3','w1','then1',NULL,'then2NULL','w3','then3','def'); +SELECT DECODE(NULL,'w1','then1',NULL,'then2NULL','w3','then3','def'); + +SELECT DECODE(1,1,'then1',2,'then2','def'); +SELECT DECODE(2,1,'then1',2,'then2','def'); +SELECT DECODE(3,1,'then1',NULL,'then2NULL',3,'then3','def'); +SELECT DECODE(NULL,1,'then1',NULL,'then2NULL',3,'then3','def'); +SELECT DECODE(CAST(NULL AS SIGNED),1,'then1',NULL,'then2NULL',3,'then3','def'); + +SELECT DECODE(1.0,1.0,'then1',2.0,'then2','def'); +SELECT DECODE(2.0,1.0,'then1',2.0,'then2','def'); +SELECT DECODE(3.0,1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +SELECT DECODE(NULL,1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); +SELECT DECODE(CAST(NULL AS DECIMAL),1.0,'then1',NULL,'then2NULL',3.0,'then3','def'); + +SELECT DECODE(1e0,1e0,'then1',2e0,'then2','def'); +SELECT DECODE(2e0,1e0,'then1',2e0,'then2','def'); +SELECT DECODE(3e0,1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +SELECT DECODE(NULL,1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); +SELECT DECODE(CAST(NULL AS DOUBLE),1e0,'then1',NULL,'then2NULL',3e0,'then3','def'); + +SELECT DECODE(NULL,NULL,1,2) FROM DUAL; +SELECT DECODE(NULL,10,10,NULL,1,2) FROM DUAL; + +SELECT DECODE_ORACLE(NULL,NULL,1,2) FROM DUAL; +SELECT DECODE_ORACLE(NULL,10,10,NULL,1,2) FROM DUAL; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(10) DEFAULT NULL); +INSERT INTO t1 VALUES (NULL),(1); +SELECT a, DECODE(a,NULL,1,2) FROM t1; +DROP TABLE t1; diff --git a/sql/item.h b/sql/item.h index f691333525c..5fb21858a7a 100644 --- a/sql/item.h +++ b/sql/item.h @@ -56,8 +56,23 @@ struct st_value C_MODE_END +class Value: public st_value +{ +public: + bool is_null() const { return m_type == DYN_COL_NULL; } + bool is_longlong() const + { + return m_type == DYN_COL_UINT || m_type == DYN_COL_INT; + } + bool is_double() const { return m_type == DYN_COL_DOUBLE; } + bool is_temporal() const { return m_type == DYN_COL_DATETIME; } + bool is_string() const { return m_type == DYN_COL_STRING; } + bool is_decimal() const { return m_type == DYN_COL_DECIMAL; } +}; + + template -class ValueBuffer: public st_value +class ValueBuffer: public Value { char buffer[buffer_size]; void reset_buffer() diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f3dafec0020..47c9fd57ee9 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2859,6 +2859,16 @@ Item *Item_func_case_simple::find_item() } +Item *Item_func_decode_oracle::find_item() +{ + uint idx; + if (!Predicant_to_list_comparator::cmp_nulls_equal(this, &idx)) + return args[idx + 1]; + Item **pos= Item_func_decode_oracle::else_expr_addr(); + return pos ? pos[0] : 0; +} + + String *Item_func_case::str_op(String *str) { DBUG_ASSERT(fixed == 1); @@ -2988,7 +2998,8 @@ static void change_item_tree_if_needed(THD *thd, bool Item_func_case_simple::prepare_predicant_and_values(THD *thd, - uint *found_types) + uint *found_types, + bool nulls_equal) { bool have_null= false; uint type_cnt; @@ -2997,7 +3008,9 @@ bool Item_func_case_simple::prepare_predicant_and_values(THD *thd, add_predicant(this, 0); for (uint i= 0 ; i < ncases; i++) { - if (add_value_skip_null("case..when", this, i * 2 + 1, &have_null)) + if (nulls_equal ? + add_value("case..when", this, i * 2 + 1) : + add_value_skip_null("case..when", this, i * 2 + 1, &have_null)) return true; } all_values_added(&tmp, &type_cnt, &m_found_types); @@ -3021,7 +3034,16 @@ void Item_func_case_simple::fix_length_and_dec() THD *thd= current_thd; Item **else_ptr= Item_func_case_simple::else_expr_addr(); if (!aggregate_then_and_else_arguments(thd, &args[2], when_count(), else_ptr)) - aggregate_switch_and_when_arguments(thd); + aggregate_switch_and_when_arguments(thd, false); +} + + +void Item_func_decode_oracle::fix_length_and_dec() +{ + THD *thd= current_thd; + Item **else_ptr= Item_func_decode_oracle::else_expr_addr(); + if (!aggregate_then_and_else_arguments(thd, &args[2], when_count(), else_ptr)) + aggregate_switch_and_when_arguments(thd, true); } @@ -3072,13 +3094,14 @@ bool Item_func_case::aggregate_then_and_else_arguments(THD *thd, Aggregate the predicant expression and all WHEN expression types and collations when string comparison */ -bool Item_func_case_simple::aggregate_switch_and_when_arguments(THD *thd) +bool Item_func_case_simple::aggregate_switch_and_when_arguments(THD *thd, + bool nulls_eq) { Item **agg= arg_buffer; uint nagg; uint ncases= when_count(); m_found_types= 0; - if (prepare_predicant_and_values(thd, &m_found_types)) + if (prepare_predicant_and_values(thd, &m_found_types, nulls_eq)) { /* If Predicant_to_list_comparator() fails to prepare components, @@ -3962,6 +3985,14 @@ void cmp_item_decimal::store_value(Item *item) } +int cmp_item_decimal::cmp_not_null(const Value *val) +{ + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_decimal()); + return my_decimal_cmp(&value, &val->m_decimal); +} + + int cmp_item_decimal::cmp(Item *arg) { my_decimal tmp_buf, *tmp= arg->val_decimal(&tmp_buf); @@ -3993,6 +4024,14 @@ void cmp_item_temporal::store_value_internal(Item *item, } +int cmp_item_datetime::cmp_not_null(const Value *val) +{ + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_temporal()); + return value != pack_time(&val->value.m_time); +} + + int cmp_item_datetime::cmp(Item *arg) { const bool rc= value != arg->val_datetime_packed(); @@ -4000,6 +4039,14 @@ int cmp_item_datetime::cmp(Item *arg) } +int cmp_item_time::cmp_not_null(const Value *val) +{ + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_temporal()); + return value != pack_time(&val->value.m_time); +} + + int cmp_item_time::cmp(Item *arg) { const bool rc= value != arg->val_time_packed(); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index ac992ee03cc..0756899f47c 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1475,6 +1475,7 @@ public: "stored argument's value <> item's value" */ virtual int cmp(Item *item)= 0; + virtual int cmp_not_null(const Value *value)= 0; // for optimized IN with row virtual int compare(cmp_item *item)= 0; virtual cmp_item *make_same()= 0; @@ -1519,6 +1520,12 @@ public: value_res= item->val_str(&value); m_null_value= item->null_value; } + int cmp_not_null(const Value *val) + { + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_string()); + return sortcmp(value_res, &val->m_string, cmp_charset) != 0; + } int cmp(Item *arg) { char buff[STRING_BUFFER_USUAL_SIZE]; @@ -1555,6 +1562,12 @@ public: value= item->val_int(); m_null_value= item->null_value; } + int cmp_not_null(const Value *val) + { + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_longlong()); + return value != val->value.m_longlong; + } int cmp(Item *arg) { const bool rc= value != arg->val_int(); @@ -1599,6 +1612,7 @@ public: { store_value_internal(item, MYSQL_TYPE_DATETIME); } + int cmp_not_null(const Value *val); int cmp(Item *arg); cmp_item *make_same(); }; @@ -1614,6 +1628,7 @@ public: { store_value_internal(item, MYSQL_TYPE_TIME); } + int cmp_not_null(const Value *val); int cmp(Item *arg); cmp_item *make_same(); }; @@ -1628,6 +1643,12 @@ public: value= item->val_real(); m_null_value= item->null_value; } + int cmp_not_null(const Value *val) + { + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_double()); + return value != val->value.m_double; + } int cmp(Item *arg) { const bool rc= value != arg->val_real(); @@ -1649,6 +1670,7 @@ public: cmp_item_decimal() {} /* Remove gcc warning */ void store_value(Item *item); int cmp(Item *arg); + int cmp_not_null(const Value *val); int compare(cmp_item *c); cmp_item *make_same(); }; @@ -1671,6 +1693,11 @@ public: value_res= item->val_str(&value); m_null_value= item->null_value; } + int cmp_not_null(const Value *val) + { + DBUG_ASSERT(false); + return TRUE; + } int cmp(Item *item) { // Should never be called @@ -1837,7 +1864,24 @@ class Predicant_to_list_comparator return UNKNOWN; return in_item->cmp(args->arguments()[m_comparators[i].m_arg_index]); } - + int cmp_args_nulls_equal(Item_args *args, uint i) + { + Predicant_to_value_comparator *cmp= + &m_comparators[m_comparators[i].m_handler_index]; + cmp_item *in_item= cmp->m_cmp_item; + DBUG_ASSERT(in_item); + Item *predicant= args->arguments()[m_predicant_index]; + Item *arg= args->arguments()[m_comparators[i].m_arg_index]; + ValueBuffer val; + if (m_comparators[i].m_handler_index == i) + in_item->store_value(predicant); + m_comparators[i].m_handler->Item_save_in_value(arg, &val); + if (predicant->null_value && val.is_null()) + return FALSE; // Two nulls are equal + if (predicant->null_value || val.is_null()) + return UNKNOWN; + return in_item->cmp_not_null(&val); + } /** Predicant_to_value_comparator - a comparator for one pair (pred,valueN). See comments above. @@ -2009,7 +2053,22 @@ public: } return true; // Not found } - + /* + Same as above, but treats two NULLs as equal, e.g. as in DECODE_ORACLE(). + */ + bool cmp_nulls_equal(Item_args *args, uint *idx) + { + for (uint i= 0 ; i < m_comparator_count ; i++) + { + DBUG_ASSERT(m_comparators[i].m_handler != NULL); + if (cmp_args_nulls_equal(args, i) == FALSE) + { + *idx= m_comparators[i].m_arg_index; + return false; // Found a matching value + } + } + return true; // Not found + } }; @@ -2103,12 +2162,14 @@ public: class Item_func_case_simple: public Item_func_case, public Predicant_to_list_comparator { +protected: uint m_found_types; uint when_count() const { return (arg_count - 1) / 2; } bool with_else() const { return arg_count % 2 == 0; } Item **else_expr_addr() const { return with_else() ? &args[arg_count - 1] : 0; } - bool aggregate_switch_and_when_arguments(THD *thd); - bool prepare_predicant_and_values(THD *thd, uint *found_types); + bool aggregate_switch_and_when_arguments(THD *thd, bool nulls_equal); + bool prepare_predicant_and_values(THD *thd, uint *found_types, + bool nulls_equal); public: Item_func_case_simple(THD *thd, List &list) :Item_func_case(thd, list), @@ -2142,6 +2203,22 @@ public: }; +class Item_func_decode_oracle: public Item_func_case_simple +{ +public: + Item_func_decode_oracle(THD *thd, List &list) + :Item_func_case_simple(thd, list) + { } + const char *func_name() const { return "decode_oracle"; } + void print(String *str, enum_query_type query_type) + { Item_func::print(str, query_type); } + void fix_length_and_dec(); + Item *find_item(); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy(thd, mem_root, this); } +}; + + /* The Item_func_in class implements in_expr IN () @@ -2321,6 +2398,11 @@ public: bool alloc_comparators(THD *thd, uint n); bool prepare_comparators(THD *, Item **args, uint arg_count); int cmp(Item *arg); + int cmp_not_null(const Value *val) + { + DBUG_ASSERT(false); + return TRUE; + } int compare(cmp_item *arg); cmp_item *make_same(); void store_value_by_template(THD *thd, cmp_item *tmpl, Item *); diff --git a/sql/item_create.cc b/sql/item_create.cc index e0bfe7a4402..b0b33041383 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -623,6 +623,19 @@ protected: }; +class Create_func_decode_oracle : public Create_native_func +{ +public: + virtual Item *create_native(THD *thd, LEX_CSTRING *name, List *item_list); + + static Create_func_decode_oracle s_singleton; + +protected: + Create_func_decode_oracle() {} + virtual ~Create_func_decode_oracle() {} +}; + + class Create_func_concat_ws : public Create_native_func { public: @@ -3894,6 +3907,21 @@ Create_func_decode_histogram::create_2_arg(THD *thd, Item *arg1, Item *arg2) return new (thd->mem_root) Item_func_decode_histogram(thd, arg1, arg2); } +Create_func_decode_oracle Create_func_decode_oracle::s_singleton; + +Item* +Create_func_decode_oracle::create_native(THD *thd, LEX_CSTRING *name, + List *item_list) +{ + uint arg_count= item_list ? item_list->elements : 0; + if (arg_count < 3) + { + my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0), name->str); + return NULL; + } + return new (thd->mem_root) Item_func_decode_oracle(thd, *item_list); +} + Create_func_concat_ws Create_func_concat_ws::s_singleton; Item* @@ -6851,6 +6879,7 @@ static Native_func_registry func_array[] = { { C_STRING_WITH_LEN("DAYOFYEAR") }, BUILDER(Create_func_dayofyear)}, { { C_STRING_WITH_LEN("DEGREES") }, BUILDER(Create_func_degrees)}, { { C_STRING_WITH_LEN("DECODE_HISTOGRAM") }, BUILDER(Create_func_decode_histogram)}, + { { C_STRING_WITH_LEN("DECODE_ORACLE") }, BUILDER(Create_func_decode_oracle)}, { { C_STRING_WITH_LEN("DES_DECRYPT") }, BUILDER(Create_func_des_decrypt)}, { { C_STRING_WITH_LEN("DES_ENCRYPT") }, BUILDER(Create_func_des_encrypt)}, { { C_STRING_WITH_LEN("DIMENSION") }, GEOM_BUILDER(Create_func_dimension)}, diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index a475914d369..8d250abd994 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -9465,7 +9465,7 @@ column_default_non_parenthesized_expr: | DECODE_SYM '(' expr ',' decode_when_list ')' { $5->push_front($3, thd->mem_root); - if (!($$= new (thd->mem_root) Item_func_case_simple(thd, *$5))) + if (!($$= new (thd->mem_root) Item_func_decode_oracle(thd, *$5))) MYSQL_YYABORT; } | DEFAULT '(' simple_ident ')' -- cgit v1.2.1 From b652430f0094e2baa475b6ea3a3d87da24202f76 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Fri, 22 Sep 2017 23:59:28 +0200 Subject: Fix tests --- mysql-test/suite/sys_vars/inc/sysvars_server.inc | 2 ++ mysql-test/suite/sys_vars/r/sysvars_server_embedded.result | 12 ++++++++++++ .../suite/sys_vars/r/sysvars_server_notembedded.result | 12 ++++++++++++ 3 files changed, 26 insertions(+) diff --git a/mysql-test/suite/sys_vars/inc/sysvars_server.inc b/mysql-test/suite/sys_vars/inc/sysvars_server.inc index 76d35f0fd55..b5c800e858a 100644 --- a/mysql-test/suite/sys_vars/inc/sysvars_server.inc +++ b/mysql-test/suite/sys_vars/inc/sysvars_server.inc @@ -30,6 +30,7 @@ select * from information_schema.system_variables 'rand_seed2', 'system_time_zone', 'version_comment', + 'version_source_revision', 'version_compile_machine', 'version_compile_os', 'version_malloc_library', 'version_ssl_library', 'version' ) @@ -53,6 +54,7 @@ select VARIABLE_NAME, VARIABLE_SCOPE, VARIABLE_TYPE, VARIABLE_COMMENT, 'rand_seed2', 'system_time_zone', 'version_comment', + 'version_source_revision', 'version_compile_machine', 'version_compile_os', 'version_malloc_library', 'version_ssl_library', 'version' ) diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index e6291112c68..a04f20dfb3e 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -19,6 +19,7 @@ variable_name not in ( 'rand_seed2', 'system_time_zone', 'version_comment', +'version_source_revision', 'version_compile_machine', 'version_compile_os', 'version_malloc_library', 'version_ssl_library', 'version' ) @@ -4267,6 +4268,7 @@ where variable_name in ( 'rand_seed2', 'system_time_zone', 'version_comment', +'version_source_revision', 'version_compile_machine', 'version_compile_os', 'version_malloc_library', 'version_ssl_library', 'version' ) @@ -4421,6 +4423,16 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME VERSION_SOURCE_REVISION +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE VARCHAR +VARIABLE_COMMENT Source control revision id for MariaDB source code +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST NULL +READ_ONLY YES +COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME VERSION_SSL_LIBRARY VARIABLE_SCOPE GLOBAL VARIABLE_TYPE VARCHAR diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index b53bc508dd1..9c85a4ea302 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -19,6 +19,7 @@ variable_name not in ( 'rand_seed2', 'system_time_zone', 'version_comment', +'version_source_revision', 'version_compile_machine', 'version_compile_os', 'version_malloc_library', 'version_ssl_library', 'version' ) @@ -5149,6 +5150,7 @@ where variable_name in ( 'rand_seed2', 'system_time_zone', 'version_comment', +'version_source_revision', 'version_compile_machine', 'version_compile_os', 'version_malloc_library', 'version_ssl_library', 'version' ) @@ -5303,6 +5305,16 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME VERSION_SOURCE_REVISION +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE VARCHAR +VARIABLE_COMMENT Source control revision id for MariaDB source code +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST NULL +READ_ONLY YES +COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME VERSION_SSL_LIBRARY VARIABLE_SCOPE GLOBAL VARIABLE_TYPE VARCHAR -- cgit v1.2.1