- Restore tests to handle the cases that were giving Valgrind warnings.

modified:
  storage/connect/mysql-test/connect/r/mysql.result
  storage/connect/mysql-test/connect/r/odbc_sqlite3.result
  storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
  storage/connect/mysql-test/connect/r/odbc_xls.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

9 files changed, 343 insertions, 156 deletions

diff --git a/storage/connect/mysql-test/connect/r/mysql.result b/storage/connect/mysql-test/connect/r/mysql.result
index aa40c91c6d1..2ae0a950eb1 100644
--- a/storage/connect/mysql-test/connect/r/mysql.result
+++ b/storage/connect/mysql-test/connect/r/mysql.result
@@ -10,15 +10,25 @@ NULL	NULL
 #
 # Testing errors
 #
-CREATE TABLE t2 (a int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+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';
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `a` int(11) DEFAULT NULL,
+  `x` 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'
 SELECT * FROM t2;
-ERROR HY000: Got error 174 '(1054) Unknown column 'y' in 'field list' [SELECT `a`, `y` FROM `t1`]' from CONNECT
+ERROR HY000: Got error 174 '(1054) Unknown column 'x' in 'field list' [SELECT `x`, `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;
@@ -29,6 +39,21 @@ 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
@@ -79,7 +104,7 @@ DROP TABLE t1;
 # Testing numeric data types
 #
 CREATE TABLE t1 (a smallint);
-CREATE TABLE t2 (a smallint) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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` (
@@ -94,7 +119,7 @@ SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a mediumint);
-CREATE TABLE t2 (a int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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` (
@@ -103,13 +128,13 @@ t1	CREATE TABLE `t1` (
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `a` int(11) DEFAULT NULL
+  `a` int(9) 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 (a int) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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` (
@@ -124,7 +149,7 @@ SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a bigint);
-CREATE TABLE t2 (a bigint) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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,7 +167,7 @@ DROP TABLE t2, t1;
 # Testing character data types
 #
 CREATE TABLE t1 (a char(10));
-CREATE TABLE t2 (a varchar(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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 +182,7 @@ SELECT * FROM t2;
 a
 DROP TABLE t2, t1;
 CREATE TABLE t1 (a varchar(10));
-CREATE TABLE t2 (a varchar(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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` (
@@ -178,7 +203,7 @@ DROP TABLE t2, t1;
 # Testing temporal data types
 #
 CREATE TABLE t1 (a date);
-CREATE TABLE t2 (a date) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,port=PORT';
+CREATE TABLE t2 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` (
diff --git a/storage/connect/mysql-test/connect/r/odbc_sqlite3.result b/storage/connect/mysql-test/connect/r/odbc_sqlite3.result
index 339dbb6a53d..9871e71fd29 100644
--- a/storage/connect/mysql-test/connect/r/odbc_sqlite3.result
+++ b/storage/connect/mysql-test/connect/r/odbc_sqlite3.result
@@ -2,14 +2,14 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `Description` varchar(128) NOT NULL,
   `Attributes` varchar(256) NOT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Drivers'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=ODBC `CATFUNC`=Drivers `tabname`=t1
 SET NAMES utf8;
 CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' CHARSET=utf8 DATA_CHARSET=utf8;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(64) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' `TABLE_TYPE`='ODBC' `DATA_CHARSET`='utf8'
+) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' `TABLE_TYPE`=ODBC `DATA_CHARSET`=utf8 `tabname`=t1
 SELECT * FROM t1;
 a
 test1
diff --git a/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result b/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
index 87f7803166c..a054e66fcc0 100644
--- a/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
+++ b/storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result
@@ -2,7 +2,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `Description` varchar(128) NOT NULL,
   `Attributes` varchar(256) NOT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Drivers'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=ODBC `CATFUNC`=Drivers `tabname`=t1
 SET NAMES utf8;
 GRANT ALL PRIVILEGES ON *.* TO user@localhost;
 REVOKE FILE ON *.* FROM user@localhost;
@@ -23,7 +23,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(64) DEFAULT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' `TABLE_TYPE`='ODBC' `DATA_CHARSET`='utf8'
+) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='Driver=SQLite3 ODBC Driver;Database=MTR_SUITE_DIR/std_data/test.sqlite3;NoWCHAR=yes' `TABLE_TYPE`=ODBC `DATA_CHARSET`=utf8 `tabname`=t1
 SELECT * FROM t1;
 a
 test1
diff --git a/storage/connect/mysql-test/connect/r/odbc_xls.result b/storage/connect/mysql-test/connect/r/odbc_xls.result
index 4f080eae986..a6b342c0862 100644
--- a/storage/connect/mysql-test/connect/r/odbc_xls.result
+++ b/storage/connect/mysql-test/connect/r/odbc_xls.result
@@ -2,7 +2,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `Name` varchar(256) NOT NULL,
   `Description` varchar(256) NOT NULL
-) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Sources'
+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=ODBC `CATFUNC`=Sources `tabname`=t1
 CREATE TABLE contact (Nom VARCHAR(128), Fonction VARCHAR(128), Company VARCHAR(128), Repertoire VARCHAR(30)) ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineXLS;DBQ=DATADIR/test/contacts.xls';;
 SELECT Nom, Fonction FROM contact WHERE Repertoire='ascii';
 Nom	Fonction
diff --git a/storage/connect/mysql-test/connect/r/pivot.result b/storage/connect/mysql-test/connect/r/pivot.result
index 8a5151a4a15..11eee6233a4 100644
--- a/storage/connect/mysql-test/connect/r/pivot.result
+++ b/storage/connect/mysql-test/connect/r/pivot.result
@@ -1,5 +1,5 @@
 #
-# PIVOT table type is temporarily not supported
+# Testing the PIVOT table type
 #
 CREATE TABLE expenses (
 Who  CHAR(10) NOT NULL,
@@ -33,6 +33,116 @@ 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';
+Warnings:
+Warning	1105	Cannot check looping reference
+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';
+Warnings:
+Warning	1105	Cannot check looping reference
+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
@@ -61,4 +171,64 @@ 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 6f71ce44c62..f6899b47504 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 (mother varchar(12) NOT NULL, children varchar(30)) ENGINE=CONNECT TABLE_TYPE=XCOL TABNAME=chlist OPTION_LIST='colname=children,port=PORT';
+CREATE TABLE child 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 c7d7cafe51b..bb4b2e78e56 100644
--- a/storage/connect/mysql-test/connect/t/mysql.test
+++ b/storage/connect/mysql-test/connect/t/mysql.test
@@ -33,30 +33,30 @@ SELECT * FROM t1;
 

 # 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'

+--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'

+--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'

+--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;

+--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 (a int, y char(10)) ENGINE=CONNECT TABLE_TYPE=MYSQL TABNAME='t1' OPTION_LIST='host=localhost,user=root,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

@@ -78,12 +78,12 @@ DROP TABLE t2;
 --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;

+--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

@@ -141,8 +141,7 @@ DROP TABLE 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'

+--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

@@ -152,8 +151,7 @@ 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'

+--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

@@ -163,8 +161,7 @@ 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'

+--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

@@ -176,8 +173,7 @@ 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'

+--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

@@ -228,8 +224,7 @@ DROP TABLE t2, t1;
 # 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'

+--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

@@ -239,8 +234,7 @@ 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'

+--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

@@ -393,8 +387,7 @@ 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'

+--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

diff --git a/storage/connect/mysql-test/connect/t/pivot.test b/storage/connect/mysql-test/connect/t/pivot.test
index cba1acfaa26..7679434bca8 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 # PIVOT table type is temporarily not supported

+--echo # Testing the PIVOT table type

 --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 17ff98a3cb5..b6998ee77e0 100644
--- a/storage/connect/mysql-test/connect/t/xcol.test
+++ b/storage/connect/mysql-test/connect/t/xcol.test
@@ -16,8 +16,7 @@ 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 (mother varchar(12) NOT NULL, children varchar(30)) 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'

 SELECT * FROM child;

 SELECT * FROM child ORDER BY mother;

 SELECT * FROM child ORDER BY children;