354
354
CREATE TABLE `t8` (
355
`pseudo` varchar(35) character set latin1 NOT NULL default '',
356
`email` varchar(60) character set latin1 NOT NULL default '',
355
`pseudo` varchar(35) NOT NULL default '',
356
`email` varchar(60) NOT NULL default '',
357
357
PRIMARY KEY (`pseudo`),
358
358
UNIQUE KEY `email` (`email`)
359
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
359
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
360
360
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
361
361
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
362
362
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
363
363
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
364
364
id select_type table type possible_keys key key_len ref rows filtered Extra
365
1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
366
4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
367
2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
368
3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
365
1 PRIMARY t8 const PRIMARY PRIMARY 142 const 1 100.00 Using index
366
4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
367
2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00
368
3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index
370
Note 1003 select 'joce' AS "pseudo",(select 'test' AS "email" from "test"."t8" where 1) AS "(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))" from "test"."t8" where 1
370
Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' AS `pseudo` from `test`.`t8` where ('joce' = 'joce')))
371
371
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
372
372
t8 WHERE pseudo='joce');
373
373
ERROR 21000: Operand should contain 1 column(s)
906
906
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
907
907
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
909
Note 1003 select "test"."t1"."a" AS "a",<in_optimizer>("test"."t1"."a",<exists>(select 1 AS "Not_used" from "test"."t2" join "test"."t3" where (("test"."t3"."a" = "test"."t2"."a") and ((<cache>("test"."t1"."a") = "test"."t2"."a") or isnull("test"."t2"."a"))) having <is_not_null_test>("test"."t2"."a"))) AS "t1.a in (select t2.a from t2,t3 where t3.a=t2.a)" from "test"."t1"
909
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
910
910
drop table t1,t2,t3;
911
create table t1 (a float);
912
select 10.5 IN (SELECT * from t1 LIMIT 1);
913
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
914
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
915
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
917
create table t1 (a int, b int, c varchar(10));
918
create table t2 (a int);
919
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
920
insert into t2 values (1),(2),(NULL);
921
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
922
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)
926
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
927
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)
931
select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
932
a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)
937
create table t1 (a int, b real, c varchar(10));
938
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
939
select ROW(1, 1, 'a') IN (select a,b,c from t1);
940
ROW(1, 1, 'a') IN (select a,b,c from t1)
942
select ROW(1, 2, 'a') IN (select a,b,c from t1);
943
ROW(1, 2, 'a') IN (select a,b,c from t1)
945
select ROW(1, 1, 'a') IN (select b,a,c from t1);
946
ROW(1, 1, 'a') IN (select b,a,c from t1)
948
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
949
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
951
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
952
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
954
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
955
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
957
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
958
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
960
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
961
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
963
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
964
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
966
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
967
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
969
CREATE TABLE t1 (a int(1));
911
CREATE TABLE t1 (a int);
970
912
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
971
913
id select_type table type possible_keys key key_len ref rows filtered Extra
972
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
973
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
975
Note 1003 select (select rand() AS "RAND()" from "test"."t1") AS "(SELECT RAND() FROM t1)" from "test"."t1"
976
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
977
id select_type table type possible_keys key key_len ref rows filtered Extra
978
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
979
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
981
Note 1003 select (select encrypt('test') AS "ENCRYPT('test')" from "test"."t1") AS "(SELECT ENCRYPT('test') FROM t1)" from "test"."t1"
914
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
915
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00
917
Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
982
918
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
983
919
id select_type table type possible_keys key key_len ref rows filtered Extra
984
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
985
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
920
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
921
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 100.00
987
Note 1003 select (select benchmark(1,1) AS "BENCHMARK(1,1)" from "test"."t1") AS "(SELECT BENCHMARK(1,1) FROM t1)" from "test"."t1"
923
Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
989
925
CREATE TABLE `t1` (
990
`mot` varchar(30) character set latin1 NOT NULL default '',
991
`topic` mediumint(8) unsigned NOT NULL default '0',
926
`mot` varchar(30) NOT NULL default '',
927
`topic` bigint NOT NULL default '0',
992
928
`date` date NOT NULL default '0000-00-00',
993
`pseudo` varchar(35) character set latin1 NOT NULL default '',
929
`pseudo` varchar(35) NOT NULL default '',
994
930
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
995
931
KEY `pseudo` (`pseudo`,`date`,`topic`),
996
932
KEY `topic` (`topic`)
997
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
933
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
998
934
CREATE TABLE `t2` (
999
`mot` varchar(30) character set latin1 NOT NULL default '',
1000
`topic` mediumint(8) unsigned NOT NULL default '0',
935
`mot` varchar(30) NOT NULL default '',
936
`topic` bigint NOT NULL default '0',
1001
937
`date` date NOT NULL default '0000-00-00',
1002
`pseudo` varchar(35) character set latin1 NOT NULL default '',
938
`pseudo` varchar(35) NOT NULL default '',
1003
939
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
1004
940
KEY `pseudo` (`pseudo`,`date`,`topic`),
1005
941
KEY `topic` (`topic`)
1006
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
942
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
1007
943
CREATE TABLE `t3` (
1008
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
1009
`maxnumrep` int(10) unsigned NOT NULL default '0',
944
`numeropost` bigint NOT NULL auto_increment,
945
`maxnumrep` int NOT NULL default '0',
1010
946
PRIMARY KEY (`numeropost`),
1011
947
UNIQUE KEY `maxnumrep` (`maxnumrep`)
1012
) ENGINE=MyISAM CHARSET=latin1;
1013
949
INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1014
950
INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');
1015
951
INSERT INTO t3 VALUES (1,1);
1409
1337
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1410
1338
id select_type table type possible_keys key key_len ref rows filtered Extra
1411
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1412
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1339
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1340
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1414
Note 1003 select "test"."t1"."s1" AS "s1",(not(<in_optimizer>("test"."t1"."s1",<exists>(<index_lookup>(<cache>("test"."t1"."s1") in t2 on s1 checking NULL having trigcond(<is_not_null_test>("test"."t2"."s1"))))))) AS "s1 NOT IN (SELECT s1 FROM t2)" from "test"."t1"
1342
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
1415
1343
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1416
1344
id select_type table type possible_keys key key_len ref rows filtered Extra
1417
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1418
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1345
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1346
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1420
Note 1003 select "test"."t1"."s1" AS "s1",<in_optimizer>("test"."t1"."s1",<exists>(<index_lookup>(<cache>("test"."t1"."s1") in t2 on s1 checking NULL having trigcond(<is_not_null_test>("test"."t2"."s1"))))) AS "s1 = ANY (SELECT s1 FROM t2)" from "test"."t1"
1348
Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
1421
1349
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1422
1350
id select_type table type possible_keys key key_len ref rows filtered Extra
1423
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1424
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
1351
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1352
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1426
Note 1003 select "test"."t1"."s1" AS "s1",(not(<in_optimizer>("test"."t1"."s1",<exists>(<index_lookup>(<cache>("test"."t1"."s1") in t2 on s1 checking NULL having trigcond(<is_not_null_test>("test"."t2"."s1"))))))) AS "s1 <> ALL (SELECT s1 FROM t2)" from "test"."t1"
1354
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
1427
1355
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1428
1356
id select_type table type possible_keys key key_len ref rows filtered Extra
1429
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
1430
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
1357
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1358
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Using where; Full scan on NULL key
1432
Note 1003 select "test"."t1"."s1" AS "s1",(not(<in_optimizer>("test"."t1"."s1",<exists>(<index_lookup>(<cache>("test"."t1"."s1") in t2 on s1 checking NULL where ("test"."t2"."s1" < 'a2') having trigcond(<is_not_null_test>("test"."t2"."s1"))))))) AS "s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')" from "test"."t1"
1360
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
1433
1361
drop table t1,t2;
1434
1362
create table t2 (a int, b int);
1435
1363
create table t3 (a int);
1461
1389
explain extended select * from t3 where a >= all (select b from t2 group by 1);
1462
1390
id select_type table type possible_keys key key_len ref rows filtered Extra
1463
1391
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1464
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1392
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort
1466
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" < <max>(select '0' AS "b" from "test"."t2" group by 1)))
1394
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))
1467
1395
select * from t3 where a >= some (select b from t2 group by 1);
1469
1397
explain extended select * from t3 where a >= some (select b from t2 group by 1);
1470
1398
id select_type table type possible_keys key key_len ref rows filtered Extra
1471
1399
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1472
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1400
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort
1474
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <nop>(("test"."t3"."a" >= <min>(select '0' AS "b" from "test"."t2" group by 1)))
1402
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))
1475
1403
select * from t3 where NULL >= any (select b from t2);
1477
1405
explain extended select * from t3 where NULL >= any (select b from t2);
1478
1406
id select_type table type possible_keys key key_len ref rows filtered Extra
1479
1407
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1480
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1408
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00
1482
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1410
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1483
1411
select * from t3 where NULL >= any (select b from t2 group by 1);
1485
1413
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
1486
1414
id select_type table type possible_keys key key_len ref rows filtered Extra
1487
1415
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1488
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1416
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort
1490
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1418
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1491
1419
select * from t3 where NULL >= some (select b from t2);
1493
1421
explain extended select * from t3 where NULL >= some (select b from t2);
1494
1422
id select_type table type possible_keys key key_len ref rows filtered Extra
1495
1423
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1496
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1424
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00
1498
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1426
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1499
1427
select * from t3 where NULL >= some (select b from t2 group by 1);
1501
1429
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
1502
1430
id select_type table type possible_keys key key_len ref rows filtered Extra
1503
1431
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1504
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1432
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort
1506
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where 0
1434
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
1507
1435
insert into t2 values (2,2), (2,1), (3,3), (3,1);
1508
1436
select * from t3 where a > all (select max(b) from t2 group by a);
1514
1442
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
1515
1443
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
1517
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" <= <max>(select max("test"."t2"."b") AS "max(b)" from "test"."t2" group by "test"."t2"."a")))
1445
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))
1518
1446
drop table t2, t3;
1519
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
1447
CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
1520
1448
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
1521
CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
1449
CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;
1522
1450
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
1523
CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
1451
CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;
1524
1452
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
1525
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1453
CREATE TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;
1526
1454
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1527
1455
select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
1528
1456
dbid name (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')
1598
1526
CREATE TABLE `t1` (
1599
`id` int(11) NOT NULL auto_increment,
1600
`id_cns` tinyint(3) unsigned NOT NULL default '0',
1527
`id` int NOT NULL auto_increment,
1528
`id_cns` int NOT NULL default '0',
1601
1529
`tipo` enum('','UNO','DUE') NOT NULL default '',
1602
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
1603
`particolare` mediumint(8) unsigned NOT NULL default '0',
1604
`generale` mediumint(8) unsigned NOT NULL default '0',
1605
`bis` tinyint(3) unsigned NOT NULL default '0',
1530
`anno_dep` int NOT NULL default '0',
1531
`particolare` bigint NOT NULL default '0',
1532
`generale` bigint NOT NULL default '0',
1533
`bis` int NOT NULL default '0',
1606
1534
PRIMARY KEY (`id`),
1607
1535
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
1608
1536
UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
1610
1538
INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);
1611
1539
CREATE TABLE `t2` (
1612
`id` tinyint(3) unsigned NOT NULL auto_increment,
1613
`max_anno_dep` smallint(6) unsigned NOT NULL default '0',
1540
`id` int NOT NULL auto_increment,
1541
`max_anno_dep` int NOT NULL default '0',
1614
1542
PRIMARY KEY (`id`)
1616
1544
INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);
1666
1594
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
1667
1595
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
1669
Note 1003 select "test"."t1"."id" AS "id","test"."t1"."text" AS "text" from "test"."t1" where (not(<in_optimizer>("test"."t1"."id",<exists>(<primary_index_lookup>(<cache>("test"."t1"."id") in t1 on PRIMARY where (("test"."t1"."id" < 8) and (<cache>("test"."t1"."id") = "test"."t1"."id")))))))
1597
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
1670
1598
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
1671
1599
id select_type table type possible_keys key key_len ref rows filtered Extra
1672
1600
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
1673
1601
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
1675
1603
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
1676
Note 1003 select "test"."tt"."id" AS "id","test"."tt"."text" AS "text" from "test"."t1" "tt" where (not(exists(select "test"."t1"."id" AS "id" from "test"."t1" where (("test"."t1"."id" < 8) and ("test"."t1"."id" = "test"."tt"."id")) having ("test"."t1"."id" is not null))))
1604
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
1677
1605
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
1678
1606
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
1679
1607
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
2230
2158
2 Rebecca 2 Spot
2232
2160
drop table t1,t2;
2233
CREATE TABLE `t1` ( `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2161
CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;
2234
2162
insert into t1 values (1);
2235
CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2163
CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;
2236
2164
insert into t2 values (1,2);
2237
2165
select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
2240
2168
drop table t1,t2;
2241
create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b));
2242
insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan');
2243
create table t2 (a int);
2244
insert into t2 values (1),(3),(2),(7);
2245
select a,b from t1 where match(b) against ('Ball') > 0;
2249
select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0);
2254
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);
2169
CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);
2255
2170
CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);
2256
2171
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');
2257
2172
INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');
2320
2235
select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;
2321
2236
(select b.x from t1 as b where b.x=a.x)
2323
CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
2238
CREATE TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));
2324
2239
INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);
2325
CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
2240
CREATE TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;
2326
2241
INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');
2327
2242
SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;
2328
2243
ERROR 42S22: Unknown column 'b.sc' in 'field list'
2372
2279
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
2373
2280
Region char(26) NOT NULL default '',
2374
2281
SurfaceArea float(10,2) NOT NULL default '0.00',
2375
IndepYear smallint(6) default NULL,
2376
Population int(11) NOT NULL default '0',
2282
IndepYear int default NULL,
2283
Population int NOT NULL default '0',
2377
2284
LifeExpectancy float(3,1) default NULL,
2378
2285
GNP float(10,2) default NULL,
2379
2286
GNPOld float(10,2) default NULL,
2380
2287
LocalName char(45) NOT NULL default '',
2381
2288
GovernmentForm char(45) NOT NULL default '',
2382
2289
HeadOfState char(60) default NULL,
2383
Capital int(11) default NULL,
2290
Capital int default NULL,
2384
2291
Code2 char(2) NOT NULL default ''
2385
2292
) ENGINE=MyISAM;
2386
2293
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
2658
2541
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2659
2542
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2661
Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two",<in_optimizer>(("test"."t1"."one","test"."t1"."two"),<exists>(select "test"."t2"."one" AS "one","test"."t2"."two" AS "two" from "test"."t2" where (("test"."t2"."flag" = '0') and trigcond(((<cache>("test"."t1"."one") = "test"."t2"."one") or isnull("test"."t2"."one"))) and trigcond(((<cache>("test"."t1"."two") = "test"."t2"."two") or isnull("test"."t2"."two")))) having (trigcond(<is_not_null_test>("test"."t2"."one")) and trigcond(<is_not_null_test>("test"."t2"."two"))))) AS "test" from "test"."t1"
2544
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2662
2545
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2663
2546
id select_type table type possible_keys key key_len ref rows filtered Extra
2664
2547
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2665
2548
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2667
Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two" from "test"."t1" semi join ("test"."t2") where (("test"."t2"."two" = "test"."t1"."two") and ("test"."t2"."one" = "test"."t1"."one") and ("test"."t2"."flag" = 'N'))
2550
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
2668
2551
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2669
2552
id select_type table type possible_keys key key_len ref rows filtered Extra
2670
2553
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2671
2554
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
2673
Note 1003 select "test"."t1"."one" AS "one","test"."t1"."two" AS "two",<in_optimizer>(("test"."t1"."one","test"."t1"."two"),<exists>(select "test"."t2"."one" AS "one","test"."t2"."two" AS "two" from "test"."t2" where ("test"."t2"."flag" = '0') group by "test"."t2"."one","test"."t2"."two" having (trigcond(((<cache>("test"."t1"."one") = "test"."t2"."one") or isnull("test"."t2"."one"))) and trigcond(((<cache>("test"."t1"."two") = "test"."t2"."two") or isnull("test"."t2"."two"))) and trigcond(<is_not_null_test>("test"."t2"."one")) and trigcond(<is_not_null_test>("test"."t2"."two"))))) AS "test" from "test"."t1"
2556
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2674
2557
DROP TABLE t1,t2;
2675
2558
CREATE TABLE t1 (a char(5), b char(5));
2676
2559
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
2781
2646
insert into t1 values (10);
2782
2647
create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
2783
2648
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
2784
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2649
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2785
2650
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2786
2651
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2787
2652
id select_type table type possible_keys key key_len ref rows Extra
2788
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2653
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
2789
2654
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2790
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
2791
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2655
2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index
2656
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2792
2657
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2793
2658
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
2796
explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2661
explain SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2797
2662
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2798
2663
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
2799
2664
id select_type table type possible_keys key key_len ref rows Extra
2800
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
2665
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
2801
2666
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
2802
2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition; Using MRR
2803
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2667
2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index
2668
SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
2804
2669
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
2805
2670
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
3396
3226
SELECT * FROM t1
3397
3227
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
3398
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
3228
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 1
3399
3229
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
3401
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
3231
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'union (select t12.i from t1 t12))
3402
3232
from t1' at line 1
3403
3233
explain select * from t1 where not exists
3404
3234
((select t11.i from t1 t11) union (select t12.i from t1 t12));
3405
3235
id select_type table type possible_keys key key_len ref rows Extra
3406
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
3407
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3408
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3236
1 PRIMARY t1 ALL NULL NULL NULL NULL 1
3237
2 SUBQUERY t11 ALL NULL NULL NULL NULL 1
3238
3 UNION t12 ALL NULL NULL NULL NULL 1
3409
3239
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
3411
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
3412
insert into t1 (a) values (FLOOR(rand() * 100));
3413
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3414
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3415
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3416
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3417
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3418
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3419
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3420
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3421
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3422
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3423
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3424
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3425
insert into t1 (a) select FLOOR(rand() * 100) from t1;
3427
(SELECT REPEAT(' ',250) FROM t1 i1
3428
WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
3429
FROM t1 ORDER BY a LIMIT 5;
3437
3241
CREATE TABLE t1 (a INT, b INT);
3438
3242
CREATE TABLE t2 (a INT);
3439
3243
INSERT INTO t2 values (1);
3924
3704
DROP TABLE t1, t2;
3925
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
3926
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
3927
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3929
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3931
CREATE INDEX I1 ON t1 (s1);
3932
CREATE INDEX I2 ON t1 (s2);
3933
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
3935
SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
3938
INSERT INTO t1 VALUES (0x41,0x41);
3939
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
3942
CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
3943
CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
3944
CREATE TABLE t3 (a3 BINARY(2) default '0');
3945
INSERT INTO t1 VALUES (1),(2),(3),(4);
3946
INSERT INTO t2 VALUES (1),(2),(3);
3947
INSERT INTO t3 VALUES (1),(2),(3);
3948
SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
3953
SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
3954
a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
3959
DROP TABLE t1,t2,t3;
3960
CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
3961
CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
3962
CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
3963
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
3964
INSERT INTO t2 VALUES (2), (3), (4), (5);
3965
INSERT INTO t3 VALUES (10), (20), (30);
3966
SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
3971
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
3973
DROP TABLE t1, t2, t3;
3974
3705
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
3975
3706
INSERT INTO t1 VALUES ('a', 'aa');
3976
3707
INSERT INTO t1 VALUES ('a', 'aaa');
4372
4103
SELECT * FROM ((SELECT 1 a INTO @a)) t1a;
4373
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4104
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4374
4105
SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a;
4375
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4106
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4376
4107
SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4377
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4108
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4378
4109
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a;
4379
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4110
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)) t1a' at line 1
4380
4111
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a;
4381
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4112
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1
4382
4113
SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a;
4383
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4114
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1
4384
4115
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a;
4385
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a))) t1a' at line 1
4116
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a))) t1a' at line 1
4386
4117
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a;
4387
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1
4118
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1
4388
4119
SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a;
4389
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1
4120
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1
4390
4121
SELECT * FROM (SELECT 1 a ORDER BY a) t1a;
4475
4206
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a);
4476
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
4207
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4477
4208
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' );
4478
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4209
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4479
4210
SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' );
4480
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4211
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4481
4212
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a);
4482
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @a)' at line 1
4213
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @a)' at line 1
4483
4214
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4484
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4215
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4485
4216
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4486
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4217
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4487
4218
SELECT ( SELECT 1 INTO @v );
4488
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4219
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4489
4220
SELECT ( SELECT 1 INTO OUTFILE 'file' );
4490
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4221
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4491
4222
SELECT ( SELECT 1 INTO DUMPFILE 'file' );
4492
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4223
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4493
4224
SELECT ( SELECT 1 UNION SELECT 1 INTO @v );
4494
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
4225
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO @v )' at line 1
4495
4226
SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' );
4496
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4227
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1
4497
4228
SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' );
4498
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4229
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1
4499
4230
SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1;
4500
4231
( SELECT a FROM t1 WHERE a = 1 ) a
4521
4252
((SELECT 1 UNION SELECT 1 UNION SELECT 1))
4523
4254
SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4524
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4255
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4525
4256
SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4526
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
4257
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ') UNION SELECT 1 )' at line 1
4527
4258
SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
4530
4261
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4531
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4262
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4532
4263
SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4533
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4264
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4534
4265
SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4535
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4266
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4536
4267
SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
4537
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4268
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1
4538
4269
SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4539
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4270
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4540
4271
SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4541
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4272
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4542
4273
SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4543
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4274
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1
4544
4275
SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
4545
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4276
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'UNION SELECT 1 )' at line 1
4546
4277
SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );