1
drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
2
drop view if exists v1;
3
SET @save_optimizer_switch=@@optimizer_switch;
4
SET optimizer_switch='outer_join_with_cache=off';
5
SET optimizer_switch='semijoin_with_cache=off';
6
set optimizer_switch='subquery_cache=on';
7
create table t1 (a int, b int);
8
insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5);
9
create table t2 (c int, d int);
10
insert into t2 values (2,3),(3,4),(5,6),(4,1);
12
* Test subquery as top item in different clauses
14
#single value subquery test (SELECT list)
16
select a, (select d from t2 where b=c) from t1;
17
a (select d from t2 where b=c)
28
show status like "subquery_cache%";
32
show status like '%Handler_read%';
40
Handler_read_rnd_deleted 0
41
Handler_read_rnd_next 31
42
set optimizer_switch='subquery_cache=off';
44
select a, (select d from t2 where b=c) from t1;
45
a (select d from t2 where b=c)
56
show status like "subquery_cache%";
60
show status like '%Handler_read%';
68
Handler_read_rnd_deleted 0
69
Handler_read_rnd_next 61
70
set optimizer_switch='subquery_cache=on';
71
#single value subquery test (where)
73
select a from t1 where (select d from t2 where b=c);
83
show status like "subquery_cache%";
87
show status like '%Handler_read%';
95
Handler_read_rnd_deleted 0
96
Handler_read_rnd_next 31
97
set optimizer_switch='subquery_cache=off';
99
select a from t1 where (select d from t2 where b=c);
109
show status like "subquery_cache%";
112
Subquery_cache_miss 0
113
show status like '%Handler_read%';
121
Handler_read_rnd_deleted 0
122
Handler_read_rnd_next 61
123
set optimizer_switch='subquery_cache=on';
124
#single value subquery test (having)
126
select a from t1 where a > 0 having (select d from t2 where b=c);
136
show status like "subquery_cache%";
139
Subquery_cache_miss 4
140
show status like '%Handler_read%';
148
Handler_read_rnd_deleted 0
149
Handler_read_rnd_next 31
150
set optimizer_switch='subquery_cache=off';
152
select a from t1 where a > 0 having (select d from t2 where b=c);
162
show status like "subquery_cache%";
165
Subquery_cache_miss 0
166
show status like '%Handler_read%';
174
Handler_read_rnd_deleted 0
175
Handler_read_rnd_next 61
176
set optimizer_switch='subquery_cache=on';
177
#single value subquery test (OUTER JOIN ON)
179
select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c);
261
show status like "subquery_cache%";
264
Subquery_cache_miss 4
265
show status like '%Handler_read%';
273
Handler_read_rnd_deleted 0
274
Handler_read_rnd_next 442
275
set optimizer_switch='subquery_cache=off';
277
select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c);
359
show status like "subquery_cache%";
362
Subquery_cache_miss 0
363
show status like '%Handler_read%';
371
Handler_read_rnd_deleted 0
372
Handler_read_rnd_next 472
373
set optimizer_switch='subquery_cache=on';
374
#single value subquery test (GROUP BY)
376
select max(a) from t1 GROUP BY (select d from t2 where b=c);
382
show status like "subquery_cache%";
384
Subquery_cache_hit 10
385
Subquery_cache_miss 4
386
show status like '%Handler_read%';
394
Handler_read_rnd_deleted 0
395
Handler_read_rnd_next 36
396
set optimizer_switch='subquery_cache=off';
398
select max(a) from t1 GROUP BY (select d from t2 where b=c);
404
show status like "subquery_cache%";
407
Subquery_cache_miss 0
408
show status like '%Handler_read%';
416
Handler_read_rnd_deleted 0
417
Handler_read_rnd_next 86
418
set optimizer_switch='subquery_cache=on';
419
#single value subquery test (distinct GROUP BY)
421
select distinct max(a) from t1 GROUP BY (select d from t2 where b=c);
427
show status like "subquery_cache%";
429
Subquery_cache_hit 10
430
Subquery_cache_miss 4
431
show status like '%Handler_read%';
439
Handler_read_rnd_deleted 0
440
Handler_read_rnd_next 41
441
set optimizer_switch='subquery_cache=off';
443
select distinct max(a) from t1 GROUP BY (select d from t2 where b=c);
449
show status like "subquery_cache%";
452
Subquery_cache_miss 0
453
show status like '%Handler_read%';
461
Handler_read_rnd_deleted 0
462
Handler_read_rnd_next 91
463
set optimizer_switch='subquery_cache=on';
464
#single value subquery test (ORDER BY)
466
select a from t1 ORDER BY (select d from t2 where b=c);
478
show status like "subquery_cache%";
481
Subquery_cache_miss 4
482
show status like '%Handler_read%';
490
Handler_read_rnd_deleted 0
491
Handler_read_rnd_next 31
492
set optimizer_switch='subquery_cache=off';
494
select a from t1 ORDER BY (select d from t2 where b=c);
506
show status like "subquery_cache%";
509
Subquery_cache_miss 0
510
show status like '%Handler_read%';
518
Handler_read_rnd_deleted 0
519
Handler_read_rnd_next 61
520
set optimizer_switch='subquery_cache=on';
521
#single value subquery test (distinct ORDER BY)
523
select distinct a from t1 ORDER BY (select d from t2 where b=c);
529
show status like "subquery_cache%";
532
Subquery_cache_miss 4
533
show status like '%Handler_read%';
541
Handler_read_rnd_deleted 1
542
Handler_read_rnd_next 36
543
set optimizer_switch='subquery_cache=off';
545
select distinct a from t1 ORDER BY (select d from t2 where b=c);
551
show status like "subquery_cache%";
554
Subquery_cache_miss 0
555
show status like '%Handler_read%';
563
Handler_read_rnd_deleted 1
564
Handler_read_rnd_next 66
565
set optimizer_switch='subquery_cache=on';
566
#single value subquery test (LEFT JOIN ON)
568
select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c);
650
show status like "subquery_cache%";
652
Subquery_cache_hit 106
653
Subquery_cache_miss 4
654
show status like '%Handler_read%';
662
Handler_read_rnd_deleted 0
663
Handler_read_rnd_next 141
664
set optimizer_switch='subquery_cache=off';
666
select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c);
748
show status like "subquery_cache%";
751
Subquery_cache_miss 0
752
show status like '%Handler_read%';
760
Handler_read_rnd_deleted 0
761
Handler_read_rnd_next 671
762
set optimizer_switch='subquery_cache=on';
763
#single value subquery test (PS)
764
prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1';
766
a (select d from t2 where b=c) + 1
777
show status like "subquery_cache%";
780
Subquery_cache_miss 4
782
a (select d from t2 where b=c) + 1
793
show status like "subquery_cache%";
795
Subquery_cache_hit 12
796
Subquery_cache_miss 8
797
deallocate prepare stmt1;
798
#single value subquery test (SP)
799
CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1;
801
a (select d from t2 where b=c) + 1
813
a (select d from t2 where b=c) + 1
827
show status like "subquery_cache%";
830
Subquery_cache_miss 0
831
select a, b , b in (select d from t2) as SUBS from t1;
843
show status like "subquery_cache%";
846
Subquery_cache_miss 4
847
insert into t1 values (7,8),(9,NULL);
848
select a, b , b in (select d from t2) as SUBS from t1;
862
show status like "subquery_cache%";
864
Subquery_cache_hit 12
865
Subquery_cache_miss 10
866
insert into t2 values (8,NULL);
867
select a, b , b in (select d from t2) as SUBS from t1;
881
show status like "subquery_cache%";
883
Subquery_cache_hit 18
884
Subquery_cache_miss 16
885
# multicolumn NOT IN with NULLs
887
set optimizer_switch='subquery_cache=off';
888
select a, b, (b, a) not in (select d, c from t2) as SUBS from t1;
902
show status like "subquery_cache%";
905
Subquery_cache_miss 0
906
set optimizer_switch='subquery_cache=on';
907
select a, b, (b, a) not in (select d, c from t2) as SUBS from t1;
921
show status like "subquery_cache%";
924
Subquery_cache_miss 6
925
# multicolumn NOT IN with NULLs (other order)
927
set optimizer_switch='subquery_cache=off';
928
select a, b, (a, b) not in (select d, c from t2) as SUBS from t1;
942
show status like "subquery_cache%";
945
Subquery_cache_miss 0
946
set optimizer_switch='subquery_cache=on';
947
select a, b, (a, b) not in (select d, c from t2) as SUBS from t1;
961
show status like "subquery_cache%";
964
Subquery_cache_miss 6
965
# multicolumn IN with NULLs
967
set optimizer_switch='subquery_cache=off';
968
select a, b, (b, a) in (select d, c from t2) as SUBS from t1;
982
show status like "subquery_cache%";
985
Subquery_cache_miss 0
986
set optimizer_switch='subquery_cache=on';
987
select a, b, (b, a) in (select d, c from t2) as SUBS from t1;
1001
show status like "subquery_cache%";
1003
Subquery_cache_hit 6
1004
Subquery_cache_miss 6
1005
# multicolumn IN with NULLs (other order)
1007
set optimizer_switch='subquery_cache=off';
1008
select a, b, (a, b) in (select d, c from t2) as SUBS from t1;
1022
show status like "subquery_cache%";
1024
Subquery_cache_hit 0
1025
Subquery_cache_miss 0
1026
set optimizer_switch='subquery_cache=on';
1027
select a, b, (a, b) in (select d, c from t2) as SUBS from t1;
1041
show status like "subquery_cache%";
1043
Subquery_cache_hit 6
1044
Subquery_cache_miss 6
1045
#IN subquery test (PS)
1046
delete from t1 where a > 6;
1047
delete from t2 where c > 6;
1048
prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1';
1061
show status like "subquery_cache%";
1063
Subquery_cache_hit 12
1064
Subquery_cache_miss 10
1077
show status like "subquery_cache%";
1079
Subquery_cache_hit 18
1080
Subquery_cache_miss 14
1081
insert into t1 values (7,8),(9,NULL);
1096
show status like "subquery_cache%";
1098
Subquery_cache_hit 24
1099
Subquery_cache_miss 20
1114
show status like "subquery_cache%";
1116
Subquery_cache_hit 30
1117
Subquery_cache_miss 26
1118
insert into t2 values (8,NULL);
1133
show status like "subquery_cache%";
1135
Subquery_cache_hit 36
1136
Subquery_cache_miss 32
1151
show status like "subquery_cache%";
1153
Subquery_cache_hit 42
1154
Subquery_cache_miss 38
1155
deallocate prepare stmt1;
1156
#IN subquery test (SP)
1157
delete from t1 where a > 6;
1158
delete from t2 where c > 6;
1159
CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1;
1172
show status like "subquery_cache%";
1174
Subquery_cache_hit 48
1175
Subquery_cache_miss 42
1188
show status like "subquery_cache%";
1190
Subquery_cache_hit 54
1191
Subquery_cache_miss 46
1192
insert into t1 values (7,8),(9,NULL);
1207
show status like "subquery_cache%";
1209
Subquery_cache_hit 60
1210
Subquery_cache_miss 52
1225
show status like "subquery_cache%";
1227
Subquery_cache_hit 66
1228
Subquery_cache_miss 58
1229
insert into t2 values (8,NULL);
1244
show status like "subquery_cache%";
1246
Subquery_cache_hit 72
1247
Subquery_cache_miss 64
1262
show status like "subquery_cache%";
1264
Subquery_cache_hit 78
1265
Subquery_cache_miss 70
1267
# test of simple exists
1268
select a, b , exists (select * from t2 where b=d) as SUBS from t1;
1282
# test of prepared statement exists
1283
show status like "subquery_cache%";
1285
Subquery_cache_hit 84
1286
Subquery_cache_miss 76
1287
prepare stmt1 from 'select a, b , exists (select * from t2 where b=d) as SUBS from t1';
1302
show status like "subquery_cache%";
1304
Subquery_cache_hit 90
1305
Subquery_cache_miss 82
1320
show status like "subquery_cache%";
1322
Subquery_cache_hit 96
1323
Subquery_cache_miss 88
1324
deallocate prepare stmt1;
1325
# test of stored procedure exists
1326
CREATE PROCEDURE p1() select a, b , exists (select * from t2 where b=d) as SUBS from t1;
1357
set optimizer_switch='subquery_cache=off';
1359
select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1;
1360
a b SUBSE SUBSI SUBSR
1373
show status like "subquery_cache%";
1375
Subquery_cache_hit 0
1376
Subquery_cache_miss 0
1377
show status like '%Handler_read%';
1379
Handler_read_first 0
1385
Handler_read_rnd_deleted 0
1386
Handler_read_rnd_next 145
1387
set optimizer_switch='subquery_cache=on';
1389
select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1;
1390
a b SUBSE SUBSI SUBSR
1403
show status like "subquery_cache%";
1405
Subquery_cache_hit 18
1406
Subquery_cache_miss 18
1407
show status like '%Handler_read%';
1409
Handler_read_first 0
1415
Handler_read_rnd_deleted 0
1416
Handler_read_rnd_next 84
1417
#several subqueries (several levels)
1418
set optimizer_switch='subquery_cache=off';
1420
set optimizer_switch='subquery_cache=off';
1422
select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1;
1436
show status like "subquery_cache%";
1438
Subquery_cache_hit 0
1439
Subquery_cache_miss 0
1440
show status like '%Handler_read%';
1442
Handler_read_first 0
1448
Handler_read_rnd_deleted 0
1449
Handler_read_rnd_next 127
1450
set optimizer_switch='subquery_cache=on';
1452
select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1;
1466
show status like "subquery_cache%";
1468
Subquery_cache_hit 6
1469
Subquery_cache_miss 10
1470
show status like '%Handler_read%';
1472
Handler_read_first 0
1478
Handler_read_rnd_deleted 0
1479
Handler_read_rnd_next 69
1482
test different types
1484
CREATE TABLE t1 ( a int, b int);
1485
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
1486
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2);
1492
CREATE TABLE t1 ( a char(1), b char (1));
1493
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
1494
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
1500
CREATE TABLE t1 ( a decimal(3,1), b decimal(3,1));
1501
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
1502
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2);
1508
CREATE TABLE t1 ( a date, b date);
1509
INSERT INTO t1 VALUES('1000-01-01','1000-01-01'),('2000-02-01','2000-02-01'),('3000-03-03','3000-03-03');
1510
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-01');
1516
CREATE TABLE t1 ( a datetime, b datetime);
1517
INSERT INTO t1 VALUES('1000-01-01 01:01:01','1000-01-01 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('3000-03-03 03:03:03','3000-03-03 03:03:03');
1518
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02');
1524
CREATE TABLE t1 ( a time, b time);
1525
INSERT INTO t1 VALUES('01:01:01','01:01:01'),('02:02:02','02:02:02'),('03:03:03','03:03:03');
1526
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '02:02:02');
1532
CREATE TABLE t1 ( a timestamp, b timestamp);
1533
INSERT INTO t1 VALUES('2000-02-02 01:01:01','2000-02-02 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('2000-02-02 03:03:03','2000-02-02 03:03:03');
1534
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02');
1540
CREATE TABLE t1 ( a bit(20), b bit(20));
1541
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
1542
SELECT a+0 FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2);
1548
CREATE TABLE t1 ( a enum('1','2','3'), b enum('1','2','3'));
1549
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
1550
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
1556
CREATE TABLE t1 ( a set('1','2','3'), b set('1','2','3'));
1557
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
1558
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
1564
CREATE TABLE t1 ( a blob, b blob);
1565
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
1566
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
1572
CREATE TABLE t1 ( a geometry, b geometry);
1573
INSERT INTO t1 VALUES(POINT(1,1),POINT(1,1)),(POINT(2,2),POINT(2,2)),(POINT(3,3),POINT(3,3));
1574
SELECT astext(a) FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = POINT(2,2));
1579
#uncacheable queries test (random and side effect)
1581
CREATE TABLE t1 (a int);
1582
INSERT INTO t1 VALUES (2), (4), (1), (3);
1583
select a, a in (select a from t1) from t1 as ext;
1584
a a in (select a from t1)
1589
show status like "subquery_cache%";
1591
Subquery_cache_hit 0
1592
Subquery_cache_miss 4
1593
select a, a in (select a from t1 where -1 < rand()) from t1 as ext;
1594
a a in (select a from t1 where -1 < rand())
1599
show status like "subquery_cache%";
1601
Subquery_cache_hit 0
1602
Subquery_cache_miss 4
1603
select a, a in (select a from t1 where -1 < benchmark(a,100)) from t1 as ext;
1604
a a in (select a from t1 where -1 < benchmark(a,100))
1609
show status like "subquery_cache%";
1611
Subquery_cache_hit 0
1612
Subquery_cache_miss 4
1614
#test of sql_big_tables switch and outer table reference in subquery with grouping
1615
set option sql_big_tables=1;
1616
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
1617
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
1618
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer;
1619
(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
1627
set option sql_big_tables=0;
1628
#test of function reference to outer query
1629
set local group_concat_max_len=400;
1630
create table t2 (a int, b int);
1631
insert into t2 values (1,1), (2,2);
1632
select b x, (select group_concat(x) from t2) from t2;
1633
x (select group_concat(x) from t2)
1637
set local group_concat_max_len=default;
1638
#aggregate functions
1639
CREATE TABLE t1 (a int, b INT);
1640
CREATE TABLE t2 (c int, d INT);
1641
insert into t1 values (2,1), (3,1), (2,4), (3,4), (10,2), (20,2), (2,5),
1642
(3,5), (100,3), (200,3), (10,6), (20,6), (20,7), (100,8), (200,8);
1643
insert into t2 values (1,1),(3,3),(20,20);
1644
aggregate function as parameter of subquery
1645
set optimizer_switch='subquery_cache=off';
1647
select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b;
1648
max(a) (select max(a) from t2 where max(a)=c)
1657
show status like "subquery_cache%";
1659
Subquery_cache_hit 0
1660
Subquery_cache_miss 0
1661
show status like '%Handler_read%';
1663
Handler_read_first 0
1669
Handler_read_rnd_deleted 0
1670
Handler_read_rnd_next 57
1671
set optimizer_switch='subquery_cache=on';
1673
select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b;
1674
max(a) (select max(a) from t2 where max(a)=c)
1683
show status like "subquery_cache%";
1685
Subquery_cache_hit 5
1686
Subquery_cache_miss 3
1687
show status like '%Handler_read%';
1689
Handler_read_first 0
1695
Handler_read_rnd_deleted 0
1696
Handler_read_rnd_next 37
1697
argument of aggregate function as parameter of subquery (illegal use)
1698
set optimizer_switch='subquery_cache=off';
1700
select max(a), (select a from t2 where a=c) from t1 group by b;
1701
max(a) (select a from t2 where a=c)
1710
show status like "subquery_cache%";
1712
Subquery_cache_hit 0
1713
Subquery_cache_miss 0
1714
show status like '%Handler_read%';
1716
Handler_read_first 0
1722
Handler_read_rnd_deleted 0
1723
Handler_read_rnd_next 57
1724
set optimizer_switch='subquery_cache=on';
1726
select max(a), (select a from t2 where a=c) from t1 group by b;
1727
max(a) (select a from t2 where a=c)
1736
show status like "subquery_cache%";
1738
Subquery_cache_hit 4
1739
Subquery_cache_miss 4
1740
show status like '%Handler_read%';
1742
Handler_read_first 0
1748
Handler_read_rnd_deleted 0
1749
Handler_read_rnd_next 41
1751
#test of flattening subquery optimisations and cache
1752
create table t0 (a int);
1753
insert into t0 values (9),(8),(7),(6),(5),(4),(3),(2),(1),(0);
1754
create table t1(a int, b int);
1755
insert into t1 values
1756
(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2);
1757
create table t2 (pk int, a int, primary key(pk));
1758
insert into t2 select a,a from t0;
1759
set optimizer_switch='default,semijoin=on,materialization=on,subquery_cache=on';
1761
select * from t1 where a in (select pk from t2);
1778
show status like "subquery_cache%";
1780
Subquery_cache_hit 0
1781
Subquery_cache_miss 0
1782
show status like '%Handler_read%';
1784
Handler_read_first 0
1790
Handler_read_rnd_deleted 0
1791
Handler_read_rnd_next 16
1792
alter table t2 drop primary key;
1793
set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=off';
1794
explain select * from t1 where a in (select pk from t2);
1795
id select_type table type possible_keys key key_len ref rows Extra
1796
1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where
1797
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
1799
select * from t1 where a in (select pk from t2);
1816
show status like "subquery_cache%";
1818
Subquery_cache_hit 0
1819
Subquery_cache_miss 0
1820
show status like '%Handler_read%';
1822
Handler_read_first 0
1828
Handler_read_rnd_deleted 0
1829
Handler_read_rnd_next 151
1830
set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on';
1831
explain select * from t1 where a in (select pk from t2);
1832
id select_type table type possible_keys key key_len ref rows Extra
1833
1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where
1834
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
1836
select * from t1 where a in (select pk from t2);
1853
show status like "subquery_cache%";
1855
Subquery_cache_hit 12
1856
Subquery_cache_miss 3
1857
show status like '%Handler_read%';
1859
Handler_read_first 0
1865
Handler_read_rnd_deleted 0
1866
Handler_read_rnd_next 43
1867
set optimizer_switch='default,semijoin=off,materialization=on,subquery_cache=on';
1868
explain select * from t1 where a in (select pk from t2);
1869
id select_type table type possible_keys key key_len ref rows Extra
1870
1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where
1871
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10
1873
select * from t1 where a in (select pk from t2);
1890
show status like "subquery_cache%";
1892
Subquery_cache_hit 12
1893
Subquery_cache_miss 3
1894
show status like '%Handler_read%';
1896
Handler_read_first 0
1902
Handler_read_rnd_deleted 0
1903
Handler_read_rnd_next 27
1904
drop table t0,t1,t2;
1905
set optimizer_switch='default';
1906
#launchpad BUG#608834
1908
`pk` int(11) NOT NULL AUTO_INCREMENT,
1909
`col_int_nokey` int(11) DEFAULT NULL,
1910
`col_int_key` int(11) DEFAULT NULL,
1911
`col_time_key` time DEFAULT NULL,
1912
`col_varchar_key` varchar(1) DEFAULT NULL,
1913
`col_varchar_nokey` varchar(1) DEFAULT NULL,
1915
KEY `col_int_key` (`col_int_key`),
1916
KEY `col_time_key` (`col_time_key`),
1917
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
1918
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
1919
INSERT INTO `t2` VALUES (10,7,8,'01:27:35','v','v');
1920
INSERT INTO `t2` VALUES (11,1,9,'19:48:31','r','r');
1921
INSERT INTO `t2` VALUES (12,5,9,'00:00:00','a','a');
1922
INSERT INTO `t2` VALUES (13,3,186,'19:53:05','m','m');
1923
INSERT INTO `t2` VALUES (14,6,NULL,'19:18:56','y','y');
1924
INSERT INTO `t2` VALUES (15,92,2,'10:55:12','j','j');
1925
INSERT INTO `t2` VALUES (16,7,3,'00:25:00','d','d');
1926
INSERT INTO `t2` VALUES (17,NULL,0,'12:35:47','z','z');
1927
INSERT INTO `t2` VALUES (18,3,133,'19:53:03','e','e');
1928
INSERT INTO `t2` VALUES (19,5,1,'17:53:30','h','h');
1929
INSERT INTO `t2` VALUES (20,1,8,'11:35:49','b','b');
1930
INSERT INTO `t2` VALUES (21,2,5,NULL,'s','s');
1931
INSERT INTO `t2` VALUES (22,NULL,5,'06:01:40','e','e');
1932
INSERT INTO `t2` VALUES (23,1,8,'05:45:11','j','j');
1933
INSERT INTO `t2` VALUES (24,0,6,'00:00:00','e','e');
1934
INSERT INTO `t2` VALUES (25,210,51,'00:00:00','f','f');
1935
INSERT INTO `t2` VALUES (26,8,4,'06:11:01','v','v');
1936
INSERT INTO `t2` VALUES (27,7,7,'13:02:46','x','x');
1937
INSERT INTO `t2` VALUES (28,5,6,'21:44:25','m','m');
1938
INSERT INTO `t2` VALUES (29,NULL,4,'22:43:58','c','c');
1940
`pk` int(11) NOT NULL AUTO_INCREMENT,
1941
`col_int_nokey` int(11) DEFAULT NULL,
1942
`col_int_key` int(11) DEFAULT NULL,
1943
`col_time_key` time DEFAULT NULL,
1944
`col_varchar_key` varchar(1) DEFAULT NULL,
1945
`col_varchar_nokey` varchar(1) DEFAULT NULL,
1947
KEY `col_int_key` (`col_int_key`),
1948
KEY `col_time_key` (`col_time_key`),
1949
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
1950
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
1951
INSERT INTO `t1` VALUES (1,NULL,2,'11:28:45','w','w');
1952
INSERT INTO `t1` VALUES (2,7,9,'20:25:14','m','m');
1953
INSERT INTO `t1` VALUES (3,9,3,'13:47:24','m','m');
1954
INSERT INTO `t1` VALUES (4,7,9,'19:24:11','k','k');
1955
INSERT INTO `t1` VALUES (5,4,NULL,'15:59:13','r','r');
1956
INSERT INTO `t1` VALUES (6,2,9,'00:00:00','t','t');
1957
INSERT INTO `t1` VALUES (7,6,3,'15:15:04','j','j');
1958
INSERT INTO `t1` VALUES (8,8,8,'11:32:06','u','u');
1959
INSERT INTO `t1` VALUES (9,NULL,8,'18:32:33','h','h');
1960
INSERT INTO `t1` VALUES (10,5,53,'15:19:25','o','o');
1961
INSERT INTO `t1` VALUES (11,NULL,0,'19:03:19',NULL,NULL);
1962
INSERT INTO `t1` VALUES (12,6,5,'00:39:46','k','k');
1963
INSERT INTO `t1` VALUES (13,188,166,NULL,'e','e');
1964
INSERT INTO `t1` VALUES (14,2,3,'00:00:00','n','n');
1965
INSERT INTO `t1` VALUES (15,1,0,'13:12:11','t','t');
1966
INSERT INTO `t1` VALUES (16,1,1,'04:56:48','c','c');
1967
INSERT INTO `t1` VALUES (17,0,9,'19:56:05','m','m');
1968
INSERT INTO `t1` VALUES (18,9,5,'19:35:19','y','y');
1969
INSERT INTO `t1` VALUES (19,NULL,6,'05:03:03','f','f');
1970
INSERT INTO `t1` VALUES (20,4,2,'18:38:59','d','d');
1971
set @@optimizer_switch='subquery_cache=off';
1972
/* cache is off */ SELECT (
1974
FROM DUAL ) AS field1 , SUM( DISTINCT table1 . `pk` ) AS field2 , (
1975
SELECT MAX( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1
1976
FROM ( t1 AS SUBQUERY2_t1 INNER JOIN t1 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk` ) )
1977
WHERE SUBQUERY2_t2 . `col_varchar_nokey` <= table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_int_nokey` < table1 . `pk` ) AS field3 , table1 . `col_time_key` AS field4 , table1 . `col_int_key` AS field5 , CONCAT ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) AS field6
1978
FROM ( t1 AS table1 INNER JOIN ( ( t1 AS table2 LEFT JOIN t2 AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_nokey` ) )
1979
WHERE ( table2 . `col_varchar_nokey` NOT IN (
1981
SELECT 'u' ) ) OR table3 . `col_varchar_nokey` <= table1 . `col_varchar_key`
1982
GROUP BY field1, field3, field4, field5, field6
1983
ORDER BY table1 . `col_int_key` , field1, field2, field3, field4, field5, field6
1985
field1 field2 field3 field4 field5 field6
1986
4 5 9 15:59:13 NULL cr
1987
4 5 9 15:59:13 NULL dr
1988
4 5 9 15:59:13 NULL er
1989
4 5 9 15:59:13 NULL fr
1990
4 5 9 15:59:13 NULL hr
1991
4 5 9 15:59:13 NULL jr
1992
4 5 9 15:59:13 NULL mr
1993
4 5 9 15:59:13 NULL rr
1994
4 5 9 15:59:13 NULL yr
1995
4 11 9 19:03:19 0 NULL
1996
4 15 9 13:12:11 0 ct
1997
4 15 9 13:12:11 0 dt
1998
4 15 9 13:12:11 0 et
1999
4 15 9 13:12:11 0 ft
2000
4 15 9 13:12:11 0 ht
2001
4 15 9 13:12:11 0 jt
2002
4 15 9 13:12:11 0 mt
2003
4 15 9 13:12:11 0 rt
2004
4 15 9 13:12:11 0 yt
2005
4 16 9 04:56:48 1 cc
2006
4 16 9 04:56:48 1 ec
2007
4 16 9 04:56:48 1 fc
2008
4 16 9 04:56:48 1 hc
2009
4 16 9 04:56:48 1 jc
2010
4 16 9 04:56:48 1 mc
2011
4 16 9 04:56:48 1 rc
2012
4 16 9 04:56:48 1 yc
2022
4 20 9 18:38:59 2 cd
2023
4 20 9 18:38:59 2 dd
2024
4 20 9 18:38:59 2 ed
2025
4 20 9 18:38:59 2 fd
2026
4 20 9 18:38:59 2 hd
2027
4 20 9 18:38:59 2 jd
2028
4 20 9 18:38:59 2 md
2029
4 20 9 18:38:59 2 rd
2030
4 20 9 18:38:59 2 yd
2049
4 14 9 00:00:00 3 cn
2050
4 14 9 00:00:00 3 dn
2051
4 14 9 00:00:00 3 en
2052
4 14 9 00:00:00 3 fn
2053
4 14 9 00:00:00 3 hn
2054
4 14 9 00:00:00 3 jn
2055
4 14 9 00:00:00 3 mn
2056
4 14 9 00:00:00 3 rn
2057
4 14 9 00:00:00 3 yn
2058
4 12 9 00:39:46 5 ck
2059
4 12 9 00:39:46 5 dk
2060
4 12 9 00:39:46 5 ek
2061
4 12 9 00:39:46 5 fk
2062
4 12 9 00:39:46 5 hk
2063
4 12 9 00:39:46 5 jk
2064
4 12 9 00:39:46 5 mk
2065
4 12 9 00:39:46 5 rk
2066
4 12 9 00:39:46 5 yk
2067
4 18 9 19:35:19 5 cy
2068
4 18 9 19:35:19 5 dy
2069
4 18 9 19:35:19 5 ey
2070
4 18 9 19:35:19 5 fy
2071
4 18 9 19:35:19 5 hy
2072
4 18 9 19:35:19 5 jy
2073
4 18 9 19:35:19 5 my
2074
4 18 9 19:35:19 5 ry
2075
4 18 9 19:35:19 5 yy
2076
4 19 9 05:03:03 6 cf
2077
4 19 9 05:03:03 6 df
2078
4 19 9 05:03:03 6 ef
2079
4 19 9 05:03:03 6 ff
2080
4 19 9 05:03:03 6 hf
2081
4 19 9 05:03:03 6 jf
2082
4 19 9 05:03:03 6 mf
2083
4 19 9 05:03:03 6 rf
2084
4 19 9 05:03:03 6 yf
2130
4 17 9 19:56:05 9 cm
2131
4 17 9 19:56:05 9 dm
2132
4 17 9 19:56:05 9 em
2133
4 17 9 19:56:05 9 fm
2134
4 17 9 19:56:05 9 hm
2135
4 17 9 19:56:05 9 jm
2136
4 17 9 19:56:05 9 mm
2137
4 17 9 19:56:05 9 rm
2138
4 17 9 19:56:05 9 ym
2139
4 10 9 15:19:25 53 co
2140
4 10 9 15:19:25 53 do
2141
4 10 9 15:19:25 53 eo
2142
4 10 9 15:19:25 53 fo
2143
4 10 9 15:19:25 53 ho
2144
4 10 9 15:19:25 53 jo
2145
4 10 9 15:19:25 53 mo
2146
4 10 9 15:19:25 53 ro
2147
4 10 9 15:19:25 53 yo
2157
set @@optimizer_switch='subquery_cache=on';
2158
/* cache is on */ SELECT (
2160
FROM DUAL ) AS field1 , SUM( DISTINCT table1 . `pk` ) AS field2 , (
2161
SELECT MAX( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1
2162
FROM ( t1 AS SUBQUERY2_t1 INNER JOIN t1 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk` ) )
2163
WHERE SUBQUERY2_t2 . `col_varchar_nokey` <= table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_int_nokey` < table1 . `pk` ) AS field3 , table1 . `col_time_key` AS field4 , table1 . `col_int_key` AS field5 , CONCAT ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) AS field6
2164
FROM ( t1 AS table1 INNER JOIN ( ( t1 AS table2 LEFT JOIN t2 AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_nokey` ) )
2165
WHERE ( table2 . `col_varchar_nokey` NOT IN (
2167
SELECT 'u' ) ) OR table3 . `col_varchar_nokey` <= table1 . `col_varchar_key`
2168
GROUP BY field1, field3, field4, field5, field6
2169
ORDER BY table1 . `col_int_key` , field1, field2, field3, field4, field5, field6
2171
field1 field2 field3 field4 field5 field6
2172
4 5 9 15:59:13 NULL cr
2173
4 5 9 15:59:13 NULL dr
2174
4 5 9 15:59:13 NULL er
2175
4 5 9 15:59:13 NULL fr
2176
4 5 9 15:59:13 NULL hr
2177
4 5 9 15:59:13 NULL jr
2178
4 5 9 15:59:13 NULL mr
2179
4 5 9 15:59:13 NULL rr
2180
4 5 9 15:59:13 NULL yr
2181
4 11 9 19:03:19 0 NULL
2182
4 15 9 13:12:11 0 ct
2183
4 15 9 13:12:11 0 dt
2184
4 15 9 13:12:11 0 et
2185
4 15 9 13:12:11 0 ft
2186
4 15 9 13:12:11 0 ht
2187
4 15 9 13:12:11 0 jt
2188
4 15 9 13:12:11 0 mt
2189
4 15 9 13:12:11 0 rt
2190
4 15 9 13:12:11 0 yt
2191
4 16 9 04:56:48 1 cc
2192
4 16 9 04:56:48 1 ec
2193
4 16 9 04:56:48 1 fc
2194
4 16 9 04:56:48 1 hc
2195
4 16 9 04:56:48 1 jc
2196
4 16 9 04:56:48 1 mc
2197
4 16 9 04:56:48 1 rc
2198
4 16 9 04:56:48 1 yc
2208
4 20 9 18:38:59 2 cd
2209
4 20 9 18:38:59 2 dd
2210
4 20 9 18:38:59 2 ed
2211
4 20 9 18:38:59 2 fd
2212
4 20 9 18:38:59 2 hd
2213
4 20 9 18:38:59 2 jd
2214
4 20 9 18:38:59 2 md
2215
4 20 9 18:38:59 2 rd
2216
4 20 9 18:38:59 2 yd
2235
4 14 9 00:00:00 3 cn
2236
4 14 9 00:00:00 3 dn
2237
4 14 9 00:00:00 3 en
2238
4 14 9 00:00:00 3 fn
2239
4 14 9 00:00:00 3 hn
2240
4 14 9 00:00:00 3 jn
2241
4 14 9 00:00:00 3 mn
2242
4 14 9 00:00:00 3 rn
2243
4 14 9 00:00:00 3 yn
2244
4 12 9 00:39:46 5 ck
2245
4 12 9 00:39:46 5 dk
2246
4 12 9 00:39:46 5 ek
2247
4 12 9 00:39:46 5 fk
2248
4 12 9 00:39:46 5 hk
2249
4 12 9 00:39:46 5 jk
2250
4 12 9 00:39:46 5 mk
2251
4 12 9 00:39:46 5 rk
2252
4 12 9 00:39:46 5 yk
2253
4 18 9 19:35:19 5 cy
2254
4 18 9 19:35:19 5 dy
2255
4 18 9 19:35:19 5 ey
2256
4 18 9 19:35:19 5 fy
2257
4 18 9 19:35:19 5 hy
2258
4 18 9 19:35:19 5 jy
2259
4 18 9 19:35:19 5 my
2260
4 18 9 19:35:19 5 ry
2261
4 18 9 19:35:19 5 yy
2262
4 19 9 05:03:03 6 cf
2263
4 19 9 05:03:03 6 df
2264
4 19 9 05:03:03 6 ef
2265
4 19 9 05:03:03 6 ff
2266
4 19 9 05:03:03 6 hf
2267
4 19 9 05:03:03 6 jf
2268
4 19 9 05:03:03 6 mf
2269
4 19 9 05:03:03 6 rf
2270
4 19 9 05:03:03 6 yf
2316
4 17 9 19:56:05 9 cm
2317
4 17 9 19:56:05 9 dm
2318
4 17 9 19:56:05 9 em
2319
4 17 9 19:56:05 9 fm
2320
4 17 9 19:56:05 9 hm
2321
4 17 9 19:56:05 9 jm
2322
4 17 9 19:56:05 9 mm
2323
4 17 9 19:56:05 9 rm
2324
4 17 9 19:56:05 9 ym
2325
4 10 9 15:19:25 53 co
2326
4 10 9 15:19:25 53 do
2327
4 10 9 15:19:25 53 eo
2328
4 10 9 15:19:25 53 fo
2329
4 10 9 15:19:25 53 ho
2330
4 10 9 15:19:25 53 jo
2331
4 10 9 15:19:25 53 mo
2332
4 10 9 15:19:25 53 ro
2333
4 10 9 15:19:25 53 yo
2344
set @@optimizer_switch= default;
2345
#launchpad BUG#609045
2347
`pk` int(11) NOT NULL AUTO_INCREMENT,
2348
`col_int_nokey` int(11) DEFAULT NULL,
2349
`col_int_key` int(11) DEFAULT NULL,
2350
`col_date_key` date DEFAULT NULL,
2351
`col_date_nokey` date DEFAULT NULL,
2352
`col_time_key` time DEFAULT NULL,
2353
`col_time_nokey` time DEFAULT NULL,
2354
`col_datetime_key` datetime DEFAULT NULL,
2355
`col_datetime_nokey` datetime DEFAULT NULL,
2356
`col_varchar_key` varchar(1) DEFAULT NULL,
2357
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2359
KEY `col_int_key` (`col_int_key`),
2360
KEY `col_date_key` (`col_date_key`),
2361
KEY `col_time_key` (`col_time_key`),
2362
KEY `col_datetime_key` (`col_datetime_key`),
2363
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2364
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
2365
INSERT INTO `t1` VALUES (1,NULL,2,NULL,NULL,'11:28:45','11:28:45','2004-10-11 18:13:16','2004-10-11 18:13:16','w','w');
2366
INSERT INTO `t1` VALUES (2,7,9,'2001-09-19','2001-09-19','20:25:14','20:25:14',NULL,NULL,'m','m');
2367
INSERT INTO `t1` VALUES (3,9,3,'2004-09-12','2004-09-12','13:47:24','13:47:24','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m');
2368
INSERT INTO `t1` VALUES (4,7,9,NULL,NULL,'19:24:11','19:24:11','2009-07-25 00:00:00','2009-07-25 00:00:00','k','k');
2369
INSERT INTO `t1` VALUES (5,4,NULL,'2002-07-19','2002-07-19','15:59:13','15:59:13',NULL,NULL,'r','r');
2370
INSERT INTO `t1` VALUES (6,2,9,'2002-12-16','2002-12-16','00:00:00','00:00:00','2008-07-27 00:00:00','2008-07-27 00:00:00','t','t');
2371
INSERT INTO `t1` VALUES (7,6,3,'2006-02-08','2006-02-08','15:15:04','15:15:04','2002-11-13 16:37:31','2002-11-13 16:37:31','j','j');
2372
INSERT INTO `t1` VALUES (8,8,8,'2006-08-28','2006-08-28','11:32:06','11:32:06','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u');
2373
INSERT INTO `t1` VALUES (9,NULL,8,'2001-04-14','2001-04-14','18:32:33','18:32:33','2003-12-10 00:00:00','2003-12-10 00:00:00','h','h');
2374
INSERT INTO `t1` VALUES (10,5,53,'2000-01-05','2000-01-05','15:19:25','15:19:25','2001-12-21 22:38:22','2001-12-21 22:38:22','o','o');
2375
INSERT INTO `t1` VALUES (11,NULL,0,'2003-12-06','2003-12-06','19:03:19','19:03:19','2008-12-13 23:16:44','2008-12-13 23:16:44',NULL,NULL);
2376
INSERT INTO `t1` VALUES (12,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2005-08-15 12:39:41','2005-08-15 12:39:41','k','k');
2377
INSERT INTO `t1` VALUES (13,188,166,'2002-11-27','2002-11-27',NULL,NULL,NULL,NULL,'e','e');
2378
INSERT INTO `t1` VALUES (14,2,3,NULL,NULL,'00:00:00','00:00:00','2006-09-11 12:06:14','2006-09-11 12:06:14','n','n');
2379
INSERT INTO `t1` VALUES (15,1,0,'2003-05-27','2003-05-27','13:12:11','13:12:11','2007-12-15 12:39:34','2007-12-15 12:39:34','t','t');
2380
INSERT INTO `t1` VALUES (16,1,1,'2005-05-03','2005-05-03','04:56:48','04:56:48','2005-08-09 00:00:00','2005-08-09 00:00:00','c','c');
2381
INSERT INTO `t1` VALUES (17,0,9,'2001-04-18','2001-04-18','19:56:05','19:56:05','2001-09-02 22:50:02','2001-09-02 22:50:02','m','m');
2382
INSERT INTO `t1` VALUES (18,9,5,'2005-12-27','2005-12-27','19:35:19','19:35:19','2005-12-16 22:58:11','2005-12-16 22:58:11','y','y');
2383
INSERT INTO `t1` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f');
2384
INSERT INTO `t1` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d');
2386
`pk` int(11) NOT NULL AUTO_INCREMENT,
2387
`col_int_nokey` int(11) DEFAULT NULL,
2388
`col_int_key` int(11) DEFAULT NULL,
2389
`col_date_key` date DEFAULT NULL,
2390
`col_date_nokey` date DEFAULT NULL,
2391
`col_time_key` time DEFAULT NULL,
2392
`col_time_nokey` time DEFAULT NULL,
2393
`col_datetime_key` datetime DEFAULT NULL,
2394
`col_datetime_nokey` datetime DEFAULT NULL,
2395
`col_varchar_key` varchar(1) DEFAULT NULL,
2396
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2398
KEY `col_int_key` (`col_int_key`),
2399
KEY `col_date_key` (`col_date_key`),
2400
KEY `col_time_key` (`col_time_key`),
2401
KEY `col_datetime_key` (`col_datetime_key`),
2402
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2404
INSERT INTO `t2` VALUES (10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v');
2405
INSERT INTO `t2` VALUES (11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r');
2406
INSERT INTO `t2` VALUES (12,5,9,'2002-09-12','2002-09-12','00:00:00','00:00:00','2006-12-03 09:37:26','2006-12-03 09:37:26','a','a');
2407
INSERT INTO `t2` VALUES (13,3,186,'2005-02-15','2005-02-15','19:53:05','19:53:05','2008-05-26 12:27:10','2008-05-26 12:27:10','m','m');
2408
INSERT INTO `t2` VALUES (14,6,NULL,NULL,NULL,'19:18:56','19:18:56','2004-12-14 16:37:30','2004-12-14 16:37:30','y','y');
2409
INSERT INTO `t2` VALUES (15,92,2,'2008-11-04','2008-11-04','10:55:12','10:55:12','2003-02-11 21:19:41','2003-02-11 21:19:41','j','j');
2410
INSERT INTO `t2` VALUES (16,7,3,'2004-09-04','2004-09-04','00:25:00','00:25:00','2009-10-18 02:27:49','2009-10-18 02:27:49','d','d');
2411
INSERT INTO `t2` VALUES (17,NULL,0,'2006-06-05','2006-06-05','12:35:47','12:35:47','2000-09-26 07:45:57','2000-09-26 07:45:57','z','z');
2412
INSERT INTO `t2` VALUES (18,3,133,'1900-01-01','1900-01-01','19:53:03','19:53:03',NULL,NULL,'e','e');
2413
INSERT INTO `t2` VALUES (19,5,1,'1900-01-01','1900-01-01','17:53:30','17:53:30','2005-11-10 12:40:29','2005-11-10 12:40:29','h','h');
2414
INSERT INTO `t2` VALUES (20,1,8,'1900-01-01','1900-01-01','11:35:49','11:35:49','2009-04-25 00:00:00','2009-04-25 00:00:00','b','b');
2415
INSERT INTO `t2` VALUES (21,2,5,'2005-01-13','2005-01-13',NULL,NULL,'2002-11-27 00:00:00','2002-11-27 00:00:00','s','s');
2416
INSERT INTO `t2` VALUES (22,NULL,5,'2006-05-21','2006-05-21','06:01:40','06:01:40','2004-01-26 20:32:32','2004-01-26 20:32:32','e','e');
2417
INSERT INTO `t2` VALUES (23,1,8,'2003-09-08','2003-09-08','05:45:11','05:45:11','2007-10-26 11:41:40','2007-10-26 11:41:40','j','j');
2418
INSERT INTO `t2` VALUES (24,0,6,'2006-12-23','2006-12-23','00:00:00','00:00:00','2005-10-07 00:00:00','2005-10-07 00:00:00','e','e');
2419
INSERT INTO `t2` VALUES (25,210,51,'2006-10-15','2006-10-15','00:00:00','00:00:00','2000-07-15 05:00:34','2000-07-15 05:00:34','f','f');
2420
INSERT INTO `t2` VALUES (26,8,4,'2005-04-06','2005-04-06','06:11:01','06:11:01','2000-04-03 16:33:32','2000-04-03 16:33:32','v','v');
2421
INSERT INTO `t2` VALUES (27,7,7,'2008-04-07','2008-04-07','13:02:46','13:02:46',NULL,NULL,'x','x');
2422
INSERT INTO `t2` VALUES (28,5,6,'2006-10-10','2006-10-10','21:44:25','21:44:25','2001-04-25 01:26:12','2001-04-25 01:26:12','m','m');
2423
INSERT INTO `t2` VALUES (29,NULL,4,'1900-01-01','1900-01-01','22:43:58','22:43:58','2000-12-27 00:00:00','2000-12-27 00:00:00','c','c');
2425
`pk` int(11) NOT NULL AUTO_INCREMENT,
2426
`col_int_nokey` int(11) DEFAULT NULL,
2427
`col_int_key` int(11) DEFAULT NULL,
2428
`col_date_key` date DEFAULT NULL,
2429
`col_date_nokey` date DEFAULT NULL,
2430
`col_time_key` time DEFAULT NULL,
2431
`col_time_nokey` time DEFAULT NULL,
2432
`col_datetime_key` datetime DEFAULT NULL,
2433
`col_datetime_nokey` datetime DEFAULT NULL,
2434
`col_varchar_key` varchar(1) DEFAULT NULL,
2435
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2437
KEY `col_int_key` (`col_int_key`),
2438
KEY `col_date_key` (`col_date_key`),
2439
KEY `col_time_key` (`col_time_key`),
2440
KEY `col_datetime_key` (`col_datetime_key`),
2441
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2443
INSERT INTO `t3` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','f','f');
2445
`pk` int(11) NOT NULL AUTO_INCREMENT,
2446
`col_int_nokey` int(11) DEFAULT NULL,
2447
`col_int_key` int(11) DEFAULT NULL,
2448
`col_date_key` date DEFAULT NULL,
2449
`col_date_nokey` date DEFAULT NULL,
2450
`col_time_key` time DEFAULT NULL,
2451
`col_time_nokey` time DEFAULT NULL,
2452
`col_datetime_key` datetime DEFAULT NULL,
2453
`col_datetime_nokey` datetime DEFAULT NULL,
2454
`col_varchar_key` varchar(1) DEFAULT NULL,
2455
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2457
KEY `col_int_key` (`col_int_key`),
2458
KEY `col_date_key` (`col_date_key`),
2459
KEY `col_time_key` (`col_time_key`),
2460
KEY `col_datetime_key` (`col_datetime_key`),
2461
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2463
INSERT INTO `t4` VALUES (1,6,NULL,'2003-05-12','2003-05-12',NULL,NULL,'2000-09-12 00:00:00','2000-09-12 00:00:00','r','r');
2464
INSERT INTO `t4` VALUES (2,8,0,'2003-01-07','2003-01-07','14:34:45','14:34:45','2004-08-10 09:09:31','2004-08-10 09:09:31','c','c');
2465
INSERT INTO `t4` VALUES (3,6,0,NULL,NULL,'11:49:48','11:49:48','2005-03-21 04:31:40','2005-03-21 04:31:40','o','o');
2466
INSERT INTO `t4` VALUES (4,6,7,'2005-03-12','2005-03-12','18:12:55','18:12:55','2002-10-25 23:50:35','2002-10-25 23:50:35','c','c');
2467
INSERT INTO `t4` VALUES (5,3,8,'2000-08-02','2000-08-02','18:30:05','18:30:05','2001-04-01 21:14:04','2001-04-01 21:14:04','d','d');
2468
INSERT INTO `t4` VALUES (6,9,4,'1900-01-01','1900-01-01','14:19:30','14:19:30','2005-03-12 06:02:34','2005-03-12 06:02:34','v','v');
2469
INSERT INTO `t4` VALUES (7,2,6,'2006-07-06','2006-07-06','05:20:04','05:20:04','2001-05-06 14:49:12','2001-05-06 14:49:12','m','m');
2470
INSERT INTO `t4` VALUES (8,1,5,'2006-12-24','2006-12-24','20:29:31','20:29:31','2004-04-25 00:00:00','2004-04-25 00:00:00','j','j');
2471
INSERT INTO `t4` VALUES (9,8,NULL,'2004-11-16','2004-11-16','07:08:09','07:08:09','2001-03-22 18:38:43','2001-03-22 18:38:43','f','f');
2472
INSERT INTO `t4` VALUES (10,0,NULL,'2002-09-09','2002-09-09','14:49:14','14:49:14','2006-04-25 21:03:02','2006-04-25 21:03:02','n','n');
2473
INSERT INTO `t4` VALUES (11,9,8,NULL,NULL,'00:00:00','00:00:00','2009-09-07 18:40:43','2009-09-07 18:40:43','z','z');
2474
INSERT INTO `t4` VALUES (12,8,8,'2008-06-24','2008-06-24','09:58:06','09:58:06','2004-03-23 00:00:00','2004-03-23 00:00:00','h','h');
2475
INSERT INTO `t4` VALUES (13,NULL,8,'2001-04-21','2001-04-21',NULL,NULL,'2009-04-15 00:08:29','2009-04-15 00:08:29','q','q');
2476
INSERT INTO `t4` VALUES (14,0,1,'2003-11-22','2003-11-22','18:24:16','18:24:16','2000-04-21 00:00:00','2000-04-21 00:00:00','w','w');
2477
INSERT INTO `t4` VALUES (15,5,1,'2004-09-12','2004-09-12','17:39:57','17:39:57','2000-02-17 19:41:23','2000-02-17 19:41:23','z','z');
2478
INSERT INTO `t4` VALUES (16,1,5,'2006-06-20','2006-06-20','08:23:21','08:23:21','2003-09-20 07:38:14','2003-09-20 07:38:14','j','j');
2479
INSERT INTO `t4` VALUES (17,1,2,NULL,NULL,NULL,NULL,'2000-11-28 20:42:12','2000-11-28 20:42:12','a','a');
2480
INSERT INTO `t4` VALUES (18,6,7,'2001-11-25','2001-11-25','21:50:46','21:50:46','2005-06-12 11:13:17','2005-06-12 11:13:17','m','m');
2481
INSERT INTO `t4` VALUES (19,6,6,'2004-10-26','2004-10-26','12:33:17','12:33:17','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n');
2482
INSERT INTO `t4` VALUES (20,1,4,'2005-01-19','2005-01-19','03:06:43','03:06:43','2006-02-09 20:41:06','2006-02-09 20:41:06','e','e');
2483
INSERT INTO `t4` VALUES (21,8,7,'2008-07-06','2008-07-06','03:46:14','03:46:14','2004-05-22 01:05:57','2004-05-22 01:05:57','u','u');
2484
INSERT INTO `t4` VALUES (22,1,0,'1900-01-01','1900-01-01','20:34:52','20:34:52','2004-03-04 13:46:31','2004-03-04 13:46:31','s','s');
2485
INSERT INTO `t4` VALUES (23,0,9,'1900-01-01','1900-01-01',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','u','u');
2486
INSERT INTO `t4` VALUES (24,4,3,'2004-06-08','2004-06-08','10:41:20','10:41:20','2004-10-20 07:20:19','2004-10-20 07:20:19','r','r');
2487
INSERT INTO `t4` VALUES (25,9,5,'2007-02-20','2007-02-20','08:43:11','08:43:11','2006-04-17 00:00:00','2006-04-17 00:00:00','g','g');
2488
INSERT INTO `t4` VALUES (26,8,1,'2008-06-18','2008-06-18',NULL,NULL,'2000-10-27 00:00:00','2000-10-27 00:00:00','o','o');
2489
INSERT INTO `t4` VALUES (27,5,1,'2008-05-15','2008-05-15','10:17:51','10:17:51','2007-04-14 08:54:06','2007-04-14 08:54:06','w','w');
2490
INSERT INTO `t4` VALUES (28,9,5,'2005-10-06','2005-10-06','06:34:09','06:34:09','2008-04-12 17:03:52','2008-04-12 17:03:52','b','b');
2491
INSERT INTO `t4` VALUES (29,5,9,NULL,NULL,'21:22:47','21:22:47','2007-02-19 17:37:09','2007-02-19 17:37:09',NULL,NULL);
2492
INSERT INTO `t4` VALUES (30,NULL,2,'2006-10-12','2006-10-12','04:02:32','04:02:32','1900-01-01 00:00:00','1900-01-01 00:00:00','y','y');
2493
INSERT INTO `t4` VALUES (31,NULL,5,'2005-01-24','2005-01-24','02:33:14','02:33:14','2001-10-10 08:32:27','2001-10-10 08:32:27','y','y');
2494
INSERT INTO `t4` VALUES (32,105,248,'2009-06-27','2009-06-27','16:32:56','16:32:56',NULL,NULL,'u','u');
2495
INSERT INTO `t4` VALUES (33,0,0,NULL,NULL,'21:32:42','21:32:42','2001-12-16 05:31:53','2001-12-16 05:31:53','p','p');
2496
INSERT INTO `t4` VALUES (34,3,8,NULL,NULL,'23:04:47','23:04:47','2003-07-19 18:03:28','2003-07-19 18:03:28','s','s');
2497
INSERT INTO `t4` VALUES (35,1,1,'1900-01-01','1900-01-01','22:05:43','22:05:43','2001-03-27 11:44:10','2001-03-27 11:44:10','e','e');
2498
INSERT INTO `t4` VALUES (36,75,255,'2005-12-22','2005-12-22','02:05:45','02:05:45','2008-06-15 02:13:00','2008-06-15 02:13:00','d','d');
2499
INSERT INTO `t4` VALUES (37,9,9,'2005-05-03','2005-05-03','00:00:00','00:00:00','2009-03-14 21:29:56','2009-03-14 21:29:56','d','d');
2500
INSERT INTO `t4` VALUES (38,7,9,'2003-05-27','2003-05-27','18:09:07','18:09:07','2005-01-02 00:00:00','2005-01-02 00:00:00','c','c');
2501
INSERT INTO `t4` VALUES (39,NULL,3,'2006-05-25','2006-05-25','10:54:06','10:54:06','2007-07-16 04:44:07','2007-07-16 04:44:07','b','b');
2502
INSERT INTO `t4` VALUES (40,NULL,9,NULL,NULL,'23:15:50','23:15:50','2003-08-26 21:38:26','2003-08-26 21:38:26','t','t');
2503
INSERT INTO `t4` VALUES (41,4,6,'2009-01-04','2009-01-04','10:17:40','10:17:40','2004-04-19 04:18:47','2004-04-19 04:18:47',NULL,NULL);
2504
INSERT INTO `t4` VALUES (42,0,4,'2009-02-14','2009-02-14','03:37:09','03:37:09','2000-01-06 20:32:48','2000-01-06 20:32:48','y','y');
2505
INSERT INTO `t4` VALUES (43,204,60,'2003-01-16','2003-01-16','22:26:06','22:26:06','2006-06-23 13:27:17','2006-06-23 13:27:17','c','c');
2506
INSERT INTO `t4` VALUES (44,0,7,'1900-01-01','1900-01-01','17:10:38','17:10:38','2007-11-27 00:00:00','2007-11-27 00:00:00','d','d');
2507
INSERT INTO `t4` VALUES (45,9,1,'2007-06-26','2007-06-26','00:00:00','00:00:00','2002-04-03 12:06:51','2002-04-03 12:06:51','x','x');
2508
INSERT INTO `t4` VALUES (46,8,6,'2004-03-27','2004-03-27','17:08:49','17:08:49','2008-12-28 09:47:42','2008-12-28 09:47:42','p','p');
2509
INSERT INTO `t4` VALUES (47,7,4,NULL,NULL,'19:04:40','19:04:40','2002-04-04 10:07:54','2002-04-04 10:07:54','e','e');
2510
INSERT INTO `t4` VALUES (48,8,NULL,'2005-06-06','2005-06-06','20:53:28','20:53:28','2003-04-26 02:55:13','2003-04-26 02:55:13','g','g');
2511
INSERT INTO `t4` VALUES (49,NULL,8,'2003-03-02','2003-03-02','11:46:03','11:46:03',NULL,NULL,'x','x');
2512
INSERT INTO `t4` VALUES (50,6,0,'2004-05-13','2004-05-13',NULL,NULL,'2009-02-19 03:17:06','2009-02-19 03:17:06','s','s');
2513
INSERT INTO `t4` VALUES (51,5,8,'2005-09-13','2005-09-13','10:58:07','10:58:07','1900-01-01 00:00:00','1900-01-01 00:00:00','e','e');
2514
INSERT INTO `t4` VALUES (52,2,151,'2005-10-03','2005-10-03','00:00:00','00:00:00','2000-11-10 08:20:01','2000-11-10 08:20:01','l','l');
2515
INSERT INTO `t4` VALUES (53,3,7,'2005-10-14','2005-10-14','09:43:15','09:43:15','2008-02-10 00:00:00','2008-02-10 00:00:00','p','p');
2516
INSERT INTO `t4` VALUES (54,7,6,NULL,NULL,'21:40:32','21:40:32','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h');
2517
INSERT INTO `t4` VALUES (55,NULL,NULL,'2005-09-16','2005-09-16','00:17:44','00:17:44',NULL,NULL,'m','m');
2518
INSERT INTO `t4` VALUES (56,145,23,'2005-03-10','2005-03-10','16:47:26','16:47:26','2001-02-05 02:01:50','2001-02-05 02:01:50','n','n');
2519
INSERT INTO `t4` VALUES (57,0,2,'2000-06-19','2000-06-19','00:00:00','00:00:00','2000-10-28 08:44:25','2000-10-28 08:44:25','v','v');
2520
INSERT INTO `t4` VALUES (58,1,4,'2002-11-03','2002-11-03','05:25:59','05:25:59','2005-03-20 10:53:59','2005-03-20 10:53:59','b','b');
2521
INSERT INTO `t4` VALUES (59,7,NULL,'2009-01-05','2009-01-05','00:00:00','00:00:00','2001-06-02 13:54:13','2001-06-02 13:54:13','x','x');
2522
INSERT INTO `t4` VALUES (60,3,NULL,'2003-05-22','2003-05-22','20:33:04','20:33:04','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r');
2523
INSERT INTO `t4` VALUES (61,NULL,77,'2005-07-02','2005-07-02','00:46:12','00:46:12','2009-07-16 13:05:43','2009-07-16 13:05:43','t','t');
2524
INSERT INTO `t4` VALUES (62,2,NULL,'1900-01-01','1900-01-01','00:00:00','00:00:00','2009-03-26 23:16:20','2009-03-26 23:16:20','w','w');
2525
INSERT INTO `t4` VALUES (63,2,NULL,'2006-06-21','2006-06-21','02:13:59','02:13:59','2003-02-06 18:12:15','2003-02-06 18:12:15','w','w');
2526
INSERT INTO `t4` VALUES (64,2,7,NULL,NULL,'02:54:47','02:54:47','2006-06-05 03:22:51','2006-06-05 03:22:51','k','k');
2527
INSERT INTO `t4` VALUES (65,8,1,'2005-12-16','2005-12-16','18:13:59','18:13:59','2002-02-10 05:47:27','2002-02-10 05:47:27','a','a');
2528
INSERT INTO `t4` VALUES (66,6,9,'2004-11-05','2004-11-05','13:53:08','13:53:08','2001-08-01 08:50:52','2001-08-01 08:50:52','t','t');
2529
INSERT INTO `t4` VALUES (67,1,6,NULL,NULL,'22:21:30','22:21:30','1900-01-01 00:00:00','1900-01-01 00:00:00','z','z');
2530
INSERT INTO `t4` VALUES (68,NULL,2,'2004-09-14','2004-09-14','11:41:50','11:41:50',NULL,NULL,'e','e');
2531
INSERT INTO `t4` VALUES (69,1,3,'2002-04-06','2002-04-06','15:20:02','15:20:02','1900-01-01 00:00:00','1900-01-01 00:00:00','q','q');
2532
INSERT INTO `t4` VALUES (70,0,0,NULL,NULL,NULL,NULL,'2000-09-23 00:00:00','2000-09-23 00:00:00','e','e');
2533
INSERT INTO `t4` VALUES (71,4,NULL,'2002-11-13','2002-11-13',NULL,NULL,'2007-07-09 08:32:49','2007-07-09 08:32:49','v','v');
2534
INSERT INTO `t4` VALUES (72,1,6,'2006-05-27','2006-05-27','07:51:52','07:51:52','2000-01-05 00:00:00','2000-01-05 00:00:00','d','d');
2535
INSERT INTO `t4` VALUES (73,1,3,'2000-12-22','2000-12-22','00:00:00','00:00:00','2000-09-24 00:00:00','2000-09-24 00:00:00','u','u');
2536
INSERT INTO `t4` VALUES (74,27,195,'2004-02-21','2004-02-21',NULL,NULL,'2005-05-06 00:00:00','2005-05-06 00:00:00','o','o');
2537
INSERT INTO `t4` VALUES (75,4,5,'2009-05-15','2009-05-15',NULL,NULL,'2000-03-11 00:00:00','2000-03-11 00:00:00','b','b');
2538
INSERT INTO `t4` VALUES (76,6,2,'2008-12-12','2008-12-12','12:31:05','12:31:05','2001-09-02 16:17:35','2001-09-02 16:17:35','c','c');
2539
INSERT INTO `t4` VALUES (77,2,7,'2000-04-15','2000-04-15','00:00:00','00:00:00','2006-04-25 05:43:44','2006-04-25 05:43:44','q','q');
2540
INSERT INTO `t4` VALUES (78,248,25,NULL,NULL,'01:16:45','01:16:45','2009-10-25 22:04:02','2009-10-25 22:04:02',NULL,NULL);
2541
INSERT INTO `t4` VALUES (79,NULL,NULL,'2001-10-18','2001-10-18','20:38:54','20:38:54','2004-08-06 00:00:00','2004-08-06 00:00:00','h','h');
2542
INSERT INTO `t4` VALUES (80,9,0,'2008-05-25','2008-05-25','00:30:15','00:30:15','2001-11-27 05:07:57','2001-11-27 05:07:57','d','d');
2543
INSERT INTO `t4` VALUES (81,75,98,'2004-12-02','2004-12-02','23:46:36','23:46:36','2009-06-28 03:18:39','2009-06-28 03:18:39','w','w');
2544
INSERT INTO `t4` VALUES (82,2,6,'2002-02-15','2002-02-15','19:03:13','19:03:13','2000-03-12 00:00:00','2000-03-12 00:00:00','m','m');
2545
INSERT INTO `t4` VALUES (83,9,5,'2002-03-03','2002-03-03','10:54:27','10:54:27',NULL,NULL,'i','i');
2546
INSERT INTO `t4` VALUES (84,4,0,NULL,NULL,'00:25:47','00:25:47','2007-10-20 00:00:00','2007-10-20 00:00:00','w','w');
2547
INSERT INTO `t4` VALUES (85,0,3,'2003-01-26','2003-01-26','08:44:27','08:44:27','2009-09-27 00:00:00','2009-09-27 00:00:00','f','f');
2548
INSERT INTO `t4` VALUES (86,0,1,'2001-12-19','2001-12-19','08:15:38','08:15:38','2002-07-16 00:00:00','2002-07-16 00:00:00','k','k');
2549
INSERT INTO `t4` VALUES (87,1,1,'2001-08-07','2001-08-07','19:56:21','19:56:21','2005-02-20 00:00:00','2005-02-20 00:00:00','v','v');
2550
INSERT INTO `t4` VALUES (88,119,147,'2005-02-16','2005-02-16','00:00:00','00:00:00',NULL,NULL,'c','c');
2551
INSERT INTO `t4` VALUES (89,1,3,'2006-06-10','2006-06-10','20:50:52','20:50:52','2001-07-16 00:00:00','2001-07-16 00:00:00','y','y');
2552
INSERT INTO `t4` VALUES (90,7,3,NULL,NULL,'03:54:39','03:54:39','2009-05-20 21:04:12','2009-05-20 21:04:12','h','h');
2553
INSERT INTO `t4` VALUES (91,2,NULL,'2005-04-06','2005-04-06','23:58:17','23:58:17','2002-03-13 10:55:40','2002-03-13 10:55:40',NULL,NULL);
2554
INSERT INTO `t4` VALUES (92,7,2,'2003-04-27','2003-04-27','12:54:58','12:54:58','2005-07-12 00:00:00','2005-07-12 00:00:00','t','t');
2555
INSERT INTO `t4` VALUES (93,2,1,'2005-10-13','2005-10-13','04:02:43','04:02:43','2006-07-22 09:46:34','2006-07-22 09:46:34','l','l');
2556
INSERT INTO `t4` VALUES (94,6,8,'2003-10-02','2003-10-02','11:31:12','11:31:12','2001-09-01 00:00:00','2001-09-01 00:00:00','a','a');
2557
INSERT INTO `t4` VALUES (95,4,8,'2005-09-09','2005-09-09','20:20:04','20:20:04','2002-05-27 18:38:45','2002-05-27 18:38:45','r','r');
2558
INSERT INTO `t4` VALUES (96,5,8,NULL,NULL,'00:22:24','00:22:24',NULL,NULL,'s','s');
2559
INSERT INTO `t4` VALUES (97,7,0,'2006-02-15','2006-02-15','10:09:31','10:09:31',NULL,NULL,'z','z');
2560
INSERT INTO `t4` VALUES (98,1,1,'1900-01-01','1900-01-01',NULL,NULL,'2009-08-08 22:38:53','2009-08-08 22:38:53','j','j');
2561
INSERT INTO `t4` VALUES (99,7,8,'2003-12-24','2003-12-24','18:45:35','18:45:35',NULL,NULL,'c','c');
2562
INSERT INTO `t4` VALUES (100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25 05:08:49','2007-04-25 05:08:49','f','f');
2563
SET @@optimizer_switch='subquery_cache=off';
2564
/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
2565
SELECT SUBQUERY2_t1 .`col_int_key`
2566
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
2567
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
2568
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
2570
COUNT( DISTINCT table2 .`col_int_key` ) (
2571
SELECT SUBQUERY2_t1 .`col_int_key`
2572
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
2573
WHERE table1 .`col_varchar_key` ) field10
2590
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2591
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2592
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2593
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2594
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2595
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2596
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2597
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2598
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2599
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2600
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2601
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2602
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2603
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2604
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2605
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2606
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2607
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2608
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2609
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2610
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2611
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2612
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2613
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2614
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2615
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2616
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2617
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2618
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2619
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2620
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2621
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2622
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2623
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2624
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2625
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2626
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2627
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2628
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2629
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2630
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2631
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2632
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2633
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2634
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2635
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2636
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2637
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2638
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2639
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2640
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2641
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2642
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2643
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2644
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2645
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2646
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2647
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2648
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2649
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2650
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2651
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2652
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2653
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2654
SET @@optimizer_switch='subquery_cache=on';
2655
/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
2656
SELECT SUBQUERY2_t1 .`col_int_key`
2657
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
2658
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
2659
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
2661
COUNT( DISTINCT table2 .`col_int_key` ) (
2662
SELECT SUBQUERY2_t1 .`col_int_key`
2663
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
2664
WHERE table1 .`col_varchar_key` ) field10
2681
Warning 1292 Truncated incorrect DOUBLE value: 'r'
2682
Warning 1292 Truncated incorrect DOUBLE value: 'c'
2683
Warning 1292 Truncated incorrect DOUBLE value: 'o'
2684
Warning 1292 Truncated incorrect DOUBLE value: 'd'
2685
Warning 1292 Truncated incorrect DOUBLE value: 'v'
2686
Warning 1292 Truncated incorrect DOUBLE value: 'm'
2687
Warning 1292 Truncated incorrect DOUBLE value: 'j'
2688
Warning 1292 Truncated incorrect DOUBLE value: 'f'
2689
Warning 1292 Truncated incorrect DOUBLE value: 'n'
2690
Warning 1292 Truncated incorrect DOUBLE value: 'z'
2691
Warning 1292 Truncated incorrect DOUBLE value: 'h'
2692
Warning 1292 Truncated incorrect DOUBLE value: 'q'
2693
Warning 1292 Truncated incorrect DOUBLE value: 'w'
2694
Warning 1292 Truncated incorrect DOUBLE value: 'a'
2695
Warning 1292 Truncated incorrect DOUBLE value: 'e'
2696
Warning 1292 Truncated incorrect DOUBLE value: 'u'
2697
Warning 1292 Truncated incorrect DOUBLE value: 's'
2698
Warning 1292 Truncated incorrect DOUBLE value: 'g'
2699
Warning 1292 Truncated incorrect DOUBLE value: 'b'
2700
Warning 1292 Truncated incorrect DOUBLE value: 'y'
2701
Warning 1292 Truncated incorrect DOUBLE value: 'p'
2702
Warning 1292 Truncated incorrect DOUBLE value: 't'
2703
Warning 1292 Truncated incorrect DOUBLE value: 'x'
2704
Warning 1292 Truncated incorrect DOUBLE value: 'l'
2705
Warning 1292 Truncated incorrect DOUBLE value: 'k'
2706
Warning 1292 Truncated incorrect DOUBLE value: 'i'
2707
drop table t1,t2,t3,t4;
2708
set @@optimizer_switch= default;
2709
#launchpad BUG#609045
2711
`pk` int(11) NOT NULL AUTO_INCREMENT,
2712
`col_int_nokey` int(11) DEFAULT NULL,
2713
`col_int_key` int(11) DEFAULT NULL,
2714
`col_varchar_key` varchar(1) DEFAULT NULL,
2715
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2717
KEY `col_int_key` (`col_int_key`),
2718
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2719
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
2720
INSERT INTO `t2` VALUES (10,7,8,'v','v');
2721
INSERT INTO `t2` VALUES (11,1,9,'r','r');
2722
INSERT INTO `t2` VALUES (12,5,9,'a','a');
2723
INSERT INTO `t2` VALUES (13,3,186,'m','m');
2724
INSERT INTO `t2` VALUES (14,6,NULL,'y','y');
2725
INSERT INTO `t2` VALUES (15,92,2,'j','j');
2726
INSERT INTO `t2` VALUES (16,7,3,'d','d');
2727
INSERT INTO `t2` VALUES (17,NULL,0,'z','z');
2728
INSERT INTO `t2` VALUES (18,3,133,'e','e');
2729
INSERT INTO `t2` VALUES (19,5,1,'h','h');
2730
INSERT INTO `t2` VALUES (20,1,8,'b','b');
2731
INSERT INTO `t2` VALUES (21,2,5,'s','s');
2732
INSERT INTO `t2` VALUES (22,NULL,5,'e','e');
2733
INSERT INTO `t2` VALUES (23,1,8,'j','j');
2734
INSERT INTO `t2` VALUES (24,0,6,'e','e');
2735
INSERT INTO `t2` VALUES (25,210,51,'f','f');
2736
INSERT INTO `t2` VALUES (26,8,4,'v','v');
2737
INSERT INTO `t2` VALUES (27,7,7,'x','x');
2738
INSERT INTO `t2` VALUES (28,5,6,'m','m');
2739
INSERT INTO `t2` VALUES (29,NULL,4,'c','c');
2741
`pk` int(11) NOT NULL AUTO_INCREMENT,
2742
`col_int_nokey` int(11) DEFAULT NULL,
2743
`col_int_key` int(11) DEFAULT NULL,
2744
`col_varchar_key` varchar(1) DEFAULT NULL,
2745
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2747
KEY `col_int_key` (`col_int_key`),
2748
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2749
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
2750
INSERT INTO `t1` VALUES (1,NULL,2,'w','w');
2751
INSERT INTO `t1` VALUES (2,7,9,'m','m');
2752
INSERT INTO `t1` VALUES (3,9,3,'m','m');
2753
INSERT INTO `t1` VALUES (4,7,9,'k','k');
2754
INSERT INTO `t1` VALUES (5,4,NULL,'r','r');
2755
INSERT INTO `t1` VALUES (6,2,9,'t','t');
2756
INSERT INTO `t1` VALUES (7,6,3,'j','j');
2757
INSERT INTO `t1` VALUES (8,8,8,'u','u');
2758
INSERT INTO `t1` VALUES (9,NULL,8,'h','h');
2759
INSERT INTO `t1` VALUES (10,5,53,'o','o');
2760
INSERT INTO `t1` VALUES (11,NULL,0,NULL,NULL);
2761
INSERT INTO `t1` VALUES (12,6,5,'k','k');
2762
INSERT INTO `t1` VALUES (13,188,166,'e','e');
2763
INSERT INTO `t1` VALUES (14,2,3,'n','n');
2764
INSERT INTO `t1` VALUES (15,1,0,'t','t');
2765
INSERT INTO `t1` VALUES (16,1,1,'c','c');
2766
INSERT INTO `t1` VALUES (17,0,9,'m','m');
2767
INSERT INTO `t1` VALUES (18,9,5,'y','y');
2768
INSERT INTO `t1` VALUES (19,NULL,6,'f','f');
2769
INSERT INTO `t1` VALUES (20,4,2,'d','d');
2770
SET @@optimizer_switch = 'subquery_cache=off';
2771
/* cache is off */ SELECT SUM( DISTINCT table1 .`pk` ) , (
2772
SELECT MAX( `col_int_nokey` )
2774
WHERE table1 .`pk` ) field3
2779
ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
2781
ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey`
2783
SUM( DISTINCT table1 .`pk` ) field3
2785
SET @@optimizer_switch = 'subquery_cache=on';
2786
/* cache is on */ SELECT SUM( DISTINCT table1 .`pk` ) , (
2787
SELECT MAX( `col_int_nokey` )
2789
WHERE table1 .`pk` ) field3
2794
ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
2796
ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey`
2798
SUM( DISTINCT table1 .`pk` ) field3
2801
set @@optimizer_switch= default;
2802
#launchpad BUG#609052
2804
`pk` int(11) NOT NULL AUTO_INCREMENT,
2805
`col_int_nokey` int(11) DEFAULT NULL,
2806
`col_int_key` int(11) DEFAULT NULL,
2807
`col_time_key` time DEFAULT NULL,
2808
`col_varchar_key` varchar(1) DEFAULT NULL,
2809
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2811
KEY `col_int_key` (`col_int_key`),
2812
KEY `col_time_key` (`col_time_key`),
2813
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2814
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
2815
INSERT INTO `t2` VALUES (10,7,8,'01:27:35','v','v');
2816
INSERT INTO `t2` VALUES (11,1,9,'19:48:31','r','r');
2817
INSERT INTO `t2` VALUES (12,5,9,'00:00:00','a','a');
2818
INSERT INTO `t2` VALUES (13,3,186,'19:53:05','m','m');
2819
INSERT INTO `t2` VALUES (14,6,NULL,'19:18:56','y','y');
2820
INSERT INTO `t2` VALUES (15,92,2,'10:55:12','j','j');
2821
INSERT INTO `t2` VALUES (16,7,3,'00:25:00','d','d');
2822
INSERT INTO `t2` VALUES (17,NULL,0,'12:35:47','z','z');
2823
INSERT INTO `t2` VALUES (18,3,133,'19:53:03','e','e');
2824
INSERT INTO `t2` VALUES (19,5,1,'17:53:30','h','h');
2825
INSERT INTO `t2` VALUES (20,1,8,'11:35:49','b','b');
2826
INSERT INTO `t2` VALUES (21,2,5,NULL,'s','s');
2827
INSERT INTO `t2` VALUES (22,NULL,5,'06:01:40','e','e');
2828
INSERT INTO `t2` VALUES (23,1,8,'05:45:11','j','j');
2829
INSERT INTO `t2` VALUES (24,0,6,'00:00:00','e','e');
2830
INSERT INTO `t2` VALUES (25,210,51,'00:00:00','f','f');
2831
INSERT INTO `t2` VALUES (26,8,4,'06:11:01','v','v');
2832
INSERT INTO `t2` VALUES (27,7,7,'13:02:46','x','x');
2833
INSERT INTO `t2` VALUES (28,5,6,'21:44:25','m','m');
2834
INSERT INTO `t2` VALUES (29,NULL,4,'22:43:58','c','c');
2836
`pk` int(11) NOT NULL AUTO_INCREMENT,
2837
`col_int_nokey` int(11) DEFAULT NULL,
2838
`col_int_key` int(11) DEFAULT NULL,
2839
`col_time_key` time DEFAULT NULL,
2840
`col_varchar_key` varchar(1) DEFAULT NULL,
2841
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2843
KEY `col_int_key` (`col_int_key`),
2844
KEY `col_time_key` (`col_time_key`),
2845
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2846
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
2847
INSERT INTO `t4` VALUES (1,6,NULL,NULL,'r','r');
2848
INSERT INTO `t4` VALUES (2,8,0,'14:34:45','c','c');
2849
INSERT INTO `t4` VALUES (3,6,0,'11:49:48','o','o');
2850
INSERT INTO `t4` VALUES (4,6,7,'18:12:55','c','c');
2851
INSERT INTO `t4` VALUES (5,3,8,'18:30:05','d','d');
2852
INSERT INTO `t4` VALUES (6,9,4,'14:19:30','v','v');
2853
INSERT INTO `t4` VALUES (7,2,6,'05:20:04','m','m');
2854
INSERT INTO `t4` VALUES (8,1,5,'20:29:31','j','j');
2855
INSERT INTO `t4` VALUES (9,8,NULL,'07:08:09','f','f');
2856
INSERT INTO `t4` VALUES (10,0,NULL,'14:49:14','n','n');
2857
INSERT INTO `t4` VALUES (11,9,8,'00:00:00','z','z');
2858
INSERT INTO `t4` VALUES (12,8,8,'09:58:06','h','h');
2859
INSERT INTO `t4` VALUES (13,NULL,8,NULL,'q','q');
2860
INSERT INTO `t4` VALUES (14,0,1,'18:24:16','w','w');
2861
INSERT INTO `t4` VALUES (15,5,1,'17:39:57','z','z');
2862
INSERT INTO `t4` VALUES (16,1,5,'08:23:21','j','j');
2863
INSERT INTO `t4` VALUES (17,1,2,NULL,'a','a');
2864
INSERT INTO `t4` VALUES (18,6,7,'21:50:46','m','m');
2865
INSERT INTO `t4` VALUES (19,6,6,'12:33:17','n','n');
2866
INSERT INTO `t4` VALUES (20,1,4,'03:06:43','e','e');
2867
INSERT INTO `t4` VALUES (21,8,7,'03:46:14','u','u');
2868
INSERT INTO `t4` VALUES (22,1,0,'20:34:52','s','s');
2869
INSERT INTO `t4` VALUES (23,0,9,NULL,'u','u');
2870
INSERT INTO `t4` VALUES (24,4,3,'10:41:20','r','r');
2871
INSERT INTO `t4` VALUES (25,9,5,'08:43:11','g','g');
2872
INSERT INTO `t4` VALUES (26,8,1,NULL,'o','o');
2873
INSERT INTO `t4` VALUES (27,5,1,'10:17:51','w','w');
2874
INSERT INTO `t4` VALUES (28,9,5,'06:34:09','b','b');
2875
INSERT INTO `t4` VALUES (29,5,9,'21:22:47',NULL,NULL);
2876
INSERT INTO `t4` VALUES (30,NULL,2,'04:02:32','y','y');
2877
INSERT INTO `t4` VALUES (31,NULL,5,'02:33:14','y','y');
2878
INSERT INTO `t4` VALUES (32,105,248,'16:32:56','u','u');
2879
INSERT INTO `t4` VALUES (33,0,0,'21:32:42','p','p');
2880
INSERT INTO `t4` VALUES (34,3,8,'23:04:47','s','s');
2881
INSERT INTO `t4` VALUES (35,1,1,'22:05:43','e','e');
2882
INSERT INTO `t4` VALUES (36,75,255,'02:05:45','d','d');
2883
INSERT INTO `t4` VALUES (37,9,9,'00:00:00','d','d');
2884
INSERT INTO `t4` VALUES (38,7,9,'18:09:07','c','c');
2885
INSERT INTO `t4` VALUES (39,NULL,3,'10:54:06','b','b');
2886
INSERT INTO `t4` VALUES (40,NULL,9,'23:15:50','t','t');
2887
INSERT INTO `t4` VALUES (41,4,6,'10:17:40',NULL,NULL);
2888
INSERT INTO `t4` VALUES (42,0,4,'03:37:09','y','y');
2889
INSERT INTO `t4` VALUES (43,204,60,'22:26:06','c','c');
2890
INSERT INTO `t4` VALUES (44,0,7,'17:10:38','d','d');
2891
INSERT INTO `t4` VALUES (45,9,1,'00:00:00','x','x');
2892
INSERT INTO `t4` VALUES (46,8,6,'17:08:49','p','p');
2893
INSERT INTO `t4` VALUES (47,7,4,'19:04:40','e','e');
2894
INSERT INTO `t4` VALUES (48,8,NULL,'20:53:28','g','g');
2895
INSERT INTO `t4` VALUES (49,NULL,8,'11:46:03','x','x');
2896
INSERT INTO `t4` VALUES (50,6,0,NULL,'s','s');
2897
INSERT INTO `t4` VALUES (51,5,8,'10:58:07','e','e');
2898
INSERT INTO `t4` VALUES (52,2,151,'00:00:00','l','l');
2899
INSERT INTO `t4` VALUES (53,3,7,'09:43:15','p','p');
2900
INSERT INTO `t4` VALUES (54,7,6,'21:40:32','h','h');
2901
INSERT INTO `t4` VALUES (55,NULL,NULL,'00:17:44','m','m');
2902
INSERT INTO `t4` VALUES (56,145,23,'16:47:26','n','n');
2903
INSERT INTO `t4` VALUES (57,0,2,'00:00:00','v','v');
2904
INSERT INTO `t4` VALUES (58,1,4,'05:25:59','b','b');
2905
INSERT INTO `t4` VALUES (59,7,NULL,'00:00:00','x','x');
2906
INSERT INTO `t4` VALUES (60,3,NULL,'20:33:04','r','r');
2907
INSERT INTO `t4` VALUES (61,NULL,77,'00:46:12','t','t');
2908
INSERT INTO `t4` VALUES (62,2,NULL,'00:00:00','w','w');
2909
INSERT INTO `t4` VALUES (63,2,NULL,'02:13:59','w','w');
2910
INSERT INTO `t4` VALUES (64,2,7,'02:54:47','k','k');
2911
INSERT INTO `t4` VALUES (65,8,1,'18:13:59','a','a');
2912
INSERT INTO `t4` VALUES (66,6,9,'13:53:08','t','t');
2913
INSERT INTO `t4` VALUES (67,1,6,'22:21:30','z','z');
2914
INSERT INTO `t4` VALUES (68,NULL,2,'11:41:50','e','e');
2915
INSERT INTO `t4` VALUES (69,1,3,'15:20:02','q','q');
2916
INSERT INTO `t4` VALUES (70,0,0,NULL,'e','e');
2917
INSERT INTO `t4` VALUES (71,4,NULL,NULL,'v','v');
2918
INSERT INTO `t4` VALUES (72,1,6,'07:51:52','d','d');
2919
INSERT INTO `t4` VALUES (73,1,3,'00:00:00','u','u');
2920
INSERT INTO `t4` VALUES (74,27,195,NULL,'o','o');
2921
INSERT INTO `t4` VALUES (75,4,5,NULL,'b','b');
2922
INSERT INTO `t4` VALUES (76,6,2,'12:31:05','c','c');
2923
INSERT INTO `t4` VALUES (77,2,7,'00:00:00','q','q');
2924
INSERT INTO `t4` VALUES (78,248,25,'01:16:45',NULL,NULL);
2925
INSERT INTO `t4` VALUES (79,NULL,NULL,'20:38:54','h','h');
2926
INSERT INTO `t4` VALUES (80,9,0,'00:30:15','d','d');
2927
INSERT INTO `t4` VALUES (81,75,98,'23:46:36','w','w');
2928
INSERT INTO `t4` VALUES (82,2,6,'19:03:13','m','m');
2929
INSERT INTO `t4` VALUES (83,9,5,'10:54:27','i','i');
2930
INSERT INTO `t4` VALUES (84,4,0,'00:25:47','w','w');
2931
INSERT INTO `t4` VALUES (85,0,3,'08:44:27','f','f');
2932
INSERT INTO `t4` VALUES (86,0,1,'08:15:38','k','k');
2933
INSERT INTO `t4` VALUES (87,1,1,'19:56:21','v','v');
2934
INSERT INTO `t4` VALUES (88,119,147,'00:00:00','c','c');
2935
INSERT INTO `t4` VALUES (89,1,3,'20:50:52','y','y');
2936
INSERT INTO `t4` VALUES (90,7,3,'03:54:39','h','h');
2937
INSERT INTO `t4` VALUES (91,2,NULL,'23:58:17',NULL,NULL);
2938
INSERT INTO `t4` VALUES (92,7,2,'12:54:58','t','t');
2939
INSERT INTO `t4` VALUES (93,2,1,'04:02:43','l','l');
2940
INSERT INTO `t4` VALUES (94,6,8,'11:31:12','a','a');
2941
INSERT INTO `t4` VALUES (95,4,8,'20:20:04','r','r');
2942
INSERT INTO `t4` VALUES (96,5,8,'00:22:24','s','s');
2943
INSERT INTO `t4` VALUES (97,7,0,'10:09:31','z','z');
2944
INSERT INTO `t4` VALUES (98,1,1,NULL,'j','j');
2945
INSERT INTO `t4` VALUES (99,7,8,'18:45:35','c','c');
2946
INSERT INTO `t4` VALUES (100,2,5,'11:49:25','f','f');
2948
`pk` int(11) NOT NULL AUTO_INCREMENT,
2949
`col_int_nokey` int(11) DEFAULT NULL,
2950
`col_int_key` int(11) DEFAULT NULL,
2951
`col_time_key` time DEFAULT NULL,
2952
`col_varchar_key` varchar(1) DEFAULT NULL,
2953
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2955
KEY `col_int_key` (`col_int_key`),
2956
KEY `col_time_key` (`col_time_key`),
2957
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2958
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
2959
INSERT INTO `t1` VALUES (1,NULL,2,'11:28:45','w','w');
2960
INSERT INTO `t1` VALUES (2,7,9,'20:25:14','m','m');
2961
INSERT INTO `t1` VALUES (3,9,3,'13:47:24','m','m');
2962
INSERT INTO `t1` VALUES (4,7,9,'19:24:11','k','k');
2963
INSERT INTO `t1` VALUES (5,4,NULL,'15:59:13','r','r');
2964
INSERT INTO `t1` VALUES (6,2,9,'00:00:00','t','t');
2965
INSERT INTO `t1` VALUES (7,6,3,'15:15:04','j','j');
2966
INSERT INTO `t1` VALUES (8,8,8,'11:32:06','u','u');
2967
INSERT INTO `t1` VALUES (9,NULL,8,'18:32:33','h','h');
2968
INSERT INTO `t1` VALUES (10,5,53,'15:19:25','o','o');
2969
INSERT INTO `t1` VALUES (11,NULL,0,'19:03:19',NULL,NULL);
2970
INSERT INTO `t1` VALUES (12,6,5,'00:39:46','k','k');
2971
INSERT INTO `t1` VALUES (13,188,166,NULL,'e','e');
2972
INSERT INTO `t1` VALUES (14,2,3,'00:00:00','n','n');
2973
INSERT INTO `t1` VALUES (15,1,0,'13:12:11','t','t');
2974
INSERT INTO `t1` VALUES (16,1,1,'04:56:48','c','c');
2975
INSERT INTO `t1` VALUES (17,0,9,'19:56:05','m','m');
2976
INSERT INTO `t1` VALUES (18,9,5,'19:35:19','y','y');
2977
INSERT INTO `t1` VALUES (19,NULL,6,'05:03:03','f','f');
2978
INSERT INTO `t1` VALUES (20,4,2,'18:38:59','d','d');
2980
`pk` int(11) NOT NULL AUTO_INCREMENT,
2981
`col_int_nokey` int(11) DEFAULT NULL,
2982
`col_int_key` int(11) DEFAULT NULL,
2983
`col_time_key` time DEFAULT NULL,
2984
`col_varchar_key` varchar(1) DEFAULT NULL,
2985
`col_varchar_nokey` varchar(1) DEFAULT NULL,
2987
KEY `col_int_key` (`col_int_key`),
2988
KEY `col_time_key` (`col_time_key`),
2989
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
2990
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
2991
INSERT INTO `t3` VALUES (10,8,8,'18:27:58',NULL,NULL);
2993
`pk` int(11) NOT NULL AUTO_INCREMENT,
2994
`col_int_nokey` int(11) DEFAULT NULL,
2995
`col_int_key` int(11) DEFAULT NULL,
2996
`col_time_key` time DEFAULT NULL,
2997
`col_varchar_key` varchar(1) DEFAULT NULL,
2998
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3000
KEY `col_int_key` (`col_int_key`),
3001
KEY `col_time_key` (`col_time_key`),
3002
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3003
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
3004
INSERT INTO `t5` VALUES (1,1,7,'01:13:38','f','f');
3005
SET @@optimizer_switch='subquery_cache=off';
3006
/* cache is off */ SELECT SQL_SMALL_RESULT MAX( DISTINCT table1 . `col_varchar_key` ) AS field1 , MIN( table1 . `col_varchar_nokey` ) AS field2 , COUNT( table1 . `col_varchar_key` ) AS field3 , table2 . `col_time_key` AS field4 , COUNT( DISTINCT table2 . `col_int_key` ) AS field5 , (
3007
SELECT MAX( SUBQUERY1_t2 . `col_int_nokey` ) AS SUBQUERY1_field1
3008
FROM ( t3 AS SUBQUERY1_t1 INNER JOIN t1 AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_key` = SUBQUERY1_t1 . `col_varchar_nokey` ) )
3009
WHERE SUBQUERY1_t2 . `pk` < SUBQUERY1_t2 . `pk` ) AS field6 , COUNT( table1 . `col_varchar_nokey` ) AS field7 , COUNT( table2 . `pk` ) AS field8 , (
3010
SELECT MAX( SUBQUERY2_t1 . `col_int_key` ) AS SUBQUERY2_field1
3011
FROM ( t5 AS SUBQUERY2_t1 LEFT JOIN t2 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `col_int_key` ) )
3012
WHERE SUBQUERY2_t2 . `col_varchar_nokey` != table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_varchar_nokey` >= 'o' ) AS field9 , CONCAT ( table1 . `col_varchar_key` , table2 . `col_varchar_nokey` ) AS field10
3013
FROM ( t4 AS table1 LEFT JOIN ( ( t1 AS table2 STRAIGHT_JOIN t1 AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) )
3015
SELECT SUBQUERY3_t1 . `pk` AS SUBQUERY3_field1
3016
FROM ( t4 AS SUBQUERY3_t1 INNER JOIN t4 AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` ) )
3017
WHERE SUBQUERY3_t1 . `col_int_key` > table3 . `pk` AND SUBQUERY3_t1 . `pk` != table3 . `pk` ) ) AND ( table1 . `pk` > 116 AND table1 . `pk` < ( 116 + 175 ) OR table1 . `pk` IN (251) ) OR table1 . `col_int_nokey` = table1 . `col_int_nokey`
3018
GROUP BY field4, field6, field9, field10
3019
HAVING field10 = 'c'
3021
field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
3022
SET @@optimizer_switch='subquery_cache=on';
3023
/* cache is on */ SELECT SQL_SMALL_RESULT MAX( DISTINCT table1 . `col_varchar_key` ) AS field1 , MIN( table1 . `col_varchar_nokey` ) AS field2 , COUNT( table1 . `col_varchar_key` ) AS field3 , table2 . `col_time_key` AS field4 , COUNT( DISTINCT table2 . `col_int_key` ) AS field5 , (
3024
SELECT MAX( SUBQUERY1_t2 . `col_int_nokey` ) AS SUBQUERY1_field1
3025
FROM ( t3 AS SUBQUERY1_t1 INNER JOIN t1 AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_key` = SUBQUERY1_t1 . `col_varchar_nokey` ) )
3026
WHERE SUBQUERY1_t2 . `pk` < SUBQUERY1_t2 . `pk` ) AS field6 , COUNT( table1 . `col_varchar_nokey` ) AS field7 , COUNT( table2 . `pk` ) AS field8 , (
3027
SELECT MAX( SUBQUERY2_t1 . `col_int_key` ) AS SUBQUERY2_field1
3028
FROM ( t5 AS SUBQUERY2_t1 LEFT JOIN t2 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `col_int_key` ) )
3029
WHERE SUBQUERY2_t2 . `col_varchar_nokey` != table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_varchar_nokey` >= 'o' ) AS field9 , CONCAT ( table1 . `col_varchar_key` , table2 . `col_varchar_nokey` ) AS field10
3030
FROM ( t4 AS table1 LEFT JOIN ( ( t1 AS table2 STRAIGHT_JOIN t1 AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) )
3032
SELECT SUBQUERY3_t1 . `pk` AS SUBQUERY3_field1
3033
FROM ( t4 AS SUBQUERY3_t1 INNER JOIN t4 AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` ) )
3034
WHERE SUBQUERY3_t1 . `col_int_key` > table3 . `pk` AND SUBQUERY3_t1 . `pk` != table3 . `pk` ) ) AND ( table1 . `pk` > 116 AND table1 . `pk` < ( 116 + 175 ) OR table1 . `pk` IN (251) ) OR table1 . `col_int_nokey` = table1 . `col_int_nokey`
3035
GROUP BY field4, field6, field9, field10
3036
HAVING field10 = 'c'
3038
field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
3039
drop table t1,t2,t3,t4,t5;
3040
set @@optimizer_switch= default;
3041
#launchpad BUG#609043
3043
`pk` int(11) NOT NULL AUTO_INCREMENT,
3044
`col_int_nokey` int(11) DEFAULT NULL,
3045
`col_int_key` int(11) DEFAULT NULL,
3046
`col_date_key` date DEFAULT NULL,
3047
`col_date_nokey` date DEFAULT NULL,
3048
`col_time_key` time DEFAULT NULL,
3049
`col_time_nokey` time DEFAULT NULL,
3050
`col_datetime_key` datetime DEFAULT NULL,
3051
`col_datetime_nokey` datetime DEFAULT NULL,
3052
`col_varchar_key` varchar(1) DEFAULT NULL,
3053
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3055
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3056
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
3057
INSERT INTO `t1` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f');
3058
INSERT INTO `t1` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d');
3060
`pk` int(11) NOT NULL AUTO_INCREMENT,
3061
`col_int_nokey` int(11) DEFAULT NULL,
3062
`col_int_key` int(11) DEFAULT NULL,
3063
`col_date_key` date DEFAULT NULL,
3064
`col_date_nokey` date DEFAULT NULL,
3065
`col_time_key` time DEFAULT NULL,
3066
`col_time_nokey` time DEFAULT NULL,
3067
`col_datetime_key` datetime DEFAULT NULL,
3068
`col_datetime_nokey` datetime DEFAULT NULL,
3069
`col_varchar_key` varchar(1) DEFAULT NULL,
3070
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3072
KEY `col_int_key` (`col_int_key`),
3073
KEY `col_date_key` (`col_date_key`),
3074
KEY `col_time_key` (`col_time_key`),
3075
KEY `col_datetime_key` (`col_datetime_key`),
3076
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3077
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
3079
`pk` int(11) NOT NULL AUTO_INCREMENT,
3080
`col_int_nokey` int(11) DEFAULT NULL,
3081
`col_int_key` int(11) DEFAULT NULL,
3082
`col_date_key` date DEFAULT NULL,
3083
`col_date_nokey` date DEFAULT NULL,
3084
`col_time_key` time DEFAULT NULL,
3085
`col_time_nokey` time DEFAULT NULL,
3086
`col_datetime_key` datetime DEFAULT NULL,
3087
`col_datetime_nokey` datetime DEFAULT NULL,
3088
`col_varchar_key` varchar(1) DEFAULT NULL,
3089
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3091
KEY `col_int_key` (`col_int_key`),
3092
KEY `col_date_key` (`col_date_key`),
3093
KEY `col_time_key` (`col_time_key`),
3094
KEY `col_datetime_key` (`col_datetime_key`),
3095
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3096
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
3098
`pk` int(11) NOT NULL AUTO_INCREMENT,
3099
`col_int_nokey` int(11) DEFAULT NULL,
3100
`col_int_key` int(11) DEFAULT NULL,
3101
`col_date_key` date DEFAULT NULL,
3102
`col_date_nokey` date DEFAULT NULL,
3103
`col_time_key` time DEFAULT NULL,
3104
`col_time_nokey` time DEFAULT NULL,
3105
`col_datetime_key` datetime DEFAULT NULL,
3106
`col_datetime_nokey` datetime DEFAULT NULL,
3107
`col_varchar_key` varchar(1) DEFAULT NULL,
3108
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3110
KEY `col_int_key` (`col_int_key`),
3111
KEY `col_date_key` (`col_date_key`),
3112
KEY `col_time_key` (`col_time_key`),
3113
KEY `col_datetime_key` (`col_datetime_key`),
3114
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3115
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
3116
INSERT INTO `t4` VALUES (100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25 05:08:49','2007-04-25 05:08:49','f','f');
3117
SET @@optimizer_switch = 'subquery_cache=off';
3118
/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
3119
SELECT SUBQUERY2_t1 .`col_int_key`
3120
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
3121
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
3122
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
3124
COUNT( DISTINCT table2 .`col_int_key` ) (
3125
SELECT SUBQUERY2_t1 .`col_int_key`
3126
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
3127
WHERE table1 .`col_varchar_key` ) field10
3131
Warning 1292 Truncated incorrect DOUBLE value: 'f'
3132
SET @@optimizer_switch = 'subquery_cache=on';
3133
/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
3134
SELECT SUBQUERY2_t1 .`col_int_key`
3135
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
3136
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
3137
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
3139
COUNT( DISTINCT table2 .`col_int_key` ) (
3140
SELECT SUBQUERY2_t1 .`col_int_key`
3141
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
3142
WHERE table1 .`col_varchar_key` ) field10
3146
Warning 1292 Truncated incorrect DOUBLE value: 'f'
3147
drop table t1,t2,t3,t4;
3148
set @@optimizer_switch= default;
3149
#launchpad BUG#611625
3151
`pk` int(11) NOT NULL AUTO_INCREMENT,
3152
`col_int_nokey` int(11) DEFAULT NULL,
3153
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3155
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
3156
INSERT INTO `t1` VALUES (1,NULL,'w');
3157
INSERT INTO `t1` VALUES (2,7,'m');
3158
INSERT INTO `t1` VALUES (3,9,'m');
3159
INSERT INTO `t1` VALUES (4,7,'k');
3160
INSERT INTO `t1` VALUES (5,4,'r');
3161
INSERT INTO `t1` VALUES (6,2,'t');
3162
INSERT INTO `t1` VALUES (7,6,'j');
3163
INSERT INTO `t1` VALUES (8,8,'u');
3164
INSERT INTO `t1` VALUES (9,NULL,'h');
3165
INSERT INTO `t1` VALUES (10,5,'o');
3166
INSERT INTO `t1` VALUES (11,NULL,NULL);
3167
INSERT INTO `t1` VALUES (12,6,'k');
3168
INSERT INTO `t1` VALUES (13,188,'e');
3169
INSERT INTO `t1` VALUES (14,2,'n');
3170
INSERT INTO `t1` VALUES (15,1,'t');
3171
INSERT INTO `t1` VALUES (16,1,'c');
3172
INSERT INTO `t1` VALUES (17,0,'m');
3173
INSERT INTO `t1` VALUES (18,9,'y');
3174
INSERT INTO `t1` VALUES (19,NULL,'f');
3175
INSERT INTO `t1` VALUES (20,4,'d');
3177
`pk` int(11) NOT NULL AUTO_INCREMENT,
3178
`col_int_nokey` int(11) DEFAULT NULL,
3179
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3181
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
3182
INSERT INTO `t3` VALUES (1,6,'r');
3183
INSERT INTO `t3` VALUES (2,8,'c');
3184
INSERT INTO `t3` VALUES (3,6,'o');
3185
INSERT INTO `t3` VALUES (4,6,'c');
3186
INSERT INTO `t3` VALUES (5,3,'d');
3187
INSERT INTO `t3` VALUES (6,9,'v');
3188
INSERT INTO `t3` VALUES (7,2,'m');
3189
INSERT INTO `t3` VALUES (8,1,'j');
3190
INSERT INTO `t3` VALUES (9,8,'f');
3191
INSERT INTO `t3` VALUES (10,0,'n');
3192
INSERT INTO `t3` VALUES (11,9,'z');
3193
INSERT INTO `t3` VALUES (12,8,'h');
3194
INSERT INTO `t3` VALUES (13,NULL,'q');
3195
INSERT INTO `t3` VALUES (14,0,'w');
3196
INSERT INTO `t3` VALUES (15,5,'z');
3197
INSERT INTO `t3` VALUES (16,1,'j');
3198
INSERT INTO `t3` VALUES (17,1,'a');
3199
INSERT INTO `t3` VALUES (18,6,'m');
3200
INSERT INTO `t3` VALUES (19,6,'n');
3201
INSERT INTO `t3` VALUES (20,1,'e');
3202
INSERT INTO `t3` VALUES (21,8,'u');
3203
INSERT INTO `t3` VALUES (22,1,'s');
3204
INSERT INTO `t3` VALUES (23,0,'u');
3205
INSERT INTO `t3` VALUES (24,4,'r');
3206
INSERT INTO `t3` VALUES (25,9,'g');
3207
INSERT INTO `t3` VALUES (26,8,'o');
3208
INSERT INTO `t3` VALUES (27,5,'w');
3209
INSERT INTO `t3` VALUES (28,9,'b');
3210
INSERT INTO `t3` VALUES (29,5,NULL);
3211
INSERT INTO `t3` VALUES (30,NULL,'y');
3212
INSERT INTO `t3` VALUES (31,NULL,'y');
3213
INSERT INTO `t3` VALUES (32,105,'u');
3214
INSERT INTO `t3` VALUES (33,0,'p');
3215
INSERT INTO `t3` VALUES (34,3,'s');
3216
INSERT INTO `t3` VALUES (35,1,'e');
3217
INSERT INTO `t3` VALUES (36,75,'d');
3218
INSERT INTO `t3` VALUES (37,9,'d');
3219
INSERT INTO `t3` VALUES (38,7,'c');
3220
INSERT INTO `t3` VALUES (39,NULL,'b');
3221
INSERT INTO `t3` VALUES (40,NULL,'t');
3222
INSERT INTO `t3` VALUES (41,4,NULL);
3223
INSERT INTO `t3` VALUES (42,0,'y');
3224
INSERT INTO `t3` VALUES (43,204,'c');
3225
INSERT INTO `t3` VALUES (44,0,'d');
3226
INSERT INTO `t3` VALUES (45,9,'x');
3227
INSERT INTO `t3` VALUES (46,8,'p');
3228
INSERT INTO `t3` VALUES (47,7,'e');
3229
INSERT INTO `t3` VALUES (48,8,'g');
3230
INSERT INTO `t3` VALUES (49,NULL,'x');
3231
INSERT INTO `t3` VALUES (50,6,'s');
3232
INSERT INTO `t3` VALUES (51,5,'e');
3233
INSERT INTO `t3` VALUES (52,2,'l');
3234
INSERT INTO `t3` VALUES (53,3,'p');
3235
INSERT INTO `t3` VALUES (54,7,'h');
3236
INSERT INTO `t3` VALUES (55,NULL,'m');
3237
INSERT INTO `t3` VALUES (56,145,'n');
3238
INSERT INTO `t3` VALUES (57,0,'v');
3239
INSERT INTO `t3` VALUES (58,1,'b');
3240
INSERT INTO `t3` VALUES (59,7,'x');
3241
INSERT INTO `t3` VALUES (60,3,'r');
3242
INSERT INTO `t3` VALUES (61,NULL,'t');
3243
INSERT INTO `t3` VALUES (62,2,'w');
3244
INSERT INTO `t3` VALUES (63,2,'w');
3245
INSERT INTO `t3` VALUES (64,2,'k');
3246
INSERT INTO `t3` VALUES (65,8,'a');
3247
INSERT INTO `t3` VALUES (66,6,'t');
3248
INSERT INTO `t3` VALUES (67,1,'z');
3249
INSERT INTO `t3` VALUES (68,NULL,'e');
3250
INSERT INTO `t3` VALUES (69,1,'q');
3251
INSERT INTO `t3` VALUES (70,0,'e');
3252
INSERT INTO `t3` VALUES (71,4,'v');
3253
INSERT INTO `t3` VALUES (72,1,'d');
3254
INSERT INTO `t3` VALUES (73,1,'u');
3255
INSERT INTO `t3` VALUES (74,27,'o');
3256
INSERT INTO `t3` VALUES (75,4,'b');
3257
INSERT INTO `t3` VALUES (76,6,'c');
3258
INSERT INTO `t3` VALUES (77,2,'q');
3259
INSERT INTO `t3` VALUES (78,248,NULL);
3260
INSERT INTO `t3` VALUES (79,NULL,'h');
3261
INSERT INTO `t3` VALUES (80,9,'d');
3262
INSERT INTO `t3` VALUES (81,75,'w');
3263
INSERT INTO `t3` VALUES (82,2,'m');
3264
INSERT INTO `t3` VALUES (83,9,'i');
3265
INSERT INTO `t3` VALUES (84,4,'w');
3266
INSERT INTO `t3` VALUES (85,0,'f');
3267
INSERT INTO `t3` VALUES (86,0,'k');
3268
INSERT INTO `t3` VALUES (87,1,'v');
3269
INSERT INTO `t3` VALUES (88,119,'c');
3270
INSERT INTO `t3` VALUES (89,1,'y');
3271
INSERT INTO `t3` VALUES (90,7,'h');
3272
INSERT INTO `t3` VALUES (91,2,NULL);
3273
INSERT INTO `t3` VALUES (92,7,'t');
3274
INSERT INTO `t3` VALUES (93,2,'l');
3275
INSERT INTO `t3` VALUES (94,6,'a');
3276
INSERT INTO `t3` VALUES (95,4,'r');
3277
INSERT INTO `t3` VALUES (96,5,'s');
3278
INSERT INTO `t3` VALUES (97,7,'z');
3279
INSERT INTO `t3` VALUES (98,1,'j');
3280
INSERT INTO `t3` VALUES (99,7,'c');
3281
INSERT INTO `t3` VALUES (100,2,'f');
3283
`pk` int(11) NOT NULL AUTO_INCREMENT,
3284
`col_int_nokey` int(11) DEFAULT NULL,
3285
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3287
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
3288
INSERT INTO `t2` VALUES (10,8,NULL);
3289
set optimizer_switch='subquery_cache=off';
3291
SELECT `col_int_nokey`
3293
WHERE table1 .`col_varchar_nokey` ) field13
3294
FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
3317
set optimizer_switch='subquery_cache=on';
3319
(SELECT `col_int_nokey`
3321
WHERE table1 .`col_varchar_nokey` ) field13
3322
FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
3345
drop table t1,t2,t3;
3346
set @@optimizer_switch= default;
3347
# LP BUG#615760 (part 1: double transformation)
3348
create table t1 (a int);
3349
insert into t1 values (1),(2);
3350
create table t2 (b int);
3351
insert into t2 values (1),(2);
3352
set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on';
3354
select * from t1 where a in (select b from t2);
3355
id select_type table type possible_keys key key_len ref rows filtered Extra
3356
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3357
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3359
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`b` from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))))
3361
set @@optimizer_switch= default;
3362
# LP BUG#615760 (part 2: incorrect heap table index flags)
3363
SET SESSION optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_condition_pushdown=off,firstmatch=off,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=on';
3365
`pk` int(11) NOT NULL AUTO_INCREMENT,
3366
`col_int_nokey` int(11) DEFAULT NULL,
3367
`col_int_key` int(11) DEFAULT NULL,
3368
`col_varchar_key` varchar(1) DEFAULT NULL,
3369
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3371
KEY `col_int_key` (`col_int_key`),
3372
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3373
) AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
3374
INSERT INTO `t1` VALUES (10,7,8,'v','v');
3375
INSERT INTO `t1` VALUES (11,1,9,'r','r');
3376
INSERT INTO `t1` VALUES (12,5,9,'a','a');
3377
INSERT INTO `t1` VALUES (13,3,186,'m','m');
3378
INSERT INTO `t1` VALUES (14,6,NULL,'y','y');
3379
INSERT INTO `t1` VALUES (15,92,2,'j','j');
3380
INSERT INTO `t1` VALUES (16,7,3,'d','d');
3381
INSERT INTO `t1` VALUES (17,NULL,0,'z','z');
3382
INSERT INTO `t1` VALUES (18,3,133,'e','e');
3383
INSERT INTO `t1` VALUES (19,5,1,'h','h');
3384
INSERT INTO `t1` VALUES (20,1,8,'b','b');
3385
INSERT INTO `t1` VALUES (21,2,5,'s','s');
3386
INSERT INTO `t1` VALUES (22,NULL,5,'e','e');
3387
INSERT INTO `t1` VALUES (23,1,8,'j','j');
3388
INSERT INTO `t1` VALUES (24,0,6,'e','e');
3389
INSERT INTO `t1` VALUES (25,210,51,'f','f');
3390
INSERT INTO `t1` VALUES (26,8,4,'v','v');
3391
INSERT INTO `t1` VALUES (27,7,7,'x','x');
3392
INSERT INTO `t1` VALUES (28,5,6,'m','m');
3393
INSERT INTO `t1` VALUES (29,NULL,4,'c','c');
3395
`pk` int(11) NOT NULL AUTO_INCREMENT,
3396
`col_int_nokey` int(11) DEFAULT NULL,
3397
`col_int_key` int(11) DEFAULT NULL,
3398
`col_varchar_key` varchar(1) DEFAULT NULL,
3399
`col_varchar_nokey` varchar(1) DEFAULT NULL,
3401
KEY `col_int_key` (`col_int_key`),
3402
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
3403
) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
3404
INSERT INTO `t2` VALUES (1,NULL,2,'w','w');
3405
INSERT INTO `t2` VALUES (2,7,9,'m','m');
3406
INSERT INTO `t2` VALUES (3,9,3,'m','m');
3407
INSERT INTO `t2` VALUES (4,7,9,'k','k');
3408
INSERT INTO `t2` VALUES (5,4,NULL,'r','r');
3409
INSERT INTO `t2` VALUES (6,2,9,'t','t');
3410
INSERT INTO `t2` VALUES (7,6,3,'j','j');
3411
INSERT INTO `t2` VALUES (8,8,8,'u','u');
3412
INSERT INTO `t2` VALUES (9,NULL,8,'h','h');
3413
INSERT INTO `t2` VALUES (10,5,53,'o','o');
3414
INSERT INTO `t2` VALUES (11,NULL,0,NULL,NULL);
3415
INSERT INTO `t2` VALUES (12,6,5,'k','k');
3416
INSERT INTO `t2` VALUES (13,188,166,'e','e');
3417
INSERT INTO `t2` VALUES (14,2,3,'n','n');
3418
INSERT INTO `t2` VALUES (15,1,0,'t','t');
3419
INSERT INTO `t2` VALUES (16,1,1,'c','c');
3420
INSERT INTO `t2` VALUES (17,0,9,'m','m');
3421
INSERT INTO `t2` VALUES (18,9,5,'y','y');
3422
INSERT INTO `t2` VALUES (19,NULL,6,'f','f');
3423
INSERT INTO `t2` VALUES (20,4,2,'d','d');
3424
SELECT table1 .`col_varchar_nokey`
3425
FROM t2 table1 RIGHT JOIN t1 LEFT JOIN (
3426
SELECT SUBQUERY1_t2 .*
3427
FROM t1 SUBQUERY1_t1 LEFT JOIN t2 SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
3429
FROM t1 ) table4 JOIN ( t1 table5 JOIN t2 table6 ON table5 .`pk` ) ON table5 .`col_varchar_nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_nokey` ON table5 .`col_varchar_key`
3430
WHERE table3 .`col_varchar_key` IN (
3431
SELECT `col_varchar_key`
3432
FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ;
3435
set @@optimizer_switch= default;
3436
set optimizer_switch='subquery_cache=on';
3437
# LP BUG#615378 (incorrect NULL result returning in Item_cache)
3439
`pk` int(11) NOT NULL AUTO_INCREMENT,
3440
`col_varchar_key` varchar(1) DEFAULT NULL,
3442
KEY `col_varchar_key` (`col_varchar_key`)
3443
) DEFAULT CHARSET=latin1;
3444
INSERT INTO `t1` VALUES (10,'v');
3445
INSERT INTO `t1` VALUES (11,'r');
3447
`pk` int(11) NOT NULL AUTO_INCREMENT,
3448
`col_varchar_key` varchar(1) DEFAULT NULL,
3450
KEY `col_varchar_key` (`col_varchar_key`)
3451
) DEFAULT CHARSET=latin1;
3452
INSERT INTO `t2` VALUES (1,'r');
3453
INSERT INTO `t2` VALUES (2,'c');
3455
`pk` int(11) NOT NULL AUTO_INCREMENT,
3456
`col_varchar_key` varchar(1) DEFAULT NULL,
3458
KEY `col_varchar_key` (`col_varchar_key`)
3459
) DEFAULT CHARSET=latin1;
3460
INSERT INTO `t3` VALUES (1,'w');
3461
SELECT SUM( DISTINCT table2 . `pk` ) AS field2 ,
3462
(SELECT SUM( SUBQUERY1_t2 . `pk` ) AS SUBQUERY1_field1
3463
FROM t2 AS SUBQUERY1_t2 STRAIGHT_JOIN
3464
t3 AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `pk` )
3465
WHERE table1 . `col_varchar_key` ) AS field3
3466
FROM ( t1 AS table1 LEFT JOIN
3467
( t2 AS table2 STRAIGHT_JOIN
3468
t3 AS table3 ON (table3 . `pk` = table2 . `pk` ) )
3469
ON (table3 . `col_varchar_key` = table1 . `col_varchar_key` ) )
3470
WHERE ( table1 . `pk` < 5 ) OR ( table1 . `col_varchar_key` IS NOT NULL)
3472
HAVING (field3 <= 'h' AND field2 != 4) ;
3474
drop tables t1, t2, t3;
3476
# Test aggregate functions as parameters to subquery cache
3478
CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b));
3479
INSERT INTO t1 VALUES
3489
SELECT a, AVG(t1.b),
3490
(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c
3497
# Test of LP BUG#800696 (deleting list of Items (OR arguments)
3500
set optimizer_switch='subquery_cache=on,in_to_exists=on';
3501
CREATE TABLE t1 ( f3 int) ;
3502
INSERT INTO t1 VALUES (0),(0);
3503
CREATE TABLE t3 ( f3 int) ;
3504
INSERT INTO t3 VALUES (0),(0);
3505
CREATE TABLE t2 ( f1 int, f2 int, f3 int) ;
3506
INSERT INTO t2 VALUES (7,0,0);
3509
WHERE t2.f2 OR t3.f3 IN
3513
WHERE t2.f1 OR t2.f3 );
3517
drop tables t1, t2, t3;
3519
# Test of LP BUG#872775 view with "outer references" bug
3521
set @@optimizer_switch= default;
3522
set optimizer_switch='subquery_cache=on';
3523
CREATE TABLE t1 (a int) ;
3524
CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ;
3525
INSERT INTO t2 VALUES (1,'x'),(2,'y');
3526
CREATE TABLE t3 (a int) ;
3527
CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ;
3528
INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3);
3529
CREATE OR REPLACE VIEW v1 AS
3536
SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );
3543
drop table t1,t2,t3,t4;
3544
SET optimizer_switch=@save_optimizer_switch;
3546
set @@optimizer_switch= default;
added
removed
mysql-test/r/subselect_cache.result
