summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/ps.result46
-rw-r--r--mysql-test/r/sp-error.result12
-rw-r--r--mysql-test/r/sp-prelocking.result23
-rw-r--r--mysql-test/r/sp.result35
-rw-r--r--mysql-test/r/trigger.result24
-rw-r--r--mysql-test/t/ps.test34
-rw-r--r--mysql-test/t/sp-error.test19
-rw-r--r--mysql-test/t/sp-prelocking.test31
-rw-r--r--mysql-test/t/sp.test36
-rw-r--r--mysql-test/t/trigger.test34
-rw-r--r--sql/field.h6
-rw-r--r--sql/sp.cc6
-rw-r--r--sql/sql_insert.cc23
-rw-r--r--sql/sql_load.cc5
-rw-r--r--sql/sql_select.cc6
-rw-r--r--sql/sql_view.cc1
16 files changed, 325 insertions, 16 deletions
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 4d108e06356..9afbca9dcb1 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -880,3 +880,49 @@ select row_count();
row_count()
1
drop table t1;
+create table t1 (a int, b int);
+insert into t1 (a,b) values (2,8),(1,9),(3,7);
+prepare stmt from "select * from t1 order by ?";
+execute stmt using @a;
+a b
+2 8
+1 9
+3 7
+set @a=1;
+execute stmt using @a;
+a b
+1 9
+2 8
+3 7
+set @a=2;
+execute stmt using @a;
+a b
+3 7
+2 8
+1 9
+deallocate prepare stmt;
+select * from t1 order by 1;
+a b
+1 9
+2 8
+3 7
+prepare stmt from "select * from t1 order by ?+1";
+set @a=0;
+execute stmt using @a;
+a b
+2 8
+1 9
+3 7
+set @a=1;
+execute stmt using @a;
+a b
+2 8
+1 9
+3 7
+deallocate prepare stmt;
+select * from t1 order by 1+1;
+a b
+2 8
+1 9
+3 7
+drop table t1;
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 67a6e55b29e..a49b282ddb7 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -634,10 +634,18 @@ flush tables;
return 5;
end|
ERROR 0A000: FLUSH is not allowed in stored function or trigger
-create procedure bug9529_90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123()
+create procedure bug9529_901234567890123456789012345678901234567890123456789012345()
begin
end|
-ERROR 42000: Identifier name 'bug9529_90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' is too long
+ERROR 42000: Identifier name 'bug9529_901234567890123456789012345678901234567890123456789012345' is too long
+drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234|
+create procedure bug17015_0123456789012345678901234567890123456789012345678901234()
+begin
+end|
+show procedure status like 'bug17015%'|
+Db Name Type Definer Modified Created Security_type Comment
+test bug17015_0123456789012345678901234567890123456789012345678901234 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+drop procedure bug17015_0123456789012345678901234567890123456789012345678901234|
drop procedure if exists bug10969|
create procedure bug10969()
begin
diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
index c2614892016..2335513b28a 100644
--- a/mysql-test/r/sp-prelocking.result
+++ b/mysql-test/r/sp-prelocking.result
@@ -214,3 +214,26 @@ drop function f1;
drop function f2;
drop function f3;
drop procedure sp1;
+drop table if exists t1;
+drop view if exists v1, v2, v3;
+drop function if exists bug15683;
+create table t1 (f1 bigint, f2 varchar(20), f3 bigint);
+insert into t1 set f1 = 1, f2 = 'schoenenbourg', f3 = 1;
+create view v1 as select 1 from t1 union all select 1;
+create view v2 as select 1 from v1;
+create view v3 as select 1 as f1 from v2;
+create function bug15683() returns bigint
+begin
+return (select count(*) from v3);
+end|
+prepare stmt from "select bug15683()";
+execute stmt;
+bug15683()
+2
+execute stmt;
+bug15683()
+2
+deallocate prepare stmt;
+drop table t1;
+drop view v1, v2, v3;
+drop function bug15683;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index fd09e9c8e94..3e139f8cce5 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4802,4 +4802,39 @@ f1 bug13575(f1)
3 ccc
drop function bug13575;
drop table t3|
+drop procedure if exists bug16474_1|
+drop procedure if exists bug16474_2|
+delete from t1|
+insert into t1 values ('c', 2), ('b', 3), ('a', 1)|
+create procedure bug16474_1()
+begin
+declare x int;
+select id from t1 order by x;
+end|
+create procedure bug16474_2(x int)
+select id from t1 order by x|
+call bug16474_1()|
+id
+c
+b
+a
+call bug16474_2(1)|
+id
+c
+b
+a
+call bug16474_2(2)|
+id
+c
+b
+a
+drop procedure bug16474_1|
+drop procedure bug16474_2|
+set @x = 2|
+select * from t1 order by @x|
+id data
+c 2
+b 3
+a 1
+delete from t1|
drop table t1,t2;
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 009cde1b747..828db549b05 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -2,6 +2,7 @@ drop table if exists t1, t2, t3, t4;
drop view if exists v1;
drop database if exists mysqltest;
drop function if exists f1;
+drop function if exists f2;
drop procedure if exists p1;
create table t1 (i int);
create trigger trg before insert on t1 for each row set @a:=1;
@@ -928,3 +929,26 @@ create trigger t1_bi before insert on t1 for each row return 0;
ERROR 42000: RETURN is only allowed in a FUNCTION
insert into t1 values (1);
drop table t1;
+create table t1 (a varchar(64), b int);
+create table t2 like t1;
+create trigger t1_ai after insert on t1 for each row
+set @a:= (select max(a) from t1);
+insert into t1 (a) values
+("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"),
+("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe");
+create trigger t2_ai after insert on t2 for each row
+set @a:= (select max(a) from t2);
+insert into t2 select * from t1;
+load data infile '../std_data_ln/words.dat' into table t1 (a);
+drop trigger t1_ai;
+drop trigger t2_ai;
+create function f1() returns int return (select max(b) from t1);
+insert into t1 values
+("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()),
+("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1());
+create function f2() returns int return (select max(b) from t2);
+insert into t2 select a, f2() from t1;
+load data infile '../std_data_ln/words.dat' into table t1 (a) set b:= f1();
+drop table t1;
+drop function f1;
+drop function f2;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index d6b239c31bf..285b5fb0aa3 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -933,4 +933,38 @@ execute ins_call;
select row_count();
drop table t1;
+#
+# BUG#16474: SP crashed MySQL
+# (when using "order by localvar", where 'localvar' is just that.
+# The actual bug test is in sp.test, this is just testing that we get the
+# expected result for prepared statements too, i.e. place holders work as
+# textual substitution. If it's a single integer, it works as the (deprecated)
+# "order by column#", otherwise it's an expression.
+#
+create table t1 (a int, b int);
+insert into t1 (a,b) values (2,8),(1,9),(3,7);
+
+# Will order by index
+prepare stmt from "select * from t1 order by ?";
+execute stmt using @a;
+set @a=1;
+execute stmt using @a;
+set @a=2;
+execute stmt using @a;
+deallocate prepare stmt;
+# For reference:
+select * from t1 order by 1;
+
+# Will not order by index.
+prepare stmt from "select * from t1 order by ?+1";
+set @a=0;
+execute stmt using @a;
+set @a=1;
+execute stmt using @a;
+deallocate prepare stmt;
+# For reference:
+select * from t1 order by 1+1;
+
+drop table t1;
+
# End of 5.0 tests
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index 4b307de2ad0..a4ab5d98922 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -904,12 +904,26 @@ end|
#
# BUG#9529: Stored Procedures: No Warning on truncation of procedure name
# during creation.
-# Note: When using utf8 for mysql.proc, this limit is much higher than before
+# BUG#17015: Routine name truncation not an error
+# When we started using utf8 for mysql.proc, this limit appeared
+# to be higher, but in reality the names were truncated.
--error ER_TOO_LONG_IDENT
-create procedure bug9529_90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123()
+create procedure bug9529_901234567890123456789012345678901234567890123456789012345()
begin
end|
+--disable_warnings
+drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234|
+--enable_warnings
+# Check the upper limit, just to make sure.
+create procedure bug17015_0123456789012345678901234567890123456789012345678901234()
+begin
+end|
+
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bug17015%'|
+drop procedure bug17015_0123456789012345678901234567890123456789012345678901234|
+
#
# BUG#10969: Stored procedures: crash if default() function
@@ -1699,4 +1713,3 @@ create aggregate function bug16896() returns int return 1;
#drop procedure if exists bugNNNN|
#--enable_warnings
#create procedure bugNNNN...
-
diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
index 049dcc576dd..a7215462afb 100644
--- a/mysql-test/t/sp-prelocking.test
+++ b/mysql-test/t/sp-prelocking.test
@@ -241,3 +241,34 @@ drop function f2;
drop function f3;
drop procedure sp1;
+#
+# Bug#15683 "crash, Function on nested VIEWs, Prepared statement"
+# Check that when creating the prelocking list a nested view
+# is not merged until it's used.
+#
+--disable_warnings
+drop table if exists t1;
+drop view if exists v1, v2, v3;
+drop function if exists bug15683;
+--enable_warnings
+create table t1 (f1 bigint, f2 varchar(20), f3 bigint);
+insert into t1 set f1 = 1, f2 = 'schoenenbourg', f3 = 1;
+create view v1 as select 1 from t1 union all select 1;
+create view v2 as select 1 from v1;
+create view v3 as select 1 as f1 from v2;
+
+delimiter |;
+create function bug15683() returns bigint
+begin
+return (select count(*) from v3);
+end|
+delimiter ;|
+
+prepare stmt from "select bug15683()";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+drop table t1;
+drop view v1, v2, v3;
+drop function bug15683;
+
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index ee7b05498fd..9e1afa53149 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5648,6 +5648,42 @@ drop function bug13575;
drop table t3|
#
+# BUG#16474: SP crashed MySQL
+# (when using "order by localvar", where 'localvar' is just that.
+#
+--disable_warnings
+drop procedure if exists bug16474_1|
+drop procedure if exists bug16474_2|
+--enable_warnings
+
+delete from t1|
+insert into t1 values ('c', 2), ('b', 3), ('a', 1)|
+
+create procedure bug16474_1()
+begin
+ declare x int;
+
+ select id from t1 order by x;
+end|
+
+# This does NOT order by column index; variable is an expression.
+create procedure bug16474_2(x int)
+ select id from t1 order by x|
+
+call bug16474_1()|
+call bug16474_2(1)|
+call bug16474_2(2)|
+drop procedure bug16474_1|
+drop procedure bug16474_2|
+
+# For reference: user variables are expressions too and do not affect ordering.
+set @x = 2|
+select * from t1 order by @x|
+
+delete from t1|
+
+
+#
# BUG#NNNN: New bug synopsis
#
#--disable_warnings
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 05a52007eeb..c5925bbd9d5 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -7,6 +7,7 @@ drop table if exists t1, t2, t3, t4;
drop view if exists v1;
drop database if exists mysqltest;
drop function if exists f1;
+drop function if exists f2;
drop procedure if exists p1;
--enable_warnings
@@ -1080,3 +1081,36 @@ create table t1 (i int);
create trigger t1_bi before insert on t1 for each row return 0;
insert into t1 values (1);
drop table t1;
+
+# Test for bug #17764 "Trigger crashes MyISAM table"
+#
+# Table was reported as crashed when it was subject table of trigger invoked
+# by insert statement which was executed with enabled bulk insert mode (which
+# is actually set of optimizations enabled by handler::start_bulk_insert())
+# and this trigger also explicitly referenced it.
+# The same problem arose when table to which bulk insert was done was also
+# referenced in function called by insert statement.
+create table t1 (a varchar(64), b int);
+create table t2 like t1;
+create trigger t1_ai after insert on t1 for each row
+ set @a:= (select max(a) from t1);
+insert into t1 (a) values
+ ("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"),
+ ("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe");
+create trigger t2_ai after insert on t2 for each row
+ set @a:= (select max(a) from t2);
+insert into t2 select * from t1;
+load data infile '../std_data_ln/words.dat' into table t1 (a);
+drop trigger t1_ai;
+drop trigger t2_ai;
+# Test that the problem for functions is fixed as well
+create function f1() returns int return (select max(b) from t1);
+insert into t1 values
+ ("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()),
+ ("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1());
+create function f2() returns int return (select max(b) from t2);
+insert into t2 select a, f2() from t1;
+load data infile '../std_data_ln/words.dat' into table t1 (a) set b:= f1();
+drop table t1;
+drop function f1;
+drop function f2;
diff --git a/sql/field.h b/sql/field.h
index e8dd7f05f99..f53227e5fd6 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -320,6 +320,12 @@ public:
/* convert decimal to longlong with overflow check */
longlong convert_decimal2longlong(const my_decimal *val, bool unsigned_flag,
int *err);
+ /* The max. number of characters */
+ inline uint32 Field::char_length() const
+ {
+ return field_length / charset()->mbmaxlen;
+ }
+
friend bool reopen_table(THD *,struct st_table *,bool);
friend int cre_myisam(my_string name, register TABLE *form, uint options,
ulonglong auto_increment_value);
diff --git a/sql/sp.cc b/sql/sp.cc
index 0446bf94e53..cfcf011032d 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -534,7 +534,11 @@ db_create_routine(THD *thd, int type, sp_head *sp)
ret= SP_GET_FIELD_FAILED;
goto done;
}
- if (sp->m_name.length > table->field[MYSQL_PROC_FIELD_NAME]->field_length)
+
+ if (system_charset_info->cset->numchars(system_charset_info,
+ sp->m_name.str,
+ sp->m_name.str+sp->m_name.length) >
+ table->field[MYSQL_PROC_FIELD_NAME]->char_length())
{
ret= SP_BAD_IDENTIFIER;
goto done;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index b6ff0ecc216..ad9606acb83 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -405,11 +405,15 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
let's *try* to start bulk inserts. It won't necessary
start them as values_list.elements should be greater than
some - handler dependent - threshold.
+ We should not start bulk inserts if this statement uses
+ functions or invokes triggers since they may access
+ to the same table and therefore should not see its
+ inconsistent state created by this optimization.
So we call start_bulk_insert to perform nesessary checks on
values_list.elements, and - if nothing else - to initialize
the code to make the call of end_bulk_insert() below safe.
*/
- if (lock_type != TL_WRITE_DELAYED)
+ if (lock_type != TL_WRITE_DELAYED && !thd->prelocked_mode)
table->file->start_bulk_insert(values_list.elements);
thd->no_trans_update= 0;
@@ -535,7 +539,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
else
#endif
{
- if (table->file->end_bulk_insert() && !error)
+ if (!thd->prelocked_mode && table->file->end_bulk_insert() && !error)
{
table->file->print_error(my_errno,MYF(0));
error=1;
@@ -2190,7 +2194,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
lex->current_select->options|= OPTION_BUFFER_RESULT;
lex->current_select->join->select_options|= OPTION_BUFFER_RESULT;
}
- else
+ else if (!thd->prelocked_mode)
{
/*
We must not yet prepare the result table if it is the same as one of the
@@ -2198,6 +2202,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
indexes on the result table, which may be used during the select, if it
is the same table (Bug #6034). Do the preparation after the select phase
in select_insert::prepare2().
+ We won't start bulk inserts at all if this statement uses functions or
+ should invoke triggers since they may access to the same table too.
*/
table->file->start_bulk_insert((ha_rows) 0);
}
@@ -2238,7 +2244,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
int select_insert::prepare2(void)
{
DBUG_ENTER("select_insert::prepare2");
- if (thd->lex->current_select->options & OPTION_BUFFER_RESULT)
+ if (thd->lex->current_select->options & OPTION_BUFFER_RESULT &&
+ !thd->prelocked_mode)
table->file->start_bulk_insert((ha_rows) 0);
DBUG_RETURN(0);
}
@@ -2341,7 +2348,8 @@ void select_insert::send_error(uint errcode,const char *err)
*/
DBUG_VOID_RETURN;
}
- table->file->end_bulk_insert();
+ if (!thd->prelocked_mode)
+ table->file->end_bulk_insert();
/*
If at least one row has been inserted/modified and will stay in the table
(the table doesn't have transactions) (example: we got a duplicate key
@@ -2376,7 +2384,7 @@ bool select_insert::send_eof()
int error,error2;
DBUG_ENTER("select_insert::send_eof");
- error=table->file->end_bulk_insert();
+ error= (!thd->prelocked_mode) ? table->file->end_bulk_insert():0;
table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
/*
@@ -2459,7 +2467,8 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
thd->cuted_fields=0;
if (info.ignore || info.handle_duplicates != DUP_ERROR)
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
- table->file->start_bulk_insert((ha_rows) 0);
+ if (!thd->prelocked_mode)
+ table->file->start_bulk_insert((ha_rows) 0);
thd->no_trans_update= 0;
thd->abort_on_warning= (!info.ignore &&
(thd->variables.sql_mode &
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index cc724c102a4..0a667c887ef 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -356,7 +356,8 @@ bool mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
if (ignore ||
handle_duplicates == DUP_REPLACE)
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
- table->file->start_bulk_insert((ha_rows) 0);
+ if (!thd->prelocked_mode)
+ table->file->start_bulk_insert((ha_rows) 0);
table->copy_blobs=1;
thd->no_trans_update= 0;
@@ -373,7 +374,7 @@ bool mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
error= read_sep_field(thd, info, table_list, fields_vars,
set_fields, set_values, read_info,
*enclosed, skip_lines, ignore);
- if (table->file->end_bulk_insert() && !error)
+ if (!thd->prelocked_mode && table->file->end_bulk_insert() && !error)
{
table->file->print_error(my_errno, MYF(0));
error= 1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6e530b58d74..2e60bdb96f0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12333,7 +12333,11 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
Item **select_item; /* The corresponding item from the SELECT clause. */
Field *from_field; /* The corresponding field from the FROM clause. */
- if (order_item->type() == Item::INT_ITEM)
+ /*
+ Local SP variables may be int but are expressions, not positions.
+ (And they can't be used before fix_fields is called for them).
+ */
+ if (order_item->type() == Item::INT_ITEM && order_item->basic_const_item())
{ /* Order by position */
uint count= (uint) order_item->val_int();
if (!count || count > fields.elements)
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 2832adc1f8f..39d1ae5c9fb 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -938,6 +938,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table)
tbl->skip_temporary= 1;
tbl->belong_to_view= top_view;
tbl->referencing_view= table;
+ tbl->prelocking_placeholder= table->prelocking_placeholder;
/*
First we fill want_privilege with SELECT_ACL (this is needed for the
tables which belongs to view subqueries and temporary table views,
View Lorry Controller Status