+#

+# Beginning of grant.inc

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=BIN;

+Warnings:

+Warning 1105 No file name. Table will use t1.bin

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+UPDATE t1 SET a=20;

+SELECT * FROM t1;

+a

+20

+DELETE FROM t1;

+SELECT * FROM t1;

+a

+INSERT INTO t1 VALUES(10);

+TRUNCATE TABLE t1;

+SELECT * FROM t1;

+a

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT * FROM v1;

+a

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=BIN FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=BIN FILE_NAME='t1.EXT';

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+INSERT INTO t1 VALUES (10);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a=20;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 FILE_NAME='t2.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=BIN;

+Warnings:

+Warning 1105 No file name. Table will use t1.bin

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+ALTER TABLE t1 FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+DROP USER user@localhost;

+#

+# End of grant.inc

+#

+#

+# Testing errors

+#

+CREATE TABLE t1

+(

+ID INT NOT NULL

+) Engine=CONNECT TABLE_TYPE=BIN FILE_NAME='nonexistent.txt';

+SELECT * FROM t1;

+ID

+Warnings:

+Warning 1105 Open(rb) error 2 on DATADIR/test/nonexistent.txt: No such file or directory

+DROP TABLE t1;

+SET time_zone='+00:00';

+CREATE TABLE t1

+(

+fig INT(4) NOT NULL FIELD_FORMAT='C',

+name CHAR(10) not null,

+birth DATE NOT NULL,

+id CHAR(5) NOT NULL FIELD_FORMAT='S',

+salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='F',

+dept INT(4) NOT NULL FIELD_FORMAT='S'

+) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=5 FILE_NAME='Testbal.dat';

+SELECT * FROM t1;

+fig name birth id salary dept

+5500 ARCHIBALD 1980-01-25 3789 4380.50 318

+123 OLIVER 1953-08-10 23456 3400.68 2158

+3123 FOO 2002-07-23 888 0.00 318

+INSERT INTO t1 VALUES (55555,'RONALD','1980-02-26','3333',4444.44,555);

+ERROR HY000: Got error 122 'Value too long for field fig (5 --> 4)' from CONNECT

+INSERT INTO t1 VALUES (5555,'RONALD','1980-02-26','3333',4444.44,555);

+SELECT * FROM t1;

+fig name birth id salary dept

+5500 ARCHIBALD 1980-01-25 3789 4380.50 318

+123 OLIVER 1953-08-10 23456 3400.68 2158

+3123 FOO 2002-07-23 888 0.00 318

+5555 RONALD 1980-02-26 3333 4444.44 555

+DROP TABLE t1;

+#

+# Testing READONLY tables

+#

+CREATE TABLE t1

+(

+fig INT(4) NOT NULL FIELD_FORMAT='C',

+name CHAR(10) not null,

+birth DATE NOT NULL,

+id CHAR(5) NOT NULL FIELD_FORMAT='S',

+salary DOUBLE(9,2) NOT NULL DEFAULT 0.00 FIELD_FORMAT='F',

+dept INT(4) NOT NULL FIELD_FORMAT='S'

+) ENGINE=CONNECT TABLE_TYPE=BIN READONLY=Yes FILE_NAME='Testbal.dat';

+INSERT INTO t1 VALUES (7777,'BILL','1973-06-30',4444,5555.555,777);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+ALTER TABLE t1 READONLY=NO;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `fig` int(4) NOT NULL `FIELD_FORMAT`='C',

+ `name` char(10) NOT NULL,

+ `birth` date NOT NULL,

+ `id` char(5) NOT NULL `FIELD_FORMAT`='S',

+ `salary` double(9,2) NOT NULL DEFAULT '0.00' `FIELD_FORMAT`='F',

+ `dept` int(4) NOT NULL `FIELD_FORMAT`='S'

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=BIN `FILE_NAME`='Testbal.dat' `READONLY`=NO

+INSERT INTO t1 VALUES (7777,'BILL','1973-06-30',4444,5555.555,777);

+SELECT * FROM t1;

+fig name birth id salary dept

+5500 ARCHIBALD 1980-01-25 3789 4380.50 318

+123 OLIVER 1953-08-10 23456 3400.68 2158

+3123 FOO 2002-07-23 888 0.00 318

+5555 RONALD 1980-02-26 3333 4444.44 555

+7777 BILL 1973-06-30 4444 5555.56 777

+ALTER TABLE t1 READONLY=YES;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `fig` int(4) NOT NULL `FIELD_FORMAT`='C',

+ `name` char(10) NOT NULL,

+ `birth` date NOT NULL,

+ `id` char(5) NOT NULL `FIELD_FORMAT`='S',

+ `salary` double(9,2) NOT NULL DEFAULT '0.00' `FIELD_FORMAT`='F',

+ `dept` int(4) NOT NULL `FIELD_FORMAT`='S'

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=BIN `FILE_NAME`='Testbal.dat' `READONLY`=YES

+INSERT INTO t1 VALUES (7777,'BILL','1973-06-30',4444,5555.555,777);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+DROP TABLE t1;

+#

+# Testing that the underlying file is created

+#

+CREATE TABLE t1

+(

+c CHAR(4) NOT NULL FIELD_FORMAT='C'

+) ENGINE=CONNECT TABLE_TYPE=BIN FILE_NAME='bin2.dat';

+INSERT INTO t1 VALUES (10),(20),(300),(4000);

+SELECT * FROM t1;

+c

+10

+20

+300

+4000

+DROP TABLE t1;

+#

+# Beginning of grant.inc

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=CSV;

+Warnings:

+Warning 1105 No file name. Table will use t1.csv

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+UPDATE t1 SET a=20;

+SELECT * FROM t1;

+a

+20

+DELETE FROM t1;

+SELECT * FROM t1;

+a

+INSERT INTO t1 VALUES(10);

+TRUNCATE TABLE t1;

+SELECT * FROM t1;

+a

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT * FROM v1;

+a

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.EXT';

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+INSERT INTO t1 VALUES (10);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a=20;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 FILE_NAME='t2.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=CSV;

+Warnings:

+Warning 1105 No file name. Table will use t1.csv

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+ALTER TABLE t1 FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+DROP USER user@localhost;

+#

+# End of grant.inc

+#

+SET NAMES utf8;

+#

+# Testing errors

+#

+CREATE TABLE t1

+(

+ID INT NOT NULL

+) Engine=CONNECT TABLE_TYPE=CSV FILE_NAME='nonexistent.txt';

+SELECT * FROM t1;

+ID

+Warnings:

+Warning 1105 Open(rb) error 2 on DATADIR/test/nonexistent.txt: No such file or directory

+DROP TABLE t1;

+#

+# Testing examples from the manual

+#

+CREATE TABLE t1

