#### Testcase derived from suite/funcs_1/views/views_master.test .

--disable_warnings
DROP DATABASE IF EXISTS test1;
DROP DATABASE IF EXISTS test2;
DROP DATABASE IF EXISTS test3;
--enable_warnings
CREATE DATABASE test1;
CREATE DATABASE test2;
CREATE DATABASE test3;

--disable_abort_on_error

  let $engine_type= MyISAM;
# let $engine_type= InnoDB;
# let $engine_type= MEMORY;


# Complicated nested JOIN-VIEWs
#
#    - Over level 26 MyISAM seems to show an exponential increase of the
#      execution time. 
#    - Starting with level 31 MyISAM seems not to reach any end.
#    - There is an extreme high CPU time consumption, but NO significant
#      disc I/O ot paging activity or change within the virtual memory
#      consumption.
#
#    There was only one execution per test variant.
#    mysql-test/var is a symlink pointing into a Linux tmpfs. 
#    I think only the user CPU time is of interest.
#
# level         runtime InnoDB
#    10         real    0m8.538s  user    0m2.322s  sys     0m0.686s
#    20         real    0m8.839s  user    0m2.595s  sys     0m0.693s
#    29         real    0m8.980s  user    0m2.760s  sys     0m0.687s
#    30         real    0m10.293s user    0m2.902s  sys     0m0.733s
#    60         real    0m12.289s user    0m4.910s  sys     0m0.764s
# level  runtime MyISAM
#    10         real    0m8.604s  user    0m2.343s  sys     0m0.709s
#    20         real    0m10.170s user    0m2.455s  sys     0m0.663s
#    29         real    0m36.409s user    0m28.821s sys     0m0.773s
#    30         real    4m5.242s  user    3m49.839s sys     0m1.098s
#    30  1)     real    0m10.636s user    0m3.259s  sys     0m0.793s
#    30  2)     real    0m10.121s user    0m2.941s  sys     0m0.658s
#    30  3)     real    0m22.023s user    0m15.684s sys     0m0.734s
#    30  4)     real    3m58.658s user    3m48.509s sys     0m0.904s
# level  runtime MEMORY
#    10         real    0m8.553s  user    0m2.345s  sys     0m0.677s
#    20         real    0m10.641s user    0m2.352s  sys     0m0.679s
#    29         real    0m10.102s user    0m2.840s  sys     0m0.729s
#    30         real    0m10.955s user    0m2.779s  sys     0m0.654s
#    60         real    0m10.450s user    0m4.988s  sys     0m0.779s

SET @max_level = 30;

USE test1;
eval CREATE TABLE t1 (f1 BIGINT, f2 CHAR(50)) ENGINE = $engine_type ;
# 1) omit the next INSERT
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
INSERT INTO t1 VALUES (5, 'five');

USE test2;
# 3) use eval CREATE TABLE t1 (f1 DECIMAL(64,30), f2 CHAR(50)) ENGINE = $engine_type;
# 4) use eval CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(50)) ENGINE = $engine_type;
eval CREATE TABLE t1 (f1 DECIMAL(64,30), f2 VARCHAR(50)) ENGINE = $engine_type;
# 1) omit the next INSERT
INSERT INTO  t1 VALUES (NULL, 'numeric column is NULL');
INSERT INTO t1 VALUES (5, 'five');

USE test3;
eval CREATE TABLE t1 (f1 BIGINT, f2 CHAR(50)) ENGINE = $engine_type;
INSERT INTO t1 VALUES (5, 'five');

USE test;

CREATE OR REPLACE VIEW test1.v0 AS SELECT * FROM test2.t1;
CREATE OR REPLACE VIEW test2.v0 AS SELECT * FROM test1.t1;
CREATE OR REPLACE VIEW test3.v0 AS SELECT * FROM test1.t1;

let $level= 1;
let $run= 1;
while ($run)
{
   --disable_query_log
   eval SET @aux = $level - 1;
   --enable_query_log
   let $sublevel= `SELECT @aux`;

   eval CREATE OR REPLACE VIEW test1.v$level AS SELECT tab1.f1, tab2.f2
               FROM test1.t1 tab1 NATURAL JOIN test2.v$sublevel tab2;
   eval CREATE OR REPLACE VIEW test2.v$level AS SELECT tab1.f1, tab2.f2
               FROM test1.t1 tab1 NATURAL JOIN test3.v$sublevel tab2;
   eval CREATE OR REPLACE VIEW test3.v$level AS SELECT tab1.f1, tab2.f2
               FROM test2.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;

   --disable_query_log
   eval SET @aux = @max_level > $level;
   --enable_query_log
   inc $level;

   let $run= `SELECT @aux`;
}

let $toplevel= `SELECT @max_level`;
eval SELECT f1, f2 FROM test1.v$toplevel;


# Clean up
# DROP DATABASE test1;
# DROP DATABASE test2;
# DROP DATABASE test3;