10 files changed, 358 insertions, 18 deletions
diff --git a/storage/connect/mysql-test/connect/t/json_java_2.test b/storage/connect/mysql-test/connect/t/json_java_2.test
new file mode 100644
index 00000000000..bb32eff4e94
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/json_java_2.test
@@ -0,0 +1,11 @@
+-- source jdbconn.inc
+-- source mongo.inc
+
+eval SET GLOBAL connect_class_path='$MTR_SUITE_DIR/std_data/Mongo2.jar';
+let $DRV= Java;
+let $VERS= 2;
+let $TYPE= JSON;
+let $CONN= CONNECTION='mongodb://localhost:27017' LRECL=4096;
+
+-- source mongo_test.inc
+-- source jdbconn_cleanup.inc
diff --git a/storage/connect/mysql-test/connect/t/json_java_3.test b/storage/connect/mysql-test/connect/t/json_java_3.test
new file mode 100644
index 00000000000..29e66cd5a1c
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/json_java_3.test
@@ -0,0 +1,11 @@
+-- source jdbconn.inc
+-- source mongo.inc
+
+eval SET GLOBAL connect_class_path='$MTR_SUITE_DIR/std_data/Mongo3.jar';
+let $DRV= Java;
+let $VERS= 3;
+let $TYPE= JSON;
+let $CONN= CONNECTION='mongodb://localhost:27017' LRECL=4096;
+
+-- source mongo_test.inc
+-- source jdbconn_cleanup.inc
diff --git a/storage/connect/mysql-test/connect/t/json_mongo_c.test b/storage/connect/mysql-test/connect/t/json_mongo_c.test
new file mode 100644
index 00000000000..b2148124487
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/json_mongo_c.test
@@ -0,0 +1,10 @@
+-- source mongo.inc
+
+let $DRV= C;
+let $VERS= 0;
+let $PROJ= {"projection":;
+let $ENDP= };
+let $TYPE= JSON;
+let $CONN= CONNECTION='mongodb://localhost:27017' LRECL=1024;
+
+-- source mongo_test.inc
diff --git a/storage/connect/mysql-test/connect/t/mongo.inc b/storage/connect/mysql-test/connect/t/mongo.inc
new file mode 100644
index 00000000000..2d7cbcfa8bd
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/mongo.inc
@@ -0,0 +1,3 @@
+let $MONGO= C:/PROGRA~1/MongoDB/Server/3.4/bin/mongo;
+let $MONGOIMPORT= C:/PROGRA~1/MongoDB/Server/3.4/bin/mongoimport;
+
diff --git a/storage/connect/mysql-test/connect/t/mongo_c.test b/storage/connect/mysql-test/connect/t/mongo_c.test
new file mode 100644
index 00000000000..1c4930e91d5
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/mongo_c.test
@@ -0,0 +1,9 @@
+-- source mongo.inc
+
+let $DRV= C;
+let $VERS= 0;
+let $PROJ= {"projection":;
+let $ENDP= };
+let $TYPE= MONGO;
+
+-- source mongo_test.inc
diff --git a/storage/connect/mysql-test/connect/t/mongo_java_2.test b/storage/connect/mysql-test/connect/t/mongo_java_2.test
new file mode 100644
index 00000000000..21da5dce68f
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/mongo_java_2.test
@@ -0,0 +1,10 @@
+-- source jdbconn.inc
+-- source mongo.inc
+
+eval SET GLOBAL connect_class_path='$MTR_SUITE_DIR/std_data/Mongo2.jar';
+let $DRV= Java;
+let $VERS= 2;
+let $TYPE= MONGO;
+
+-- source mongo_test.inc
+-- source jdbconn_cleanup.inc
diff --git a/storage/connect/mysql-test/connect/t/mongo_java_3.test b/storage/connect/mysql-test/connect/t/mongo_java_3.test
new file mode 100644
index 00000000000..b7584adcc7e
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/mongo_java_3.test
@@ -0,0 +1,10 @@
+-- source jdbconn.inc
+-- source mongo.inc
+
+eval SET GLOBAL connect_class_path='$MTR_SUITE_DIR/std_data/Mongo3.jar';
+let $DRV= Java;
+let $VERS= 3;
+let $TYPE= MONGO;
+
+-- source mongo_test.inc
+-- source jdbconn_cleanup.inc
diff --git a/storage/connect/mysql-test/connect/t/mongo_test.inc b/storage/connect/mysql-test/connect/t/mongo_test.inc
new file mode 100644
index 00000000000..c223e70f719
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/mongo_test.inc
@@ -0,0 +1,203 @@
+--echo #
+--echo # Test the MONGO table type
+--echo #
+eval CREATE TABLE t1 (Document varchar(1024) field_format='*')
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants $CONN
+OPTION_LIST='Driver=$DRV,Version=$VERS' DATA_CHARSET=utf8;
+SELECT * from t1 limit 3;
+DROP TABLE t1;
+
+--echo #
+--echo # Test catfunc
+--echo #
+eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants CATFUNC=columns
+OPTION_LIST='Level=1,Driver=$DRV,Version=$VERS' DATA_CHARSET=utf8 $CONN;
+SELECT * from t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Explicit columns
+--echo #
+eval CREATE TABLE t1 (
+_id VARCHAR(24) NOT NULL,
+name VARCHAR(255) NOT NULL,
+cuisine VARCHAR(255) NOT NULL,
+borough VARCHAR(255) NOT NULL,
+restaurant_id VARCHAR(255) NOT NULL)
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants
+CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8
+OPTION_LIST='Driver=$DRV,Version=$VERS';
+SELECT * FROM t1 LIMIT 10;
+DROP TABLE t1;
+
+--echo #
+--echo # Test discovery
+--echo #
+eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants
+OPTION_LIST='Level=1,Driver=$DRV,Version=$VERS' $CONN DATA_CHARSET=utf8;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 LIMIT 5;
+DROP TABLE t1;
+
+--echo #
+--echo # Dropping a column
+--echo #
+let $COLIST= $PROJ{"grades":0}$ENDP;
+eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants DATA_CHARSET=utf8
+COLIST='$COLIST' OPTION_LIST='Driver=$DRV,Version=$VERS,level=0' $CONN;
+SELECT * FROM t1 LIMIT 10;
+DROP TABLE t1;
+
+--echo #
+--echo # Specifying Jpath
+--echo #
+eval CREATE TABLE t1 (
+_id VARCHAR(24) NOT NULL,
+name VARCHAR(64) NOT NULL,
+cuisine CHAR(200) NOT NULL,
+borough CHAR(16) NOT NULL,
+street VARCHAR(65) FIELD_FORMAT='address.street',
+building CHAR(16) FIELD_FORMAT='address.building',
+zipcode CHAR(5) FIELD_FORMAT='address.zipcode',
+grade CHAR(1) FIELD_FORMAT='grades.0.grade',
+score INT(4) NOT NULL FIELD_FORMAT='grades.0.score',
+`date` DATE FIELD_FORMAT='grades.0.date',
+restaurant_id VARCHAR(255) NOT NULL)
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='restaurants' DATA_CHARSET=utf8
+OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
+--vertical_results
+SELECT * FROM t1 LIMIT 1;
+--horizontal_results
+SELECT name, street, score, date FROM t1 LIMIT 5;
+SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10;
+SELECT COUNT(*) FROM t1 WHERE grade = 'A';
+SELECT * FROM t1 WHERE cuisine = 'English';
+SELECT * FROM t1 WHERE score = building;
+DROP TABLE t1;
+
+--echo #
+--echo # Specifying Filter
+--echo #
+eval CREATE TABLE t1 (
+_id CHAR(24) NOT NULL,
+name CHAR(64) NOT NULL,
+borough CHAR(16) NOT NULL,
+restaurant_id CHAR(8) NOT NULL)
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants DATA_CHARSET=utf8
+FILTER='{"cuisine":"French","borough":{"\$ne":"Manhattan"}}'
+OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
+SELECT name FROM t1 WHERE borough = 'Queens';
+DROP TABLE t1;
+
+--echo #
+--echo # Testing pipeline
+--echo #
+eval CREATE TABLE t1 (
+name VARCHAR(64) NOT NULL,
+borough CHAR(16) NOT NULL,
+date DATETIME NOT NULL,
+grade CHAR(1) NOT NULL,
+score INT(4) NOT NULL)
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='restaurants' DATA_CHARSET=utf8
+COLIST='{"pipeline":[{"\$match":{"cuisine":"French"}},{"\$unwind":"\$grades"},{"\$project":{"_id":0,"name":1,"borough":1,"date":"\$grades.date","grade":"\$grades.grade","score":"\$grades.score"}}]}'
+OPTION_LIST='Driver=$DRV,Version=$VERS,Pipeline=1' $CONN;
+SELECT * FROM t1 LIMIT 10;
+SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx';
+DROP TABLE t1;
+
+--echo #
+--echo # try level 2 discovery
+--echo #
+let $COLIST= $PROJ{"cuisine":0}$ENDP;
+eval CREATE TABLE t1
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants
+FILTER='{"cuisine":"French","borough":{"\$ne":"Manhattan"}}'
+COLIST='$COLIST' $CONN
+OPTION_LIST='Driver=$DRV,level=2,version=$VERS';
+SHOW CREATE TABLE t1;
+IF ($TYPE == MONGO)
+{
+SELECT name, borough, address_street, grades_0_score AS score FROM t1 WHERE grades_0_grade = 'B';
+}
+IF ($TYPE == JSON)
+{
+SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B';
+}
+DROP TABLE t1;
+
+--echo #
+--echo # try CRUD operations
+--echo #
+--exec $MONGO --eval "db.testcoll.drop()" --quiet
+eval CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64))
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='testcoll'
+OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
+DELETE FROM t1;
+INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three');
+SELECT * FROM t1;
+UPDATE t1 SET msg = 'Deux' WHERE _id = 2;
+DELETE FROM t1 WHERE msg IS NULL;
+SELECT * FROM t1;
+DELETE FROM t1;
+DROP TABLE t1;
+--exec $MONGO --eval "db.testcoll.drop()" --quiet
+
+--echo #
+--echo # List states whose population is equal or more than 10 millions
+--echo #
+--exec $MONGO --eval "db.cities.drop()" --quiet
+--exec $MONGOIMPORT --quiet $MTR_SUITE_DIR/std_data/cities.json
+eval CREATE TABLE t1 (
+  _id char(5) NOT NULL,
+  city char(16) NOT NULL,
+  loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0',
+  loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1',
+  pop int(11) NOT NULL,
+  state char(2) NOT NULL)
+ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE='MONGO' TABNAME='cities'
+OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN DATA_CHARSET='utf8';
+--echo # Using SQL for grouping
+SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC;
+DROP TABLE t1;
+
+--echo # Using a pipeline for grouping
+eval CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL)
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='cities' DATA_CHARSET=utf8
+COLIST='{"pipeline":[{"\$group":{"_id":"\$state","totalPop":{"\$sum":"\$pop"}}},{"\$match":{"totalPop":{"\$gte":10000000}}},{"\$sort":{"totalPop":-1}}]}'
+OPTION_LIST='Driver=$DRV,Version=$VERS,Pipeline=1' $CONN;
+SELECT * FROM t1;
+DROP TABLE t1;
+--exec $MONGO --eval "db.cities.drop()" --quiet
+
+--echo #
+--echo # Test making array
+--echo #
+eval CREATE TABLE t1 (
+  _id int(4) NOT NULL,
+  item CHAR(8) NOT NULL,
+  prices_0 INT(6) FIELD_FORMAT='prices.0',
+  prices_1 INT(6) FIELD_FORMAT='prices.1',
+  prices_2 INT(6) FIELD_FORMAT='prices.2',
+  prices_3 INT(6) FIELD_FORMAT='prices.3',
+  prices_4 INT(6) FIELD_FORMAT='prices.4')
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='testcoll' DATA_CHARSET=utf8
+OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
+INSERT INTO t1 VALUES
+(1,'journal',87,45,63,12,78),
+(2,'notebook',123,456,789,NULL,NULL),
+(3,'paper',5,7,3,8,NULL),
+(4,'planner',25,71,NULL,44,27),
+(5,'postcard',5,7,3,8,NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Test array aggregation
+--echo #
+eval CREATE TABLE t1
+ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='testcoll'
+COLIST='{"pipeline":[{"\$project":{"_id":0,"item":1,"total":{"\$sum":"\$prices"},"average":{"\$avg":"\$prices"}}}]}'
+OPTION_LIST='Driver=$DRV,Version=$VERS,Pipeline=YES' $CONN;
+SELECT * FROM t1;
+DROP TABLE t1;
+--exec $MONGO --eval "db.testcoll.drop()" --quiet
diff --git a/storage/connect/mysql-test/connect/t/tbl.test b/storage/connect/mysql-test/connect/t/tbl.test
index 3dc4b2e64b0..4f8497d4e6a 100644
--- a/storage/connect/mysql-test/connect/t/tbl.test
+++ b/storage/connect/mysql-test/connect/t/tbl.test
@@ -1,4 +1,4 @@
--- source include/not_embedded.inc
+--source include/not_embedded.inc
 
 let $MYSQLD_DATADIR= `select @@datadir`;
 let $PORT= `select @@port`;
@@ -51,20 +51,3 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
-
---echo #
---echo # Checking thread TBL tables
---echo #
-CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL SRCDEF='select 11 as v';
-SELECT * FROM t1;
-
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL SRCDEF='select 22 as v';
-SELECT * FROM t2;
-
---replace_result $PORT PORT
---eval CREATE TABLE total (v BIGINT(20) UNSIGNED NOT NULL) ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t1,t2' OPTION_LIST='thread=yes,port=$PORT';
-SELECT * FROM total order by v desc;
-
-DROP TABLE total;
-DROP TABLE t1;
-DROP TABLE t2;
diff --git a/storage/connect/mysql-test/connect/t/tbl_thread.test b/storage/connect/mysql-test/connect/t/tbl_thread.test
new file mode 100644
index 00000000000..abc1ef34729
--- /dev/null
+++ b/storage/connect/mysql-test/connect/t/tbl_thread.test
@@ -0,0 +1,90 @@
+-- source myconn.inc
+
+connection default;
+
+let $PORT= `select @@port`;
+
+--echo #
+--echo # Checking thread TBL tables
+--echo #
+CREATE TABLE t1 (a int, b char(10));
+INSERT INTO t1 VALUES (0,'test00'),(1,'test01'),(2,'test02'),(3,'test03');
+SELECT * FROM t1;
+
+connection master;
+
+CREATE TABLE rt2 (a int, b char(10));
+INSERT INTO rt2 VALUES (4,'test04'),(5,'test05'),(6,'test06'),(7,'test07');
+SELECT * FROM rt2;
+
+connection slave;
+
+USE test;
+CREATE TABLE rt3 (a int, b char(10));
+INSERT INTO rt3 VALUES (8,'test08'),(9,'test09'),(10,'test10'),(11,'test11');
+SELECT * FROM rt3;
+
+connection default;
+
+--replace_result $MASTER_MYPORT MASTER_PORT
+eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL
+CONNECTION='mysql://root@127.0.0.1:$MASTER_MYPORT/test/rt2';
+SELECT * FROM t2;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval CREATE TABLE t3 ENGINE=CONNECT TABLE_TYPE=MYSQL
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/rt3';
+SELECT * FROM t3;
+
+--replace_result $PORT PORT
+eval CREATE TABLE total (a int, b char(10))
+ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t1,t2,t3'
+OPTION_LIST='thread=yes,port=$PORT';
+SELECT * FROM total order by a desc;
+
+connection master;
+
+DROP TABLE rt2;
+
+connection slave;
+
+DROP TABLE rt3;
+
+connection default;
+
+DROP TABLE t1,t2,t3,total;
+
+--echo #
+--echo # Old thread TBL tables test modified
+--echo #
+--replace_result $MASTER_MYPORT MASTER_PORT
+--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL SRCDEF='select 11 as v' OPTION_LIST='port=$MASTER_MYPORT'
+SELECT * FROM t1;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL SRCDEF='select 22 as v' OPTION_LIST='port=$SLAVE_MYPORT'
+SELECT * FROM t2;
+
+--replace_result $PORT PORT
+--eval CREATE TABLE total (v BIGINT(20) UNSIGNED NOT NULL) ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t1,t2' OPTION_LIST='thread=yes,port=$PORT';
+SELECT * FROM total order by v desc;
+DROP TABLE t1,t2,total;
+
+--echo #
+--echo # Old thread TBL tables test not modified
+--echo #
+CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL SRCDEF='select 11 as v';
+SELECT * FROM t1;
+
+CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL SRCDEF='select 22 as v';
+SELECT * FROM t2;
+
+--replace_result $PORT PORT
+--eval CREATE TABLE total (v BIGINT(20) UNSIGNED NOT NULL) ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t1,t2' OPTION_LIST='thread=yes,port=$PORT';
+SELECT * FROM total order by v desc;
+
+DROP TABLE total;
+DROP TABLE t1;
+DROP TABLE t2;
+
+-- source myconn_cleanup.inc