summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/union.result42
-rw-r--r--mysql-test/t/union.test10
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/sql_base.cc11
-rw-r--r--sql/sql_class.h4
-rw-r--r--sql/sql_help.cc2
-rw-r--r--sql/sql_insert.cc4
-rw-r--r--sql/sql_lex.cc29
-rw-r--r--sql/sql_load.cc2
-rw-r--r--sql/sql_olap.cc2
-rw-r--r--sql/sql_prepare.cc2
-rw-r--r--sql/sql_select.cc8
-rw-r--r--sql/sql_union.cc25
-rw-r--r--sql/sql_update.cc2
-rw-r--r--sql/table.h2
15 files changed, 68 insertions, 79 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 23ed06dee6a..9640f18db78 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -539,7 +539,7 @@ aa
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` char(2) default NULL
+ `a` char(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT 12 as a UNION select "aa" as a;
@@ -550,7 +550,7 @@ aa
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` char(2) default NULL
+ `a` char(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT 12 as a UNION select 12.2 as a;
@@ -561,7 +561,7 @@ a
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` double(4,1) default NULL
+ `a` double(4,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
@@ -585,7 +585,7 @@ it2
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `it2` int(11) default NULL
+ `it2` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT i from t2 UNION select f from t2;
@@ -799,7 +799,7 @@ select * from t1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `1` bigint(1) default NULL
+ `1` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select _latin1"test" union select _latin2"testt" ;
@@ -808,7 +808,7 @@ create table t1 select _latin2"test" union select _latin2"testt" ;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `test` char(5) character set latin2 default NULL
+ `test` char(5) character set latin2 NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (s char(200));
@@ -856,7 +856,7 @@ Variable_name Value
Slow_queries 1
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
count(*)
-0
+10
26
show status like 'Slow_queries';
Variable_name Value
@@ -893,3 +893,31 @@ third
2
3
drop table t1;
+create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
+insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
+select col1 n from t1 union select col2 n from t1 order by n;
+n
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+alter table t1 add index myindex (col2);
+select col1 n from t1 union select col2 n from t1 order by n;
+n
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+drop table t1;
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 6c3a22a33cf..c51b779b502 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -484,3 +484,13 @@ insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
select name2 from t1 union all select name from t1 union all select id from t1;
drop table t1;
+#
+# Bug #2809 (UNION fails on MyIsam tables when index on second column from
+# same table)
+#
+create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
+insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
+select col1 n from t1 union select col2 n from t1 order by n;
+alter table t1 add index myindex (col2);
+select col1 n from t1 union select col2 n from t1 order by n;
+drop table t1;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index c79c942979a..d09028aee24 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -665,7 +665,7 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table,
bool insert_fields(THD *thd,TABLE_LIST *tables,
const char *db_name, const char *table_name,
List_iterator<Item> *it);
-bool setup_tables(TABLE_LIST *tables, my_bool reinit);
+bool setup_tables(TABLE_LIST *tables);
int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
List<Item> *sum_func_list, uint wild_num);
int setup_fields(THD *thd, Item** ref_pointer_array, TABLE_LIST *tables,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0ededa80ad6..e2d29e58eca 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2106,8 +2106,6 @@ int setup_fields(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
SYNOPSIS
setup_tables()
tables - tables list
- reinit - true if called for table reinitialization before
- subquery reexecuting
RETURN
0 ok; In this case *map will includes the choosed index
@@ -2122,7 +2120,7 @@ int setup_fields(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
table->map is not set and all Item_field will be regarded as const items.
*/
-bool setup_tables(TABLE_LIST *tables, my_bool reinit)
+bool setup_tables(TABLE_LIST *tables)
{
DBUG_ENTER("setup_tables");
uint tablenr=0;
@@ -2149,13 +2147,6 @@ bool setup_tables(TABLE_LIST *tables, my_bool reinit)
table->keys_in_use_for_query.subtract(map);
}
table->used_keys.intersect(table->keys_in_use_for_query);
- if ((table_list->shared || table->clear_query_id) && !reinit)
- {
- table->clear_query_id= 0;
- /* Clear query_id that may have been set by previous select */
- for (Field **ptr=table->field ; *ptr ; ptr++)
- (*ptr)->query_id=0;
- }
}
if (tablenr > MAX_TABLES)
{
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 9d971b1a196..b8cce3096ef 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1076,11 +1076,11 @@ public:
uint hidden_field_count;
uint group_parts,group_length,group_null_parts;
uint quick_group;
- bool using_indirect_summary_function, all_nulls;
+ bool using_indirect_summary_function;
TMP_TABLE_PARAM()
:copy_funcs_it(copy_funcs), copy_field(0), group_parts(0),
- group_length(0), group_null_parts(0), all_nulls(0)
+ group_length(0), group_null_parts(0)
{}
~TMP_TABLE_PARAM()
{
diff --git a/sql/sql_help.cc b/sql/sql_help.cc
index 3c98b7b0bb4..d5516fe3337 100644
--- a/sql/sql_help.cc
+++ b/sql/sql_help.cc
@@ -686,7 +686,7 @@ int mysqld_help(THD *thd, const char *mask)
goto end;
}
/* Init tables and fields to be usable from items */
- setup_tables(tables, 0);
+ setup_tables(tables);
memcpy((char*) used_fields, (char*) init_used_fields, sizeof(used_fields));
if (init_fields(thd, tables, used_fields, array_elements(used_fields)))
{
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 58c3d143a4f..d13e5ca0f18 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -84,7 +84,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
table_list.grant=table->grant;
thd->dupp_field=0;
- if (setup_tables(&table_list, 0) ||
+ if (setup_tables(&table_list) ||
setup_fields(thd, 0, &table_list,fields,1,0,0))
return -1;
if (thd->dupp_field)
@@ -204,7 +204,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list,
}
if (check_insert_fields(thd,table,fields,*values,1) ||
- setup_tables(insert_table_list, 0) ||
+ setup_tables(insert_table_list) ||
setup_fields(thd, 0, insert_table_list, *values, 0, 0, 0) ||
(duplic == DUP_UPDATE &&
(setup_fields(thd, 0, insert_table_list, update_fields, 0, 0, 0) ||
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 5ffe8c1c365..96a0a86c43d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1376,28 +1376,17 @@ create_total_list_n_last_return(THD *thd_arg,
{
TABLE_LIST *cursor;
next_table= aux->next;
- for (cursor= **result_arg; cursor; cursor= cursor->next)
- if (!strcmp(cursor->db, aux->db) &&
- !strcmp(cursor->real_name, aux->real_name) &&
- !strcmp(cursor->alias, aux->alias))
- break;
- if (!cursor)
+ /* Add not used table to the total table list */
+ if (!(cursor= (TABLE_LIST *) thd->memdup((char*) aux,
+ sizeof(*aux))))
{
- /* Add not used table to the total table list */
- if (!(cursor= (TABLE_LIST *) thd->memdup((char*) aux,
- sizeof(*aux))))
- {
- send_error(thd,0);
- return 1;
- }
- *new_table_list= cursor;
- cursor->table_list= aux; //to be able mark this table as shared
- new_table_list= &cursor->next;
- *new_table_list= 0; // end result list
+ send_error(thd,0);
+ return 1;
}
- else
- // Mark that it's used twice
- cursor->table_list->shared= aux->shared= 1;
+ *new_table_list= cursor;
+ cursor->table_list= aux;
+ new_table_list= &cursor->next;
+ *new_table_list= 0; // end result list
aux->table_list= cursor;
}
}
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 175791ef31e..0c35e99ed08 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -123,7 +123,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
else
{ // Part field list
thd->dupp_field=0;
- if (setup_tables(table_list, 0) ||
+ if (setup_tables(table_list) ||
setup_fields(thd, 0, table_list, fields, 1, 0, 0))
DBUG_RETURN(-1);
if (thd->dupp_field)
diff --git a/sql/sql_olap.cc b/sql/sql_olap.cc
index efc4cf0921d..026ddbae7c7 100644
--- a/sql/sql_olap.cc
+++ b/sql/sql_olap.cc
@@ -152,7 +152,7 @@ int handle_olaps(LEX *lex, SELECT_LEX *select_lex)
List<Item> all_fields(select_lex->item_list);
- if (setup_tables((TABLE_LIST *)select_lex->table_list.first, 0) ||
+ if (setup_tables((TABLE_LIST *)select_lex->table_list.first) ||
setup_fields(lex->thd, 0, (TABLE_LIST *)select_lex->table_list.first,
select_lex->item_list, 1, &all_fields,1) ||
setup_fields(lex->thd, 0, (TABLE_LIST *)select_lex->table_list.first,
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 2cf0000d973..3e51844e8cf 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -679,7 +679,7 @@ static bool mysql_test_upd_fields(Prepared_statement *stmt,
#endif
if (open_and_lock_tables(thd, table_list))
DBUG_RETURN(1);
- if (setup_tables(table_list, 0) ||
+ if (setup_tables(table_list) ||
setup_fields(thd, 0, table_list, fields, 1, 0, 0) ||
setup_conds(thd, table_list, &conds) || thd->net.report_error)
DBUG_RETURN(1);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0e1a25bc42b..6a35eda9ab4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -298,7 +298,7 @@ JOIN::prepare(Item ***rref_pointer_array,
/* Check that all tables, fields, conds and order are ok */
- if (setup_tables(tables_list, 0) ||
+ if (setup_tables(tables_list) ||
setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
select_lex->setup_ref_array(thd, og_num) ||
setup_fields(thd, (*rref_pointer_array), tables_list, fields_list, 1,
@@ -1014,7 +1014,7 @@ JOIN::reinit()
if (unit->select_limit_cnt == HA_POS_ERROR)
select_lex->options&= ~OPTION_FOUND_ROWS;
- if (setup_tables(tables_list, 1))
+ if (setup_tables(tables_list))
DBUG_RETURN(1);
/* Reset of sum functions */
@@ -4964,8 +4964,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
tmp_from_field, group != 0,not_all_columns);
if (!new_field)
goto err; // Should be OOM
- if (param->all_nulls)
- new_field->flags&= ~NOT_NULL_FLAG; // Because of outer join
tmp_from_field++;
*(reg_field++)= new_field;
reclength+=new_field->pack_length();
@@ -5001,8 +4999,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
goto err; // Got OOM
continue; // Some kindf of const item
}
- if (param->all_nulls)
- new_field->flags&= ~NOT_NULL_FLAG; // Because of outer join
if (type == Item::SUM_FUNC_ITEM)
((Item_sum *) item)->result_field= new_field;
tmp_from_field++;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index f2e9016aec3..2f55ec6e211 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -206,7 +206,6 @@ int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
if (first_select->next_select())
{
union_result->tmp_table_param.field_count= types.elements;
- union_result->tmp_table_param.all_nulls= true;
if (!(table= create_tmp_table(thd_arg,
&union_result->tmp_table_param, types,
(ORDER*) 0, !union_option, 1,
@@ -310,30 +309,6 @@ int st_select_lex_unit::exec()
sl->options|= found_rows_for_union;
}
sl->join->select_options=sl->options;
- /*
- As far as union share table space we should reassign table map,
- which can be spoiled by 'prepare' of JOIN of other UNION parts
- if it use same tables
- */
- uint tablenr=0;
- ulong query_id= thd->query_id;
- for (TABLE_LIST *table_list= (TABLE_LIST*) sl->table_list.first;
- table_list;
- table_list= table_list->next, tablenr++)
- {
- if (table_list->shared)
- {
- /*
- review notes: Check it carefully. I still can't understand
- why I should not touch table->used_keys. For my point of
- view we should do here same procedura as it was done by
- setup_table
- */
- setup_table_map(table_list->table, table_list, tablenr);
- }
- for (unsigned int i=0; i < table_list->table->fields; i++)
- table_list->table->field[i]->query_id= query_id;
- }
res= sl->join->optimize();
}
if (!res)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index df7b2cf809f..b228cfad9bb 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -94,7 +94,7 @@ int mysql_update(THD *thd,
tables.table= table;
tables.alias= table_list->alias;
- if (setup_tables(update_table_list, 0) ||
+ if (setup_tables(update_table_list) ||
setup_conds(thd,update_table_list,&conds) ||
thd->lex->select_lex.setup_ref_array(thd, order_num) ||
setup_order(thd, thd->lex->select_lex.ref_pointer_array,
diff --git a/sql/table.h b/sql/table.h
index 00fe803cde2..c027e2ccc44 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -173,7 +173,7 @@ typedef struct st_table_list
GRANT_INFO grant;
thr_lock_type lock_type;
uint outer_join; /* Which join type */
- uint shared; /* Used in union or in multi-upd */
+ uint shared; /* Used in multi-upd */
uint32 db_length, real_name_length;
bool straight; /* optimize with prev table */
bool updating; /* for replicate-do/ignore table */
View Lorry Controller Status