From 05826af9268264fd2195381b491a72bcf426d531 Mon Sep 17 00:00:00 2001
From: "pem@mysql.com" <>
Date: Thu, 16 Feb 2006 13:40:37 +0100
Subject: Fixed BUG#17015: Routine name truncation not an error   The name
 length was checked "the old way", not taking charsets into account,   when
 creating a stored routine.   Fixing this enforces the real limit (64
 characters) again, and no truncation   is possible.

---
 mysql-test/t/sp-error.test | 19 ++++++++++++++++---
 1 file changed, 16 insertions(+), 3 deletions(-)

(limited to 'mysql-test/t')

diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index c4745537de3..9f98b776633 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -926,12 +926,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
@@ -1721,4 +1735,3 @@ create aggregate function bug16896() returns int return 1;
 #drop procedure if exists bugNNNN|
 #--enable_warnings
 #create procedure bugNNNN...
-
-- 
cgit v1.2.1


From 61f2dc713b5ad13cd1ccea4fc38614bfa748199a Mon Sep 17 00:00:00 2001
From: "pem@mysql.com" <>
Date: Fri, 10 Mar 2006 14:04:56 +0100
Subject: Fixed BUG#16474: SP crashed MySQL   fix_fields() was not called for
 "order by" variables if the type was a   "constant integer", and thus
 interpreted as a column index.   However, a local variable is an expression
 and should not be interpreted   as a column index. Instead it behaves just
 like when using a user variable   for instance (i.e. it will not affect the
 ordering).

---
 mysql-test/t/sp.test | 31 +++++++++++++++++++++++++++++++
 1 file changed, 31 insertions(+)

(limited to 'mysql-test/t')

diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index e6823693b3d..af7ce57b252 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5717,6 +5717,37 @@ drop table t3|
 drop procedure bug16887|
 
 
+#
+# 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|
+delete from t1|
+
+
 #
 # BUG#NNNN: New bug synopsis
 #
-- 
cgit v1.2.1


From 71defb7a8020e0797cdc3b7b68c1c96adcb7a4e6 Mon Sep 17 00:00:00 2001
From: "konstantin@mysql.com" <>
Date: Tue, 28 Mar 2006 15:06:29 +0400
Subject: A fix and a test case for Bug#15683 "crash, Function on nested VIEWs,
 Prepared statement": we didn't mark the nested views as 'prelockng
 placeholders' when building the prelocking list. This resulted in these views
 being processed (merged, materialized) before they are actually used.

---
 mysql-test/t/sp-prelocking.test | 31 +++++++++++++++++++++++++++++++
 1 file changed, 31 insertions(+)

(limited to 'mysql-test/t')

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;
+
-- 
cgit v1.2.1


From b310d4fb4d4fb6cf109e95f68192b5532318ae22 Mon Sep 17 00:00:00 2001
From: "pem@mysql.com" <>
Date: Tue, 28 Mar 2006 14:16:21 +0200
Subject: Post review fixes for BUG#16474: SP crashed MySQL.

---
 mysql-test/t/ps.test | 34 ++++++++++++++++++++++++++++++++++
 mysql-test/t/sp.test |  5 +++++
 2 files changed, 39 insertions(+)

(limited to 'mysql-test/t')

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.test b/mysql-test/t/sp.test
index af7ce57b252..1d1272b0b2a 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5745,6 +5745,11 @@ 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|
 
 
-- 
cgit v1.2.1


From 17785d169bdc4e75151469a0b101378dd49524d2 Mon Sep 17 00:00:00 2001
From: "dlenev@mysql.com" <>
Date: Wed, 29 Mar 2006 14:53:00 +0400
Subject: Proposed fix for bug #17764 "Trigger crashes MyISAM table"

A table with an on insert trigger was reported as crashed when the insert
was processed with bulk insert mode on (handler::start_bulk_insert).
The trigger was also selecting from the same table, and that caused
the "crash".
The same problem was present when an insert statement, which was processed
in bulk mode, also used a stored function that was reading the same table.

This fix disables bulk inserts if a statement uses functions or invokes
triggers. Implementing more granular checks will require much more code and
therefore can hardly be done in 5.0
---
 mysql-test/t/trigger.test | 34 ++++++++++++++++++++++++++++++++++
 1 file changed, 34 insertions(+)

(limited to 'mysql-test/t')

diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 1d68b519f1d..d8e2bd7c1cb 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
 
@@ -1057,3 +1058,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;
-- 
cgit v1.2.1