From 9aeeba48646cceaf7658cdbb4d9cdf47e6957c02 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 30 Sep 2014 21:11:03 +0400 Subject: MDEV-6808: MariaDB 10.0.13 crash with optimizer_use_condition_selectivity > 1 Fix a trivial typo: in table_multi_eq_cond_selectivity(), reset the loop variable between loops. --- sql/sql_select.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index eff1c382945..5477fc85406 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7199,7 +7199,7 @@ double table_multi_eq_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, uint i; KEYUSE *keyuse= pos->key; uint key= keyuse->key; - + // psergey-todo: why does the following loop not include 'i' ??? for (i= 0; i < keyparts; i++) { uint fldno; @@ -7372,6 +7372,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, already taken into account in table->cond_selectivity. */ keyuse= pos->key; + keyparts=0; while (keyuse->table == table && keyuse->key == key) { if (!(keyuse->used_tables & (rem_tables | table->map))) -- cgit v1.2.1 From 5a677749cb6e58f96752236407e0d7e837d97273 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 30 Sep 2014 21:28:36 +0400 Subject: MDEV-6808, part#2. Fix apparently-wrong code in table_multi_eq_selectivity(). --- sql/sql_select.cc | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5477fc85406..918cda808c4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7199,17 +7199,20 @@ double table_multi_eq_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, uint i; KEYUSE *keyuse= pos->key; uint key= keyuse->key; - // psergey-todo: why does the following loop not include 'i' ??? for (i= 0; i < keyparts; i++) { + if (i > 0) + keyuse+= ref_keyuse_steps[i-1]; uint fldno; if (is_hash_join_key_no(key)) fldno= keyuse->keypart; else - fldno= table->key_info[key].key_part[keyparts-1].fieldnr - 1; + fldno= table->key_info[key].key_part[i].fieldnr - 1; if (fld->field_index == fldno) break; } + keyuse= pos->key; + if (i == keyparts) { /* -- cgit v1.2.1 From 70823e1d9126fd3adff0644f8b9798c1f634824c Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Tue, 30 Sep 2014 20:31:14 +0300 Subject: MDEV-5120 Test suite test maria-no-logging fails The reason for the failure was a bug in an include file on debian that causes 'struct stat' to have different sized depending on the environment. This patch fixes so that we always include my_global.h or my_config.h before we include any other files. Other things: - Removed #include in some include files; Better to always do this at the top level to have as few "always-include-this-file-first' files as possible. - Removed usage of some include files that where already included by my_global.h or by other files. client/mysql_plugin.c: Use my_global.h first client/mysqlslap.c: Remove duplicated include files extra/comp_err.c: Remove duplicated include files include/m_string.h: Remove duplicated include files include/maria.h: Remove duplicated include files libmysqld/emb_qcache.cc: Use my_global.h first plugin/semisync/semisync.h: Use my_pthread.h first sql/datadict.cc: Use my_global.h first sql/debug_sync.cc: Use my_global.h first sql/derror.cc: Use my_global.h first sql/des_key_file.cc: Use my_global.h first sql/discover.cc: Use my_global.h first sql/event_data_objects.cc: Use my_global.h first sql/event_db_repository.cc: Use my_global.h first sql/event_parse_data.cc: Use my_global.h first sql/event_queue.cc: Use my_global.h first sql/event_scheduler.cc: Use my_global.h first sql/events.cc: Use my_global.h first sql/field.cc: Use my_global.h first Remove duplicated include files sql/field_conv.cc: Use my_global.h first sql/filesort.cc: Use my_global.h first Remove duplicated include files sql/gstream.cc: Use my_global.h first sql/ha_ndbcluster.cc: Use my_global.h first sql/ha_ndbcluster_binlog.cc: Use my_global.h first sql/ha_ndbcluster_cond.cc: Use my_global.h first sql/ha_partition.cc: Use my_global.h first sql/handler.cc: Use my_global.h first sql/hash_filo.cc: Use my_global.h first sql/hostname.cc: Use my_global.h first sql/init.cc: Use my_global.h first sql/item.cc: Use my_global.h first sql/item_buff.cc: Use my_global.h first sql/item_cmpfunc.cc: Use my_global.h first sql/item_create.cc: Use my_global.h first sql/item_geofunc.cc: Use my_global.h first sql/item_inetfunc.cc: Use my_global.h first sql/item_row.cc: Use my_global.h first sql/item_strfunc.cc: Use my_global.h first sql/item_subselect.cc: Use my_global.h first sql/item_sum.cc: Use my_global.h first sql/item_timefunc.cc: Use my_global.h first sql/item_xmlfunc.cc: Use my_global.h first sql/key.cc: Use my_global.h first sql/lock.cc: Use my_global.h first sql/log.cc: Use my_global.h first sql/log_event.cc: Use my_global.h first sql/log_event_old.cc: Use my_global.h first sql/mf_iocache.cc: Use my_global.h first sql/mysql_install_db.cc: Remove duplicated include files sql/mysqld.cc: Remove duplicated include files sql/net_serv.cc: Remove duplicated include files sql/opt_range.cc: Use my_global.h first sql/opt_subselect.cc: Use my_global.h first sql/opt_sum.cc: Use my_global.h first sql/parse_file.cc: Use my_global.h first sql/partition_info.cc: Use my_global.h first sql/procedure.cc: Use my_global.h first sql/protocol.cc: Use my_global.h first sql/records.cc: Use my_global.h first sql/records.h: Don't include my_global.h Better to do this at the upper level sql/repl_failsafe.cc: Use my_global.h first sql/rpl_filter.cc: Use my_global.h first sql/rpl_gtid.cc: Use my_global.h first sql/rpl_handler.cc: Use my_global.h first sql/rpl_injector.cc: Use my_global.h first sql/rpl_record.cc: Use my_global.h first sql/rpl_record_old.cc: Use my_global.h first sql/rpl_reporting.cc: Use my_global.h first sql/rpl_rli.cc: Use my_global.h first sql/rpl_tblmap.cc: Use my_global.h first sql/rpl_utility.cc: Use my_global.h first sql/set_var.cc: Added comment sql/slave.cc: Use my_global.h first sql/sp.cc: Use my_global.h first sql/sp_cache.cc: Use my_global.h first sql/sp_head.cc: Use my_global.h first sql/sp_pcontext.cc: Use my_global.h first sql/sp_rcontext.cc: Use my_global.h first sql/spatial.cc: Use my_global.h first sql/sql_acl.cc: Use my_global.h first sql/sql_admin.cc: Use my_global.h first sql/sql_analyse.cc: Use my_global.h first sql/sql_audit.cc: Use my_global.h first sql/sql_base.cc: Use my_global.h first sql/sql_binlog.cc: Use my_global.h first sql/sql_bootstrap.cc: Use my_global.h first Use my_global.h first sql/sql_cache.cc: Use my_global.h first sql/sql_class.cc: Use my_global.h first sql/sql_client.cc: Use my_global.h first sql/sql_connect.cc: Use my_global.h first sql/sql_crypt.cc: Use my_global.h first sql/sql_cursor.cc: Use my_global.h first sql/sql_db.cc: Use my_global.h first sql/sql_delete.cc: Use my_global.h first sql/sql_derived.cc: Use my_global.h first sql/sql_do.cc: Use my_global.h first sql/sql_error.cc: Use my_global.h first sql/sql_explain.cc: Use my_global.h first sql/sql_expression_cache.cc: Use my_global.h first sql/sql_handler.cc: Use my_global.h first sql/sql_help.cc: Use my_global.h first sql/sql_insert.cc: Use my_global.h first sql/sql_lex.cc: Use my_global.h first sql/sql_load.cc: Use my_global.h first sql/sql_locale.cc: Use my_global.h first sql/sql_manager.cc: Use my_global.h first sql/sql_parse.cc: Use my_global.h first sql/sql_partition.cc: Use my_global.h first sql/sql_plugin.cc: Added comment sql/sql_prepare.cc: Use my_global.h first sql/sql_priv.h: Added error if we use this before including my_global.h This check is here becasue so many files includes sql_priv.h first. sql/sql_profile.cc: Use my_global.h first sql/sql_reload.cc: Use my_global.h first sql/sql_rename.cc: Use my_global.h first sql/sql_repl.cc: Use my_global.h first sql/sql_select.cc: Use my_global.h first sql/sql_servers.cc: Use my_global.h first sql/sql_show.cc: Added comment sql/sql_signal.cc: Use my_global.h first sql/sql_statistics.cc: Use my_global.h first sql/sql_table.cc: Use my_global.h first sql/sql_tablespace.cc: Use my_global.h first sql/sql_test.cc: Use my_global.h first sql/sql_time.cc: Use my_global.h first sql/sql_trigger.cc: Use my_global.h first sql/sql_udf.cc: Use my_global.h first sql/sql_union.cc: Use my_global.h first sql/sql_update.cc: Use my_global.h first sql/sql_view.cc: Use my_global.h first sql/sys_vars.cc: Added comment sql/table.cc: Use my_global.h first sql/thr_malloc.cc: Use my_global.h first sql/transaction.cc: Use my_global.h first sql/uniques.cc: Use my_global.h first sql/unireg.cc: Use my_global.h first sql/unireg.h: Removed inclusion of my_global.h storage/archive/ha_archive.cc: Added comment storage/blackhole/ha_blackhole.cc: Use my_global.h first storage/csv/ha_tina.cc: Use my_global.h first storage/csv/transparent_file.cc: Use my_global.h first storage/federated/ha_federated.cc: Use my_global.h first storage/federatedx/federatedx_io.cc: Use my_global.h first storage/federatedx/federatedx_io_mysql.cc: Use my_global.h first storage/federatedx/federatedx_io_null.cc: Use my_global.h first storage/federatedx/federatedx_txn.cc: Use my_global.h first storage/heap/ha_heap.cc: Use my_global.h first storage/innobase/handler/handler0alter.cc: Use my_global.h first storage/maria/ha_maria.cc: Use my_global.h first storage/maria/unittest/ma_maria_log_cleanup.c: Remove duplicated include files storage/maria/unittest/test_file.c: Added comment storage/myisam/ha_myisam.cc: Move sql_plugin.h first as this includes my_global.h storage/myisammrg/ha_myisammrg.cc: Use my_global.h first storage/oqgraph/oqgraph_thunk.cc: Use my_config.h and my_global.h first One could not include my_global.h before oqgraph_thunk.h (don't know why) storage/spider/ha_spider.cc: Use my_global.h first storage/spider/hs_client/config.cpp: Use my_global.h first storage/spider/hs_client/escape.cpp: Use my_global.h first storage/spider/hs_client/fatal.cpp: Use my_global.h first storage/spider/hs_client/hstcpcli.cpp: Use my_global.h first storage/spider/hs_client/socket.cpp: Use my_global.h first storage/spider/hs_client/string_util.cpp: Use my_global.h first storage/spider/spd_conn.cc: Use my_global.h first storage/spider/spd_copy_tables.cc: Use my_global.h first storage/spider/spd_db_conn.cc: Use my_global.h first storage/spider/spd_db_handlersocket.cc: Use my_global.h first storage/spider/spd_db_mysql.cc: Use my_global.h first storage/spider/spd_db_oracle.cc: Use my_global.h first storage/spider/spd_direct_sql.cc: Use my_global.h first storage/spider/spd_i_s.cc: Use my_global.h first storage/spider/spd_malloc.cc: Use my_global.h first storage/spider/spd_param.cc: Use my_global.h first storage/spider/spd_ping_table.cc: Use my_global.h first storage/spider/spd_sys_table.cc: Use my_global.h first storage/spider/spd_table.cc: Use my_global.h first storage/spider/spd_trx.cc: Use my_global.h first storage/xtradb/handler/handler0alter.cc: Use my_global.h first storage/xtradb/handler/i_s.cc: Use my_global.h first --- sql/sql_select.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index eff1c382945..a84c727669d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -29,6 +29,7 @@ #pragma implementation // gcc: Class implementation #endif +#include #include "sql_priv.h" #include "unireg.h" #include "sql_select.h" -- cgit v1.2.1 From c0977073e18d070810c20026defc63794154e288 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 3 Oct 2014 23:04:25 +0200 Subject: MDEV-6743 crash in GROUP_CONCAT(IF () ORDER BY 1) backport the new fix from 10.0 --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1b57cb24308..af2b489c8b6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20425,7 +20425,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, order_item->full_name(), thd->where); return TRUE; } - order->item= ref_pointer_array + count - 1; + thd->change_item_tree((Item**)&order->item, (Item*)(ref_pointer_array + count - 1)); order->in_field_list= 1; order->counter= count; order->counter_used= 1; -- cgit v1.2.1 From fc58ba6c76ef752e859146fefc9e6fbc564ab900 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 7 Oct 2014 11:55:39 +0200 Subject: MDEV-5553 A view or procedure with a non existing definer can block "SHOW TABLE STATUS" with an unclear error message Don't double-check privileges for a column in the GROUP BY that refers to the same column in SELECT clause. Privileges were already checked for SELECT clause. --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index af2b489c8b6..3b960d457a7 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20458,7 +20458,7 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, order_item_type == Item::REF_ITEM) { from_field= find_field_in_tables(thd, (Item_ident*) order_item, tables, - NULL, &view_ref, IGNORE_ERRORS, TRUE, + NULL, &view_ref, IGNORE_ERRORS, FALSE, FALSE); if (!from_field) from_field= (Field*) not_found_field; -- cgit v1.2.1 From b261ec393a3a82a1b6a5d51af632b9fa72016115 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 14 Oct 2014 15:11:06 +0400 Subject: MDEV-6484: Assertion `tab->ref.use_count' failed on query with joins, constant table, multi-part key - test_if_skip_sort_order()/create_ref_for_key() may change table access from EQ_REF(index1) to REF(index2). - Doing so doesn't make much sense from optimization POV, but since they are doing it, they should update tab->read_record.unlock_row accordingly. --- sql/sql_select.cc | 3 +++ 1 file changed, 3 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3b960d457a7..a7ba1c734e9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8178,6 +8178,9 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, } else j->type=JT_EQ_REF; + + j->read_record.unlock_row= (j->type == JT_EQ_REF)? + join_read_key_unlock_row : rr_unlock_row; DBUG_RETURN(0); } -- cgit v1.2.1 From af4d469a8d32aa04272d49767e8b8e05e0118441 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 16 Oct 2014 22:58:08 +0400 Subject: MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() - Don't attempt to use BKA for materialized derived tables. The table is neither filled nor fully opened yet, so attempt to call handler->multi_range_read_info() causes crash. --- sql/sql_select.cc | 15 ++++++++++++++- 1 file changed, 14 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 149c6d1126c..127c73ca873 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10614,7 +10614,20 @@ uint check_join_cache_usage(JOIN_TAB *tab, goto no_join_cache; if (tab->ref.is_access_triggered()) goto no_join_cache; - + + /* + Don't use BKA for materialized tables. We could actually have a + meaningful use of BKA when linked join buffers are used. + + The problem is, the temp.table is not filled (actually not even opened + properly) yet, and this doesn't let us call + handler->multi_range_read_info(). It is possible to come up with + estimates, etc. without acessing the table, but it seems not to worth the + effort now. + */ + if (tab->table->pos_in_table_list->is_materialized_derived()) + goto no_join_cache; + if (!tab->is_ref_for_hash_join()) { flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; -- cgit v1.2.1 From 1a996bde1e3721b8d2d05c9477ee2dd7921d3fdc Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 20 Oct 2014 23:35:34 +0400 Subject: MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() (Attempt #2) - Don't attempt to use BKA for materialized derived tables. The table is neither filled nor fully opened yet, so attempt to call handler->multi_range_read_info() causes crash. --- sql/sql_select.cc | 15 ++++++++++++++- 1 file changed, 14 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 149c6d1126c..3a4aa41378d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10549,6 +10549,19 @@ uint check_join_cache_usage(JOIN_TAB *tab, cache_level--; } + /* + Don't use BKA for materialized tables. We could actually have a + meaningful use of BKA when linked join buffers are used. + + The problem is, the temp.table is not filled (actually not even opened + properly) yet, and this doesn't let us call + handler->multi_range_read_info(). It is possible to come up with + estimates, etc. without acessing the table, but it seems not to worth the + effort now. + */ + if (tab->table->pos_in_table_list->is_materialized_derived()) + no_bka_cache= true; + /* Don't use join buffering if we're dictated not to by no_jbuf_after (This is not meaningfully used currently) @@ -10615,7 +10628,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (tab->ref.is_access_triggered()) goto no_join_cache; - if (!tab->is_ref_for_hash_join()) + if (!tab->is_ref_for_hash_join() && !no_bka_cache) { flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; if (tab->table->covering_keys.is_set(tab->ref.key)) -- cgit v1.2.1 From 592b7fbac9cfdd47a1f360e55465b250f2432254 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 28 Oct 2014 14:33:31 -0700 Subject: Fixed bug mdev-6325. Field::selectivity should be set for all fields used in range conditions. --- sql/sql_select.cc | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 127c73ca873..636b5702a93 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4407,8 +4407,7 @@ add_key_field(JOIN *join, if (is_const) { stat[0].const_keys.merge(possible_keys); - if (possible_keys.is_clear_all()) - bitmap_set_bit(&field->table->cond_set, field->field_index); + bitmap_set_bit(&field->table->cond_set, field->field_index); } else if (!eq_func) { -- cgit v1.2.1 From 94c8f33569ec2b8094928463150567d4dcf67398 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 29 Oct 2014 01:20:45 +0300 Subject: MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled - TABLE::create_key_part_by_field() should not set PART_KEY_FLAG in field->flags = The reason is that it is used by hash join code which calls it to create a hash table lookup structure. It doesn't create a real index. = Another caller of the function is TABLE::add_tmp_key(). Made it to set the flag itself. - The differences in join_cache.result could also be observed before this patch: one could put "FLUSH TABLES" before the queries and get exactly the same difference. --- sql/sql_select.cc | 31 +++++++++++++++++++++++++++---- 1 file changed, 27 insertions(+), 4 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 161c00405b0..5263a0c2e30 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3530,6 +3530,7 @@ make_join_statistics(JOIN *join, List &tables_list, join->impossible_where= false; if (conds && const_count) { + COND_EQUAL *orig_cond_equal = join->cond_equal; conds->update_used_tables(); conds= remove_eq_conds(join->thd, conds, &join->cond_value); if (conds && conds->type() == Item::COND_ITEM && @@ -3556,7 +3557,21 @@ make_join_statistics(JOIN *join, List &tables_list, join->cond_equal->current_level.empty(); join->cond_equal->current_level.push_back((Item_equal*) conds); } - } + } + + if (orig_cond_equal != join->cond_equal) + { + /* + If join->cond_equal has changed all references to it from COND_EQUAL + objects associated with ON expressions must be updated. + */ + for (JOIN_TAB **pos=stat_vector+const_count ; (s= *pos) ; pos++) + { + if (*s->on_expr_ref && s->cond_equal && + s->cond_equal->upper_levels == orig_cond_equal) + s->cond_equal->upper_levels= join->cond_equal; + } + } } /* Calc how many (possible) matched records in each table */ @@ -11999,10 +12014,18 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, if (upper) { TABLE_LIST *native_sjm= embedding_sjm(item_equal->context_field); - if (item_const && upper->get_const()) + Item *upper_const= upper->get_const(); + if (item_const && upper_const) { - /* Upper item also has "field_item=const". Don't produce equality here */ - item= 0; + /* + Upper item also has "field_item=const". + Don't produce equality if const is equal to item_const. + */ + Item_func_eq *func= new Item_func_eq(item_const, upper_const); + func->set_cmp_func(); + func->quick_fix_field(); + if (func->val_int()) + item= 0; } else { -- cgit v1.2.1 From a8341dfd6e001faa0b3b0227650eea6721316f04 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 29 Oct 2014 01:46:05 +0300 Subject: MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() (Backport to 5.3) (Attempt #2) - Don't attempt to use BKA for materialized derived tables. The table is neither filled nor fully opened yet, so attempt to call handler->multi_range_read_info() causes crash. --- sql/sql_select.cc | 15 ++++++++++++++- 1 file changed, 14 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5263a0c2e30..9f2e7f531bb 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9663,6 +9663,19 @@ uint check_join_cache_usage(JOIN_TAB *tab, cache_level--; } + /* + Don't use BKA for materialized tables. We could actually have a + meaningful use of BKA when linked join buffers are used. + + The problem is, the temp.table is not filled (actually not even opened + properly) yet, and this doesn't let us call + handler->multi_range_read_info(). It is possible to come up with + estimates, etc. without acessing the table, but it seems not to worth the + effort now. + */ + if (tab->table->pos_in_table_list->is_materialized_derived()) + no_bka_cache= true; + /* Don't use join buffering if we're dictated not to by no_jbuf_after (This is not meaningfully used currently) @@ -9729,7 +9742,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (tab->ref.is_access_triggered()) goto no_join_cache; - if (!tab->is_ref_for_hash_join()) + if (!tab->is_ref_for_hash_join() && !no_bka_cache) { flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; if (tab->table->covering_keys.is_set(tab->ref.key)) -- cgit v1.2.1 From e52b1637e0d59d3a4368fe2ce3a41c47d4041c2a Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Tue, 18 Nov 2014 16:33:29 +0400 Subject: MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns Disallow using indexes on non-temporal columns to optimize ref access, range access and table elimination when the counterpart's cmp_type is TIME_RESULT, e.g.: SELECT * FROM t1 WHERE indexed_int_column=time_expression; Only index on a temporal column can be used to optimize temporal comparison operations. --- sql/sql_select.cc | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ef69f8c11c8..ce17adf44d4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4440,6 +4440,10 @@ add_key_field(JOIN *join, if (!eq_func) // eq_func is NEVER true when num_values > 1 return; + if ((*value)->cmp_type() == TIME_RESULT && + field->cmp_type() != TIME_RESULT) + return; + /* We can't use indexes when comparing a string index to a number or two strings if the effective collation -- cgit v1.2.1 From a8bd285f7c0b65f76df0d81472eab270de12532c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 18 Nov 2014 22:25:41 +0100 Subject: MDEV-6785 Wrong result on 2nd execution of PS with aggregate function, FROM SQ or MERGE view a different fix for view.test --ps-protocol crash (revert the old fix that has caused a regression) --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ce17adf44d4..12cfa12cab9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -633,7 +633,7 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array, res= setup_conds(thd, tables, leaves, conds); if (thd->lex->current_select->first_cond_optimization) { - if (!res && *conds) + if (!res && *conds && ! thd->lex->current_select->merged_into) (*reserved)= (*conds)->exists2in_reserved_items(); else (*reserved)= 0; -- cgit v1.2.1 From 55dd89e9195a36dd977e4485becc701135adddb8 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 19 Nov 2014 10:33:49 +0400 Subject: MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column --- sql/sql_select.cc | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ce17adf44d4..180f9d8642b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4444,6 +4444,28 @@ add_key_field(JOIN *join, field->cmp_type() != TIME_RESULT) return; + /* + Note, for ITEM/ENUM columns: + - field->cmp_type() returns INT_RESULT + - field->result_type() returns STRING_RESULT + - field->type() returns MYSQL_TYPE_STRING + + Using field->real_type() to detect ENUM/SET, + as they need a special handling: + - Conditions between a ENUM/SET filter and a TIME expression + cannot be optimized. They were filtered out in the previous if block. + - It's Ok to use ref access for an ENUM/SET field compared to an + INT/REAL/DECIMAL expression. + - It's Ok to use ref for an ENUM/SET field compared to a STRING + expression if the collation of the field and the collation of + the condition match. + */ + if ((field->real_type() == MYSQL_TYPE_ENUM || + field->real_type() == MYSQL_TYPE_SET) && + (*value)->cmp_type () == STRING_RESULT && + field->charset() != cond->compare_collation()) + return; + /* We can't use indexes when comparing a string index to a number or two strings if the effective collation -- cgit v1.2.1