1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
let $MYSQLD_DATADIR= `select @@datadir`;
--copy_file $MTR_SUITE_DIR/std_data/emp.txt $MYSQLD_DATADIR/test/emp.txt
--copy_file $MTR_SUITE_DIR/std_data/sexe.csv $MYSQLD_DATADIR/test/sexe.csv
--copy_file $MTR_SUITE_DIR/std_data/sitmat.csv $MYSQLD_DATADIR/test/sitmat.csv
--echo #
--echo # Testing indexing
--echo #
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;
SELECT SUM(brut) from t1;
--echo #
--echo # Testing file mapping
--echo #
ALTER TABLE t1 MAPPED=yes;
SELECT * FROM t1 LIMIT 10;
SELECT SUM(brut) FROM t1;
--echo #
--echo # Test the indexes (made when creating the table)
--echo #
SELECT * FROM t1 WHERE matricule = '0091';
SELECT * FROM t1 WHERE nom = 'FOCH';
SELECT * FROM t1 WHERE nom = 'FOCH' and prenom = 'DENIS';
--echo #
--echo # Testing UPDATE
--echo #
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';
--echo #
--echo # Testing JOIN
--echo #
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;
SELECT nom, prenom, genre FROM t1 NATURAL JOIN t2 LIMIT 10;
--echo #
--echo # Another table
--echo #
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;
SELECT nom, prenom, genre, situation FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE nom = 'FOCH';
--echo #
--echo # Testing DELETE
--echo #
DELETE FROM t1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
--echo #
--echo # MDEV-28299: Server crashes in
--echo # XINDXS::Range/CntIndexRange (Connect engine)
--echo #
CREATE TABLE t1 ( a int not null, KEY (a))engine=CONNECT;
SELECT * FROM t1 WHERE a=1;
INSERT INTO t1 values (1),(2),(1);
SELECT * FROM t1 WHERE a=1;
DROP TABLE t1;
CREATE TABLE t1 (a int, b int, pk int, PRIMARY KEY (pk)) engine=CONNECT;
SELECT x.a
FROM t1 AS x JOIN t1 AS y ON (x.a = y.b)
WHERE x.pk > 3;
INSERT INTO t1 values (1,2,1),(2,1,2),(1,2,3),(3,4,4);
SELECT x.a
FROM t1 AS x JOIN t1 AS y ON (x.a = y.b)
WHERE x.pk > 3;
INSERT INTO t1 values (1,2,5);
SELECT x.a
FROM t1 AS x JOIN t1 AS y ON (x.a = y.b)
WHERE x.pk > 3;
DROP TABLE t1;
--echo #
--echo # MDEV-27591 Connect tables (FIX/DOS) don't work with DESC keys - wrong results
--echo #
--error ER_UNKNOWN_ERROR
CREATE TABLE t1 (
id INT,
f VARCHAR(32),
PRIMARY KEY (id DESC)
) ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='emp.txt';
#
# Clean up
#
--remove_file $MYSQLD_DATADIR/test/emp.txt
--remove_file $MYSQLD_DATADIR/test/sexe.csv
--remove_file $MYSQLD_DATADIR/test/sitmat.csv
|