+(

+name CHAR(12) NOT NULL,

+birth DATE NOT NULL DATE_FORMAT='DD/MM/YY',

+children SMALLINT(2) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='people.csv'

+ HEADER=1 SEP_CHAR=';' QUOTED=1;

+SELECT * FROM t1;

+name birth children

+Archibald 2001-05-17 3

+Nabucho 2003-08-12 2

+INSERT INTO t1 VALUES ('RONALD','1980-02-26',4);

+SELECT * FROM t1;

+name birth children

+Archibald 2001-05-17 3

+Nabucho 2003-08-12 2

+RONALD 1980-02-26 4

+DROP TABLE t1;

+SELECT REPLACE(LOAD_FILE('DATADIR/test/people.csv'),'\r\n','\n');;

+REPLACE(LOAD_FILE('DATADIR/test/people.csv'),'\r\n','\n')

+Name;birth;children

+"Archibald";17/05/01;3

+"Nabucho";12/08/03;2

+"RONALD";26/02/80;4

+

+#

+# Testing READONLY tables

+#

+CREATE TABLE t1

+(

+name CHAR(12) NOT NULL,

+birth DATE NOT NULL DATE_FORMAT='DD/MM/YY',

+children SMALLINT(2) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='people.csv'

+ HEADER=1 SEP_CHAR=';' QUOTED=1 READONLY=yes;

+INSERT INTO t1 VALUES ('BILL','1973-06-30',5);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+UPDATE t1 SET children=6 WHERE name='BILL';

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+DELETE FROM t1 WHERE name='BILL';

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+TRUNCATE TABLE t1;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+SELECT * FROM t1;

+name birth children

+Archibald 2001-05-17 3

+Nabucho 2003-08-12 2

+RONALD 1980-02-26 4

+ALTER TABLE t1 READONLY=no;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `name` char(12) NOT NULL,

+ `birth` date NOT NULL `DATE_FORMAT`='DD/MM/YY',

+ `children` smallint(2) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=CSV `FILE_NAME`='people.csv' `HEADER`=1 `SEP_CHAR`=';' `QUOTED`=1 `READONLY`=no

+INSERT INTO t1 VALUES ('BILL','1973-06-30',5);

+SELECT * FROM t1;

+name birth children

+Archibald 2001-05-17 3

+Nabucho 2003-08-12 2

+RONALD 1980-02-26 4

+BILL 1973-06-30 5

+ALTER TABLE t1 READONLY=1;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `name` char(12) NOT NULL,

+ `birth` date NOT NULL `DATE_FORMAT`='DD/MM/YY',

+ `children` smallint(2) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=CSV `FILE_NAME`='people.csv' `HEADER`=1 `SEP_CHAR`=';' `QUOTED`=1 `READONLY`=1

+INSERT INTO t1 VALUES ('BILL','1973-06-30',5);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+SELECT * FROM t1;

+name birth children

+Archibald 2001-05-17 3

+Nabucho 2003-08-12 2

+RONALD 1980-02-26 4

+BILL 1973-06-30 5

+DROP TABLE t1;

+#

+# Testing that the underlying file is created

+#

+CREATE TABLE t1

+(

+c1 CHAR(12) NOT NULL,

+c2 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='tmp.csv'

+ HEADER=1 SEP_CHAR=',' QUOTED=1;

+INSERT INTO t1 VALUES (10,10),(20,20),(300,300),(4000,4000), ('a b','c d');

+SELECT * FROM t1;

+c1 c2

+10 10

+20 20

+300 300

+4000 4000

+a b c d

+DROP TABLE t1;

+SELECT REPLACE(LOAD_FILE('DATADIR/test/tmp.csv'),'\r\n','\n');;

+REPLACE(LOAD_FILE('DATADIR/test/tmp.csv'),'\r\n','\n')

+"c1","c2"

+"10","10"

+"20","20"

+"300","300"

+"4000","4000"

+"a b","c d"

+

+#

+# Creating a CSV table from a MyISAM table

+#

+CREATE TABLE t1 (a VARCHAR(10) NOT NULL, b INT NOT NULL) ENGINE=MyISAM;

+INSERT INTO t1 VALUES ('test1',1), ('test2',2);

+CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t2.csv'

+ AS SELECT * FROM t1;

+SELECT * FROM t2;

+a b

+test1 1

+test2 2

+DROP TABLE t2;

+DROP TABLE t1;

+SELECT REPLACE(LOAD_FILE('DATADIR/test/t2.csv'),'\r\n','\n');;

+REPLACE(LOAD_FILE('DATADIR/test/t2.csv'),'\r\n','\n')

+test1,1

+test2,2

+

+#

+# Testing international data

+#

+CREATE TABLE t1

+(

+c1 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv'

+ CHARSET=utf8;

+INSERT INTO t1 VALUES ('á');

+SELECT * FROM t1;

+c1

+á

+DROP TABLE t1;

+SELECT HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'));;

+HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'))

+C3A10A

+CREATE TABLE t1

+(

+c1 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv'

+ CHARSET=utf8 DATA_CHARSET=latin1;

+INSERT INTO t1 VALUES ('á');

+SELECT * FROM t1;

+c1

+á

+DROP TABLE t1;

+SELECT HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'));;

+HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'))

+E10A

+CREATE TABLE t1

+(

+c1 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv';

+INSERT INTO t1 VALUES ('á');

+SELECT * FROM t1;

+c1

+á

+DROP TABLE t1;

+SELECT HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'));;

+HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'))

+E10A

+CREATE TABLE t1

+(

+c1 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv'

+ CHARSET=latin1;

+INSERT INTO t1 VALUES ('á');

+SELECT * FROM t1;

+c1

+á

+DROP TABLE t1;

+SELECT HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'));;

+HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'))

+E10A

+CREATE TABLE t1

+(

+c1 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv'

+ CHARSET=latin1 DATA_CHARSET=utf8;

+INSERT INTO t1 VALUES ('á');

+SELECT * FROM t1;

+c1

+á

+DROP TABLE t1;

+SELECT HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'));;

+HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'))

+C3A10A

+CREATE TABLE t1

+(

+c1 CHAR(12) CHARACTER SET latin1 NOT NULL,

+c2 CHAR(12) CHARACTER SET utf8 NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv';

+INSERT INTO t1 VALUES ('á','á');

+SELECT * FROM t1;

+c1 c2

+á á

+DROP TABLE t1;

+SELECT HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'));;

+HEX(REPLACE(LOAD_FILE('DATADIR/test/t1.csv'),'\r\n','\n'))

+E12CC3A10A

+#

+# Beginning of grant.inc

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF;

+Warnings:

+Warning 1105 No file name. Table will use t1.dbf

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+UPDATE t1 SET a=20;

+SELECT * FROM t1;

+a

+20

+DELETE FROM t1;

+SELECT * FROM t1;

+a

+INSERT INTO t1 VALUES(10);

+TRUNCATE TABLE t1;

+SELECT * FROM t1;

+a

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT * FROM v1;

+a

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.EXT';

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+INSERT INTO t1 VALUES (10);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a=20;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 FILE_NAME='t2.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF;

+Warnings:

+Warning 1105 No file name. Table will use t1.dbf

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+ALTER TABLE t1 FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+DROP USER user@localhost;

+#

+# End of grant.inc

+#

+#

+# Testing errors

+#

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t1.dbf'

+SELECT * FROM t1;

+a

+Warnings:

+Warning 1105 Open(rb) error 2 on DATADIR/test/t1.dbf: No such file or directory

+DROP TABLE t1;

+CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: Cannot open DATADIR/test/t1.dbf

+SHOW WARNINGS;

+Level Code Message

+Error 1105 Cannot open DATADIR/test/t1.dbf

+CREATE PROCEDURE test.dbf_field(in fieldno INT, in content BLOB) DETERMINISTIC

+BEGIN

+SELECT '---';

+SELECT fieldno AS `FieldN`;

+SELECT TRIM(TRAILING 0x00 FROM LEFT(content, 10)) AS `Name`;

+SELECT SUBSTRING(content, 12, 1) AS `Type`;

+SELECT CONV(HEX(REVERSE(SUBSTRING(content,13,4))),16,10) AS `Offset`;

+SELECT CONV(HEX(REVERSE(SUBSTRING(content,17,1))),16,10) AS `Length`;

+SELECT CONV(HEX(REVERSE(SUBSTRING(content,18,1))),16,10) AS `Dec`;

+SELECT HEX(REVERSE(SUBSTRING(content,19,1))) AS `Flags`;

+-- SELECT CONV(HEX(REVERSE(SUBSTRING(content,20,4))),16,10) AS `Next`;

+-- SELECT CONV(HEX(REVERSE(SUBSTRING(content,24,4))),16,10) AS `Step`;

+END//

+CREATE PROCEDURE test.dbf_header(in fname VARCHAR(1024)) DETERMINISTIC

+BEGIN

+DECLARE content BLOB;

+DECLARE offset INT;

+DECLARE fieldno INT;

+SELECT '--------';

+SELECT LOAD_FILE(fname) INTO content;

+SELECT LENGTH(content) AS FileSize;

+SELECT HEX(LEFT(content, 1)) AS DBF_Version;

+SELECT CONV(HEX(REVERSE(SUBSTRING(content,5,4))),16,10) AS NRecords;

+SELECT CONV(HEX(REVERSE(SUBSTRING(content,9,2))),16,10) AS FirstRecPos;

+SELECT CONV(HEX(REVERSE(SUBSTRING(content,11,2))),16,10) AS RecLength;

+SELECT HEX(REVERSE(SUBSTRING(content,29,2))) AS TableFlags;

+SELECT HEX(REVERSE(SUBSTRING(content,30,1))) AS CodePageMark;

+SET offset=33;

+SET fieldno=0;

+WHILE SUBSTR(content, offset, 1) <> 0x0D AND offset + 32 < LENGTH(content) DO

+CALL dbf_field(fieldno, SUBSTRING(content, offset, 32));

+SET offset=offset + 32;

+SET fieldno=fieldno + 1;

+END WHILE;

+SELECT '--------';

+END//

+#

+# Testing READONLY tables

+#

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t1.dbf'

+INSERT INTO t1 VALUES (10),(20);

+SELECT * FROM t1;

+a

+10

+20

+ALTER TABLE t1 READONLY=Yes;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t1.dbf' `READONLY`=Yes

+INSERT INTO t1 VALUES (30);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+UPDATE t1 SET a=30 WHERE a=10;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+DELETE FROM t1 WHERE a=10;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+TRUNCATE TABLE t1;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+ALTER TABLE t1 READONLY=NO;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t1.dbf' `READONLY`=NO

+INSERT INTO t1 VALUES (30);

+SELECT * FROM t1;

+a

+10

+20

+30

+DROP TABLE t1;

+#

+# This SQL script crashed (dbf01.sql)

+#

+CREATE TABLE t1

+(

+a int(11) NOT NULL,

+b char(10) NOT NULL,

+c varchar(10) NOT NULL

+) ENGINE=CONNECT table_type=DBF file_name='t1.dbf';

+INSERT INTO t1 VALUES (1,'1','1');

+INSERT INTO t1 VALUES (2,'2','2');

+SELECT * FROM t1;

+a b c

+1 1 1

+2 2 2

+DROP TABLE t1;

+#

+# Testing that table options in lower case and mixed case are understood:

+#

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT table_type=dbf file_name='t1.dbf';

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=dbf `file_name`='t1.dbf'

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+DROP TABLE t1;

+CREATE TABLE t1 (a CHAR(10) NOT NULL) ENGINE=CONNECT Table_Type=dbf File_Name='t1.dbf';

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` char(10) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `Table_Type`=dbf `File_Name`='t1.dbf'

+INSERT INTO t1 VALUES ('test');

+SELECT * FROM t1;

+a

+test

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 77

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 11

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing multiple columns

+#

+CREATE TABLE t1

+(

+a INT NOT NULL,

+b CHAR(10) NOT NULL,

+c VARCHAR(10) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (1,'1','1');

+INSERT INTO t1 VALUES (2,'2','2');

+SELECT * FROM t1;

+a b c

+1 1 1

+2 2 2

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 194

+DBF_Version 03

+NRecords 2

+FirstRecPos 130

+RecLength 32

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type N

+Offset 0

+Length 11

+Dec 0

+Flags 00

+--- ---

+FieldN 1

+Name b

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+--- ---

+FieldN 2

+Name c

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing long column name

+#

+CREATE TABLE t1

+(

+a012345678901234567890123456789 INT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: DBF: Column name 'a012345678901234567890123456789' is too long (max=10)

+#

+# Testing 2 columns with long names (12)

+#

+CREATE TABLE t1

+(

+a0123456789a INT NOT NULL,

+b0123456789b INT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t02x11.dbf';

+ERROR HY000: DBF: Column name 'a0123456789a' is too long (max=10)

+#

+# Testing 2 columns with long names (11)

+#

+CREATE TABLE t1

+(

+a012345678a INT NOT NULL,

+b012345678b INT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t02x12.dbf';

+ERROR HY000: DBF: Column name 'a012345678a' is too long (max=10)

+#

+# Testing 2 columns name length 10 (maximum possible length)

+#

+CREATE TABLE t1

+(

+a01234567a INT NOT NULL,

+b01234567b INT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t02x13.dbf';

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a01234567a` int(11) NOT NULL,

+ `b01234567b` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t02x13.dbf'

+INSERT INTO t1 VALUES (1,2);

+SELECT * FROM t1;

+a01234567a b01234567b

+1 2

+DROP TABLE t1;

+#

+# Testing BIGINT

+#

+CREATE TABLE t1

+(

+a bigint NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFF);

+INSERT INTO t1 VALUES (-0x8000000000000000);

+SELECT * FROM t1;

+a

+9223372036854775807

+-9223372036854775808

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 108

+DBF_Version 03

+NRecords 2

+FirstRecPos 66

+RecLength 21

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type N

+Offset 0

+Length 20

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing TINYINT

+#

+CREATE TABLE t1

+(

+a TINYINT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (123);

+SELECT * FROM t1;

+a

+123

+DROP TABLE t1;

+#

+# Testing SMALLINT

+#

+CREATE TABLE t1

+(

+a SMALLINT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (0x7FFF);

+INSERT INTO t1 VALUES (-0x8000);

+SELECT * FROM t1;

+a

+32767

+-32768

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 80

+DBF_Version 03

+NRecords 2

+FirstRecPos 66

+RecLength 7

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type N

+Offset 0

+Length 6

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing VARCHAR

+#

+CREATE TABLE t1

+(

+a VARCHAR(255) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (REPEAT('a',255));

+SELECT LENGTH(a) FROM t1;

+LENGTH(a)

+255

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 322

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 256

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type C

+Offset 0

+Length 255

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing too long CHAR

+# All columns longer than 255 bytes should be rejected

+#

+CREATE TABLE t1

+(

+a CHAR(86) CHARACTER SET utf8 NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: DBF: Column length too big for 'a' (max=255)

+#

+# Testing too long VARCHAR

+# All columns longer than 255 bytes should be rejected

+#

+CREATE TABLE t1

+(

+a VARCHAR(256) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: DBF: Column length too big for 'a' (max=255)

+CREATE TABLE t1

+(

+a VARCHAR(86) CHARACTER SET utf8 NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: DBF: Column length too big for 'a' (max=255)

+CREATE TABLE t1

+(

+a VARCHAR(64000) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: DBF: Column length too big for 'a' (max=255)

+#

+# Testing BLOB

+#

+CREATE TABLE t1

+(

+a BLOB

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: Unsupported type for column 'a'

+CREATE TABLE t1

+(

+a TINYBLOB

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: Unsupported type for column 'a'

+CREATE TABLE t1

+(

+a MEDIUMBLOB

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: Unsupported type for column 'a'

+CREATE TABLE t1

+(

+a LONGBLOB

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+ERROR HY000: Unsupported type for column 'a'

+#

+# Testing DATE

+#

+CREATE TABLE t1

+(

+a DATE NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES ('2001-01-01');

+SELECT * FROM t1;

+a

+2001-01-01

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 75

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 9

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type D

+Offset 0

+Length 8

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing FLOAT

+#

+CREATE TABLE t1

+(

+a FLOAT(12,4) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (123);

+SELECT * FROM t1;

+a

+123.0000

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 79

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 13

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type F

+Offset 0

+Length 12

+Dec 4

+Flags 00

+-------- --------

+DROP TABLE t1;

+#

+# Testing double

+#

+CREATE TABLE t1

+(

+a DOUBLE(20,5) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES (123);

+INSERT INTO t1 VALUES (123456789.12345);

+SELECT * FROM t1;

+a

+123.00000

+123456789.12345

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 108

+DBF_Version 03

+NRecords 2

+FirstRecPos 66

+RecLength 21

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type F

+Offset 0

+Length 20

+Dec 5

+Flags 00

+-------- --------

+DROP TABLE IF EXISTS t1;

+#

+# Testing ALTER

+#

+CREATE TABLE t1

+(

+a VARCHAR(10) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES ('10');

+SELECT * FROM t1;

+a

+10

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 77

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 11

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+-------- --------

+ALTER TABLE t1 MODIFY a VARCHAR(10) NOT NULL;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` varchar(10) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t1.dbf'

+SELECT * FROM t1;

+a

+10

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 77

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 11

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+-------- --------

+ALTER TABLE t1 MODIFY a INT(10) NOT NULL;

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(10) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DBF `FILE_NAME`='t1.dbf'

+SELECT * FROM t1;

+a

+10

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 77

+DBF_Version 03

+NRecords 1

+FirstRecPos 66

+RecLength 11

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name a

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE IF EXISTS t1;

+#

+# Testing NULL

+#

+CREATE TABLE t1

+(

+c1 VARCHAR(10) NOT NULL,

+c2 VARCHAR(10) NOT NULL DEFAULT 'def',

+i1 INT NOT NULL,

+i2 INT NOT NULL DEFAULT 123

+) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='t1.dbf';

+INSERT INTO t1 VALUES ('10','10',10,10);

+INSERT INTO t1(c1,i1) VALUES ('20',20);

+INSERT INTO t1 VALUES ('30',DEFAULT,30,DEFAULT);

+SELECT * FROM t1;

+c1 c2 i1 i2

+10 10 10 10

+20 def 20 123

+30 def 30 123

+CALL dbf_header('MYSQLD_DATADIR/test/t1.dbf');

+-------- --------

+FileSize 291

+DBF_Version 03

+NRecords 3

+FirstRecPos 162

+RecLength 43

+TableFlags 0000

+CodePageMark 00

+--- ---

+FieldN 0

+Name c1

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+--- ---

+FieldN 1

+Name c2

+Type C

+Offset 0

+Length 10

+Dec 0

+Flags 00

+--- ---

+FieldN 2

+Name i1

+Type N

+Offset 0

+Length 11

+Dec 0

+Flags 00

+--- ---

+FieldN 3

+Name i2

+Type N

+Offset 0

+Length 11

+Dec 0

+Flags 00

+-------- --------

+DROP TABLE IF EXISTS t1;

+DROP PROCEDURE test.dbf_field;

+DROP PROCEDURE test.dbf_header;

+#

+# Testing FILE privilege

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (

+path VARCHAR(256) NOT NULL flag=1,

+fname VARCHAR(256) NOT NULL,

+ftype CHAR(4) NOT NULL,

+size DOUBLE(12,0) NOT NULL flag=5

+) ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.*';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 (

+path VARCHAR(256) NOT NULL flag=1,

+fname VARCHAR(256) NOT NULL,

+ftype CHAR(4) NOT NULL,

+size DOUBLE(12,0) NOT NULL flag=5

+) ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.*';

+SELECT fname, ftype, size FROM t1 WHERE size>0;

+fname ftype size

+t1 .frm 8654

+SELECT user();

+user()

+user@localhost

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO t1 VALUES ('xxx');

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1 WHERE a='xxx';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a='yyy' WHERE a='xxx';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1;

+DROP USER user@localhost;

+#

+# Testing FILE privileges done

+#

+CREATE TABLE t1 (

+path VARCHAR(256) NOT NULL flag=1,

+fname VARCHAR(256) NOT NULL,

+ftype CHAR(4) NOT NULL,

+size DOUBLE(12,0) NOT NULL flag=5

+) ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.txt'

+ OPTION_LIST='subdir=1';

+SELECT * FROM t1;

+path fname ftype size

+SELECT fname, ftype, size FROM t1 ORDER BY fname, ftype, size;

+fname ftype size

+boys .txt 282

+boys2 .txt 282

+boyswin .txt 288

+ALTER TABLE t1 OPTION_LIST='subdir=0';

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `path` varchar(256) NOT NULL `flag`=1,

+ `fname` varchar(256) NOT NULL,

+ `ftype` char(4) NOT NULL,

+ `size` double(12,0) NOT NULL `flag`=5

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=DIR `FILE_NAME`='*.txt' `OPTION_LIST`='subdir=0'

+SELECT fname, ftype, size FROM t1 ORDER BY fname, ftype, size;

+fname ftype size

+boys .txt 282

+boyswin .txt 288

+INSERT INTO t1 VALUES ('','','','');

+ERROR HY000: Got error 174 'COLBLK SetBuffer: undefined Access Method' from CONNECT

+DROP TABLE t1;

+CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=DIR FILE_NAME='*.txt';

+ERROR HY000: Cannot get column info for table type DIR

+#

+# Beginning of grant.inc

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=FIX;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+UPDATE t1 SET a=20;

+SELECT * FROM t1;

+a

+20

+DELETE FROM t1;

+SELECT * FROM t1;

+a

+INSERT INTO t1 VALUES(10);

+TRUNCATE TABLE t1;

+SELECT * FROM t1;

+a

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT * FROM v1;

+a

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='t1.EXT';

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+INSERT INTO t1 VALUES (10);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a=20;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 FILE_NAME='t2.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=FIX;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+INSERT INTO t1 VALUES (10);

+SELECT user();

+user()

+user@localhost

+ALTER TABLE t1 FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+DROP USER user@localhost;

+#

+# End of grant.inc

+#

+#

+# Testing errors

+#

+CREATE TABLE t1

+(

+ID INT NOT NULL

+) Engine=CONNECT TABLE_TYPE=DOS FILE_NAME='nonexistent.txt';

+SELECT * FROM t1;

+ID

+Warnings:

+Warning 1105 Open(rb) error 2 on DATADIR/test/nonexistent.txt: No such file or directory

+DROP TABLE t1;

+#

+# Testing READONLY tables

+#

+CREATE TABLE t1

+(

+id INT NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='t1.txt';

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+id

+10

+ALTER TABLE t1 READONLY=1;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `id` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=FIX `FILE_NAME`='t1.txt' `READONLY`=1

+INSERT INTO t1 VALUES (20);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+UPDATE t1 SET id=20 WHERE id=10;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+DELETE FROM t1 WHERE id=10;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+TRUNCATE TABLE t1;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+ALTER TABLE t1 READONLY=0;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `id` int(11) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=FIX `FILE_NAME`='t1.txt' `READONLY`=0

+INSERT INTO t1 VALUES (20);

+SELECT * FROM t1;

+id

+10

+20

+DROP TABLE t1;

+#

+# Testing manual examples

+#

+CREATE TABLE t1

+(

+number CHAR(4) not null,

+location CHAR(15) NOT NULL flag=5,

+director CHAR(5) NOT NULL flag=20,

+function CHAR(12) NOT NULL flag=26,

+name CHAR(22) NOT NULL flag=38

+) ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='dept.dat';

+SELECT * FROM t1;

+number location director function name

+0318 KINGSTON 70012 SALES Bank/Insurance

+0021 ARMONK 87777 CHQ Corporate headquarter

+0319 HARRISON 40567 SALES Federal Administration

+2452 POUGHKEEPSIE 31416 DEVELOPMENT Research & development

+DROP TABLE t1;

+CREATE TABLE t1

+(

+name char(12) not null,

+city char(12) not null,

+birth date not null date_format='DD/MM/YYYY',

+hired date not null date_format='DD/MM/YYYY' flag=36

+) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='boys.txt' ENDING=1;

+SELECT * FROM t1;

+name city birth hired

+John Boston 2 1986-01-05 2010-06-02

+Henry Boston 0 1987-06-07 2008-04-01

+George San Jose 1 1981-08-01 2010-06-02

+Sam Chicago 2 1979-11-02 2007-10-10

+James Dallas 1 1992-05-03 2009-12-14

+Bill Boston 1 1986-09-01 2008-02-10

+DROP TABLE t1;

+CREATE TABLE t1

+(

+name char(12) not null,

+city char(12) not null,

+birth date not null date_format='DD/MM/YYYY',

+hired date not null date_format='DD/MM/YYYY' flag=36

+) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='boys.txt' LRECL=47 ENDING=1;

+SELECT * FROM t1;

+name city birth hired

+John Boston 2 1986-01-05 2010-06-02

+Henry Boston 0 1987-06-07 2008-04-01

+George San Jose 1 1981-08-01 2010-06-02

+Sam Chicago 2 1979-11-02 2007-10-10

+James Dallas 1 1992-05-03 2009-12-14

+Bill Boston 1 1986-09-01 2008-02-10

+DROP TABLE t1;

+CREATE TABLE t1

+(

+name char(12) not null,

+city char(12) not null,

+birth date not null date_format='DD/MM/YYYY',

+hired date not null date_format='DD/MM/YYYY' flag=36

+) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='boyswin.txt' ENDING=2;

+SELECT * FROM t1;

+name city birth hired

+John Boston 2 1986-01-05 2010-06-02

+Henry Boston 0 1987-06-07 2008-04-01

+George San Jose 1 1981-08-01 2010-06-02

+Sam Chicago 2 1979-11-02 2007-10-10

+James Dallas 1 1992-05-03 2009-12-14

+Bill Boston 1 1986-09-01 2008-02-10

+DROP TABLE t1;

+CREATE TABLE t1

+(

+name char(12) not null,

+city char(12) not null,

+birth date not null date_format='DD/MM/YYYY',

+hired date not null date_format='DD/MM/YYYY' flag=36

+) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='boyswin.txt' LRECL=47 ENDING=2;

+SELECT * FROM t1;

+name city birth hired

+John Boston 2 1986-01-05 2010-06-02

+Henry Boston 0 1987-06-07 2008-04-01

+George San Jose 1 1981-08-01 2010-06-02

+Sam Chicago 2 1979-11-02 2007-10-10

+James Dallas 1 1992-05-03 2009-12-14

+Bill Boston 1 1986-09-01 2008-02-10

+DROP TABLE t1;

+#

+# Testing errors

+#

+CREATE TABLE t1

+(

+ID INT NOT NULL field_format=' %n%d%n'

+) Engine=CONNECT table_type=FMT file_name='nonexistent.txt';

+SELECT * FROM t1;

+ID

+Warnings:

+Warning 1105 Open(rb) error 2 on DATADIR/test/nonexistent.txt: No such file or directory

+DROP TABLE t1;

+#

+# Testing update on FMT tables

+#

+CREATE TABLE t1

+(

+id INT NOT NULL field_format=' %n%d%n'

+) ENGINE=CONNECT TABLE_TYPE=FMT FILE_NAME='t1.txt';

+INSERT INTO t1 VALUES (10),(20);

+ERROR HY000: Got error 122 'Writing FMT files is not implemented yet' from CONNECT

+DROP TABLE t1;

+#

+# Testing manual examples

+#

+CREATE TABLE t1

+(

+ID Integer(5) not null field_format=' %n%d%n',

+NAME Char(16) not null field_format=" , '%n%[^']%n'",

+DEPNO Integer(4) not null field_format=' , #%n%d%n',

+SALARY Double(12,2) not null field_format=' ; %n%f%n'

+) Engine=CONNECT table_type=FMT file_name='funny.txt';

+SELECT * FROM t1;

+ID NAME DEPNO SALARY

+12345 BERTRAND 200 5009.13

+56 POIROT-DELMOTTE 4256 18009.00

+345 TRUCMUCHE 67 19000.25

+DROP TABLE t1;

+CREATE TABLE t1

+(

+ID Integer(5) not null field_format=' %n%d%n',

+NAME Char(16) not null field_format=" , '%n%[^']%n'",

+DEPNO Integer(4) not null field_format=' , #%n%d%n',

+SALARY Double(12,2) not null field_format=' ; %n%f%n'

+) Engine=CONNECT table_type=FMT file_name='funny2.txt';

+SELECT * FROM t1;

+ERROR HY000: Got error 122 'Bad format line 2 field 3 of t1' from CONNECT

+DROP TABLE t1;

+CREATE TABLE t1

+(

+ID Integer(5) not null field_format=' %n%d%n',

+NAME Char(16) not null field_format=' , ''%n%[^'']%m',

+DEPNO Integer(4) not null field_format=''' , #%n%d%m',

+SALARY Double(12,2) not null field_format=' ; %n%f%n'

+) Engine=CONNECT table_type=FMT file_name='funny2.txt';

+SELECT * FROM t1;

+ID NAME DEPNO SALARY

+12345 BERTRAND 200 5009.13

+56 POIROT-DELMOTTE 0 18009.00

+345 67 19000.25

+UPDATE t1 SET SALARY=1234;

+ERROR HY000: Got error 122 'Writing FMT files is not implemented yet' from CONNECT

+DELETE FROM t1 WHERE ID=56;

+SELECT * FROM t1;

+ID NAME DEPNO SALARY

+12345 BERTRAND 200 5009.13

+345 67 19000.25

+DROP TABLE t1;

+#

+# Testing features not specific to any TABLE_TYPE

+#

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=NON_EXISTING;

+ERROR HY000: Unsupported table type NON_EXISTING

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=FIX;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+ALTER TABLE t1 TABLE_TYPE=NON_EXISTING;

+ERROR HY000: Unsupported table type NON_EXISTING

+SELECT * FROM t1;

+a

+10

+DROP TABLE t1;

+#

+# Testing indexing

+#

+CREATE TABLE t1

+(

+matricule INT(4) KEY NOT NULL field_format='Z',

+nom VARCHAR(16) NOT NULL,

+prenom VARCHAR(20) NOT NULL,

+sexe SMALLINT(1) NOT NULL COMMENT 'sexe 1:M 2:F',

+aanais INT(4) NOT NULL,

+mmnais INT(2) NOT NULL,

+ddentree DATE NOT NULL date_format='YYYYMM',

+ddnom DATE NOT NULL date_format='YYYYMM',

+brut INT(5) NOT NULL,

+net DOUBLE(8,2) NOT NULL,

+service INT(2) NOT NULL,

+sitmat CHAR(1) NOT NULL,

+formation CHAR(5) NOT NULL,

+INDEX NP(nom,prenom)

+) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='emp.txt' ENDING=2;

+SELECT * FROM t1 LIMIT 10;

+matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation

+5745 ESCOURCHE BENEDICTE 2 1935 7 1962-12-01 1994-05-01 18345 14275.50 0 M TECHN

+9692 VICENTE LAURENCE 2 1941 8 1967-10-01 1989-01-01 16212 13032.80 0 M ANGL

+9146 NICOLAS ROGER 1 1941 6 1964-07-01 1995-02-01 34173 25098.65 0 M SANS

+2985 TESSEREAU MARIE HELENE 2 1941 9 1967-01-01 1990-01-01 19323 14933.78 0 V SANS

+3368 MOGADOR ALAIN 1 1941 1 1961-09-01 1993-11-01 43303 31420.55 0 C SANS

+7394 CHAUSSEE ERIC DENIS 1 1944 9 1965-11-01 1983-12-01 32002 23583.86 0 M ANGL

+4655 MAILLOT GEORGES 1 1945 5 1970-09-01 1986-12-01 24700 18541.64 0 C ANGL

+2825 CAMILLE NADINE 2 1956 9 1994-01-01 1993-01-01 19494 15050.45 0 M SANS

+1460 BRUYERES JEAN MARC 1 1958 8 1984-08-01 1988-05-01 20902 15980.07 0 M SANS

+4974 LONES GERARD 1 1959 10 1979-01-01 1994-12-01 16081 12916.70 0 M SANS

+SELECT SUM(brut) from t1;

+SUM(brut)

+64319029

+#

+# Testing file mapping

+#

+ALTER TABLE t1 MAPPED=yes;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SELECT * FROM t1 LIMIT 10;

+matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation

+5745 ESCOURCHE BENEDICTE 2 1935 7 1962-12-01 1994-05-01 18345 14275.50 0 M TECHN

+9692 VICENTE LAURENCE 2 1941 8 1967-10-01 1989-01-01 16212 13032.80 0 M ANGL

+9146 NICOLAS ROGER 1 1941 6 1964-07-01 1995-02-01 34173 25098.65 0 M SANS

+2985 TESSEREAU MARIE HELENE 2 1941 9 1967-01-01 1990-01-01 19323 14933.78 0 V SANS

+3368 MOGADOR ALAIN 1 1941 1 1961-09-01 1993-11-01 43303 31420.55 0 C SANS

+7394 CHAUSSEE ERIC DENIS 1 1944 9 1965-11-01 1983-12-01 32002 23583.86 0 M ANGL

+4655 MAILLOT GEORGES 1 1945 5 1970-09-01 1986-12-01 24700 18541.64 0 C ANGL

+2825 CAMILLE NADINE 2 1956 9 1994-01-01 1993-01-01 19494 15050.45 0 M SANS

+1460 BRUYERES JEAN MARC 1 1958 8 1984-08-01 1988-05-01 20902 15980.07 0 M SANS

+4974 LONES GERARD 1 1959 10 1979-01-01 1994-12-01 16081 12916.70 0 M SANS

+SELECT SUM(brut) FROM t1;

+SUM(brut)

+64319029

+#

+# Test the indexes (made when creating the table)

+#

+SELECT * FROM t1 WHERE matricule = '0091';

+matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation

+91 THIVERNAL DIDIER JEAN 1 1951 10 1980-05-01 1991-10-01 14715 12024.71 1 M SANS

+SELECT * FROM t1 WHERE nom = 'FOCH';

+matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation

+1977 FOCH BERNADETTE 2 1958 3 1992-02-01 1991-02-01 8656 8145.03 1 . SANS

+5707 FOCH DENIS 1 1977 7 1996-07-01 1995-07-01 7803 7679.36 15 C COMPT

+2552 FOCH FRANCK 1 1962 12 1986-06-01 1990-11-01 12882 10745.81 13 M SANS

+2634 FOCH JOCELYNE 2 1953 3 1996-01-01 1995-01-01 12499 10473.09 41 M INFOR

+5765 FOCH ROBERT 1 1957 1 1981-03-01 1993-03-01 16081 12916.32 52 M ALLEM

+4080 FOCH SERGE 1 1959 3 1981-03-01 1981-05-01 11131 9658.24 5 M SANS

+SELECT * FROM t1 WHERE nom = 'FOCH' and prenom = 'DENIS';

+matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation

+5707 FOCH DENIS 1 1977 7 1996-07-01 1995-07-01 7803 7679.36 15 C COMPT

+#

+# Testing UPDATE

+#

+UPDATE t1 SET aanais = aanais + 16;

+UPDATE t1 SET ddentree = adddate(ddentree, interval 16 year);

+UPDATE t1 SET ddnom = adddate(ddnom, interval 16 year);

+SELECT * FROM t1 WHERE nom = 'FOCH';

+matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat formation

+1977 FOCH BERNADETTE 2 1974 3 2008-02-01 2007-02-01 8656 8145.03 1 . SANS

+5707 FOCH DENIS 1 1993 7 2012-07-01 2011-07-01 7803 7679.36 15 C COMPT

+2552 FOCH FRANCK 1 1978 12 2002-06-01 2006-11-01 12882 10745.81 13 M SANS

+2634 FOCH JOCELYNE 2 1969 3 2012-01-01 2011-01-01 12499 10473.09 41 M INFOR

+5765 FOCH ROBERT 1 1973 1 1997-03-01 2009-03-01 16081 12916.32 52 M ALLEM

+4080 FOCH SERGE 1 1975 3 1997-03-01 1997-05-01 11131 9658.24 5 M SANS

+#

+# Testing JOIN

+#

+create table t2

+(

+sexe INT(1) KEY,

+genre CHAR(8) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='sexe.csv' SEP_CHAR=';' ENDING=2;

+SELECT * FROM t2;

+sexe genre

+0 Inconnu

+1 Masculin

+2 Feminin

+SELECT nom, prenom, genre FROM t1 NATURAL JOIN t2 LIMIT 10;

+nom prenom genre

+ESCOURCHE BENEDICTE Feminin

+VICENTE LAURENCE Feminin

+NICOLAS ROGER Masculin

+TESSEREAU MARIE HELENE Feminin

+MOGADOR ALAIN Masculin

+CHAUSSEE ERIC DENIS Masculin

+MAILLOT GEORGES Masculin

+CAMILLE NADINE Feminin

+BRUYERES JEAN MARC Masculin

+LONES GERARD Masculin

+#

+# Another table

+#

+CREATE TABLE t3 (

+sitmat CHAR(1) KEY,

+situation CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='sitmat.csv' SEP_CHAR=';' ENDING=2;

+SELECT * FROM t3;

+sitmat situation

+. Inconnu

+C Celibataire

+D Divorce

+L Union libre

+M Marie

+S Separe

+V Veuf

+SELECT nom, prenom, genre, situation FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE nom = 'FOCH';

+nom prenom genre situation

+FOCH BERNADETTE Feminin Inconnu

+FOCH DENIS Masculin Celibataire

+FOCH FRANCK Masculin Marie

+FOCH JOCELYNE Feminin Marie

+FOCH ROBERT Masculin Marie

+FOCH SERGE Masculin Marie

+#

+# Testing DELETE

+#

+DELETE FROM t1;

+DROP TABLE t1;

+DROP TABLE t2;

+DROP TABLE t3;

+#

+# Checking FILE privileges

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=INI;

+Warnings:

+Warning 1105 No file name. Table will use t1.ini

+INSERT INTO t1 VALUES ('sec1','val1');

+SELECT * FROM t1;

+sec val

+sec1 val1

+UPDATE t1 SET val='val11';

+SELECT * FROM t1;

+sec val

+sec1 val11

+DELETE FROM t1;

+SELECT * FROM t1;

+sec val

+INSERT INTO t1 VALUES('sec2','val2');

+TRUNCATE TABLE t1;

+SELECT * FROM t1;

+sec val

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT * FROM v1;

+sec val

+DROP VIEW v1;

+DROP TABLE t1;

+CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.EXT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.EXT';

+INSERT INTO t1 VALUES ('sec1','val1');

+SELECT user();

+user()

+user@localhost

+INSERT INTO t1 VALUES ('sec2','val2');

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET val='val11';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES ('sec3','val3');

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET val='val11';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DROP VIEW v1;

+DROP TABLE t1;

+DROP USER user@localhost;

+#

+# Checking FILE privileges: done

+#

+#

+# Testing errors

+#

+CREATE TABLE t1

+(

+ID INT

+) Engine=CONNECT TABLE_TYPE=INI FILE_NAME='nonexistent.txt';

+SELECT * FROM t1;

+ID

+DROP TABLE t1;

+#

+# Testing examples from the manual

+#

+CREATE TABLE t1

+(

+contact CHAR(16) flag=1,

+name CHAR(20),

+forename CHAR(32),

+hired date date_format='DD/MM/YYYY',

+address CHAR(64),

+city CHAR(20),

+zipcode CHAR(8),

+tel CHAR(16)

+) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='contact.ini';

+SELECT contact, name, hired, city, tel FROM t1;

+contact name hired city tel

+BER Bertrand NULL Issy-les-Mlx 09.54.36.29.60

+WEL Schmitt 1985-02-19 Berlin 03.43.377.360

+UK1 Smith 2003-11-08 London NULL

+UPDATE t1 SET forename= 'Harry' where contact='UK1';

+SELECT * FROM t1 WHERE contact='UK1';

+contact name forename hired address city zipcode tel

+UK1 Smith Harry 2003-11-08 143 Blum Rd. London NW1 2BP NULL

+INSERT INTO t1 (contact,forename) VALUES ('UK1','Harrison');

+SELECT * FROM t1 WHERE contact='UK1';

+contact name forename hired address city zipcode tel

+UK1 Smith Harrison 2003-11-08 143 Blum Rd. London NW1 2BP NULL

+INSERT INTO t1 (contact,forename) VALUES ('UK2','John');

+SELECT * FROM t1 WHERE contact='UK2';

+contact name forename hired address city zipcode tel

+UK2 NULL John NULL NULL NULL NULL NULL

+DROP TABLE t1;

+SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n');;

+REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n')

+[BER]

+name=Bertrand

+forename=Olivier

+address=21 rue Ferdinand Buisson

+city=Issy-les-Mlx

+zipcode=92130

+tel=09.54.36.29.60

+cell=06.70.06.04.16

+[WEL]

+name=Schmitt

+forename=Bernard

+hired=19/02/1985

+address=64 tiergarten strasse

+city=Berlin

+zipcode=95013

+tel=03.43.377.360

+[UK1]

+name=Smith

+forename=Harrison

+hired=08/11/2003

+address=143 Blum Rd.

+city=London

+zipcode=NW1 2BP

+[UK2]

+forename=John

+

+CREATE TABLE t1

+(

+section CHAR(16) flag=1,

+keyname CHAR(16) flag=2,

+value CHAR(32)

+) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='contact.ini'

+ OPTION_LIST='Layout=Row';

+UPDATE t1 SET value='Paul' WHERE section='UK2' AND keyname='forename';

+SELECT * FROM t1;

+section keyname value

+BER name Bertrand

+BER forename Olivier

+BER address 21 rue Ferdinand Buisson

+BER city Issy-les-Mlx

+BER zipcode 92130

+BER tel 09.54.36.29.60

+BER cell 06.70.06.04.16

+WEL name Schmitt

+WEL forename Bernard

+WEL hired 19/02/1985

+WEL address 64 tiergarten strasse

+WEL city Berlin

+WEL zipcode 95013

+WEL tel 03.43.377.360

+UK1 name Smith

+UK1 forename Harrison

+UK1 hired 08/11/2003

+UK1 address 143 Blum Rd.

+UK1 city London

+UK1 zipcode NW1 2BP

+UK2 forename Paul

+DROP TABLE t1;

+SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n');;

+REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n')

+[BER]

+name=Bertrand

+forename=Olivier

+address=21 rue Ferdinand Buisson

+city=Issy-les-Mlx

+zipcode=92130

+tel=09.54.36.29.60

+cell=06.70.06.04.16

+[WEL]

+name=Schmitt

+forename=Bernard

+hired=19/02/1985

+address=64 tiergarten strasse

+city=Berlin

+zipcode=95013

+tel=03.43.377.360

+[UK1]

+name=Smith

+forename=Harrison

+hired=08/11/2003

+address=143 Blum Rd.

+city=London

+zipcode=NW1 2BP

+[UK2]

+forename=Paul

+

+#

+# Testing that the underlying file is created

+#

+CREATE TABLE t1

+(

+contact CHAR(12) NOT NULL flag=1,

+c2 CHAR(12) NOT NULL

+) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='tmp.ini';

+INSERT INTO t1 VALUES (10,10),(20,20),(300,300),(4000,4000), ('a b','c d');

+SELECT * FROM t1;

+contact c2

+10 10

+20 20

+300 300

+4000 4000

+a b c d

+DROP TABLE t1;

+SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/tmp.ini'),'\r\n','\n'),'\n\n','\n');;

+REPLACE(REPLACE(LOAD_FILE('DATADIR/test/tmp.ini'),'\r\n','\n'),'\n\n','\n')

+[10]

+c2=10

+[20]

+c2=20

+[300]

+c2=300

+[4000]

+c2=4000

+[a b]

+c2=c d

+

+#

+# Testing bad table

+#

+CREATE TABLE t1

+(

+id INT

+) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.ini';

+INSERT INTO t1 VALUES (10);

+ERROR HY000: Got error 122 'Section name must come first on Insert' from CONNECT

+SELECT * FROM t1;

+id

+DROP TABLE t1;

+#

+# Testing READONLY tables

+#

+CREATE TABLE t1

+(

+contact CHAR(10) flag=1,

+c2 CHAR(60)

+) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.ini';

+INSERT INTO t1 VALUES ('UK',10),('FR',20),('RU',30);

+SELECT * FROM t1;

+contact c2

+UK 10

+FR 20

+RU 30

+ALTER TABLE t1 READONLY=1;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `contact` char(10) DEFAULT NULL `flag`=1,

+ `c2` char(60) DEFAULT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=INI `FILE_NAME`='t1.ini' `READONLY`=1

+INSERT INTO t1 VALUES ('US',40);

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+UPDATE t1 SET c2=20 WHERE c2=10;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+DELETE FROM t1 WHERE c2=10;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+TRUNCATE TABLE t1;

+ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT

+ALTER TABLE t1 READONLY=0;

+Warnings:

+Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `contact` char(10) DEFAULT NULL `flag`=1,

+ `c2` char(60) DEFAULT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=INI `FILE_NAME`='t1.ini' `READONLY`=0

+INSERT INTO t1 VALUES ('US',40);

+SELECT * FROM t1;

+contact c2

+UK 10

+FR 20

+RU 30

+US 40

+DROP TABLE t1;

+#

+# Bug: TABLE_TYPE=ini does not clear memory between CREATE TABLEs

+#

+CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL)

+ENGINE=CONNECT TABLE_TYPE=INI;

+Warnings:

+Warning 1105 No file name. Table will use t1.ini

+INSERT INTO t1 VALUES ('sec1','val1'),('sec2','val2');

+SELECT sec AS s, val AS v FROM t1;

+s v

+sec1 val1

+sec2 val2

+DROP TABLE t1;

+CREATE TABLE t1 (sec2 CHAR(10) NOT NULL FLAG=1, val2 CHAR(10) NOT NULL)

+ENGINE=CONNECT TABLE_TYPE=INI;

+Warnings:

+Warning 1105 No file name. Table will use t1.ini

+INSERT INTO t1 VALUES ('sec1','val11'),('sec2','val22');

+SELECT sec2 AS s, val2 AS v FROM t1;

+s v

+sec1 val11

+sec2 val22

+SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n');;

+REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n')

+[sec1]

+val2=val11

+[sec2]

+val2=val22

+

+DROP TABLE t1;

+CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL)

+ENGINE=CONNECT TABLE_TYPE=INI;

+Warnings:

+Warning 1105 No file name. Table will use t1.ini

+CREATE TABLE t2 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL)

+ENGINE=CONNECT TABLE_TYPE=INI;

+Warnings:

+Warning 1105 No file name. Table will use t2.ini

+INSERT INTO t1 VALUES('1sec1','1val1'),('1sec2','1val2');

+INSERT INTO t2 VALUES('2sec1','2val1'),('2sec2','2val2');

+SELECT sec AS s, val AS v FROM t1;

+s v

+1sec1 1val1

+1sec2 1val2

+SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n');;

+REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n')

+[1sec1]

+val=1val1

+[1sec2]

+val=1val2

+

+SELECT sec AS s, val AS v FROM t2;

+s v

+2sec1 2val1

+2sec2 2val2

+SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t2.ini'),'\r\n','\n'),'\n\n','\n');;

+REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t2.ini'),'\r\n','\n'),'\n\n','\n')

+[2sec1]

+val=2val1

+[2sec2]

+val=2val2

+

+DROP TABLE t1, t2;

+#

+# Testing FILE privilege

+#

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=MySQL OPTION_LIST='host=localhost,user=root1,port=$PORT';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1remote (a INT NOT NULL);

+INSERT INTO t1remote VALUES (10),(20),(30);

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=MySQL TABNAME=t1remote OPTION_LIST='host=localhost,user=root,port=$PORT';

+SELECT * FROM t1;

+a

+10

+20

+30

+SELECT user();

+user()

+user@localhost

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO t1 VALUES ('xxx');

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1 WHERE a='xxx';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a='yyy' WHERE a='xxx';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1, t1remote;

+DROP USER user@localhost;

+#

+# Testing FILE privileges done

+#

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

+a b

+NULL NULL

+0 test00

+1 test01

+2 test02

+3 test03

+#

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

+SHOW CREATE TABLE t2;

+Table Create Table

+t2 CREATE TABLE `t2` (

+ `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 'x' in 'field list' [SELECT `x`, `y` FROM `t1`]' from CONNECT

+DROP TABLE t2;

+ALTER TABLE t1 RENAME t1backup;

+SELECT * FROM t2;

+ERROR 42S02: Table 'test.t2' doesn't exist

+ALTER TABLE t1backup RENAME t1;

+#

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

+t2 CREATE TABLE `t2` (

+ `a` int(11) DEFAULT NULL,

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

+a b

+NULL NULL

+0 test00

+1 test01

+2 test02

+3 test03

+DROP TABLE t2;

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

+SHOW CREATE TABLE t2;

+Table Create Table

+t2 CREATE TABLE `t2` (

+ `a` int(11) NOT NULL,

+ `b` char(10) NOT NULL

+) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=MYSQL `TABNAME`='t1' `OPTION_LIST`='host=localhost,user=root,port=PORT'

+SELECT * FROM t2;

+a b

+0

+0 test00

+1 test01

+2 test02

+3 test03

+DROP TABLE t2;

+CREATE TABLE t2 (a char(10), b int) 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` char(10) DEFAULT NULL,

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

+NULL NULL

+0 0

+1 0

+2 0

+3 0

+DROP TABLE t2;

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` smallint(6) DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` mediumint(9) DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` int(11) DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` bigint(20) DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

+SELECT * FROM t2;

+a

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` char(10) DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` varchar(10) DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

+SELECT * FROM t2;

+a

+DROP TABLE t2, t1;

+#

+# Testing binary data types

+#

+#

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

+SHOW CREATE TABLE t1;

+Table Create Table

+t1 CREATE TABLE `t1` (

+ `a` date DEFAULT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

+SELECT * FROM t2;

+a

+DROP TABLE t2, t1;

+#

+# Testing FIX null columns

+#

+CREATE TABLE t1

+(

+id INT NOT NULL,

+nb INT,

+msg VARCHAR(12)

+) ENGINE=CONNECT TABLE_TYPE=FIX;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+INSERT INTO t1 values(NULL,1,'Hello');

+ERROR 23000: Column 'id' cannot be null

+INSERT INTO t1 values(10,4,NULL),(20,2,'Hello'),(0,0,'Zero');

+SELECT * FROM t1;

+id nb msg

+10 4 NULL

+20 2 Hello

+0 NULL Zero

+SELECT* FROM t1 WHERE id IS NULL;

+id nb msg

+SELECT * FROM t1 WHERE nb IS NULL;

+id nb msg

+0 NULL Zero

+SELECT * FROM t1 WHERE msg IS NOT NULL;

+id nb msg

+20 2 Hello

+0 NULL Zero

+DROP TABLE t1;

+#

+# Testing CSV null columns

+#

+CREATE TABLE t1

+(

+id INT NOT NULL,

+nb INT,

+msg VARCHAR(12)

+) ENGINE=CONNECT TABLE_TYPE=CSV HEADER=1;

+Warnings:

+Warning 1105 No file name. Table will use t1.csv

+INSERT INTO t1 values(NULL,1,'Hello');

+ERROR 23000: Column 'id' cannot be null

+INSERT INTO t1 values(10,4,NULL),(20,2,'Hello'),(0,0,'Zero');

+SELECT * FROM t1;

+id nb msg

+10 4 NULL

+20 2 Hello

+0 NULL Zero

+SELECT* FROM t1 WHERE id IS NULL;

+id nb msg

+SELECT * FROM t1 WHERE nb IS NULL;

+id nb msg

+0 NULL Zero

+SELECT * FROM t1 WHERE msg IS NOT NULL;

+id nb msg

+20 2 Hello

+0 NULL Zero

+DROP TABLE t1;

+#

+# Testing BIN null columns

+#

+CREATE TABLE t1

+(

+id INT NOT NULL,

+nb INT,

+msg VARCHAR(12)

+) ENGINE=CONNECT TABLE_TYPE=BIN;

+Warnings:

+Warning 1105 No file name. Table will use t1.bin

+INSERT INTO t1 values(NULL,1,'Hello');

+ERROR 23000: Column 'id' cannot be null

+INSERT INTO t1 values(10,4,NULL),(20,2,'Hello'),(0,0,'Zero');

+SELECT * FROM t1;

+id nb msg

+10 4 NULL

+20 2 Hello

+0 NULL Zero

+SELECT* FROM t1 WHERE id IS NULL;

+id nb msg

+SELECT * FROM t1 WHERE nb IS NULL;

+id nb msg

+0 NULL Zero

+SELECT * FROM t1 WHERE msg IS NOT NULL;

+id nb msg

+20 2 Hello

+0 NULL Zero

+DROP TABLE t1;

+#

+# Testing DBF null columns

+#

+CREATE TABLE t1

+(

+id INT NOT NULL,

+nb INT,

+msg VARCHAR(12)

+) ENGINE=CONNECT TABLE_TYPE=DBF;

+Warnings:

+Warning 1105 No file name. Table will use t1.dbf

+INSERT INTO t1 values(NULL,1,'Hello');

+ERROR 23000: Column 'id' cannot be null

+INSERT INTO t1 values(10,4,NULL),(20,2,'Hello'),(0,0,'Zero');

+SELECT * FROM t1;

+id nb msg

+10 4 NULL

+20 2 Hello

+0 NULL Zero

+SELECT* FROM t1 WHERE id IS NULL;

+id nb msg

+SELECT * FROM t1 WHERE nb IS NULL;

+id nb msg

+0 NULL Zero

+SELECT * FROM t1 WHERE msg IS NOT NULL;

+id nb msg

+20 2 Hello

+0 NULL Zero

+DROP TABLE t1;

+#

+# Testing INI null columns

+#

+CREATE TABLE t1

+(

+`sec` char(8) NOT NULL flag=1,

+`key` char(12)

+) ENGINE=CONNECT TABLE_TYPE=INI;

+Warnings:

+Warning 1105 No file name. Table will use t1.ini

+INSERT INTO t1(sec) values('S1');

+SELECT * FROM t1;

+sec key

+INSERT INTO t1 values('S1','Newval');

+SELECT * FROM t1;

+sec key

+S1 Newval

+DROP TABLE t1;

+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

+SET NAMES utf8;

+GRANT ALL PRIVILEGES ON *.* TO user@localhost;

+REVOKE FILE ON *.* FROM user@localhost;

+SELECT user();

+user()

+user@localhost

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Drivers;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=ODBC CATFUNC=Sources;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='Driver=SQLite3 ODBC Driver;Database=MYSQL_TEST_DIR/suite/connect/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=MYSQL_TEST_DIR/suite/connect/std_data/test.sqlite3;NoWCHAR=yes' `TABLE_TYPE`=ODBC `DATA_CHARSET`=utf8

+SELECT * FROM t1;

+a

+test1

+test2

+тест1

+тест2

+ÆÇÈÉË

+SELECT user();

+user()

+user@localhost

+SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO t1 VALUES ('xxx');

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM t1 WHERE a='xxx';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE t1 SET a='yyy' WHERE a='xxx';

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+TRUNCATE TABLE t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+ALTER TABLE t1 READONLY=1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+CREATE VIEW v1 AS SELECT * FROM t1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+# Testing a VIEW created with FILE privileges but accessed with no FILE

+SELECT user();

+user()

+root@localhost

+CREATE VIEW v1 AS SELECT * FROM t1;

+SELECT user();

+user()

+user@localhost

+SELECT * FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+INSERT INTO v1 VALUES (2);

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+UPDATE v1 SET a=123;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+DELETE FROM v1;

+ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)

+SELECT user();

+user()

+root@localhost

+DROP VIEW v1;

+DROP TABLE t1;

+DROP USER user@localhost;

+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

+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

+Du Halgouet Tanguy NULL

+Vandamme Anna NULL

+Thomas Willy NULL

+Thomas Dominique NULL

+Lemonnier Nathalie Directeur Marketing Client

+Menseau Eric NULL

+DROP TABLE contact;

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='DATADIR/t1.dbf';

+ERROR HY000: The MariaDB server is running with the --secure-file-priv option so it cannot execute this statement

+CREATE TABLE t1 (a INT NOT NULL) ENGINE=CONNECT TABLE_TYPE=DBF FILE_NAME='SECUREDATADIR/t1.dbf';

+INSERT INTO t1 VALUES (10);

+SELECT * FROM t1;

+a

+10

+DROP TABLE t1;

+#

+# Checking TBL tables

+#

+CREATE TABLE t1 (

+a INT NOT NULL,

+message CHAR(10)) ENGINE=connect;

+Warnings:

+Warning 1105 No table_type. Was set to DOS

+Warning 1105 No file name. Table will use t1.dos

+INSERT INTO t1 VALUES (1,'Testing'),(2,'dos table'),(3,'t1');

+SELECT * FROM t1;

+a message

+1 Testing

+2 dos table

+3 t1

+CREATE TABLE t2 (

+a INT NOT NULL,

+message CHAR(10)) ENGINE=connect TABLE_TYPE=BIN;

+Warnings:

+Warning 1105 No file name. Table will use t2.bin

+INSERT INTO t2 VALUES (1,'Testing'),(2,NULL),(3,'t2');

+SELECT * FROM t2;

+a message

+1 Testing

+2 NULL

+3 t2

+CREATE TABLE t3 (

+a INT NOT NULL,

+message CHAR(10)) ENGINE=connect TABLE_TYPE=CSV;

+Warnings:

+Warning 1105 No file name. Table will use t3.csv

+INSERT INTO t3 VALUES (1,'Testing'),(2,'csv table'),(3,'t3');

+SELECT * FROM t3;

+a message

+1 Testing

+2 csv table

+3 t3

+CREATE TABLE t4 (

+ta INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

+message CHAR(20)) ENGINE=MyISAM;

+INSERT INTO t4 (message) VALUES ('Testing'),('myisam table'),('t4');

+SELECT * FROM t4;

+ta message

+1 Testing

+2 myisam table

+3 t4

+CREATE TABLE total (

+tabname CHAR(8) NOT NULL SPECIAL='TABID',

+ta TINYINT NOT NULL FLAG=1,

+message CHAR(20))

+engine=CONNECT table_type=TBL table_list='t1,t2,t3,t4';

+select * from total;

+tabname ta message

+t1 1 Testing

+t1 2 dos table

+t1 3 t1

+t2 1 Testing

+t2 2 NULL

+t2 3 t2

+t3 1 Testing

+t3 2 csv table

+t3 3 t3

+t4 1 Testing

+t4 2 myisam table

+t4 3 t4

+select * from total where tabname = 't2';

+tabname ta message

+t2 1 Testing

+t2 2 NULL

+t2 3 t2

+select * from total where tabname = 't2' and ta = 3;

+tabname ta message

+t2 3 t2

+select * from total where tabname in ('t1','t4');

+tabname ta message

+t1 1 Testing

+t1 2 dos table

+t1 3 t1

+t4 1 Testing

+t4 2 myisam table

+t4 3 t4

+select * from total where ta = 3 and tabname in ('t1','t2');

+tabname ta message

+t1 3 t1

+t2 3 t2

+select * from total where tabname <> 't2';

+tabname ta message

+t1 1 Testing

+t1 2 dos table

+t1 3 t1

+t3 1 Testing

+t3 2 csv table

+t3 3 t3

+t4 1 Testing

+t4 2 myisam table

+t4 3 t4

+select * from total where tabname != 't2' and ta = 3;

+tabname ta message

+t1 3 t1

+t3 3 t3

+t4 3 t4

+select * from total where tabname not in ('t2','t3');

+tabname ta message

+t1 1 Testing

+t1 2 dos table

+t1 3 t1

+t4 1 Testing

+t4 2 myisam table

+t4 3 t4

+select * from total where ta = 3 and tabname in ('t2','t3');

+tabname ta message

+t2 3 t2

+t3 3 t3

+select * from total where ta = 3 or tabname in ('t2','t4');

+tabname ta message

+t1 3 t1

+t2 1 Testing

+t2 2 NULL

+t2 3 t2

+t3 3 t3

+t4 1 Testing

+t4 2 myisam table

+t4 3 t4

+select * from total where not tabname = 't2';

+tabname ta message

+t1 1 Testing

+t1 2 dos table

+t1 3 t1

+t3 1 Testing

+t3 2 csv table

+t3 3 t3

+t4 1 Testing

+t4 2 myisam table

+t4 3 t4

+select * from total where tabname = 't2' or tabname = 't1';

+tabname ta message

+t1 1 Testing

+t1 2 dos table

+t1 3 t1

+t2 1 Testing

+t2 2 NULL

+t2 3 t2

+DROP TABLE total;

+DROP TABLE t1;

+DROP TABLE t2;

+DROP TABLE t3;

+DROP TABLE t4;

+CREATE TABLE employee

+(

+serialno CHAR(5) NOT NULL,

+name VARCHAR(12) NOT NULL FLAG=6,

+sex TINYINT(1) NOT NULL,

+title VARCHAR(15) NOT NULL FLAG=20,

+manager CHAR(5) NOT NULL,

+department CHAR(4) NOT NULL FLAG=41,

+secretary CHAR(5) NOT NULL FLAG=46,

+salary DOUBLE(8,2) NOT NULL FLAG=52

+) ENGINE=connect TABLE_TYPE=fix FILE_NAME='employee.dat' ENDING=1;

+SELECT * FROM employee;

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00

+36666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00

+00137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00

+CREATE PROCEDURE test.tst_up() DETERMINISTIC

+BEGIN

+SELECT * FROM t1;

+UPDATE t1 SET salary = salary + 1, title = 'RESEARCH' WHERE title = 'SCIENTIST';

+UPDATE t1 SET salary = salary + 1, title = 'TECHNICIAN' WHERE title = 'ENGINEER';

+UPDATE t1 SET title = 'PUPPET' WHERE name = 'TONGHO';

+UPDATE t1 SET salary = 0. WHERE title = 'XXX';

+SELECT * FROM t1;

+DELETE FROM t1 WHERE title = 'SECRETARY';

+DELETE FROM t1 WHERE title = 'DIRECTOR';

+DELETE FROM t1 WHERE title = 'TYPIST';

+SELECT * FROM t1;

+DELETE FROM t1 LIMIT 3;

+INSERT INTO t1(serialno, name, title, salary) VALUES('66666','NEWMAN','ENGINEER',10000.80);

+SELECT * FROM t1;

+DROP TABLE t1;

+END//

+#

+# Testing DOS table changes

+#

+CREATE TABLE t1 ENGINE=connect AS SELECT * FROM employee;

+Warnings:

+Warning 1105 No table_type. Was set to DOS

+Warning 1105 No file name. Table will use t1.dos

+CALL test.tst_up();

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00

+36666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00

+00137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+66666 NEWMAN 0 ENGINEER 10000.80

+#

+# Testing DOS table changes

+#

+CREATE TABLE t1 ENGINE=connect mapped=yes AS SELECT * FROM employee;

+Warnings:

+Warning 1105 No table_type. Was set to DOS

+Warning 1105 No file name. Table will use t1.dos

+CALL test.tst_up();

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00

+36666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00

+00137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+66666 NEWMAN 0 ENGINEER 10000.80

+#

+# Testing FIX table changes

+#

+CREATE TABLE t1 ENGINE=connect TABLE_TYPE=fix AS SELECT * FROM employee;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+CALL test.tst_up();

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00

+36666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00

+00137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+66666 NEWMAN 0 ENGINEER 10000.80

+#

+# Testing FIX table changes

+#

+CREATE TABLE t1 ENGINE=connect TABLE_TYPE=fix mapped=yes AS SELECT * FROM employee;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+CALL test.tst_up();

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 ENGINEER 87777 2452 11111 13400.00

+36666 BIGHORN 1 SCIENTIST 31416 2452 11111 11000.00

+00137 BROWNY 1 ENGINEER 40567 0319 12345 10500.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 ENGINEER 40567 0319 12345 10000.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45

+24888 PLUMHEAD 2 TYPIST 27845 0318 2800.00

+87777 STRONG 1 DIRECTOR 0021 22222 23000.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+70012 WERTHER 1 DIRECTOR 87777 0318 27845 14500.00

+40567 QUINN 1 DIRECTOR 87777 0319 55555 14000.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 TECHNICIAN 31416 2452 11111 9001.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+serialno name sex title manager department secretary salary

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 RESEARCH 31416 2452 11111 8001.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 TECHNICIAN 70012 0318 24888 7401.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 PUPPET 70012 0318 24888 6801.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+76543 BULLOZER 1 SALESMAN 40567 0319 12345 14800.00

+31416 ORELLY 1 TECHNICIAN 87777 2452 11111 13401.00

+36666 BIGHORN 1 RESEARCH 31416 2452 11111 11001.00

+00137 BROWNY 1 TECHNICIAN 40567 0319 12345 10501.00

+73111 WHEELFOR 1 SALESMAN 70012 0318 24888 10030.00

+00023 MARTIN 1 TECHNICIAN 40567 0319 12345 10001.00

+66666 NEWMAN 0 ENGINEER 10000.80

+#

+# Testing FIX table changes

+#

+CREATE TABLE t1 ENGINE=connect TABLE_TYPE=fix huge=yes AS SELECT * FROM employee;

+Warnings:

+Warning 1105 No file name. Table will use t1.fix

+CALL test.tst_up();

+serialno name sex title manager department secretary salary

+74200 BANCROFT 2 SALESMAN 70012 0318 24888 9600.00

+02345 SMITH 1 ENGINEER 31416 2452 11111 9000.00

+78943 MERCHANT 1 SALESMAN 70012 0318 24888 8700.00

+07654 FUNNIGUY 1 ADMINISTRATOR 40567 0319 33333 8500.00

+45678 BUGHAPPY 1 PROGRAMMER 40567 0319 12345 8500.00

+34567 BIGHEAD 1 SCIENTIST 31416 2452 11111 8000.00

+77777 SHRINKY 2 ADMINISTRATOR 70012 0318 27845 7500.00

+74234 WALTER 1 ENGINEER 70012 0318 24888 7400.00

+56789 FODDERMAN 1 SALESMAN 40567 0319 12345 7000.00

+73452 TONGHO 1 ENGINEER 70012 0318 24888 6800.00

+22222 SHORTSIGHT 2 SECRETARY 87777 0021 5500.00

+55555 MESSIFUL 2 SECRETARY 40567 0319 12345 5000.50

+27845 HONEY 2 SECRETARY 70012 0318 24888 4900.00

+98765 GOOSEPEN 1 ADMINISTRATOR 07654 0319 33333 4700.00

+11111 CHERRY 2 SECRETARY 31416 2452 4500.00

+33333 MONAPENNY 2 SECRETARY 07654 0319 3800.00

+12345 KITTY 2 TYPIST 40567 0319 3000.45