diff options
Diffstat (limited to 'storage/connect/mysql-test/connect/r/mysql_index.result')
-rw-r--r-- | storage/connect/mysql-test/connect/r/mysql_index.result | 436 |
1 files changed, 436 insertions, 0 deletions
diff --git a/storage/connect/mysql-test/connect/r/mysql_index.result b/storage/connect/mysql-test/connect/r/mysql_index.result new file mode 100644 index 00000000..54acc7be --- /dev/null +++ b/storage/connect/mysql-test/connect/r/mysql_index.result @@ -0,0 +1,436 @@ +# +# Make remote table +# +CREATE TABLE t1 ( +id int(11) NOT NULL, +msg char(100) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES(1,'Un'),(3,'Trois'),(5,'Cinq'); +INSERT INTO t1 VALUES(2,'Two'),(4,'Four'),(6,'Six'); +SELECT * FROM t1; +id msg +1 Un +3 Trois +5 Cinq +2 Two +4 Four +6 Six +# +# Make local MYSQL table with indexed id column +# +CREATE TABLE t2 ( +id int(11) NOT NULL, +msg char(100) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=CONNECT DEFAULT CHARSET=latin1 TABLE_TYPE=MYSQL TABNAME=t1; +# +# Testing SELECT, etc. +# +SELECT * FROM t2; +id msg +1 Un +3 Trois +5 Cinq +2 Two +4 Four +6 Six +SELECT * FROM t2 WHERE id = 3; +id msg +3 Trois +SELECT * FROM t2 WHERE id IN (2,4); +id msg +2 Two +4 Four +SELECT * FROM t2 WHERE id IN (2,4) AND msg = 'Two'; +id msg +2 Two +SELECT * FROM t2 WHERE id > 4; +id msg +5 Cinq +6 Six +SELECT * FROM t2 WHERE id >= 3; +id msg +3 Trois +4 Four +5 Cinq +6 Six +SELECT * FROM t2 WHERE id < 3; +id msg +1 Un +2 Two +SELECT * FROM t2 WHERE id < 2 OR id > 4; +id msg +1 Un +5 Cinq +6 Six +SELECT * FROM t2 WHERE id <= 3; +id msg +1 Un +2 Two +3 Trois +SELECT * FROM t2 WHERE id BETWEEN 3 AND 5; +id msg +3 Trois +4 Four +5 Cinq +SELECT * FROM t2 WHERE id > 2 AND id < 6; +id msg +3 Trois +4 Four +5 Cinq +SELECT * FROM t2 ORDER BY id; +id msg +1 Un +2 Two +3 Trois +4 Four +5 Cinq +6 Six +UPDATE t2 SET msg = 'Five' WHERE id = 5; +Warnings: +Note 1105 t1: 1 affected rows +SELECT * FROM t2; +id msg +1 Un +3 Trois +5 Five +2 Two +4 Four +6 Six +DELETE FROM t2 WHERE id = 4; +Warnings: +Note 1105 t1: 1 affected rows +SELECT * FROM t2; +id msg +1 Un +3 Trois +5 Five +2 Two +6 Six +DROP TABLE t2; +DROP TABLE t1; +# +# Make local FIX table with indices matricule and nom/prenom +# +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; +# +# Make MYSQL table with same indices +# +CREATE TABLE t2 +( +matricule INT(4) KEY NOT NULL, +nom VARCHAR(16) NOT NULL, +prenom VARCHAR(20) NOT NULL, +sexe SMALLINT(1) NOT NULL, +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=MYSQL CONNECTIOn='mysql://root@localhost/test/t1'; +SELECT * FROM t2 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 matricule, nom, prenom FROM t2 WHERE nom IN ('FOCH','MOGADOR'); +matricule nom prenom +1977 FOCH BERNADETTE +5707 FOCH DENIS +2552 FOCH FRANCK +2634 FOCH JOCELYNE +5765 FOCH ROBERT +4080 FOCH SERGE +3368 MOGADOR ALAIN +explain SELECT matricule, nom, prenom FROM t2 WHERE nom IN ('FOCH','MOGADOR'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range NP NP 18 NULL 4 Using where +SELECT matricule, nom, prenom FROM t2 WHERE nom = 'FOCH' OR nom = 'MOGADOR'; +matricule nom prenom +1977 FOCH BERNADETTE +5707 FOCH DENIS +2552 FOCH FRANCK +2634 FOCH JOCELYNE +5765 FOCH ROBERT +4080 FOCH SERGE +3368 MOGADOR ALAIN +SELECT matricule, nom, prenom FROM t2 WHERE nom < 'ADDAX'; +matricule nom prenom +1122 ACACIAS SERGE +115 ACHILLE JACQUES +1340 ABBE MICHELE +1644 ACARDIE BEATE +2728 ABOUT CATHERINE MARIE +2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK +3395 ADAM JEAN CLAUDE +398 ABREUVOIR JEAN LUC +4038 ADAM JANICK +4552 ABBADIE MONIQUE +6124 ABELIAS DELIA +6314 ABERDEN EVELYNE +6399 ABEILLES RENE +6627 ABBAYE GERALD +7961 ABBE KATIA +8596 ABEBERRY PATRICK +8673 ABEL JEAN PIERRE +895 ABORD CHANTAL +9270 ABBE SOPHIE +SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL'; +matricule nom prenom +1340 ABBE MICHELE +2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK +4552 ABBADIE MONIQUE +6399 ABEILLES RENE +6627 ABBAYE GERALD +7961 ABBE KATIA +8596 ABEBERRY PATRICK +8673 ABEL JEAN PIERRE +9270 ABBE SOPHIE +SELECT matricule, nom, prenom FROM t2 WHERE nom > 'YVON'; +matricule nom prenom +1325 ZOLA CHRISTINE +4102 ZOUAVES ALAIN +4859 ZORI CATHERINE +5357 ZOLA BERNARD +5441 ZOLA BRIGITTE +8738 ZILINA JEAN LOUIS +9742 YZENGREMER MICHEL +SELECT matricule, nom, prenom FROM t2 WHERE nom >= 'YVON'; +matricule nom prenom +1325 ZOLA CHRISTINE +4102 ZOUAVES ALAIN +4859 ZORI CATHERINE +5357 ZOLA BERNARD +5389 YVON CAROLE +5441 ZOLA BRIGITTE +8738 ZILINA JEAN LOUIS +9742 YZENGREMER MICHEL +SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL' OR nom > 'YVON'; +matricule nom prenom +1325 ZOLA CHRISTINE +1340 ABBE MICHELE +2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK +4102 ZOUAVES ALAIN +4552 ABBADIE MONIQUE +4859 ZORI CATHERINE +5357 ZOLA BERNARD +5441 ZOLA BRIGITTE +6399 ABEILLES RENE +6627 ABBAYE GERALD +7961 ABBE KATIA +8596 ABEBERRY PATRICK +8673 ABEL JEAN PIERRE +8738 ZILINA JEAN LOUIS +9270 ABBE SOPHIE +9742 YZENGREMER MICHEL +SELECT matricule, nom, prenom FROM t2 WHERE nom > 'HELEN' AND nom < 'HEROS'; +matricule nom prenom +1291 HERMITAGE XAVIER +2085 HEOL GUY PAUL +2579 HERANDIERE PIERRE +2673 HENNER LILIANE +3309 HELENE ISABELLE +403 HERMITTE PHILIPPE +4050 HERBILLON FRANCOIS +4254 HENIN SERGE +4666 HELLEN PIERRE +5781 HELSINKI DANIELLE +6185 HERMITTE FRANCOIS +7093 HERAULTS DANIEL +7626 HENIN PHILIPPE +8365 HELIOTROPES LISE +9096 HELENA PHILIPPE +9231 HERBILLON MADELEINE +9716 HENRI JACQUES +9749 HEROLD ISABELLE +SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS'; +matricule nom prenom +1291 HERMITAGE XAVIER +2085 HEOL GUY PAUL +2579 HERANDIERE PIERRE +2673 HENNER LILIANE +3309 HELENE ISABELLE +403 HERMITTE PHILIPPE +4050 HERBILLON FRANCOIS +4254 HENIN SERGE +4666 HELLEN PIERRE +5781 HELSINKI DANIELLE +6185 HERMITTE FRANCOIS +6199 HELEN MARTIAL +7093 HERAULTS DANIEL +7626 HENIN PHILIPPE +8365 HELIOTROPES LISE +8445 HEROS SYLVIE +9096 HELENA PHILIPPE +9231 HERBILLON MADELEINE +9716 HENRI JACQUES +9749 HEROLD ISABELLE +SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS' AND prenom = 'PHILIPPE'; +matricule nom prenom +403 HERMITTE PHILIPPE +7626 HENIN PHILIPPE +9096 HELENA PHILIPPE +SELECT matricule, nom, prenom FROM t2 ORDER BY nom,prenom LIMIT 10; +matricule nom prenom +4552 ABBADIE MONIQUE +307 ABBAYE ANNICK +6627 ABBAYE GERALD +7961 ABBE KATIA +1340 ABBE MICHELE +9270 ABBE SOPHIE +2945 ABBEVILLE PASCAL +8596 ABEBERRY PATRICK +6399 ABEILLES RENE +8673 ABEL JEAN PIERRE +SELECT a.nom, a.prenom, b.nom FROM t1 a STRAIGHT_JOIN t2 b ON a.prenom = b.prenom WHERE a.nom = 'FOCH' AND a.nom != b.nom; +nom prenom nom +FOCH BERNADETTE BERTIN +FOCH BERNADETTE BOISSY +FOCH BERNADETTE HUNTZIGER +FOCH BERNADETTE LATECOERE +FOCH BERNADETTE LEGER +FOCH BERNADETTE MONTJUSTIN +FOCH BERNADETTE ONZE +FOCH BERNADETTE PALMAROLE +FOCH BERNADETTE PLOUHARNEL +FOCH DENIS AMBOISE +FOCH DENIS BERARD +FOCH DENIS BERIN +FOCH DENIS BILLEHOU +FOCH DENIS BOILEAU +FOCH DENIS CONNE +FOCH DENIS COULOUBRIER +FOCH DENIS COUTURIER +FOCH DENIS EPINETTES +FOCH DENIS FIGOURNAS +FOCH DENIS ISTANBUL +FOCH DENIS ITALIE +FOCH DENIS LACATE +FOCH DENIS MAROLLES +FOCH DENIS MONTELIER +FOCH DENIS MONTILS +FOCH DENIS POINTE +FOCH DENIS PORTO +FOCH DENIS REINOTS +FOCH DENIS REMPART +FOCH DENIS ROUSSIER +FOCH DENIS TORTE +FOCH DENIS TOULON +FOCH DENIS VALMANTE +FOCH FRANCK BEARN +FOCH FRANCK ILLIERS +FOCH FRANCK JEANPIERRE +FOCH FRANCK LABBE +FOCH FRANCK LACOMBE +FOCH FRANCK LEROY +FOCH FRANCK MONTALEIGNE +FOCH FRANCK ORVEAU +FOCH FRANCK PURPAN +FOCH FRANCK ROCQUENCOURT +FOCH FRANCK RUSSIE +FOCH JOCELYNE ALEXIS +FOCH JOCELYNE AUGUSTE +FOCH JOCELYNE BASSE +FOCH JOCELYNE CARRERE +FOCH JOCELYNE CHAPELLE +FOCH JOCELYNE FLEMING +FOCH JOCELYNE GAMBADES +FOCH JOCELYNE KENNEDY +FOCH JOCELYNE PEYBERT +FOCH JOCELYNE PIED +FOCH JOCELYNE PONTAROUX +FOCH ROBERT AGRIANT +FOCH ROBERT ANNECY +FOCH ROBERT BONVIN +FOCH ROBERT CHENIER +FOCH ROBERT CURAT +FOCH ROBERT DAUDET +FOCH ROBERT GIOTERAIE +FOCH ROBERT GRAFFIANE +FOCH ROBERT GUILLOTIERE +FOCH ROBERT LAMOTHE +FOCH ROBERT MARRONIERS +FOCH ROBERT NIMES +FOCH ROBERT NORD +FOCH ROBERT PEYNIBLOU +FOCH ROBERT PIECE +FOCH ROBERT PLAGNE +FOCH ROBERT POMMERY +FOCH ROBERT PRESIDENT +FOCH ROBERT PUJADE +FOCH ROBERT QUILICHINI +FOCH ROBERT RIOU +FOCH ROBERT ROLL +FOCH ROBERT ROSSA +FOCH ROBERT SABLONS +FOCH ROBERT STRASBOURG +FOCH ROBERT TIRE +FOCH ROBERT TUBY +FOCH ROBERT VIARMES +FOCH SERGE ACACIAS +FOCH SERGE ANDALUCIA +FOCH SERGE ARCACHON +FOCH SERGE BEACH +FOCH SERGE BELLES +FOCH SERGE BOUTON +FOCH SERGE BREUIL +FOCH SERGE CARREFOUR +FOCH SERGE CHATEAU +FOCH SERGE COLLETTE +FOCH SERGE COOLE +FOCH SERGE ECLUSE +FOCH SERGE EGUILLON +FOCH SERGE GOAS +FOCH SERGE GREFFIER +FOCH SERGE HENIN +FOCH SERGE JARDIN +FOCH SERGE LEONIE +FOCH SERGE LOZERE +FOCH SERGE MARSAT +FOCH SERGE MONTAGNE +FOCH SERGE MORIZET +FOCH SERGE NOVEMBRE +FOCH SERGE ORANGERIE +FOCH SERGE PLAISANCE +FOCH SERGE RESISTANCE +FOCH SERGE RESTANQUES +FOCH SERGE ROSSAYS +FOCH SERGE SARTRE +FOCH SERGE SAVIGNAC +FOCH SERGE SEGUR +FOCH SERGE VANOEL +FOCH SERGE WILSON +DROP TABLE t2; +DROP TABLE t1; |