1 files changed, 61 insertions, 0 deletions
diff --git a/storage/connect/mysql-test/connect/t/occur.test b/storage/connect/mysql-test/connect/t/occur.test
new file mode 100644
index 00000000..7d7bca87
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/occur.test
@@ -0,0 +1,61 @@
+-- source include/not_embedded.inc
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+let $PORT= `select @@port`;
+--copy_file $MTR_SUITE_DIR/std_data/employee.dat $MYSQLD_DATADIR/test/employee.dat
+
+CREATE TABLE employee (
+serialno CHAR(5) NOT NULL,
+name VARCHAR(12) NOT NULL FLAG=6,
+sex TINYINT(1) NOT NULL,
+title VARCHAR(15) NOT NULL FLAG=20,
+manager CHAR(5) DEFAULT NULL,
+department CHAR(4) NOT NULL FLAG=41,
+secretary CHAR(5) DEFAULT NULL FLAG=46,
+salary DOUBLE(8,2) NOT NULL FLAG=52
+) ENGINE=connect TABLE_TYPE=fix FILE_NAME='employee.dat' ENDING=1;
+SELECT * FROM employee;
+
+--replace_result $PORT PORT
+--eval CREATE TABLE occurs (name CHAR(12), sex CHAR(1), title CHAR(15), department CHAR(4), salary DOUBLE(8,2), id_of CHAR(12), id CHAR(5) NOT NULL) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=employee OPTION_LIST='OccurCol=ID,RankCol=ID_OF,Colist=serialno;manager;secretary,port=$PORT';
+SELECT * FROM occurs;
+
+DROP TABLE occurs;
+DROP TABLE employee;
+
+CREATE TABLE pets (
+name VARCHAR(12) NOT NULL,
+dog INT NOT NULL DEFAULT 0,
+cat INT NOT NULL DEFAULT 0,
+rabbit INT NOT NULL DEFAULT 0,
+bird INT NOT NULL DEFAULT 0,
+fish INT NOT NULL DEFAULT 0) ENGINE=MYISAM;
+INSERT INTO pets(name,dog) VALUES('John',2);
+INSERT INTO pets(name,cat) VALUES('Bill',1);
+INSERT INTO pets(name,dog,cat) VALUES('Mary',1,1);
+INSERT INTO pets(name,rabbit) VALUES('Lisbeth',2);
+INSERT INTO pets(name,cat,bird) VALUES('Kevin',2,6);
+INSERT INTO pets(name,dog,fish) VALUES('Donald',1,3);
+SELECT * FROM pets;
+
+--replace_result $PORT PORT
+--eval CREATE TABLE xpet (name VARCHAR(12) NOT NULL, race CHAR(6) NOT NULL, number INT) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=pets OPTION_LIST='OccurCol=number,RankCol=race,Colist=dog;cat;rabbit;bird;fish,port=$PORT'
+
+SELECT * FROM xpet;
+SELECT name FROM xpet;
+SELECT name FROM xpet WHERE race = 'cat' AND number = 0;
+SELECT name, SUM(number) pets FROM xpet GROUP BY name;
+
+ALTER TABLE xpet MODIFY number INT NOT NULL;
+
+SELECT * FROM xpet;
+SELECT * FROM xpet WHERE number > 1;
+SELECT DISTINCT name FROM xpet WHERE number > 1;
+SELECT name FROM xpet;
+SELECT name, race FROM xpet;
+SELECT name, count(*) FROM xpet GROUP BY name, LEAST(number,1);
+SELECT name, number, count(*) FROM xpet GROUP BY name, number;
+
+DROP TABLE xpet;
+DROP TABLE pets;
+--remove_file $MYSQLD_DATADIR/test/employee.dat