- Applying temporary restrictions to test files.

  This is to suppress some valgrind warnings and consist principally in:
  1 - Not supporting connect_assisted_discovery to all PROXY based table types
  2 - Not supporting the PIVOT table type
  This temporarily until the valgrind errors/warnings are fixed

modified:
  storage/connect/ha_connect.cc
  storage/connect/mysql-test/connect/r/mysql.result
  storage/connect/mysql-test/connect/r/pivot.result
  storage/connect/mysql-test/connect/r/xcol.result
  storage/connect/mysql-test/connect/t/mysql.test
  storage/connect/mysql-test/connect/t/pivot.test
  storage/connect/mysql-test/connect/t/xcol.test

7 files changed, 565 insertions, 736 deletions

diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc
index 3325d39f1d8..2da0c6f504d 100644
--- a/storage/connect/ha_connect.cc
+++ b/storage/connect/ha_connect.cc
@@ -3593,7 +3593,12 @@ static int connect_assisted_discovery(handlerton *hton, THD* thd,
     case TAB_TBL:
     case TAB_XCL:
     case TAB_OCCUR:
-      ok= true;
+      if (!stricmp(tab, create_info->alias) &&
+         (!db || !stricmp(db, table_s->db.str)))
+        sprintf(g->Message, "A %s table cannot refer to itself", topt->type);
+      else
+        ok= true;
+
       break;
     default:
       sprintf(g->Message, "Cannot get column info for table type %s", topt->type);
@@ -3976,6 +3981,7 @@ int ha_connect::create(const char *name, TABLE *table_arg,
     switch (type) {
       case TAB_PRX:
       case TAB_XCL:
+      case TAB_PIVOT:
       case TAB_OCCUR:
         if (options->srcdef) {
           strcpy(g->Message, "Cannot check looping reference");
diff --git a/storage/connect/mysql-test/connect/r/mysql.result b/storage/connect/mysql-test/connect/r/mysql.result
index 8d5507e121a..602f20dd025 100644
--- a/storage/connect/mysql-test/connect/r/mysql.result
+++ b/storage/connect/mysql-test/connect/r/mysql.result
@@ -10,48 +10,25 @@ NULL	NULL
 #
 # Testing errors
 #
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root1,port=PORT';
-ERROR HY000: (1045) Access denied for user 'root1'@'localhost' (using password: NO)
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL DBNAME='unknown' TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
-ERROR HY000: (1049) Unknown database 'unknown'
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL OPTION_LIST='host=localhost,user=root,port=PORT' DBNAME='unknown' TABNAME='t1';
-ERROR HY000: (1049) Unknown database 'unknown'
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='unknown' OPTION_LIST='host=localhost,user=root,port=PORT';
-ERROR HY000: (1146) Table 'test.unknown' doesn't exist [SHOW FULL COLUMNS FROM unknown FROM test]
-SHOW CREATE TABLE t2;
-ERROR 42S02: Table 'test.t2' doesn't exist
-CREATE TABLE t2 (x int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `x` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL,
   `y` char(10) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=16020'
 SELECT * FROM t2;
-ERROR HY000: Got error 174 '(1054) Unknown column 'x' in 'field list' [SELECT `x`, `y` FROM `t1`]' from CONNECT
+ERROR HY000: Got error 174 '(1054) Unknown column 'y' in 'field list' [SELECT `a`, `y` FROM `t1`]' from CONNECT
 DROP TABLE t2;
+CREATE TABLE t2 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 ALTER TABLE t1 RENAME t1backup;
 SELECT * FROM t2;
-ERROR 42S02: Table 'test.t2' doesn't exist
+ERROR HY000: Got error 174 '(1146) Table 'test.t1' doesn't exist [SELECT `a`, `b` FROM `t1`]' from CONNECT
 ALTER TABLE t1backup RENAME t1;
+DROP TABLE t2;
 #
 # Testing SELECT, etc.
 #
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
-SHOW CREATE TABLE t2;
-Table	Create Table
-t2	CREATE TABLE `t2` (
-  `a` int(11) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
-SELECT * FROM t2;
-a	b
-NULL	NULL
-0	test00
-1	test01
-2	test02
-3	test03
-DROP TABLE t2;
 CREATE TABLE t2 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -102,7 +79,7 @@ DROP TABLE t1;
 # Testing numeric data types
 #
 CREATE TABLE t1 (a smallint);
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a smallint) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -112,12 +89,12 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` smallint(6) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a mediumint);
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -126,13 +103,13 @@ t1	CREATE TABLE `t1` (
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `a` int(9) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+  `a` int(11) DEFAULT NULL
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a int);
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -142,12 +119,12 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` int(11) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a bigint);
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a bigint) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -157,7 +134,7 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` bigint(20) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
@@ -165,7 +142,7 @@ DROP TABLE t2, t1;
 # Testing character data types
 #
 CREATE TABLE t1 (a char(10));
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a varchar(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -175,12 +152,12 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` varchar(10) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a varchar(10));
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a varchar(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -190,7 +167,7 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` varchar(10) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
@@ -201,7 +178,7 @@ DROP TABLE t2, t1;
 # Testing temporal data types
 #
 CREATE TABLE t1 (a date);
-CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 (a date) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -211,7 +188,7 @@ SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` date DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MYSQL' `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'
 SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
diff --git a/storage/connect/mysql-test/connect/r/pivot.result b/storage/connect/mysql-test/connect/r/pivot.result
index 1b790662c4b..8a5151a4a15 100644
--- a/storage/connect/mysql-test/connect/r/pivot.result
+++ b/storage/connect/mysql-test/connect/r/pivot.result
@@ -1,5 +1,5 @@
 #
-# Testing the PIVOT table type
+# PIVOT table type is temporarily not supported
 #
 CREATE TABLE expenses (
 Who  CHAR(10) NOT NULL,
@@ -33,112 +33,6 @@ Joe	5	Food	12.00
 Janet	3	Beer	18.00
 Janet	4	Car	17.00
 Janet	5	Food	12.00
-#
-# Pivoting from What
-#
-CREATE TABLE pivex (
-Who  CHAR(10) NOT NULL,
-Week INT(2) NOT NULL,
-Beer DOUBLE(8,2) FLAG=1,
-Car  DOUBLE(8,2) FLAG=1,
-Food DOUBLE(8,2) FLAG=1)
-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses;
-ALTER TABLE pivex OPTION_LIST='port=PORT';
-Warnings:
-Warning	1105	The current version of CONNECT did not check what you changed in ALTER. Use at your own risk
-SELECT * FROM pivex;
-Who	Week	Beer	Car	Food
-Beth	3	16.00	0.00	0.00
-Beth	4	15.00	0.00	17.00
-Beth	5	20.00	0.00	12.00
-Janet	3	18.00	19.00	18.00
-Janet	4	0.00	17.00	0.00
-Janet	5	33.00	12.00	12.00
-Joe	3	18.00	20.00	31.00
-Joe	4	49.00	0.00	34.00
-Joe	5	14.00	0.00	12.00
-#
-# Restricting the columns in a Pivot Table
-#
-ALTER TABLE pivex DROP COLUMN week;
-SELECT * FROM pivex;
-Who	Beer	Car	Food
-Beth	51.00	0.00	29.00
-Janet	51.00	48.00	30.00
-Joe	81.00	20.00	77.00
-#
-# Using a source definition
-#
-DROP TABLE pivex;
-CREATE TABLE pivex (
-Who  CHAR(10) NOT NULL,
-Week INT(2) NOT NULL,
-Beer DOUBLE(8,2) FLAG=1,
-Car  DOUBLE(8,2) FLAG=1,
-Food DOUBLE(8,2) FLAG=1)
-ENGINE=CONNECT TABLE_TYPE=PIVOT
-SRCDEF='select who, week, what, sum(amount) as amount from expenses where week in (4,5) group by who, week, what';
-ALTER TABLE pivex OPTION_LIST='PivotCol=what,FncCol=amount,port=PORT';
-Warnings:
-Warning	1105	The current version of CONNECT did not check what you changed in ALTER. Use at your own risk
-SELECT * FROM pivex;
-Who	Week	Beer	Car	Food
-Beth	4	15.00	0.00	17.00
-Beth	5	20.00	0.00	12.00
-Janet	4	0.00	17.00	0.00
-Janet	5	33.00	12.00	12.00
-Joe	4	49.00	0.00	34.00
-Joe	5	14.00	0.00	12.00
-#
-# Pivoting from Week
-#
-DROP TABLE pivex;
-CREATE TABLE pivex (
-Who  CHAR(10) NOT NULL,
-What CHAR(12) NOT NULL,
-`3` DOUBLE(8,2) FLAG=1,
-`4` DOUBLE(8,2) FLAG=1,
-`5` DOUBLE(8,2) FLAG=1)
-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses;
-ALTER TABLE pivex OPTION_LIST='PivotCol=Week,port=PORT';
-Warnings:
-Warning	1105	The current version of CONNECT did not check what you changed in ALTER. Use at your own risk
-SELECT * FROM pivex;
-Who	What	3	4	5
-Beth	Beer	16.00	15.00	20.00
-Beth	Food	0.00	17.00	12.00
-Janet	Beer	18.00	0.00	33.00
-Janet	Car	19.00	17.00	12.00
-Janet	Food	18.00	0.00	12.00
-Joe	Beer	18.00	49.00	14.00
-Joe	Car	20.00	0.00	0.00
-Joe	Food	31.00	34.00	12.00
-#
-# Using scalar functions and expresssions
-#
-DROP TABLE pivex;
-CREATE TABLE pivex (
-Who  CHAR(10) NOT NULL,
-What CHAR(12) NOT NULL,
-First  DOUBLE(8,2) FLAG=1,
-Middle DOUBLE(8,2) FLAG=1,
-Last   DOUBLE(8,2) FLAG=1)
-ENGINE=CONNECT TABLE_TYPE=PIVOT
-SRCDEF='select who, what, case when week=3 then ''First'' when week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) * 6.56 as amnt from expenses group by who, what, wk';
-ALTER TABLE pivex OPTION_LIST='PivotCol=wk,FncCol=amnt,port=PORT';
-Warnings:
-Warning	1105	The current version of CONNECT did not check what you changed in ALTER. Use at your own risk
-SELECT * FROM pivex;
-Who	What	First	Middle	Last
-Beth	Beer	104.96	98.40	131.20
-Beth	Food	0.00	111.52	78.72
-Janet	Beer	118.08	0.00	216.48
-Janet	Car	124.64	111.52	78.72
-Janet	Food	118.08	0.00	78.72
-Joe	Beer	118.08	321.44	91.84
-Joe	Car	131.20	0.00	0.00
-Joe	Food	203.36	223.04	78.72
-DROP TABLE pivex;
 DROP TABLE expenses;
 #
 # Make the PETS table
@@ -167,64 +61,4 @@ Kevin	cat	2
 Kevin	bird	6
 Donald	dog	1
 Donald	fish	3
-#
-# Pivot the PETS table
-#
-CREATE TABLE pivet (
-name VARCHAR(12) NOT NULL,
-dog    INT NOT NULL DEFAULT 0 FLAG=1,
-cat    INT NOT NULL DEFAULT 0 FLAG=1,
-rabbit INT NOT NULL DEFAULT 0 FLAG=1,
-bird   INT NOT NULL DEFAULT 0 FLAG=1,
-fish   INT NOT NULL DEFAULT 0 FLAG=1)
-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';
-SELECT * FROM pivet;
-name	dog	cat	rabbit	bird	fish
-John	2	0	0	0	0
-Bill	0	1	0	0	0
-Mary	1	1	0	0	0
-Lisbeth	0	0	2	0	0
-Kevin	0	2	0	6	0
-Donald	1	0	0	0	3
-DROP TABLE pivet;
-#
-# Testing the "data" column list
-#
-CREATE TABLE pivet (
-name VARCHAR(12) NOT NULL,
-dog  INT NOT NULL DEFAULT 0 FLAG=1,
-cat  INT NOT NULL DEFAULT 0 FLAG=1)
-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';
-SELECT * FROM pivet;
-ERROR HY000: Got error 122 'Cannot find matching column' from CONNECT
-ALTER TABLE pivet OPTION_LIST='PivotCol=race,groupby=1,accept=1';
-Warnings:
-Warning	1105	The current version of CONNECT did not check what you changed in ALTER. Use at your own risk
-SELECT * FROM pivet;
-name	dog	cat
-John	2	0
-Bill	0	1
-Mary	1	1
-Lisbeth	0	0
-Kevin	0	2
-Donald	1	0
-DROP TABLE pivet;
-#
-# Adding a "dump" column
-#
-CREATE TABLE pivet (
-name VARCHAR(12) NOT NULL,
-dog   INT NOT NULL DEFAULT 0 FLAG=1,
-cat   INT NOT NULL DEFAULT 0 FLAG=1,
-other INT NOT NULL DEFAULT 0 FLAG=2)
-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';
-SELECT * FROM pivet;
-name	dog	cat	other
-John	2	0	0
-Bill	0	1	0
-Mary	1	1	0
-Lisbeth	0	0	2
-Kevin	0	2	6
-Donald	1	0	3
-DROP TABLE pivet;
 DROP TABLE pets;
diff --git a/storage/connect/mysql-test/connect/r/xcol.result b/storage/connect/mysql-test/connect/r/xcol.result
index f6899b47504..6f71ce44c62 100644
--- a/storage/connect/mysql-test/connect/r/xcol.result
+++ b/storage/connect/mysql-test/connect/r/xcol.result
@@ -20,7 +20,7 @@ Lisbeth	Lucy,Charles,Diana
 Corinne	NULL
 Claude	Marc
 Janet	Arthur,Sandra,Peter,John
-CREATE TABLE child ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=chlist OPTION_LIST='colname=children,port=PORT';
+CREATE TABLE child (mother varchar(12) NOT NULL, children varchar(30)) ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=chlist OPTION_LIST='colname=children,port=PORT';
 SELECT * FROM child;
 mother	children
 Sophia	Vivian
diff --git a/storage/connect/mysql-test/connect/t/mysql.test b/storage/connect/mysql-test/connect/t/mysql.test
index c7eacbd3d06..9f8660467d9 100644
--- a/storage/connect/mysql-test/connect/t/mysql.test
+++ b/storage/connect/mysql-test/connect/t/mysql.test
@@ -1,420 +1,431 @@
--- source include/not_embedded.inc
-
-#
-# TODO: consider a possibility to run this test
-# against some remote MySQL server
-#
-
-let $PORT= `select @@port`;
-
---disable_query_log
---replace_result $PORT PORT
---error 0,ER_UNKNOWN_ERROR
---eval CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-if (!`SELECT count(*) FROM INFORMATION_SCHEMA.TABLES
-  WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
-  AND ENGINE='CONNECT'
-  AND CREATE_OPTIONS LIKE '%`table_type`=MySQL%'`)
-{
-  Skip Need MySQL support;
-}
-DROP TABLE t1;
---enable_query_log
-
-# TODO: remote VARCHAR is displayed as CHAR
-
-CREATE TABLE t1 (a int, b char(10));
-INSERT INTO t1 VALUES (NULL,NULL),(0,'test00'),(1,'test01'),(2,'test02'),(3,'test03');
-SELECT * FROM t1;
-
---echo #
---echo # Testing errors
---echo #
-
-# Bad user name
-# Suppress "mysql_real_connect failed:" (printed in _DEBUG build)
---replace_result $PORT PORT "mysql_real_connect failed: " ""
---error ER_UNKNOWN_ERROR
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root1,port=$PORT'
-
-# Bad database name
---replace_result $PORT PORT "mysql_real_connect failed: " ""
---error ER_UNKNOWN_ERROR
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL DBNAME='unknown' TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-
-# Bad database name, with OPTION_LIST going first.
---replace_result $PORT PORT "mysql_real_connect failed: " ""
---error ER_UNKNOWN_ERROR
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL OPTION_LIST='host=localhost,user=root,port=$PORT' DBNAME='unknown' TABNAME='t1'
-
-# Bad table name
---replace_result $PORT PORT
---error ER_UNKNOWN_ERROR
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='unknown' OPTION_LIST='host=localhost,user=root,port=$PORT'
---error ER_NO_SUCH_TABLE
-SHOW CREATE TABLE t2;
-
-# Bad column name
---replace_result $PORT PORT
---eval CREATE TABLE t2 (x int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
---error ER_GET_ERRMSG
-SELECT * FROM t2;
-DROP TABLE t2;
-
-# The remote table disappeared
-ALTER TABLE t1 RENAME t1backup;
---error ER_NO_SUCH_TABLE
-SELECT * FROM t2;
-ALTER TABLE t1backup RENAME t1;
-
-
-
---echo #
---echo # Testing SELECT, etc.
---echo #
-
-# Automatic table structure
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2;
-
-
-# Explicit table structure
---replace_result $PORT PORT
---eval CREATE TABLE t2 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2;
-
-
-# Explicit table structure: remote NULL, local NOT NULL
---replace_result $PORT PORT
---eval CREATE TABLE t2 (a INT NOT NULL, b CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2;
-
-
-# Explicit table structure with wrong column types
---replace_result $PORT PORT
---eval CREATE TABLE t2 (a char(10), b int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2;
-
-DROP TABLE t1;
-
---echo #
---echo # Testing numeric data types
---echo #
-
-# TODO: tinyint is mapped to smallint
-#CREATE TABLE t1 (a tinyint);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: unsigned does not work
-#CREATE TABLE t1 (a tinyint unsigned);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-CREATE TABLE t1 (a smallint);
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-CREATE TABLE t1 (a mediumint);
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-CREATE TABLE t1 (a int);
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-
-# TODO: bigint is mapped to double(20,0)
-CREATE TABLE t1 (a bigint);
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-
-# TODO: ERROR 1439: Display width out of range for 'a' (max = 255)
-#CREATE TABLE t1 (a float);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1439: Display width out of range for 'a' (max = 255)
-#CREATE TABLE t1 (a double);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: decimal is converted to double
-#CREATE TABLE t1 (a decimal(20,5));
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: add test for BIT
-
---echo #
---echo # Testing character data types
---echo #
-
-# TODO: char is mapped to varchar
-CREATE TABLE t1 (a char(10));
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-CREATE TABLE t1 (a varchar(10));
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type tinytext
-#CREATE TABLE t1 (a tinytext);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type mediumtext
-#CREATE TABLE t1 (a mediumtext);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: text is converted to varchar(256)
-#CREATE TABLE t1 (a text);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type longtext
-#CREATE TABLE t1 (a longtext);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-#TODO: add tests for ENUM
-#TODO: add tests for SET
-
---echo #
---echo # Testing binary data types
---echo #
-
-# TODO: ERROR 1105: Unsupported column type binary
-#CREATE TABLE t1 (a binary(10));
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type varbinary
-#CREATE TABLE t1 (a varbinary(10));
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type tinyblob
-#CREATE TABLE t1 (a tinyblob);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type mediumblob
-#CREATE TABLE t1 (a mediumblob);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: blob is converted to varchar(256)
-#CREATE TABLE t1 (a blob);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type longblob
-#CREATE TABLE t1 (a longblob);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: ERROR 1105: Unsupported column type geometry
-#CREATE TABLE t1 (a geometry);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
---echo #
---echo # Testing temporal data types
---echo #
-
-# TODO: time is converted to date
-#CREATE TABLE t1 (a time);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-CREATE TABLE t1 (a date);
---replace_result $PORT PORT
---eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
---replace_result $PORT PORT
-SHOW CREATE TABLE t1;
---replace_result $PORT PORT
-SHOW CREATE TABLE t2;
-SELECT * FROM t2;
-DROP TABLE t2, t1;
-
-# TODO: datetime is converted to date
-#CREATE TABLE t1 (a datetime);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: timestamp is converted to date
-#CREATE TABLE t1 (a timestamp);
-#--replace_result $PORT PORT
-#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t1;
-#--replace_result $PORT PORT
-#SHOW CREATE TABLE t2;
-#SELECT * FROM t2;
-#DROP TABLE t2, t1;
-
-# TODO: add test for YEAR
-# TODO: add tests for fractional seconds
-
+-- source include/not_embedded.inc

+

+#

+# TODO: consider a possibility to run this test

+# against some remote MySQL server

+#

+

+let $PORT= `select @@port`;

+

+--disable_query_log

+--replace_result $PORT PORT

+--error 0,ER_UNKNOWN_ERROR

+--eval CREATE TABLE t1 (a INT) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+if (!`SELECT count(*) FROM INFORMATION_SCHEMA.TABLES

+  WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'

+  AND ENGINE='CONNECT'

+  AND CREATE_OPTIONS LIKE '%`table_type`=MySQL%'`)

+{

+  Skip Need MySQL support;

+}

+DROP TABLE t1;

+--enable_query_log

+

+# TODO: remote VARCHAR is displayed as CHAR

+

+CREATE TABLE t1 (a int, b char(10));

+INSERT INTO t1 VALUES (NULL,NULL),(0,'test00'),(1,'test01'),(2,'test02'),(3,'test03');

+SELECT * FROM t1;

+

+--echo #

+--echo # Testing errors

+--echo #

+

+# Bad user name

+# Suppress "mysql_real_connect failed:" (printed in _DEBUG build)

+#--replace_result $PORT PORT "mysql_real_connect failed: " ""

+#--error ER_UNKNOWN_ERROR

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root1,port=$PORT'

+

+# Bad database name

+#--replace_result $PORT PORT "mysql_real_connect failed: " ""

+#--error ER_UNKNOWN_ERROR

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL DBNAME='unknown' TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+

+# Bad database name, with OPTION_LIST going first.

+#--replace_result $PORT PORT "mysql_real_connect failed: " ""

+#--error ER_UNKNOWN_ERROR

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL OPTION_LIST='host=localhost,user=root,port=$PORT' DBNAME='unknown' TABNAME='t1'

+

+# Bad table name

+#--replace_result $PORT PORT

+#--error ER_UNKNOWN_ERROR

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='unknown' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--error ER_NO_SUCH_TABLE

+#SHOW CREATE TABLE t2;

+

+# Bad column name

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 (x int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+--eval CREATE TABLE t2 (a int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+SHOW CREATE TABLE t2;

+--error ER_GET_ERRMSG

+SELECT * FROM t2;

+DROP TABLE t2;

+

+# The remote table disappeared

+--replace_result $PORT PORT

+--eval CREATE TABLE t2 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+ALTER TABLE t1 RENAME t1backup;

+#--error ER_NO_SUCH_TABLE

+--error ER_GET_ERRMSG

+SELECT * FROM t2;

+ALTER TABLE t1backup RENAME t1;

+DROP TABLE t2;

+

+

+--echo #

+--echo # Testing SELECT, etc.

+--echo #

+

+# Automatic table structure

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2;

+

+

+# Explicit table structure

+--replace_result $PORT PORT

+--eval CREATE TABLE t2 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2;

+

+

+# Explicit table structure: remote NULL, local NOT NULL

+--replace_result $PORT PORT

+--eval CREATE TABLE t2 (a INT NOT NULL, b CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2;

+

+

+# Explicit table structure with wrong column types

+--replace_result $PORT PORT

+--eval CREATE TABLE t2 (a char(10), b int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2;

+

+DROP TABLE t1;

+

+--echo #

+--echo # Testing numeric data types

+--echo #

+

+# TODO: tinyint is mapped to smallint

+#CREATE TABLE t1 (a tinyint);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: unsigned does not work

+#CREATE TABLE t1 (a tinyint unsigned);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+CREATE TABLE t1 (a smallint);

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a smallint) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+CREATE TABLE t1 (a mediumint);

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+CREATE TABLE t1 (a int);

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+

+# TODO: bigint is mapped to double(20,0)

+CREATE TABLE t1 (a bigint);

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a bigint) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+

+# TODO: ERROR 1439: Display width out of range for 'a' (max = 255)

+#CREATE TABLE t1 (a float);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1439: Display width out of range for 'a' (max = 255)

+#CREATE TABLE t1 (a double);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: decimal is converted to double

+#CREATE TABLE t1 (a decimal(20,5));

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: add test for BIT

+

+--echo #

+--echo # Testing character data types

+--echo #

+

+# TODO: char is mapped to varchar

+CREATE TABLE t1 (a char(10));

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a varchar(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+CREATE TABLE t1 (a varchar(10));

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a varchar(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type tinytext

+#CREATE TABLE t1 (a tinytext);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type mediumtext

+#CREATE TABLE t1 (a mediumtext);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: text is converted to varchar(256)

+#CREATE TABLE t1 (a text);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type longtext

+#CREATE TABLE t1 (a longtext);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+#TODO: add tests for ENUM

+#TODO: add tests for SET

+

+--echo #

+--echo # Testing binary data types

+--echo #

+

+# TODO: ERROR 1105: Unsupported column type binary

+#CREATE TABLE t1 (a binary(10));

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type varbinary

+#CREATE TABLE t1 (a varbinary(10));

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type tinyblob

+#CREATE TABLE t1 (a tinyblob);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type mediumblob

+#CREATE TABLE t1 (a mediumblob);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: blob is converted to varchar(256)

+#CREATE TABLE t1 (a blob);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type longblob

+#CREATE TABLE t1 (a longblob);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: ERROR 1105: Unsupported column type geometry

+#CREATE TABLE t1 (a geometry);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+--echo #

+--echo # Testing temporal data types

+--echo #

+

+# TODO: time is converted to date

+#CREATE TABLE t1 (a time);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+CREATE TABLE t1 (a date);

+--replace_result $PORT PORT

+#-eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--eval CREATE TABLE t2 (a date) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+--replace_result $PORT PORT

+SHOW CREATE TABLE t1;

+--replace_result $PORT PORT

+SHOW CREATE TABLE t2;

+SELECT * FROM t2;

+DROP TABLE t2, t1;

+

+# TODO: datetime is converted to date

+#CREATE TABLE t1 (a datetime);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: timestamp is converted to date

+#CREATE TABLE t1 (a timestamp);

+#--replace_result $PORT PORT

+#--eval CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=$PORT'

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t1;

+#--replace_result $PORT PORT

+#SHOW CREATE TABLE t2;

+#SELECT * FROM t2;

+#DROP TABLE t2, t1;

+

+# TODO: add test for YEAR

+# TODO: add tests for fractional seconds

+

diff --git a/storage/connect/mysql-test/connect/t/pivot.test b/storage/connect/mysql-test/connect/t/pivot.test
index 7679434bca8..cba1acfaa26 100644
--- a/storage/connect/mysql-test/connect/t/pivot.test
+++ b/storage/connect/mysql-test/connect/t/pivot.test
@@ -5,7 +5,7 @@ let $PORT= `select @@port`;
 --copy_file $MTR_SUITE_DIR/std_data/expenses.txt $MYSQLD_DATADIR/test/expenses.txt

 

 --echo #

---echo # Testing the PIVOT table type

+--echo # PIVOT table type is temporarily not supported

 --echo #

 CREATE TABLE expenses (

 Who  CHAR(10) NOT NULL,

@@ -15,73 +15,73 @@ Amount DOUBLE(8,2))
 ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='expenses.txt' ENDING=2;

 SELECT * FROM expenses;

 

---echo #

---echo # Pivoting from What

---echo #

-CREATE TABLE pivex (

-Who  CHAR(10) NOT NULL,

-Week INT(2) NOT NULL,

-Beer DOUBLE(8,2) FLAG=1,

-Car  DOUBLE(8,2) FLAG=1,

-Food DOUBLE(8,2) FLAG=1)

-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses;

---replace_result $PORT PORT

---eval ALTER TABLE pivex OPTION_LIST='port=$PORT'

-SELECT * FROM pivex;

+#--echo #

+#--echo # Pivoting from What

+#--echo #

+#CREATE TABLE pivex (

+#Who  CHAR(10) NOT NULL,

+#Week INT(2) NOT NULL,

+#Beer DOUBLE(8,2) FLAG=1,

+#Car  DOUBLE(8,2) FLAG=1,

+#Food DOUBLE(8,2) FLAG=1)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses;

+#--replace_result $PORT PORT

+#--eval ALTER TABLE pivex OPTION_LIST='port=$PORT'

+#SELECT * FROM pivex;

 

---echo #

---echo # Restricting the columns in a Pivot Table

---echo #

-ALTER TABLE pivex DROP COLUMN week;

-SELECT * FROM pivex;

+#--echo #

+#--echo # Restricting the columns in a Pivot Table

+#--echo #

+#ALTER TABLE pivex DROP COLUMN week;

+#SELECT * FROM pivex;

 

---echo #

---echo # Using a source definition

---echo #

-DROP TABLE pivex;

-CREATE TABLE pivex (

-Who  CHAR(10) NOT NULL,

-Week INT(2) NOT NULL,

-Beer DOUBLE(8,2) FLAG=1,

-Car  DOUBLE(8,2) FLAG=1,

-Food DOUBLE(8,2) FLAG=1)

-ENGINE=CONNECT TABLE_TYPE=PIVOT

-SRCDEF='select who, week, what, sum(amount) as amount from expenses where week in (4,5) group by who, week, what';

---replace_result $PORT PORT

---eval ALTER TABLE pivex OPTION_LIST='PivotCol=what,FncCol=amount,port=$PORT'

-SELECT * FROM pivex;

+#--echo #

+#--echo # Using a source definition

+#--echo #

+#DROP TABLE pivex;

+#CREATE TABLE pivex (

+#Who  CHAR(10) NOT NULL,

+#Week INT(2) NOT NULL,

+#Beer DOUBLE(8,2) FLAG=1,

+#Car  DOUBLE(8,2) FLAG=1,

+#Food DOUBLE(8,2) FLAG=1)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT

+#SRCDEF='select who, week, what, sum(amount) as amount from expenses where week in (4,5) group by who, week, what';

+#--replace_result $PORT PORT

+#--eval ALTER TABLE pivex OPTION_LIST='PivotCol=what,FncCol=amount,port=$PORT'

+#SELECT * FROM pivex;

 

---echo #

---echo # Pivoting from Week

---echo #

-DROP TABLE pivex;

-CREATE TABLE pivex (

-Who  CHAR(10) NOT NULL,

-What CHAR(12) NOT NULL,

-`3` DOUBLE(8,2) FLAG=1,

-`4` DOUBLE(8,2) FLAG=1,

-`5` DOUBLE(8,2) FLAG=1)

-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses;

---replace_result $PORT PORT

---eval ALTER TABLE pivex OPTION_LIST='PivotCol=Week,port=$PORT'

-SELECT * FROM pivex;

+#--echo #

+#--echo # Pivoting from Week

+#--echo #

+#DROP TABLE pivex;

+#CREATE TABLE pivex (

+#Who  CHAR(10) NOT NULL,

+#What CHAR(12) NOT NULL,

+#`3` DOUBLE(8,2) FLAG=1,

+#`4` DOUBLE(8,2) FLAG=1,

+#`5` DOUBLE(8,2) FLAG=1)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses;

+#--replace_result $PORT PORT

+#--eval ALTER TABLE pivex OPTION_LIST='PivotCol=Week,port=$PORT'

+#SELECT * FROM pivex;

 

---echo #

---echo # Using scalar functions and expresssions

---echo #

-DROP TABLE pivex;

-CREATE TABLE pivex (

-Who  CHAR(10) NOT NULL,

-What CHAR(12) NOT NULL,

-First  DOUBLE(8,2) FLAG=1,

-Middle DOUBLE(8,2) FLAG=1,

-Last   DOUBLE(8,2) FLAG=1)

-ENGINE=CONNECT TABLE_TYPE=PIVOT

-SRCDEF='select who, what, case when week=3 then ''First'' when week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) * 6.56 as amnt from expenses group by who, what, wk';

---replace_result $PORT PORT

---eval ALTER TABLE pivex OPTION_LIST='PivotCol=wk,FncCol=amnt,port=$PORT'

-SELECT * FROM pivex;

-DROP TABLE pivex;

+#--echo #

+#--echo # Using scalar functions and expresssions

+#--echo #

+#DROP TABLE pivex;

+#CREATE TABLE pivex (

+#Who  CHAR(10) NOT NULL,

+#What CHAR(12) NOT NULL,

+#First  DOUBLE(8,2) FLAG=1,

+#Middle DOUBLE(8,2) FLAG=1,

+#Last   DOUBLE(8,2) FLAG=1)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT

+#SRCDEF='select who, what, case when week=3 then ''First'' when week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) * 6.56 as amnt from expenses group by who, what, wk';

+#--replace_result $PORT PORT

+#--eval ALTER TABLE pivex OPTION_LIST='PivotCol=wk,FncCol=amnt,port=$PORT'

+#SELECT * FROM pivex;

+#DROP TABLE pivex;

 DROP TABLE expenses;

 

 --echo #

@@ -102,45 +102,45 @@ INSERT INTO pets VALUES('Donald','dog',1);
 INSERT INTO pets VALUES('Donald','fish',3);

 SELECT * FROM pets;

 

---echo #

---echo # Pivot the PETS table

---echo #

-CREATE TABLE pivet (

-name VARCHAR(12) NOT NULL,

-dog    INT NOT NULL DEFAULT 0 FLAG=1,

-cat    INT NOT NULL DEFAULT 0 FLAG=1,

-rabbit INT NOT NULL DEFAULT 0 FLAG=1,

-bird   INT NOT NULL DEFAULT 0 FLAG=1,

-fish   INT NOT NULL DEFAULT 0 FLAG=1)

-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';

-SELECT * FROM pivet;

-DROP TABLE pivet;

+#--echo #

+#--echo # Pivot the PETS table

+#--echo #

+#CREATE TABLE pivet (

+#name VARCHAR(12) NOT NULL,

+#dog    INT NOT NULL DEFAULT 0 FLAG=1,

+#cat    INT NOT NULL DEFAULT 0 FLAG=1,

+#rabbit INT NOT NULL DEFAULT 0 FLAG=1,

+#bird   INT NOT NULL DEFAULT 0 FLAG=1,

+#fish   INT NOT NULL DEFAULT 0 FLAG=1)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';

+#SELECT * FROM pivet;

+#DROP TABLE pivet;

 

---echo #

---echo # Testing the "data" column list

---echo #

-CREATE TABLE pivet (

-name VARCHAR(12) NOT NULL,

-dog  INT NOT NULL DEFAULT 0 FLAG=1,

-cat  INT NOT NULL DEFAULT 0 FLAG=1)

-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';

---error ER_GET_ERRMSG

-SELECT * FROM pivet;

-ALTER TABLE pivet OPTION_LIST='PivotCol=race,groupby=1,accept=1';

-SELECT * FROM pivet;

-DROP TABLE pivet;

+#--echo #

+#--echo # Testing the "data" column list

+#--echo #

+#CREATE TABLE pivet (

+#name VARCHAR(12) NOT NULL,

+#dog  INT NOT NULL DEFAULT 0 FLAG=1,

+#cat  INT NOT NULL DEFAULT 0 FLAG=1)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';

+#--error ER_GET_ERRMSG

+#SELECT * FROM pivet;

+#ALTER TABLE pivet OPTION_LIST='PivotCol=race,groupby=1,accept=1';

+#SELECT * FROM pivet;

+#DROP TABLE pivet;

 

---echo #

---echo # Adding a "dump" column

---echo #

-CREATE TABLE pivet (

-name VARCHAR(12) NOT NULL,

-dog   INT NOT NULL DEFAULT 0 FLAG=1,

-cat   INT NOT NULL DEFAULT 0 FLAG=1,

-other INT NOT NULL DEFAULT 0 FLAG=2)

-ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';

-SELECT * FROM pivet;

+#--echo #

+#--echo # Adding a "dump" column

+#--echo #

+#CREATE TABLE pivet (

+#name VARCHAR(12) NOT NULL,

+#dog   INT NOT NULL DEFAULT 0 FLAG=1,

+#cat   INT NOT NULL DEFAULT 0 FLAG=1,

+#other INT NOT NULL DEFAULT 0 FLAG=2)

+#ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1';

+#SELECT * FROM pivet;

 

-DROP TABLE pivet;

+#DROP TABLE pivet;

 DROP TABLE pets;

 --remove_file $MYSQLD_DATADIR/test/expenses.txt

diff --git a/storage/connect/mysql-test/connect/t/xcol.test b/storage/connect/mysql-test/connect/t/xcol.test
index b6998ee77e0..17ff98a3cb5 100644
--- a/storage/connect/mysql-test/connect/t/xcol.test
+++ b/storage/connect/mysql-test/connect/t/xcol.test
@@ -16,7 +16,8 @@ INSERT INTO chlist VALUES('Janet','Arthur,Sandra,Peter,John');
 SELECT * FROM chlist;

 

 --replace_result $PORT PORT

---eval CREATE TABLE child ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=chlist OPTION_LIST='colname=children,port=$PORT'

+#-eval CREATE TABLE child ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=chlist OPTION_LIST='colname=children,port=$PORT'

+--eval CREATE TABLE child (mother varchar(12) NOT NULL, children varchar(30)) ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=chlist OPTION_LIST='colname=children,port=$PORT'

 SELECT * FROM child;

 SELECT * FROM child ORDER BY mother;

 SELECT * FROM child ORDER BY children;