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.

1 files changed, 63 insertions, 0 deletions

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;