New tests for prepared statements:

- 'ps_10nestset' uses a "nested set" approach for an employee 
  hierarchy, then does arithmetic on the "salary" field;
  (soon) to be extended by inserts / deletes which imply
  mass updates on the "l"/"r" fields showing the set inclusion,
- 'ps_11bugs' will get (some of ?) those bug DB entries which
  refer to prepared statements, but whose number does not appear
  in a test file comment - so it will also be extended.

4 files changed, 184 insertions, 0 deletions

diff --git a/mysql-test/r/ps_10nestset.result b/mysql-test/r/ps_10nestset.result
new file mode 100644
index 00000000000..10f0a741b54
--- /dev/null
+++ b/mysql-test/r/ps_10nestset.result
@@ -0,0 +1,68 @@
+use test;
+drop table if exists personnel;
+Warnings:
+Note	1051	Unknown table 'personnel'
+create table personnel  (
+id     INTEGER AUTO_INCREMENT PRIMARY KEY,
+emp    CHAR(10) NOT NULL,
+salary DECIMAL(6,2) NOT NULL,
+l INTEGER NOT NULL,
+r INTEGER NOT NULL);
+prepare st_ins from 'insert into personnel set emp = ?, salary = ?, l = ?, r = ?';
+set @arg_nam= 'Jerry';
+set @arg_sal= 1000;
+set @arg_l= 1;
+set @arg_r= 12;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Bert';
+set @arg_sal=  900;
+set @arg_l= 2;
+set @arg_r=  3;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Chuck';
+set @arg_sal=  900;
+set @arg_l= 4;
+set @arg_r= 11;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Donna';
+set @arg_sal=  800;
+set @arg_l= 5;
+set @arg_r=  6;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Eddie';
+set @arg_sal=  700;
+set @arg_l= 7;
+set @arg_r=  8;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Fred';
+set @arg_sal=  600;
+set @arg_l= 9;
+set @arg_r= 10;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+select * from personnel;
+id	emp	salary	l	r
+1	Jerry	1000.00	1	12
+2	Bert	900.00	2	3
+3	Chuck	900.00	4	11
+4	Donna	800.00	5	6
+5	Eddie	700.00	7	8
+6	Fred	600.00	9	10
+prepare st_raise_base from 'update personnel set salary = salary * ( 1 + ? ) where r - l = 1';
+prepare st_raise_mgr  from 'update personnel set salary = salary + ? where r - l > 1';
+set @arg_percent= .10;
+set @arg_amount= 100;
+execute st_raise_base using @arg_percent;
+execute st_raise_mgr  using @arg_amount;
+execute st_raise_base using @arg_percent;
+execute st_raise_mgr  using @arg_amount;
+execute st_raise_base using @arg_percent;
+execute st_raise_mgr  using @arg_amount;
+select * from personnel;
+id	emp	salary	l	r
+1	Jerry	1300.00	1	12
+2	Bert	1197.90	2	3
+3	Chuck	1200.00	4	11
+4	Donna	1064.80	5	6
+5	Eddie	931.70	7	8
+6	Fred	798.60	9	10
+drop table personnel;
diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result
new file mode 100644
index 00000000000..2aa0df6a431
--- /dev/null
+++ b/mysql-test/r/ps_11bugs.result
@@ -0,0 +1,21 @@
+use test;
+drop table if exists test_select;
+Warnings:
+Note	1051	Unknown table 'test_select'
+CREATE TABLE test_select(session_id  char(9) NOT NULL);
+INSERT INTO test_select VALUES ("abc");
+SELECT * FROM test_select;
+session_id
+abc
+prepare st_1180 from 'SELECT * FROM test_select WHERE ?="1111" and session_id = "abc"';
+set @arg1= 'abc';
+execute st_1180 using @arg1;
+session_id
+set @arg1= '1111';
+execute st_1180 using @arg1;
+session_id
+abc
+set @arg1= 'abc';
+execute st_1180 using @arg1;
+session_id
+drop table test_select;
diff --git a/mysql-test/t/ps_10nestset.test b/mysql-test/t/ps_10nestset.test
new file mode 100644
index 00000000000..2c6009af9de
--- /dev/null
+++ b/mysql-test/t/ps_10nestset.test
@@ -0,0 +1,63 @@
+###############################################
+#                                             #
+#   Prepared Statements test on               #
+#   "nested sets" representing hierarchies    #
+#                                             #
+###############################################
+
+# Source: http://kris.koehntopp.de/artikel/sql-self-references (dated 1999)
+# Source: http://dbmsmag.com/9603d06.html (dated 1996)
+
+use test;
+
+drop table if exists personnel;
+
+# "Nested Set": This table represents an employee list with a hierarchy tree.
+# The tree is not modeled by "parent" links but rather by showing the "left"
+# and "right" border of any person's "region". By convention, "l" < "r".
+# As it is a tree, these "regions" of two persons A and B are either disjoint,
+# or A's region is completely contained in B's (B is A's boss), or vice versa.
+# See the references for more info.
+
+create table personnel  (
+  id     INTEGER AUTO_INCREMENT PRIMARY KEY,
+  emp    CHAR(10) NOT NULL,
+  salary DECIMAL(6,2) NOT NULL,
+  l INTEGER NOT NULL,
+  r INTEGER NOT NULL);
+
+prepare st_ins from 'insert into personnel set emp = ?, salary = ?, l = ?, r = ?';
+
+# Initial employee list:
+# Jerry ( Bert ( ) Chuck ( Donna ( ) Eddie ( ) Fred ( ) ) )
+set @arg_nam= 'Jerry'; set @arg_sal= 1000; set @arg_l= 1; set @arg_r= 12;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Bert';  set @arg_sal=  900; set @arg_l= 2; set @arg_r=  3;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Chuck'; set @arg_sal=  900; set @arg_l= 4; set @arg_r= 11;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Donna'; set @arg_sal=  800; set @arg_l= 5; set @arg_r=  6;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Eddie'; set @arg_sal=  700; set @arg_l= 7; set @arg_r=  8;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+set @arg_nam= 'Fred';  set @arg_sal=  600; set @arg_l= 9; set @arg_r= 10;
+execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
+
+select * from personnel;
+
+# Three successive raises, each one is 100 units for managers, 10 percent for others.
+prepare st_raise_base from 'update personnel set salary = salary * ( 1 + ? ) where r - l = 1';
+prepare st_raise_mgr  from 'update personnel set salary = salary + ? where r - l > 1';
+let $1= 3;
+set @arg_percent= .10;
+set @arg_amount= 100;
+while ($1)
+{
+  execute st_raise_base using @arg_percent;
+  execute st_raise_mgr  using @arg_amount;
+  dec $1;
+}
+
+select * from personnel;
+
+drop table personnel;
diff --git a/mysql-test/t/ps_11bugs.test b/mysql-test/t/ps_11bugs.test
new file mode 100644
index 00000000000..53afc8b5a78
--- /dev/null
+++ b/mysql-test/t/ps_11bugs.test
@@ -0,0 +1,32 @@
+###############################################
+#                                             #
+#   Prepared Statements                       #
+#   re-testing bug DB entries                 #
+#                                             #
+###############################################
+
+use test;
+
+# bug#1180: optimized away part of WHERE clause cause incorect prepared satatement results
+
+drop table if exists test_select;
+
+CREATE TABLE test_select(session_id  char(9) NOT NULL);
+INSERT INTO test_select VALUES ("abc");
+SELECT * FROM test_select;
+
+prepare st_1180 from 'SELECT * FROM test_select WHERE ?="1111" and session_id = "abc"';
+
+# Must not find a row
+set @arg1= 'abc';
+execute st_1180 using @arg1;
+
+# Now, it should find one row
+set @arg1= '1111';
+execute st_1180 using @arg1;
+
+# Back to non-matching
+set @arg1= 'abc';
+execute st_1180 using @arg1;
+
+drop table test_select;