EVOLUTION-MANAGER

Edit File: mysql_index.result

# # 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 > 3; id msg 5 Cinq 4 Four 6 Six SELECT * FROM t2 WHERE id >= 3; id msg 3 Trois 5 Cinq 4 Four 6 Six SELECT * FROM t2 WHERE id < 3; id msg 1 Un 2 Two SELECT * FROM t2 WHERE id < 3 OR id > 4; id msg 1 Un 2 Two 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 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 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 6124 ABELIAS DELIA 6314 ABERDEN EVELYNE 895 ABORD CHANTAL 2728 ABOUT CATHERINE MARIE 398 ABREUVOIR JEAN LUC 1122 ACACIAS SERGE 1644 ACARDIE BEATE 115 ACHILLE JACQUES 4038 ADAM JANICK 3395 ADAM JEAN CLAUDE SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL'; 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 matricule, nom, prenom FROM t2 WHERE nom > 'YVON'; matricule nom prenom 9742 YZENGREMER MICHEL 8738 ZILINA JEAN LOUIS 5357 ZOLA BERNARD 5441 ZOLA BRIGITTE 1325 ZOLA CHRISTINE 4859 ZORI CATHERINE 4102 ZOUAVES ALAIN SELECT matricule, nom, prenom FROM t2 WHERE nom >= 'YVON'; matricule nom prenom 5389 YVON CAROLE 9742 YZENGREMER MICHEL 8738 ZILINA JEAN LOUIS 5357 ZOLA BERNARD 5441 ZOLA BRIGITTE 1325 ZOLA CHRISTINE 4859 ZORI CATHERINE 4102 ZOUAVES ALAIN SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL' OR nom > 'YVON'; 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 9742 YZENGREMER MICHEL 8738 ZILINA JEAN LOUIS 5357 ZOLA BERNARD 5441 ZOLA BRIGITTE 1325 ZOLA CHRISTINE 4859 ZORI CATHERINE 4102 ZOUAVES ALAIN SELECT matricule, nom, prenom FROM t2 WHERE nom > 'HELEN' AND nom < 'HEROS'; matricule nom prenom 9096 HELENA PHILIPPE 3309 HELENE ISABELLE 8365 HELIOTROPES LISE 4666 HELLEN PIERRE 5781 HELSINKI DANIELLE 7626 HENIN PHILIPPE 4254 HENIN SERGE 2673 HENNER LILIANE 9716 HENRI JACQUES 2085 HEOL GUY PAUL 2579 HERANDIERE PIERRE 7093 HERAULTS DANIEL 4050 HERBILLON FRANCOIS 9231 HERBILLON MADELEINE 1291 HERMITAGE XAVIER 6185 HERMITTE FRANCOIS 403 HERMITTE PHILIPPE 9749 HEROLD ISABELLE SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS'; matricule nom prenom 6199 HELEN MARTIAL 9096 HELENA PHILIPPE 3309 HELENE ISABELLE 8365 HELIOTROPES LISE 4666 HELLEN PIERRE 5781 HELSINKI DANIELLE 7626 HENIN PHILIPPE 4254 HENIN SERGE 2673 HENNER LILIANE 9716 HENRI JACQUES 2085 HEOL GUY PAUL 2579 HERANDIERE PIERRE 7093 HERAULTS DANIEL 4050 HERBILLON FRANCOIS 9231 HERBILLON MADELEINE 1291 HERMITAGE XAVIER 6185 HERMITTE FRANCOIS 403 HERMITTE PHILIPPE 9749 HEROLD ISABELLE 8445 HEROS SYLVIE SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS' AND prenom = 'PHILIPPE'; matricule nom prenom 9096 HELENA PHILIPPE 7626 HENIN PHILIPPE 403 HERMITTE PHILIPPE SELECT matricule, nom, prenom FROM t2 ORDER BY nom LIMIT 10; matricule nom prenom 4552 ABBADIE MONIQUE 6627 ABBAYE GERALD 307 ABBAYE ANNICK 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 SERGE ACACIAS FOCH ROBERT AGRIANT FOCH JOCELYNE ALEXIS FOCH DENIS AMBOISE FOCH SERGE ANDALUCIA FOCH ROBERT ANNECY FOCH SERGE ARCACHON FOCH JOCELYNE AUGUSTE FOCH JOCELYNE BASSE FOCH SERGE BEACH FOCH FRANCK BEARN FOCH SERGE BELLES FOCH DENIS BERARD FOCH DENIS BERIN FOCH BERNADETTE BERTIN FOCH DENIS BILLEHOU FOCH DENIS BOILEAU FOCH BERNADETTE BOISSY FOCH ROBERT BONVIN FOCH SERGE BOUTON FOCH SERGE BREUIL FOCH SERGE CARREFOUR FOCH JOCELYNE CARRERE FOCH JOCELYNE CHAPELLE FOCH SERGE CHATEAU FOCH ROBERT CHENIER FOCH SERGE COLLETTE FOCH DENIS CONNE FOCH SERGE COOLE FOCH DENIS COULOUBRIER FOCH DENIS COUTURIER FOCH ROBERT CURAT FOCH ROBERT DAUDET FOCH SERGE ECLUSE FOCH SERGE EGUILLON FOCH DENIS EPINETTES FOCH DENIS FIGOURNAS FOCH JOCELYNE FLEMING FOCH JOCELYNE GAMBADES FOCH ROBERT GIOTERAIE FOCH SERGE GOAS FOCH ROBERT GRAFFIANE FOCH SERGE GREFFIER FOCH ROBERT GUILLOTIERE FOCH SERGE HENIN FOCH BERNADETTE HUNTZIGER FOCH FRANCK ILLIERS FOCH DENIS ISTANBUL FOCH DENIS ITALIE FOCH SERGE JARDIN FOCH FRANCK JEANPIERRE FOCH JOCELYNE KENNEDY FOCH FRANCK LABBE FOCH DENIS LACATE FOCH FRANCK LACOMBE FOCH ROBERT LAMOTHE FOCH BERNADETTE LATECOERE FOCH BERNADETTE LEGER FOCH SERGE LEONIE FOCH FRANCK LEROY FOCH SERGE LOZERE FOCH DENIS MAROLLES FOCH ROBERT MARRONIERS FOCH SERGE MARSAT FOCH SERGE MONTAGNE FOCH FRANCK MONTALEIGNE FOCH DENIS MONTELIER FOCH DENIS MONTILS FOCH BERNADETTE MONTJUSTIN FOCH SERGE MORIZET FOCH ROBERT NIMES FOCH ROBERT NORD FOCH SERGE NOVEMBRE FOCH BERNADETTE ONZE FOCH SERGE ORANGERIE FOCH FRANCK ORVEAU FOCH BERNADETTE PALMAROLE FOCH JOCELYNE PEYBERT FOCH ROBERT PEYNIBLOU FOCH ROBERT PIECE FOCH JOCELYNE PIED FOCH ROBERT PLAGNE FOCH SERGE PLAISANCE FOCH BERNADETTE PLOUHARNEL FOCH DENIS POINTE FOCH ROBERT POMMERY FOCH JOCELYNE PONTAROUX FOCH DENIS PORTO FOCH ROBERT PRESIDENT FOCH ROBERT PUJADE FOCH FRANCK PURPAN FOCH ROBERT QUILICHINI FOCH DENIS REINOTS FOCH DENIS REMPART FOCH SERGE RESISTANCE FOCH SERGE RESTANQUES FOCH ROBERT RIOU FOCH FRANCK ROCQUENCOURT FOCH ROBERT ROLL FOCH ROBERT ROSSA FOCH SERGE ROSSAYS FOCH DENIS ROUSSIER FOCH FRANCK RUSSIE FOCH ROBERT SABLONS FOCH SERGE SARTRE FOCH SERGE SAVIGNAC FOCH SERGE SEGUR FOCH ROBERT STRASBOURG FOCH ROBERT TIRE FOCH DENIS TORTE FOCH DENIS TOULON FOCH ROBERT TUBY FOCH DENIS VALMANTE FOCH SERGE VANOEL FOCH ROBERT VIARMES FOCH SERGE WILSON DROP TABLE t2; DROP TABLE t1;