632
632
drop table t11, t12, t2;
633
CREATE TABLE t1 (x int);
634
create table t2 (a int);
633
CREATE TABLE t1 (x int) ENGINE=MyISAM;
634
create table t2 (a int) ENGINE=MyISAM;
635
635
create table t3 (b int);
636
636
insert into t2 values (1);
637
637
insert into t3 values (1),(2);
680
680
drop table t1, t2, t3;
681
CREATE TABLE t1 (x int not null, y int, primary key (x));
681
CREATE TABLE t1 (x int not null, y int, primary key (x)) ENGINE=MyISAM;
682
682
create table t2 (a int);
683
683
create table t3 (a int);
684
684
insert into t2 values (1);
3662
3662
DROP TABLE t1,t2;
3663
CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
3663
CREATE TABLE t1 (a int, b int, PRIMARY KEY (b));
3664
3664
CREATE TABLE t2 (x int auto_increment, y int, z int,
3665
3665
PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
3666
create table t3 (a int);
3667
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3668
insert into t1 select RAND()*1000, A.a + 10*(B.a+10*(C.a+10*D.a))
3669
from t3 A, t3 B, t3 C, t3 D where D.a<3;
3670
insert into t2(y,z) select t1.b, RAND()*1000 from t1, t3;
3666
3671
SET SESSION sort_buffer_size = 32 * 1024;
3668
3673
Warning 1292 Truncated incorrect sort_buffer_size value: '32768'
4345
4350
DROP TABLE t1, t2, t3, t4;
4351
create table t1 (a float(5,4) zerofill);
4352
create table t2 (a float(5,4),b float(2,0));
4353
select t1.a from t1 where
4354
t1.a= (select b from t2 limit 1) and not
4355
t1.a= (select a from t2 limit 1) ;
4358
CREATE TABLE t1 (a INT);
4359
INSERT INTO t1 VALUES (1),(2);
4360
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
4361
id select_type table type possible_keys key key_len ref rows filtered Extra
4362
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4363
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
4365
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key)))
4366
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
4367
id select_type table type possible_keys key key_len ref rows filtered Extra
4368
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
4369
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
4371
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key)))
4346
4373
End of 5.0 tests.
4347
4374
create table t_out (subcase char(3),
4348
4375
a1 char(2), b1 char(2), c1 char(2));
4600
CREATE TABLE t1(c int, KEY(c));
4601
CREATE TABLE t2(a int, b int);
4602
INSERT INTO t2 VALUES (1, 10), (2, NULL);
4603
INSERT INTO t1 VALUES (1), (3);
4604
SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10);
4573
4608
CREATE TABLE t1( a INT );
4574
4609
INSERT INTO t1 VALUES (1),(2);
4575
4610
CREATE TABLE t2( a INT, b INT );