1
1
drop table if exists t0,t1,t2,t3,t4,t5;
3
grp int(11) default NULL,
4
a bigint(20) unsigned default NULL,
5
5
c char(10) NOT NULL default ''
7
7
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
8
create table t2 (id int, a bigint not null, c char(10), d int, primary key (a));
8
create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
9
9
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
10
10
select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
129
129
drop table t1,t2;
130
130
CREATE TABLE t1 (
132
uniq_id INT NOT NULL AUTO_INCREMENT,
133
start_num INT NOT NULL DEFAULT 1,
134
increment INT NOT NULL DEFAULT 1,
131
usr_id INT unsigned NOT NULL,
132
uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
133
start_num INT unsigned NOT NULL DEFAULT 1,
134
increment INT unsigned NOT NULL DEFAULT 1,
135
135
PRIMARY KEY (uniq_id),
136
136
INDEX usr_uniq_idx (usr_id, uniq_id),
137
137
INDEX uniq_usr_idx (uniq_id, usr_id)
139
139
CREATE TABLE t2 (
140
id INT NOT NULL DEFAULT 0,
141
usr2_id INT NOT NULL DEFAULT 0,
142
max INT NOT NULL DEFAULT 0,
143
c_amount INT NOT NULL DEFAULT 0,
144
d_max INT NOT NULL DEFAULT 0,
145
d_num INT NOT NULL DEFAULT 0,
146
orig_time INT NOT NULL DEFAULT 0,
147
c_time INT NOT NULL DEFAULT 0,
140
id INT unsigned NOT NULL DEFAULT 0,
141
usr2_id INT unsigned NOT NULL DEFAULT 0,
142
max INT unsigned NOT NULL DEFAULT 0,
143
c_amount INT unsigned NOT NULL DEFAULT 0,
144
d_max INT unsigned NOT NULL DEFAULT 0,
145
d_num INT unsigned NOT NULL DEFAULT 0,
146
orig_time INT unsigned NOT NULL DEFAULT 0,
147
c_time INT unsigned NOT NULL DEFAULT 0,
148
148
active ENUM ("no","yes") NOT NULL,
149
149
PRIMARY KEY (id,usr2_id),
150
150
INDEX id_idx (id),
182
182
3 4 84676 NULL NULL NULL
183
183
drop table t1,t2;
184
184
CREATE TABLE t1 (
185
cod_asig int DEFAULT '0' NOT NULL,
185
cod_asig int(11) DEFAULT '0' NOT NULL,
186
186
desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
187
187
desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
188
188
desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
189
189
desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
190
190
cred_total double(3,1) DEFAULT '0.0' NOT NULL,
191
pre_requisit int(11),
193
preco_requisit int(11),
194
194
PRIMARY KEY (cod_asig)
196
196
INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
202
202
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
203
203
INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','CĂźlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
204
204
CREATE TABLE t2 (
205
idAssignatura int DEFAULT '0' NOT NULL,
206
Grup int DEFAULT '0' NOT NULL,
207
Places int DEFAULT '0' NOT NULL,
208
PlacesOcupades int DEFAULT '0',
205
idAssignatura int(11) DEFAULT '0' NOT NULL,
206
Grup int(11) DEFAULT '0' NOT NULL,
207
Places smallint(6) DEFAULT '0' NOT NULL,
208
PlacesOcupades int(11) DEFAULT '0',
209
209
PRIMARY KEY (idAssignatura,Grup)
211
211
INSERT INTO t2 VALUES (10360,12,333,0);
234
234
INSERT INTO t2 VALUES (11416,11416,32767,0);
235
235
INSERT INTO t2 VALUES (11409,0,0,0);
236
236
CREATE TABLE t3 (
237
id int NOT NULL auto_increment,
237
id int(11) NOT NULL auto_increment,
238
238
dni_pasaporte char(16) DEFAULT '' NOT NULL,
239
idPla int DEFAULT '0' NOT NULL,
240
cod_asig int DEFAULT '0' NOT NULL,
241
any int DEFAULT '0' NOT NULL,
242
quatrimestre int DEFAULT '0' NOT NULL,
239
idPla int(11) DEFAULT '0' NOT NULL,
240
cod_asig int(11) DEFAULT '0' NOT NULL,
241
any smallint(6) DEFAULT '0' NOT NULL,
242
quatrimestre smallint(6) DEFAULT '0' NOT NULL,
243
243
estat char(1) DEFAULT 'M' NOT NULL,
244
244
PRIMARY KEY (id),
245
245
UNIQUE dni_pasaporte (dni_pasaporte,idPla),
248
248
INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
249
249
CREATE TABLE t4 (
250
id int NOT NULL auto_increment,
251
papa int DEFAULT '0' NOT NULL,
252
fill int DEFAULT '0' NOT NULL,
253
idPla int DEFAULT '0' NOT NULL,
250
id int(11) NOT NULL auto_increment,
251
papa int(11) DEFAULT '0' NOT NULL,
252
fill int(11) DEFAULT '0' NOT NULL,
253
idPla int(11) DEFAULT '0' NOT NULL,
254
254
PRIMARY KEY (id),
255
255
KEY papa (idPla,papa),
256
256
UNIQUE papa_2 (idPla,papa,fill)
373
373
CREATE TABLE t1 (
374
t1_id bigint NOT NULL auto_increment,
374
t1_id bigint(21) NOT NULL auto_increment,
375
375
PRIMARY KEY (t1_id)
377
377
CREATE TABLE t2 (
378
t2_id bigint NOT NULL auto_increment,
378
t2_id bigint(21) NOT NULL auto_increment,
379
379
PRIMARY KEY (t2_id)
381
381
CREATE TABLE t3 (
382
t3_id bigint NOT NULL auto_increment,
382
t3_id bigint(21) NOT NULL auto_increment,
383
383
PRIMARY KEY (t3_id)
385
385
CREATE TABLE t4 (
386
seq_0_id bigint DEFAULT '0' NOT NULL,
387
seq_1_id bigint DEFAULT '0' NOT NULL,
386
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
387
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
388
388
KEY seq_0_id (seq_0_id),
389
389
KEY seq_1_id (seq_1_id)
391
391
CREATE TABLE t5 (
392
seq_0_id bigint DEFAULT '0' NOT NULL,
393
seq_1_id bigint DEFAULT '0' NOT NULL,
392
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
393
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
394
394
KEY seq_1_id (seq_1_id),
395
395
KEY seq_0_id (seq_0_id)
594
594
2 1 NULL NULL NULL NULL
596
596
CREATE TABLE t1 (
597
id int DEFAULT '0' NOT NULL,
597
id int(11) DEFAULT '0' NOT NULL,
598
598
name tinytext DEFAULT '' NOT NULL,
602
Warning 1101 BLOB/TEXT column 'name' can't have a default value
601
603
INSERT INTO t1 VALUES (1,'yes'),(2,'no');
602
604
CREATE TABLE t2 (
603
id int DEFAULT '0' NOT NULL,
604
idx int DEFAULT '0' NOT NULL,
605
id int(11) DEFAULT '0' NOT NULL,
606
idx int(11) DEFAULT '0' NOT NULL,
605
607
UNIQUE id (id,idx)
607
609
INSERT INTO t2 VALUES (1,1);
615
617
drop table t1,t2;
616
create table t1 (bug_id bigint, reporter bigint);
617
create table t2 (bug_id bigint, who bigint, index(who));
618
create table t1 (bug_id mediumint, reporter mediumint);
619
create table t2 (bug_id mediumint, who mediumint, index(who));
618
620
insert into t2 values (1,1),(1,2);
619
621
insert into t1 values (1,1),(2,1);
620
622
SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id = t2.bug_id AND t2.who = 2) WHERE (t1.reporter = 2 OR t2.who = 2);
621
623
bug_id reporter bug_id who
623
625
drop table t1,t2;
624
create table t1 (fooID int auto_increment, primary key (fooID));
625
create table t2 (fooID int not null, barID int not null, primary key (fooID,barID));
626
create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));
627
create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
626
628
insert into t1 (fooID) values (10),(20),(30);
627
629
insert into t2 values (10,1),(20,2),(30,3);
628
630
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
629
631
id select_type table type possible_keys key key_len ref rows Extra
630
1 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index
631
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
632
1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index
633
1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index
632
634
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
633
635
fooID barID fooID
711
713
drop table t1,t2;
712
714
create table t1 (
713
match_id int not null auto_increment,
714
home int default '0',
715
match_id tinyint(3) unsigned not null auto_increment,
716
home tinyint(3) unsigned default '0',
715
717
unique key match_id (match_id),
716
718
key match_id_2 (match_id)
718
720
insert into t1 values("1", "2");
719
721
create table t2 (
720
player_id int default '0',
721
match_1_h int default '0',
722
player_id tinyint(3) unsigned default '0',
723
match_1_h tinyint(3) unsigned default '0',
722
724
key player_id (player_id)
724
726
insert into t2 values("1", "5");
736
738
order by m.match_id desc;
737
739
id select_type table type possible_keys key key_len ref rows Extra
738
740
1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
739
1 SIMPLE m const match_id,match_id_2 match_id 4 const 1
741
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
740
742
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
741
743
(t2 s left join t1 m on m.match_id = 1)
742
744
order by UUX desc;
743
745
id select_type table type possible_keys key key_len ref rows Extra
744
746
1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
745
1 SIMPLE m const match_id,match_id_2 match_id 4 const 1
747
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
746
748
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
747
749
(t2 s left join t1 m on m.match_id = 1)
748
750
order by UUX desc;
790
792
drop table t1, t2;
791
793
CREATE TABLE t1 (
792
ts_id bigint default NULL,
793
inst_id int default NULL,
794
ts_id bigint(20) default NULL,
795
inst_id tinyint(4) default NULL,
794
796
flag_name varchar(64) default NULL,
796
798
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
799
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
798
800
CREATE TABLE t2 (
799
ts_id bigint default NULL,
800
inst_id int default NULL,
801
ts_id bigint(20) default NULL,
802
inst_id tinyint(4) default NULL,
801
803
flag_name varchar(64) default NULL,
803
805
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
806
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
805
807
INSERT INTO t1 VALUES
806
808
(111056548820001, 0, 'flag1', NULL),
807
809
(111056548820001, 0, 'flag2', NULL),
857
859
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
858
860
id select_type table type possible_keys key key_len ref rows Extra
859
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
861
1 SIMPLE t1 system NULL NULL NULL NULL 1
860
862
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
861
863
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
864
866
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
865
867
id select_type table type possible_keys key key_len ref rows Extra
866
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
868
1 SIMPLE t1 system NULL NULL NULL NULL 1
867
869
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
868
870
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
869
871
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
872
874
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
873
875
id select_type table type possible_keys key key_len ref rows Extra
874
1 SIMPLE t0 index PRIMARY PRIMARY 4 NULL 1 Using index
875
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a0 1 Using index
876
1 SIMPLE t0 system PRIMARY NULL NULL NULL 1
877
1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
876
878
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
877
879
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
878
880
INSERT INTO t0 VALUES (0);
961
963
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
962
964
drop table t1, t2;
963
965
set group_concat_max_len=default;
964
create table t1 (gid int not null, x int not null, y int not null, art int not null, primary key (gid,x,y));
966
create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y));
965
967
insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
966
create table t2 (gid int not null, x int not null, y int not null, id int not null, primary key (gid,id,x,y), key id (id));
968
create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id));
967
969
insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
968
create table t3 ( set_id int not null, id int not null, name char(12) not null, primary key (id,set_id));
970
create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id));
969
971
insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
970
972
explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
971
973
left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
972
974
and t1.gid =1 and t2.gid =1 and t3.set_id =1;
973
975
id select_type table type possible_keys key key_len ref rows Extra
974
1 SIMPLE t1 const PRIMARY PRIMARY 12 const,const,const 1
975
1 SIMPLE t2 const PRIMARY,id PRIMARY 16 const,const,const,const 1 Using index
976
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
976
1 SIMPLE t1 const PRIMARY PRIMARY 10 const,const,const 1
977
1 SIMPLE t2 const PRIMARY,id PRIMARY 14 const,const,const,const 1 Using index
978
1 SIMPLE t3 const PRIMARY PRIMARY 3 const,const 1
977
979
drop tables t1,t2,t3;
978
980
CREATE TABLE t1 (c11 int);
979
981
CREATE TABLE t2 (c21 int);
1153
1155
DROP TABLE t1,t2;
1154
1156
CREATE TABLE t1 (
1155
f1 varchar(16) collate utf8_swedish_ci PRIMARY KEY,
1156
f2 varchar(16) collate utf8_swedish_ci
1157
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
1158
f2 varchar(16) collate latin1_swedish_ci
1158
1160
CREATE TABLE t2 (
1159
f1 varchar(16) collate utf8_swedish_ci PRIMARY KEY,
1160
f3 varchar(16) collate utf8_swedish_ci
1161
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
1162
f3 varchar(16) collate latin1_swedish_ci
1162
1164
INSERT INTO t1 VALUES ('bla','blah');
1163
1165
INSERT INTO t2 VALUES ('bla','sheep');