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.cc33
-rw-r--r--sql/sql_update.cc2
-rw-r--r--sql/table.h2
15 files changed, 68 insertions, 87 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 512517991e6..f89d41aa982 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
@@ -879,3 +879,31 @@ d 444 d 454
NULL NULL f 666
NULL NULL g 777
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 60963548eed..7d1818ab724 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -473,3 +473,13 @@ create table t1 ( RID int(11) not null default '0', IID int(11) not null def
insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
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 5e265412be7..54b8c486673 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -668,7 +668,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 19e6e37d43e..b11c71b6ff7 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2147,8 +2147,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
@@ -2163,7 +2161,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;
@@ -2190,13 +2188,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 d79cb186de2..b0899428f32 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 6ed3dca63e3..ccb8296b929 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 7982bd35f30..d93134a3a0c 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1373,28 +1373,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 f910e947720..56a78bfd589 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 69e3469bd7f..7ae25efebc6 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 */
@@ -4985,8 +4985,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();
@@ -5022,8 +5020,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 dc372bcd0b5..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,38 +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);
- }
- /*
- Mark that we are using all fields in the table. This is needed
- for InnoDB when using the same table but different fields in
- an UNION
- QQ: Replace query_id with a bit map of used fields and
- change this for each union part. After this change,
- this code can be removed.
- */
- 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 977dd2595de..f0491bc37d1 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -95,7 +95,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