drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
drop view if exists v1; SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; SET optimizer_switch='semijoin_with_cache=off'; set optimizer_switch='subquery_cache=on';
create table t1 (a int, b int);
insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5);
create table t2 (c int, d int);
insert into t2 values (2,3),(3,4),(5,6),(4,1);
*
* Test subquery as top item in different clauses
*
#single value subquery test (SELECT list)
flush global status;
flush status;
select a, (select d from t2 where b=c) from t1;
a (select d from t2 where b=c) 13 31 13 31 31 46 46 5 NULL 5 NULL 46
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 31
analyze format=json
select a, (select d from t2 where b=c) from t1;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 10, "r_rows": 10, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
], "subqueries": [
{ "subquery_cache": { "r_loops": 10, "r_hit_ratio": 60, "query_block": { "select_id": 2, "cost": "REPLACED", "r_loops": 4, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "r_loops": 4, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 18.75, "attached_condition": "t1.b = t2.c", "r_filtered": 18.75
}
}
]
}
}
}
]
}
}
analyze format=json
select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 10, "r_rows": 10, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
], "subqueries": [
{ "subquery_cache": { "r_loops": 10, "r_hit_ratio": 60, "query_block": { "union_result": { "table_name": "<union3,4>", "access_type": "ALL", "r_loops": 4, "r_rows": 1, "query_specifications": [
{ "query_block": { "select_id": 3, "cost": "REPLACED", "r_loops": 4, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "r_loops": 4, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 18.75, "attached_condition": "t1.b = t2.c", "r_filtered": 18.75
}
}
]
}
},
{ "query_block": { "select_id": 4, "operation": "UNION", "table": { "message": "No tables used"
}
}
}
]
}
}
}
},
{ "subquery_cache": { "r_loops": 10, "r_hit_ratio": 60, "query_block": { "select_id": 2, "cost": "REPLACED", "r_loops": 4, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "r_loops": 4, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 18.75, "attached_condition": "t1.b = t2.c", "r_filtered": 18.75
}
}
]
}
}
}
]
}
}
explain format=json
select a, (select d from t2 where b=c) from t1;
EXPLAIN
{ "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [
{ "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100
}
}
], "subqueries": [
{ "subquery_cache": { "state": "uninitialized", "query_block": { "select_id": 2, "cost": "COST_REPLACED", "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 4, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = t2.c"
}
}
]
}
}
}
]
}
}
explain format=json
select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
EXPLAIN
{ "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [
{ "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100
}
}
], "subqueries": [
{ "subquery_cache": { "state": "uninitialized", "query_block": { "union_result": { "table_name": "<union3,4>", "access_type": "ALL", "query_specifications": [
{ "query_block": { "select_id": 3, "cost": "COST_REPLACED", "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 4, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = t2.c"
}
}
]
}
},
{ "query_block": { "select_id": 4, "operation": "UNION", "table": { "message": "No tables used"
}
}
}
]
}
}
}
},
{ "subquery_cache": { "state": "uninitialized", "query_block": { "select_id": 2, "cost": "COST_REPLACED", "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 4, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b = t2.c"
}
}
]
}
}
}
]
}
} set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select a, (select d from t2 where b=c) from t1;
a (select d from t2 where b=c) 13 31 13 31 31 46 46 5 NULL 5 NULL 46
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 61 set optimizer_switch='subquery_cache=on';
#single value subquery test (where)
flush global status;
flush status;
select a from t1 where (select d from t2 where b=c);
a 1 3 1 3 3 4 4 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 31 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select a from t1 where (select d from t2 where b=c);
a 1 3 1 3 3 4 4 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 61 set optimizer_switch='subquery_cache=on';
#single value subquery test (having)
flush global status;
flush status;
select a from t1 where a > 0 having (select d from t2 where b=c);
a 1 3 1 3 3 4 4 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 31 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select a from t1 where a > 0 having (select d from t2 where b=c);
a 1 3 1 3 3 4 4 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 61 set optimizer_switch='subquery_cache=on';
#single value subquery test (OUTER JOIN ON)
flush global status;
flush status;
select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c);
a a 11 31 11 31 31 41 41 51 51 41 13 33 13 33 33 43 43 53 53 43 11 31 11 31 31 41 41 51 51 41 13 33 13 33 33 43 43 53 53 43 13 33 13 33 33 43 43 53 53 43 14 34 14 34 34 44 44 54 54 44 14 34 14 34 34 44 44 54 54 44 14 34 14 34 34 44 44 54 54 44
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 442 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c);
a a 11 31 11 31 31 41 41 51 51 41 13 33 13 33 33 43 43 53 53 43 11 31 11 31 31 41 41 51 51 41 13 33 13 33 33 43 43 53 53 43 13 33 13 33 33 43 43 53 53 43 14 34 14 34 34 44 44 54 54 44 14 34 14 34 34 44 44 54 54 44 14 34 14 34 34 44 44 54 54 44
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 472 set optimizer_switch='subquery_cache=on';
#single value subquery test (GROUP BY)
flush global status;
flush status;
select max(a) from t1 GROUP BY (select d from t2 where b=c); max(a) 5 3 1 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 10
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 17
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 4
Handler_read_rnd_deleted 0
Handler_read_rnd_next 36 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select max(a) from t1 GROUP BY (select d from t2 where b=c); max(a) 5 3 1 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 10
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 4
Handler_read_rnd_deleted 0
Handler_read_rnd_next 86 set optimizer_switch='subquery_cache=on';
#single value subquery test (distinct GROUP BY)
flush global status;
flush status;
select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); max(a) 5 3 1 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 10
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 17
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 4
Handler_read_rnd_deleted 0
Handler_read_rnd_next 41 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); max(a) 5 3 1 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 10
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 4
Handler_read_rnd_deleted 0
Handler_read_rnd_next 91 set optimizer_switch='subquery_cache=on';
#single value subquery test (ORDER BY)
flush global status;
flush status;
select a from t1 ORDER BY (select d from t2 where b=c);
a 5 5 3 3 3 1 1 4 4 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 31 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select a from t1 ORDER BY (select d from t2 where b=c);
a 5 5 3 3 3 1 1 4 4 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 61 set optimizer_switch='subquery_cache=on';
#single value subquery test (distinct ORDER BY)
flush global status;
flush status;
select distinct a from t1 ORDER BY (select d from t2 where b=c);
a 5 3 1 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 7
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 4
Handler_read_rnd_deleted 1
Handler_read_rnd_next 36 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select distinct a from t1 ORDER BY (select d from t2 where b=c);
a 5 3 1 4
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 4
Handler_read_rnd_deleted 1
Handler_read_rnd_next 66 set optimizer_switch='subquery_cache=on';
#single value subquery test (LEFT JOIN ON)
flush global status;
flush status;
select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c);
a a 11 13 11 13 13 14 14 14 31 33 31 33 33 34 34 34 11 13 11 13 13 14 14 14 31 33 31 33 33 34 34 34 31 33 31 33 33 34 34 34 41 43 41 43 43 44 44 44 41 43 41 43 43 44 44 44 51 53 51 53 53 54 54 54 51 53 51 53 53 54 54 54 41 43 41 43 43 44 44 44
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 106
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 70
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 141 set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c);
a a 11 13 11 13 13 14 14 14 31 33 31 33 33 34 34 34 11 13 11 13 13 14 14 14 31 33 31 33 33 34 34 34 31 33 31 33 33 34 34 34 41 43 41 43 43 44 44 44 41 43 41 43 43 44 44 44 51 53 51 53 53 54 54 54 51 53 51 53 53 54 54 54 41 43 41 43 43 44 44 44
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 671 set optimizer_switch='subquery_cache=on';
#single value subquery test (PS)
prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1';
execute stmt1;
a (select d from t2 where b=c) + 1 14 32 14 32 32 47 47 5 NULL 5 NULL 47
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
execute stmt1;
a (select d from t2 where b=c) + 1 14 32 14 32 32 47 47 5 NULL 5 NULL 47
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 12
Subquery_cache_miss 8
deallocate prepare stmt1;
#single value subquery test (SP)
CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1;
call p1;
a (select d from t2 where b=c) + 1 14 32 14 32 32 47 47 5 NULL 5 NULL 47
call p1;
a (select d from t2 where b=c) + 1 14 32 14 32 32 47 47 5 NULL 5 NULL 47
drop procedure p1;
#IN subquery test
flush global status;
flush status;
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
select a, b , b in (select d from t2) as SUBS from t1;
a b SUBS 120 341 120 341 341 450 450 561 561 450
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 4
insert into t1 values (7,8),(9,NULL);
select a, b , b in (select d from t2) as SUBS from t1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 780 9 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 12
Subquery_cache_miss 10
insert into t2 values (8,NULL);
select a, b , b in (select d from t2) as SUBS from t1;
a b SUBS 12 NULL 341 12 NULL 341 341 45 NULL 45 NULL 561 561 45 NULL 78 NULL 9 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 18
Subquery_cache_miss 16
# multicolumn NOT IN with NULLs
flush global status;
flush status; set optimizer_switch='subquery_cache=off';
select a, b, (b, a) not in (select d, c from t2) as SUBS from t1;
a b SUBS 121 340 121 340 340 451 451 560 560 451 781 9 NULL 1
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0 set optimizer_switch='subquery_cache=on';
select a, b, (b, a) not in (select d, c from t2) as SUBS from t1;
a b SUBS 121 340 121 340 340 451 451 560 560 451 781 9 NULL 1
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 6
# multicolumn NOT IN with NULLs (other order)
flush global status;
flush status; set optimizer_switch='subquery_cache=off';
select a, b, (a, b) not in (select d, c from t2) as SUBS from t1;
a b SUBS 121 341 121 341 341 451 451 561 561 451 78 NULL 9 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0 set optimizer_switch='subquery_cache=on';
select a, b, (a, b) not in (select d, c from t2) as SUBS from t1;
a b SUBS 121 341 121 341 341 451 451 561 561 451 78 NULL 9 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 6
# multicolumn IN with NULLs
flush global status;
flush status; set optimizer_switch='subquery_cache=off';
select a, b, (b, a) in (select d, c from t2) as SUBS from t1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 780 9 NULL 0
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0 set optimizer_switch='subquery_cache=on';
select a, b, (b, a) in (select d, c from t2) as SUBS from t1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 780 9 NULL 0
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 6
# multicolumn IN with NULLs (other order)
flush global status;
flush status; set optimizer_switch='subquery_cache=off';
select a, b, (a, b) in (select d, c from t2) as SUBS from t1;
a b SUBS 120 340 120 340 340 450 450 560 560 450 78 NULL 9 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0 set optimizer_switch='subquery_cache=on';
select a, b, (a, b) in (select d, c from t2) as SUBS from t1;
a b SUBS 120 340 120 340 340 450 450 560 560 450 78 NULL 9 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 6
#IN subquery test (PS)
delete from t1 where a > 6;
delete from t2 where c > 6;
prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1';
execute stmt1;
a b SUBS 120 341 120 341 341 450 450 561 561 450
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 12
Subquery_cache_miss 10
execute stmt1;
a b SUBS 120 341 120 341 341 450 450 561 561 450
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 18
Subquery_cache_miss 14
insert into t1 values (7,8),(9,NULL);
execute stmt1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL NULL 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 24
Subquery_cache_miss 20
execute stmt1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL NULL 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 30
Subquery_cache_miss 26
insert into t2 values (8,NULL);
execute stmt1;
a b SUBS 12 NULL 341 12 NULL 341 341 45 NULL 45 NULL 561 561 45 NULL 9 NULL NULL 78 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 36
Subquery_cache_miss 32
execute stmt1;
a b SUBS 12 NULL 341 12 NULL 341 341 45 NULL 45 NULL 561 561 45 NULL 9 NULL NULL 78 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 42
Subquery_cache_miss 38
deallocate prepare stmt1;
#IN subquery test (SP)
delete from t1 where a > 6;
delete from t2 where c > 6;
CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1;
call p1();
a b SUBS 120 341 120 341 341 450 450 561 561 450
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 48
Subquery_cache_miss 42
call p1();
a b SUBS 120 341 120 341 341 450 450 561 561 450
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 54
Subquery_cache_miss 46
insert into t1 values (7,8),(9,NULL);
call p1();
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL NULL 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 60
Subquery_cache_miss 52
call p1();
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL NULL 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 66
Subquery_cache_miss 58
insert into t2 values (8,NULL);
call p1();
a b SUBS 12 NULL 341 12 NULL 341 341 45 NULL 45 NULL 561 561 45 NULL 9 NULL NULL 78 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 72
Subquery_cache_miss 64
call p1();
a b SUBS 12 NULL 341 12 NULL 341 341 45 NULL 45 NULL 561 561 45 NULL 9 NULL NULL 78 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 78
Subquery_cache_miss 70
drop procedure p1;
# test of simple exists
select a, b , exists (select * from t2 where b=d) as SUBS from t1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL 0 780
# test of prepared statement exists
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 84
Subquery_cache_miss 76
prepare stmt1 from 'select a, b , exists (select * from t2 where b=d) as SUBS from t1';
execute stmt1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL 0 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 90
Subquery_cache_miss 82
execute stmt1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL 0 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 96
Subquery_cache_miss 88
deallocate prepare stmt1;
# test of stored procedure exists
CREATE PROCEDURE p1() select a, b , exists (select * from t2 where b=d) as SUBS from t1;
call p1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL 0 780
call p1;
a b SUBS 120 341 120 341 341 450 450 561 561 450 9 NULL 0 780
drop procedure p1;
#several subqueries set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
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;
a b SUBSE SUBSI SUBSR 120 NULL 3 34111 120 NULL 3 34111 34111 450 NULL 6 450 NULL 6 5611 NULL 5611 NULL 450 NULL 6 9 NULL 0 NULL NULL 780 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 11
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 145 set optimizer_switch='subquery_cache=on';
flush global status;
flush status;
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;
a b SUBSE SUBSI SUBSR 120 NULL 3 34111 120 NULL 3 34111 34111 450 NULL 6 450 NULL 6 5611 NULL 5611 NULL 450 NULL 6 9 NULL 0 NULL NULL 780 NULL NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 18
Subquery_cache_miss 18
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 29
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 84
#several subqueries (several levels) set optimizer_switch='subquery_cache=off';
flush global status;
flush status; set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1;
a b SUNS1 120 341 120 341 341 450 450 56 NULL 56 NULL 450 9 NULL NULL 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 127 set optimizer_switch='subquery_cache=on';
flush global status;
flush status;
select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1;
a b SUNS1 120 341 120 341 341 450 450 56 NULL 56 NULL 450 9 NULL NULL 780
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 6
Subquery_cache_miss 10
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 12
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 69
#clean up
drop table t1,t2;
test different types
#int
CREATE TABLE t1 ( a int, b int);
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2);
a 1 3
DROP TABLE t1;
#char
CREATE TABLE t1 ( a char(1), b char (1));
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
a 1 3
DROP TABLE t1;
#decimal
CREATE TABLE t1 ( a decimal(3,1), b decimal(3,1));
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2);
a 1.0 3.0
DROP TABLE t1;
#date
CREATE TABLE t1 ( a date, b date);
INSERT INTO t1 VALUES('1000-01-01','1000-01-01'),('2000-02-01','2000-02-01'),('3000-03-03','3000-03-03');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-01');
a 1000-01-01 3000-03-03
DROP TABLE t1;
#datetime
CREATE TABLE t1 ( a datetime, b datetime);
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');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02');
a 1000-01-0101:01:01 3000-03-0303:03:03
DROP TABLE t1;
#time
CREATE TABLE t1 ( a time, b time);
INSERT INTO t1 VALUES('01:01:01','01:01:01'),('02:02:02','02:02:02'),('03:03:03','03:03:03');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '02:02:02');
a 01:01:01 03:03:03
DROP TABLE t1;
#timestamp
CREATE TABLE t1 ( a timestamp, b timestamp);
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');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02');
a 2000-02-0201:01:01 2000-02-0203:03:03
DROP TABLE t1;
#bit
CREATE TABLE t1 ( a bit(20), b bit(20));
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
SELECT a+0 FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2);
a+0 1 3
DROP TABLE t1;
#enum
CREATE TABLE t1 ( a enum('1','2','3'), b enum('1','2','3'));
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
a 1 3
DROP TABLE t1;
#set
CREATE TABLE t1 ( a set('1','2','3'), b set('1','2','3'));
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
a 1 3
DROP TABLE t1;
#blob
CREATE TABLE t1 ( a blob, b blob);
INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3');
SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2');
a 1 3
DROP TABLE t1;
#geometry
CREATE TABLE t1 ( a geometry, b geometry);
INSERT INTO t1 VALUES(POINT(1,1),POINT(1,1)),(POINT(2,2),POINT(2,2)),(POINT(3,3),POINT(3,3));
SELECT astext(a) FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = POINT(2,2));
astext(a)
POINT(11)
POINT(33)
DROP TABLE t1;
#uncacheable queries test (random and side effect)
flush global status;
flush status;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2), (4), (1), (3);
select a, a in (select a from t1) from t1 as ext;
a a in (select a from t1) 21 41 11 31
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 4
select a, a in (select a from t1 where -1 < rand()) from t1 as ext;
a a in (select a from t1 where -1 < rand()) 21 41 11 31
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 4
select a, a in (select a from t1 where -1 < benchmark(a,100)) from t1 as ext;
a a in (select a from t1 where -1 < benchmark(a,100)) 21 41 11 31
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 4
drop table t1;
#test of big_tables switch and outer table reference in subquery with grouping set tmp_memory_table_size=0;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer;
(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) 1 2 3 4 5 6
drop table t1; set tmp_memory_table_size=default;
#test of function reference to outer query set local group_concat_max_len=400;
create table t2 (a int, b int);
insert into t2 values (1,1), (2,2);
select b x, (select group_concat(x) from t2) from t2;
x (select group_concat(x) from t2) 11,1 22,2
drop table t2; set local group_concat_max_len=default;
#aggregate functions
CREATE TABLE t1 (a int, b INT);
CREATE TABLE t2 (c int, d INT);
insert into t1 values (2,1), (3,1), (2,4), (3,4), (10,2), (20,2), (2,5),
(3,5), (100,3), (200,3), (10,6), (20,6), (20,7), (100,8), (200,8);
insert into t2 values (1,1),(3,3),(20,20);
aggregate function as parameter of subquery set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; max(a) (select max(a) from t2 where max(a)=c) 33 2020 200 NULL 33 33 2020 2020 200 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 15
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 8
Handler_read_rnd_deleted 0
Handler_read_rnd_next 57 set optimizer_switch='subquery_cache=on';
flush global status;
flush status;
select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; max(a) (select max(a) from t2 where max(a)=c) 33 2020 200 NULL 33 33 2020 2020 200 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 5
Subquery_cache_miss 3
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 21
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 8
Handler_read_rnd_deleted 0
Handler_read_rnd_next 37
argument of aggregate function as parameter of subquery (illegal use) set optimizer_switch='subquery_cache=off';
flush global status;
flush status;
select max(a), (select a from t2 where a=c) from t1 group by b; max(a) (select a from t2 where a=c) 3 NULL 20 NULL 200 NULL 3 NULL 3 NULL 20 NULL 2020 200 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 15
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 8
Handler_read_rnd_deleted 0
Handler_read_rnd_next 57 set optimizer_switch='subquery_cache=on';
flush global status;
flush status;
select max(a), (select a from t2 where a=c) from t1 group by b; max(a) (select a from t2 where a=c) 3 NULL 20 NULL 200 NULL 3 NULL 3 NULL 20 NULL 2020 200 NULL
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 4
Subquery_cache_miss 4
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 22
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 8
Handler_read_rnd_deleted 0
Handler_read_rnd_next 41
drop table t1,t2;
#test of flattening subquery optimisations and cache
create table t0 (a int);
insert into t0 values (9),(8),(7),(6),(5),(4),(3),(2),(1),(0);
create table t1(a int, b int);
insert into t1 values
(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);
create table t2 (pk int, a int, primary key(pk));
insert into t2 select a,a from t0; set optimizer_switch='default,semijoin=on,materialization=on,subquery_cache=on';
flush global status;
flush status;
select * from t1 where a in (select pk from t2);
a b 00 11 22 00 11 22 00 11 22 00 11 22 00 11 22
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 15
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 16
alter table t2 drop primary key; set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=off';
explain select * from t1 where a in (select pk from t2);
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
flush global status;
flush status;
select * from t1 where a in (select pk from t2);
a b 00 11 22 00 11 22 00 11 22 00 11 22 00 11 22
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 0
Subquery_cache_miss 0
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 151 set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on';
explain select * from t1 where a in (select pk from t2);
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
flush global status;
flush status;
select * from t1 where a in (select pk from t2);
a b 00 11 22 00 11 22 00 11 22 00 11 22 00 11 22
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 12
Subquery_cache_miss 3
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 15
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 43 set optimizer_switch='default,semijoin=off,materialization=on,subquery_cache=on';
explain select * from t1 where a in (select pk from t2);
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10
flush global status;
flush status;
select * from t1 where a in (select pk from t2);
a b 00 11 22 00 11 22 00 11 22 00 11 22 00 11 22
show status like "subquery_cache%";
Variable_name Value
Subquery_cache_hit 12
Subquery_cache_miss 3
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 18
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 27
drop table t0,t1,t2; set optimizer_switch='default';
#launchpad BUG#608834
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (10,7,8,'01:27:35','v','v');
INSERT INTO `t2` VALUES (11,1,9,'19:48:31','r','r');
INSERT INTO `t2` VALUES (12,5,9,'00:00:00','a','a');
INSERT INTO `t2` VALUES (13,3,186,'19:53:05','m','m');
INSERT INTO `t2` VALUES (14,6,NULL,'19:18:56','y','y');
INSERT INTO `t2` VALUES (15,92,2,'10:55:12','j','j');
INSERT INTO `t2` VALUES (16,7,3,'00:25:00','d','d');
INSERT INTO `t2` VALUES (17,NULL,0,'12:35:47','z','z');
INSERT INTO `t2` VALUES (18,3,133,'19:53:03','e','e');
INSERT INTO `t2` VALUES (19,5,1,'17:53:30','h','h');
INSERT INTO `t2` VALUES (20,1,8,'11:35:49','b','b');
INSERT INTO `t2` VALUES (21,2,5,NULL,'s','s');
INSERT INTO `t2` VALUES (22,NULL,5,'06:01:40','e','e');
INSERT INTO `t2` VALUES (23,1,8,'05:45:11','j','j');
INSERT INTO `t2` VALUES (24,0,6,'00:00:00','e','e');
INSERT INTO `t2` VALUES (25,210,51,'00:00:00','f','f');
INSERT INTO `t2` VALUES (26,8,4,'06:11:01','v','v');
INSERT INTO `t2` VALUES (27,7,7,'13:02:46','x','x');
INSERT INTO `t2` VALUES (28,5,6,'21:44:25','m','m');
INSERT INTO `t2` VALUES (29,NULL,4,'22:43:58','c','c');
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (1,NULL,2,'11:28:45','w','w');
INSERT INTO `t1` VALUES (2,7,9,'20:25:14','m','m');
INSERT INTO `t1` VALUES (3,9,3,'13:47:24','m','m');
INSERT INTO `t1` VALUES (4,7,9,'19:24:11','k','k');
INSERT INTO `t1` VALUES (5,4,NULL,'15:59:13','r','r');
INSERT INTO `t1` VALUES (6,2,9,'00:00:00','t','t');
INSERT INTO `t1` VALUES (7,6,3,'15:15:04','j','j');
INSERT INTO `t1` VALUES (8,8,8,'11:32:06','u','u');
INSERT INTO `t1` VALUES (9,NULL,8,'18:32:33','h','h');
INSERT INTO `t1` VALUES (10,5,53,'15:19:25','o','o');
INSERT INTO `t1` VALUES (11,NULL,0,'19:03:19',NULL,NULL);
INSERT INTO `t1` VALUES (12,6,5,'00:39:46','k','k');
INSERT INTO `t1` VALUES (13,188,166,NULL,'e','e');
INSERT INTO `t1` VALUES (14,2,3,'00:00:00','n','n');
INSERT INTO `t1` VALUES (15,1,0,'13:12:11','t','t');
INSERT INTO `t1` VALUES (16,1,1,'04:56:48','c','c');
INSERT INTO `t1` VALUES (17,0,9,'19:56:05','m','m');
INSERT INTO `t1` VALUES (18,9,5,'19:35:19','y','y');
INSERT INTO `t1` VALUES (19,NULL,6,'05:03:03','f','f');
INSERT INTO `t1` VALUES (20,4,2,'18:38:59','d','d'); set @@optimizer_switch='subquery_cache=off'; /* cache is off */ SELECT (
SELECT 4
FROM DUAL ) AS field1 , SUM( DISTINCT table1 . `pk` ) AS field2 , (
SELECT MAX( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1
FROM ( t1 AS SUBQUERY2_t1 INNER JOIN t1 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk` ) )
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
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` ) )
WHERE ( table2 . `col_varchar_nokey` NOT IN (
SELECT 'd' UNION
SELECT 'u' ) ) OR table3 . `col_varchar_nokey` <= table1 . `col_varchar_key`
GROUP BY field1, field3, field4, field5, field6
ORDER BY table1 . `col_int_key` , field1, field2, field3, field4, field5, field6 ;
field1 field2 field3 field4 field5 field6 45915:59:13 NULL cr 45915:59:13 NULL dr 45915:59:13 NULL er 45915:59:13 NULL fr 45915:59:13 NULL hr 45915:59:13 NULL jr 45915:59:13 NULL mr 45915:59:13 NULL rr 45915:59:13 NULL yr 411919:03:190 NULL 415913:12:110 ct 415913:12:110 dt 415913:12:110 et 415913:12:110 ft 415913:12:110 ht 415913:12:110 jt 415913:12:110 mt 415913:12:110 rt 415913:12:110 yt 416904:56:481 cc 416904:56:481 ec 416904:56:481 fc 416904:56:481 hc 416904:56:481 jc 416904:56:481 mc 416904:56:481 rc 416904:56:481 yc 41911:28:452 cw 41911:28:452 dw 41911:28:452 ew 41911:28:452 fw 41911:28:452 hw 41911:28:452 jw 41911:28:452 mw 41911:28:452 rw 41911:28:452 yw 420918:38:592 cd 420918:38:592 dd 420918:38:592 ed 420918:38:592 fd 420918:38:592 hd 420918:38:592 jd 420918:38:592 md 420918:38:592 rd 420918:38:592 yd 43913:47:243 cm 43913:47:243 dm 43913:47:243 em 43913:47:243 fm 43913:47:243 hm 43913:47:243 jm 43913:47:243 mm 43913:47:243 rm 43913:47:243 ym 47915:15:043 cj 47915:15:043 dj 47915:15:043 ej 47915:15:043 fj 47915:15:043 hj 47915:15:043 jj 47915:15:043 mj 47915:15:043 rj 47915:15:043 yj 414900:00:003 cn 414900:00:003 dn 414900:00:003 en 414900:00:003 fn 414900:00:003 hn 414900:00:003 jn 414900:00:003 mn 414900:00:003 rn 414900:00:003 yn 412900:39:465 ck 412900:39:465 dk 412900:39:465 ek 412900:39:465 fk 412900:39:465 hk 412900:39:465 jk 412900:39:465 mk 412900:39:465 rk 412900:39:465 yk 418919:35:195 cy 418919:35:195 dy 418919:35:195 ey 418919:35:195 fy 418919:35:195 hy 418919:35:195 jy 418919:35:195 my 418919:35:195 ry 418919:35:195 yy 419905:03:036 cf 419905:03:036 df 419905:03:036 ef 419905:03:036 ff 419905:03:036 hf 419905:03:036 jf 419905:03:036 mf 419905:03:036 rf 419905:03:036 yf 48911:32:068 cu 48911:32:068 du 48911:32:068 eu 48911:32:068 fu 48911:32:068 hu 48911:32:068 ju 48911:32:068 mu 48911:32:068 ru 48911:32:068 yu 49818:32:338 ch 49818:32:338 dh 49818:32:338 eh 49818:32:338 fh 49818:32:338 hh 49818:32:338 jh 49818:32:338 mh 49818:32:338 rh 49818:32:338 yh 42920:25:149 cm 42920:25:149 dm 42920:25:149 em 42920:25:149 fm 42920:25:149 hm 42920:25:149 jm 42920:25:149 mm 42920:25:149 rm 42920:25:149 ym 44919:24:119 ck 44919:24:119 dk 44919:24:119 ek 44919:24:119 fk 44919:24:119 hk 44919:24:119 jk 44919:24:119 mk 44919:24:119 rk 44919:24:119 yk 46900:00:009 ct 46900:00:009 dt 46900:00:009 et 46900:00:009 ft 46900:00:009 ht 46900:00:009 jt 46900:00:009 mt 46900:00:009 rt 46900:00:009 yt 417919:56:059 cm 417919:56:059 dm 417919:56:059 em 417919:56:059 fm 417919:56:059 hm 417919:56:059 jm 417919:56:059 mm 417919:56:059 rm 417919:56:059 ym 410915:19:2553 co 410915:19:2553 do 410915:19:2553 eo 410915:19:2553 fo 410915:19:2553 ho 410915:19:2553 jo 410915:19:2553 mo 410915:19:2553 ro 410915:19:2553 yo 4139 NULL 166 ce 4139 NULL 166 de 4139 NULL 166 ee 4139 NULL 166 fe 4139 NULL 166 he 4139 NULL 166 je 4139 NULL 166 me 4139 NULL 166 re 4139 NULL 166 ye set @@optimizer_switch='subquery_cache=on'; /* cache is on */ SELECT (
SELECT 4
FROM DUAL ) AS field1 , SUM( DISTINCT table1 . `pk` ) AS field2 , (
SELECT MAX( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1
FROM ( t1 AS SUBQUERY2_t1 INNER JOIN t1 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk` ) )
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
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` ) )
WHERE ( table2 . `col_varchar_nokey` NOT IN (
SELECT 'd' UNION
SELECT 'u' ) ) OR table3 . `col_varchar_nokey` <= table1 . `col_varchar_key`
GROUP BY field1, field3, field4, field5, field6
ORDER BY table1 . `col_int_key` , field1, field2, field3, field4, field5, field6 ;
field1 field2 field3 field4 field5 field6 45915:59:13 NULL cr 45915:59:13 NULL dr 45915:59:13 NULL er 45915:59:13 NULL fr 45915:59:13 NULL hr 45915:59:13 NULL jr 45915:59:13 NULL mr 45915:59:13 NULL rr 45915:59:13 NULL yr 411919:03:190 NULL 415913:12:110 ct 415913:12:110 dt 415913:12:110 et 415913:12:110 ft 415913:12:110 ht 415913:12:110 jt 415913:12:110 mt 415913:12:110 rt 415913:12:110 yt 416904:56:481 cc 416904:56:481 ec 416904:56:481 fc 416904:56:481 hc 416904:56:481 jc 416904:56:481 mc 416904:56:481 rc 416904:56:481 yc 41911:28:452 cw 41911:28:452 dw 41911:28:452 ew 41911:28:452 fw 41911:28:452 hw 41911:28:452 jw 41911:28:452 mw 41911:28:452 rw 41911:28:452 yw 420918:38:592 cd 420918:38:592 dd 420918:38:592 ed 420918:38:592 fd 420918:38:592 hd 420918:38:592 jd 420918:38:592 md 420918:38:592 rd 420918:38:592 yd 43913:47:243 cm 43913:47:243 dm 43913:47:243 em 43913:47:243 fm 43913:47:243 hm 43913:47:243 jm 43913:47:243 mm 43913:47:243 rm 43913:47:243 ym 47915:15:043 cj 47915:15:043 dj 47915:15:043 ej 47915:15:043 fj 47915:15:043 hj 47915:15:043 jj 47915:15:043 mj 47915:15:043 rj 47915:15:043 yj 414900:00:003 cn 414900:00:003 dn 414900:00:003 en 414900:00:003 fn 414900:00:003 hn 414900:00:003 jn 414900:00:003 mn 414900:00:003 rn 414900:00:003 yn 412900:39:465 ck 412900:39:465 dk 412900:39:465 ek 412900:39:465 fk 412900:39:465 hk 412900:39:465 jk 412900:39:465 mk 412900:39:465 rk 412900:39:465 yk 418919:35:195 cy 418919:35:195 dy 418919:35:195 ey 418919:35:195 fy 418919:35:195 hy 418919:35:195 jy 418919:35:195 my 418919:35:195 ry 418919:35:195 yy 419905:03:036 cf 419905:03:036 df 419905:03:036 ef 419905:03:036 ff 419905:03:036 hf 419905:03:036 jf 419905:03:036 mf 419905:03:036 rf 419905:03:036 yf 48911:32:068 cu 48911:32:068 du 48911:32:068 eu 48911:32:068 fu 48911:32:068 hu 48911:32:068 ju 48911:32:068 mu 48911:32:068 ru 48911:32:068 yu 49818:32:338 ch 49818:32:338 dh 49818:32:338 eh 49818:32:338 fh 49818:32:338 hh 49818:32:338 jh 49818:32:338 mh 49818:32:338 rh 49818:32:338 yh 42920:25:149 cm 42920:25:149 dm 42920:25:149 em 42920:25:149 fm 42920:25:149 hm 42920:25:149 jm 42920:25:149 mm 42920:25:149 rm 42920:25:149 ym 44919:24:119 ck 44919:24:119 dk 44919:24:119 ek 44919:24:119 fk 44919:24:119 hk 44919:24:119 jk 44919:24:119 mk 44919:24:119 rk 44919:24:119 yk 46900:00:009 ct 46900:00:009 dt 46900:00:009 et 46900:00:009 ft 46900:00:009 ht 46900:00:009 jt 46900:00:009 mt 46900:00:009 rt 46900:00:009 yt 417919:56:059 cm 417919:56:059 dm 417919:56:059 em 417919:56:059 fm 417919:56:059 hm 417919:56:059 jm 417919:56:059 mm 417919:56:059 rm 417919:56:059 ym 410915:19:2553 co 410915:19:2553 do 410915:19:2553 eo 410915:19:2553 fo 410915:19:2553 ho 410915:19:2553 jo 410915:19:2553 mo 410915:19:2553 ro 410915:19:2553 yo 4139 NULL 166 ce 4139 NULL 166 de 4139 NULL 166 ee 4139 NULL 166 fe 4139 NULL 166 he 4139 NULL 166 je 4139 NULL 166 me 4139 NULL 166 re 4139 NULL 166 ye
drop table t1,t2; set @@optimizer_switch= default;
#launchpad BUG#609045
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
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');
INSERT INTO `t1` VALUES (2,7,9,'2001-09-19','2001-09-19','20:25:14','20:25:14',NULL,NULL,'m','m');
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');
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');
INSERT INTO `t1` VALUES (5,4,NULL,'2002-07-19','2002-07-19','15:59:13','15:59:13',NULL,NULL,'r','r');
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');
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');
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');
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');
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');
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);
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');
INSERT INTO `t1` VALUES (13,188,166,'2002-11-27','2002-11-27',NULL,NULL,NULL,NULL,'e','e');
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');
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');
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');
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');
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');
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');
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');
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);
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');
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');
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');
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');
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');
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');
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');
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');
INSERT INTO `t2` VALUES (18,3,133,'1900-01-01','1900-01-01','19:53:03','19:53:03',NULL,NULL,'e','e');
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');
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');
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');
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');
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');
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');
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');
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');
INSERT INTO `t2` VALUES (27,7,7,'2008-04-07','2008-04-07','13:02:46','13:02:46',NULL,NULL,'x','x');
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');
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');
CREATE TABLE `t3` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);
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');
CREATE TABLE `t4` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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);
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');
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');
INSERT INTO `t4` VALUES (32,105,248,'2009-06-27','2009-06-27','16:32:56','16:32:56',NULL,NULL,'u','u');
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');
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');
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');
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');
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');
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');
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');
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');
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);
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');
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');
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');
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');
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');
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');
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');
INSERT INTO `t4` VALUES (49,NULL,8,'2003-03-02','2003-03-02','11:46:03','11:46:03',NULL,NULL,'x','x');
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');
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');
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');
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');
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');
INSERT INTO `t4` VALUES (55,NULL,NULL,'2005-09-16','2005-09-16','00:17:44','00:17:44',NULL,NULL,'m','m');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
INSERT INTO `t4` VALUES (68,NULL,2,'2004-09-14','2004-09-14','11:41:50','11:41:50',NULL,NULL,'e','e');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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);
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');
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');
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');
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');
INSERT INTO `t4` VALUES (83,9,5,'2002-03-03','2002-03-03','10:54:27','10:54:27',NULL,NULL,'i','i');
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');
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');
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');
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');
INSERT INTO `t4` VALUES (88,119,147,'2005-02-16','2005-02-16','00:00:00','00:00:00',NULL,NULL,'c','c');
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');
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');
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);
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');
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');
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');
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');
INSERT INTO `t4` VALUES (96,5,8,NULL,NULL,'00:22:24','00:22:24',NULL,NULL,'s','s');
INSERT INTO `t4` VALUES (97,7,0,'2006-02-15','2006-02-15','10:09:31','10:09:31',NULL,NULL,'z','z');
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');
INSERT INTO `t4` VALUES (99,7,8,'2003-12-24','2003-12-24','18:45:35','18:45:35',NULL,NULL,'c','c');
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'); SET @@optimizer_switch='subquery_cache=off'; /* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
GROUP BY field10 ;
COUNT( DISTINCT table2 .`col_int_key` ) (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) field10 1 NULL c 1 NULL d 1 NULL e 1 NULL f 1 NULL h 1 NULL j 2 NULL k 2 NULL m 1 NULL n 1 NULL o 0 NULL r 2 NULL t 1 NULL u 1 NULL w 1 NULL y
Warnings:
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'a' SET @@optimizer_switch='subquery_cache=on'; /* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
GROUP BY field10 ;
COUNT( DISTINCT table2 .`col_int_key` ) (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) field10 1 NULL c 1 NULL d 1 NULL e 1 NULL f 1 NULL h 1 NULL j 2 NULL k 2 NULL m 1 NULL n 1 NULL o 0 NULL r 2 NULL t 1 NULL u 1 NULL w 1 NULL y
Warnings:
Warning 1292 Truncated incorrect BOOLEAN value: 'a'
Warning 1292 Truncated incorrect BOOLEAN value: 'b'
Warning 1292 Truncated incorrect BOOLEAN value: 'c'
Warning 1292 Truncated incorrect BOOLEAN value: 'd'
Warning 1292 Truncated incorrect BOOLEAN value: 'e'
Warning 1292 Truncated incorrect BOOLEAN value: 'f'
Warning 1292 Truncated incorrect BOOLEAN value: 'g'
Warning 1292 Truncated incorrect BOOLEAN value: 'h'
Warning 1292 Truncated incorrect BOOLEAN value: 'i'
Warning 1292 Truncated incorrect BOOLEAN value: 'j'
Warning 1292 Truncated incorrect BOOLEAN value: 'k'
Warning 1292 Truncated incorrect BOOLEAN value: 'l'
Warning 1292 Truncated incorrect BOOLEAN value: 'm'
Warning 1292 Truncated incorrect BOOLEAN value: 'n'
Warning 1292 Truncated incorrect BOOLEAN value: 'o'
Warning 1292 Truncated incorrect BOOLEAN value: 'p'
Warning 1292 Truncated incorrect BOOLEAN value: 'q'
Warning 1292 Truncated incorrect BOOLEAN value: 'r'
Warning 1292 Truncated incorrect BOOLEAN value: 's'
Warning 1292 Truncated incorrect BOOLEAN value: 't'
Warning 1292 Truncated incorrect BOOLEAN value: 'u'
Warning 1292 Truncated incorrect BOOLEAN value: 'v'
Warning 1292 Truncated incorrect BOOLEAN value: 'w'
Warning 1292 Truncated incorrect BOOLEAN value: 'x'
Warning 1292 Truncated incorrect BOOLEAN value: 'y'
Warning 1292 Truncated incorrect BOOLEAN value: 'z'
drop table t1,t2,t3,t4; set @@optimizer_switch= default;
#launchpad BUG#609045
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (10,7,8,'v','v');
INSERT INTO `t2` VALUES (11,1,9,'r','r');
INSERT INTO `t2` VALUES (12,5,9,'a','a');
INSERT INTO `t2` VALUES (13,3,186,'m','m');
INSERT INTO `t2` VALUES (14,6,NULL,'y','y');
INSERT INTO `t2` VALUES (15,92,2,'j','j');
INSERT INTO `t2` VALUES (16,7,3,'d','d');
INSERT INTO `t2` VALUES (17,NULL,0,'z','z');
INSERT INTO `t2` VALUES (18,3,133,'e','e');
INSERT INTO `t2` VALUES (19,5,1,'h','h');
INSERT INTO `t2` VALUES (20,1,8,'b','b');
INSERT INTO `t2` VALUES (21,2,5,'s','s');
INSERT INTO `t2` VALUES (22,NULL,5,'e','e');
INSERT INTO `t2` VALUES (23,1,8,'j','j');
INSERT INTO `t2` VALUES (24,0,6,'e','e');
INSERT INTO `t2` VALUES (25,210,51,'f','f');
INSERT INTO `t2` VALUES (26,8,4,'v','v');
INSERT INTO `t2` VALUES (27,7,7,'x','x');
INSERT INTO `t2` VALUES (28,5,6,'m','m');
INSERT INTO `t2` VALUES (29,NULL,4,'c','c');
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (1,NULL,2,'w','w');
INSERT INTO `t1` VALUES (2,7,9,'m','m');
INSERT INTO `t1` VALUES (3,9,3,'m','m');
INSERT INTO `t1` VALUES (4,7,9,'k','k');
INSERT INTO `t1` VALUES (5,4,NULL,'r','r');
INSERT INTO `t1` VALUES (6,2,9,'t','t');
INSERT INTO `t1` VALUES (7,6,3,'j','j');
INSERT INTO `t1` VALUES (8,8,8,'u','u');
INSERT INTO `t1` VALUES (9,NULL,8,'h','h');
INSERT INTO `t1` VALUES (10,5,53,'o','o');
INSERT INTO `t1` VALUES (11,NULL,0,NULL,NULL);
INSERT INTO `t1` VALUES (12,6,5,'k','k');
INSERT INTO `t1` VALUES (13,188,166,'e','e');
INSERT INTO `t1` VALUES (14,2,3,'n','n');
INSERT INTO `t1` VALUES (15,1,0,'t','t');
INSERT INTO `t1` VALUES (16,1,1,'c','c');
INSERT INTO `t1` VALUES (17,0,9,'m','m');
INSERT INTO `t1` VALUES (18,9,5,'y','y');
INSERT INTO `t1` VALUES (19,NULL,6,'f','f');
INSERT INTO `t1` VALUES (20,4,2,'d','d'); SET @@optimizer_switch = 'subquery_cache=off'; /* cache is off */ SELECT SUM( DISTINCT table1 .`pk` ) , (
SELECT MAX( `col_int_nokey` )
FROM t1
WHERE table1 .`pk` ) field3
FROM t1 table1
JOIN (
t1 table2
JOIN t2 table3
ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
)
ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey`
GROUP BY field3 ;
SUM( DISTINCT table1 .`pk` ) field3 210188 SET @@optimizer_switch = 'subquery_cache=on'; /* cache is on */ SELECT SUM( DISTINCT table1 .`pk` ) , (
SELECT MAX( `col_int_nokey` )
FROM t1
WHERE table1 .`pk` ) field3
FROM t1 table1
JOIN (
t1 table2
JOIN t2 table3
ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
)
ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey`
GROUP BY field3 ;
SUM( DISTINCT table1 .`pk` ) field3 210188
drop table t1,t2; set @@optimizer_switch= default;
#launchpad BUG#609052
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (10,7,8,'01:27:35','v','v');
INSERT INTO `t2` VALUES (11,1,9,'19:48:31','r','r');
INSERT INTO `t2` VALUES (12,5,9,'00:00:00','a','a');
INSERT INTO `t2` VALUES (13,3,186,'19:53:05','m','m');
INSERT INTO `t2` VALUES (14,6,NULL,'19:18:56','y','y');
INSERT INTO `t2` VALUES (15,92,2,'10:55:12','j','j');
INSERT INTO `t2` VALUES (16,7,3,'00:25:00','d','d');
INSERT INTO `t2` VALUES (17,NULL,0,'12:35:47','z','z');
INSERT INTO `t2` VALUES (18,3,133,'19:53:03','e','e');
INSERT INTO `t2` VALUES (19,5,1,'17:53:30','h','h');
INSERT INTO `t2` VALUES (20,1,8,'11:35:49','b','b');
INSERT INTO `t2` VALUES (21,2,5,NULL,'s','s');
INSERT INTO `t2` VALUES (22,NULL,5,'06:01:40','e','e');
INSERT INTO `t2` VALUES (23,1,8,'05:45:11','j','j');
INSERT INTO `t2` VALUES (24,0,6,'00:00:00','e','e');
INSERT INTO `t2` VALUES (25,210,51,'00:00:00','f','f');
INSERT INTO `t2` VALUES (26,8,4,'06:11:01','v','v');
INSERT INTO `t2` VALUES (27,7,7,'13:02:46','x','x');
INSERT INTO `t2` VALUES (28,5,6,'21:44:25','m','m');
INSERT INTO `t2` VALUES (29,NULL,4,'22:43:58','c','c');
CREATE TABLE `t4` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `t4` VALUES (1,6,NULL,NULL,'r','r');
INSERT INTO `t4` VALUES (2,8,0,'14:34:45','c','c');
INSERT INTO `t4` VALUES (3,6,0,'11:49:48','o','o');
INSERT INTO `t4` VALUES (4,6,7,'18:12:55','c','c');
INSERT INTO `t4` VALUES (5,3,8,'18:30:05','d','d');
INSERT INTO `t4` VALUES (6,9,4,'14:19:30','v','v');
INSERT INTO `t4` VALUES (7,2,6,'05:20:04','m','m');
INSERT INTO `t4` VALUES (8,1,5,'20:29:31','j','j');
INSERT INTO `t4` VALUES (9,8,NULL,'07:08:09','f','f');
INSERT INTO `t4` VALUES (10,0,NULL,'14:49:14','n','n');
INSERT INTO `t4` VALUES (11,9,8,'00:00:00','z','z');
INSERT INTO `t4` VALUES (12,8,8,'09:58:06','h','h');
INSERT INTO `t4` VALUES (13,NULL,8,NULL,'q','q');
INSERT INTO `t4` VALUES (14,0,1,'18:24:16','w','w');
INSERT INTO `t4` VALUES (15,5,1,'17:39:57','z','z');
INSERT INTO `t4` VALUES (16,1,5,'08:23:21','j','j');
INSERT INTO `t4` VALUES (17,1,2,NULL,'a','a');
INSERT INTO `t4` VALUES (18,6,7,'21:50:46','m','m');
INSERT INTO `t4` VALUES (19,6,6,'12:33:17','n','n');
INSERT INTO `t4` VALUES (20,1,4,'03:06:43','e','e');
INSERT INTO `t4` VALUES (21,8,7,'03:46:14','u','u');
INSERT INTO `t4` VALUES (22,1,0,'20:34:52','s','s');
INSERT INTO `t4` VALUES (23,0,9,NULL,'u','u');
INSERT INTO `t4` VALUES (24,4,3,'10:41:20','r','r');
INSERT INTO `t4` VALUES (25,9,5,'08:43:11','g','g');
INSERT INTO `t4` VALUES (26,8,1,NULL,'o','o');
INSERT INTO `t4` VALUES (27,5,1,'10:17:51','w','w');
INSERT INTO `t4` VALUES (28,9,5,'06:34:09','b','b');
INSERT INTO `t4` VALUES (29,5,9,'21:22:47',NULL,NULL);
INSERT INTO `t4` VALUES (30,NULL,2,'04:02:32','y','y');
INSERT INTO `t4` VALUES (31,NULL,5,'02:33:14','y','y');
INSERT INTO `t4` VALUES (32,105,248,'16:32:56','u','u');
INSERT INTO `t4` VALUES (33,0,0,'21:32:42','p','p');
INSERT INTO `t4` VALUES (34,3,8,'23:04:47','s','s');
INSERT INTO `t4` VALUES (35,1,1,'22:05:43','e','e');
INSERT INTO `t4` VALUES (36,75,255,'02:05:45','d','d');
INSERT INTO `t4` VALUES (37,9,9,'00:00:00','d','d');
INSERT INTO `t4` VALUES (38,7,9,'18:09:07','c','c');
INSERT INTO `t4` VALUES (39,NULL,3,'10:54:06','b','b');
INSERT INTO `t4` VALUES (40,NULL,9,'23:15:50','t','t');
INSERT INTO `t4` VALUES (41,4,6,'10:17:40',NULL,NULL);
INSERT INTO `t4` VALUES (42,0,4,'03:37:09','y','y');
INSERT INTO `t4` VALUES (43,204,60,'22:26:06','c','c');
INSERT INTO `t4` VALUES (44,0,7,'17:10:38','d','d');
INSERT INTO `t4` VALUES (45,9,1,'00:00:00','x','x');
INSERT INTO `t4` VALUES (46,8,6,'17:08:49','p','p');
INSERT INTO `t4` VALUES (47,7,4,'19:04:40','e','e');
INSERT INTO `t4` VALUES (48,8,NULL,'20:53:28','g','g');
INSERT INTO `t4` VALUES (49,NULL,8,'11:46:03','x','x');
INSERT INTO `t4` VALUES (50,6,0,NULL,'s','s');
INSERT INTO `t4` VALUES (51,5,8,'10:58:07','e','e');
INSERT INTO `t4` VALUES (52,2,151,'00:00:00','l','l');
INSERT INTO `t4` VALUES (53,3,7,'09:43:15','p','p');
INSERT INTO `t4` VALUES (54,7,6,'21:40:32','h','h');
INSERT INTO `t4` VALUES (55,NULL,NULL,'00:17:44','m','m');
INSERT INTO `t4` VALUES (56,145,23,'16:47:26','n','n');
INSERT INTO `t4` VALUES (57,0,2,'00:00:00','v','v');
INSERT INTO `t4` VALUES (58,1,4,'05:25:59','b','b');
INSERT INTO `t4` VALUES (59,7,NULL,'00:00:00','x','x');
INSERT INTO `t4` VALUES (60,3,NULL,'20:33:04','r','r');
INSERT INTO `t4` VALUES (61,NULL,77,'00:46:12','t','t');
INSERT INTO `t4` VALUES (62,2,NULL,'00:00:00','w','w');
INSERT INTO `t4` VALUES (63,2,NULL,'02:13:59','w','w');
INSERT INTO `t4` VALUES (64,2,7,'02:54:47','k','k');
INSERT INTO `t4` VALUES (65,8,1,'18:13:59','a','a');
INSERT INTO `t4` VALUES (66,6,9,'13:53:08','t','t');
INSERT INTO `t4` VALUES (67,1,6,'22:21:30','z','z');
INSERT INTO `t4` VALUES (68,NULL,2,'11:41:50','e','e');
INSERT INTO `t4` VALUES (69,1,3,'15:20:02','q','q');
INSERT INTO `t4` VALUES (70,0,0,NULL,'e','e');
INSERT INTO `t4` VALUES (71,4,NULL,NULL,'v','v');
INSERT INTO `t4` VALUES (72,1,6,'07:51:52','d','d');
INSERT INTO `t4` VALUES (73,1,3,'00:00:00','u','u');
INSERT INTO `t4` VALUES (74,27,195,NULL,'o','o');
INSERT INTO `t4` VALUES (75,4,5,NULL,'b','b');
INSERT INTO `t4` VALUES (76,6,2,'12:31:05','c','c');
INSERT INTO `t4` VALUES (77,2,7,'00:00:00','q','q');
INSERT INTO `t4` VALUES (78,248,25,'01:16:45',NULL,NULL);
INSERT INTO `t4` VALUES (79,NULL,NULL,'20:38:54','h','h');
INSERT INTO `t4` VALUES (80,9,0,'00:30:15','d','d');
INSERT INTO `t4` VALUES (81,75,98,'23:46:36','w','w');
INSERT INTO `t4` VALUES (82,2,6,'19:03:13','m','m');
INSERT INTO `t4` VALUES (83,9,5,'10:54:27','i','i');
INSERT INTO `t4` VALUES (84,4,0,'00:25:47','w','w');
INSERT INTO `t4` VALUES (85,0,3,'08:44:27','f','f');
INSERT INTO `t4` VALUES (86,0,1,'08:15:38','k','k');
INSERT INTO `t4` VALUES (87,1,1,'19:56:21','v','v');
INSERT INTO `t4` VALUES (88,119,147,'00:00:00','c','c');
INSERT INTO `t4` VALUES (89,1,3,'20:50:52','y','y');
INSERT INTO `t4` VALUES (90,7,3,'03:54:39','h','h');
INSERT INTO `t4` VALUES (91,2,NULL,'23:58:17',NULL,NULL);
INSERT INTO `t4` VALUES (92,7,2,'12:54:58','t','t');
INSERT INTO `t4` VALUES (93,2,1,'04:02:43','l','l');
INSERT INTO `t4` VALUES (94,6,8,'11:31:12','a','a');
INSERT INTO `t4` VALUES (95,4,8,'20:20:04','r','r');
INSERT INTO `t4` VALUES (96,5,8,'00:22:24','s','s');
INSERT INTO `t4` VALUES (97,7,0,'10:09:31','z','z');
INSERT INTO `t4` VALUES (98,1,1,NULL,'j','j');
INSERT INTO `t4` VALUES (99,7,8,'18:45:35','c','c');
INSERT INTO `t4` VALUES (100,2,5,'11:49:25','f','f');
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (1,NULL,2,'11:28:45','w','w');
INSERT INTO `t1` VALUES (2,7,9,'20:25:14','m','m');
INSERT INTO `t1` VALUES (3,9,3,'13:47:24','m','m');
INSERT INTO `t1` VALUES (4,7,9,'19:24:11','k','k');
INSERT INTO `t1` VALUES (5,4,NULL,'15:59:13','r','r');
INSERT INTO `t1` VALUES (6,2,9,'00:00:00','t','t');
INSERT INTO `t1` VALUES (7,6,3,'15:15:04','j','j');
INSERT INTO `t1` VALUES (8,8,8,'11:32:06','u','u');
INSERT INTO `t1` VALUES (9,NULL,8,'18:32:33','h','h');
INSERT INTO `t1` VALUES (10,5,53,'15:19:25','o','o');
INSERT INTO `t1` VALUES (11,NULL,0,'19:03:19',NULL,NULL);
INSERT INTO `t1` VALUES (12,6,5,'00:39:46','k','k');
INSERT INTO `t1` VALUES (13,188,166,NULL,'e','e');
INSERT INTO `t1` VALUES (14,2,3,'00:00:00','n','n');
INSERT INTO `t1` VALUES (15,1,0,'13:12:11','t','t');
INSERT INTO `t1` VALUES (16,1,1,'04:56:48','c','c');
INSERT INTO `t1` VALUES (17,0,9,'19:56:05','m','m');
INSERT INTO `t1` VALUES (18,9,5,'19:35:19','y','y');
INSERT INTO `t1` VALUES (19,NULL,6,'05:03:03','f','f');
INSERT INTO `t1` VALUES (20,4,2,'18:38:59','d','d');
CREATE TABLE `t3` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `t3` VALUES (10,8,8,'18:27:58',NULL,NULL);
CREATE TABLE `t5` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `t5` VALUES (1,1,7,'01:13:38','f','f'); SET @@optimizer_switch='subquery_cache=off'; /* 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 , (
SELECT MAX( SUBQUERY1_t2 . `col_int_nokey` ) AS SUBQUERY1_field1
FROM ( t3 AS SUBQUERY1_t1 INNER JOIN t1 AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_key` = SUBQUERY1_t1 . `col_varchar_nokey` ) )
WHERE SUBQUERY1_t2 . `pk` < SUBQUERY1_t2 . `pk` ) AS field6 , COUNT( table1 . `col_varchar_nokey` ) AS field7 , COUNT( table2 . `pk` ) AS field8 , (
SELECT MAX( SUBQUERY2_t1 . `col_int_key` ) AS SUBQUERY2_field1
FROM ( t5 AS SUBQUERY2_t1 LEFT JOIN t2 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `col_int_key` ) )
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
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` ) )
WHERE ( EXISTS (
SELECT SUBQUERY3_t1 . `pk` AS SUBQUERY3_field1
FROM ( t4 AS SUBQUERY3_t1 INNER JOIN t4 AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` ) )
WHERE SUBQUERY3_t1 . `col_int_key` > table3 . `pk` AND SUBQUERY3_t1 . `pk` != table3 . `pk` ) ) AND( table1 . `pk` > 116AND table1 . `pk` < ( 116 + 175 ) OR table1 . `pk` IN (251) ) OR table1 . `col_int_nokey` = table1 . `col_int_nokey`
GROUP BY field4, field6, field9, field10
HAVING field10 = 'c' ;
field1 field2 field3 field4 field5 field6 field7 field8 field9 field10 SET @@optimizer_switch='subquery_cache=on'; /* 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 , (
SELECT MAX( SUBQUERY1_t2 . `col_int_nokey` ) AS SUBQUERY1_field1
FROM ( t3 AS SUBQUERY1_t1 INNER JOIN t1 AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_key` = SUBQUERY1_t1 . `col_varchar_nokey` ) )
WHERE SUBQUERY1_t2 . `pk` < SUBQUERY1_t2 . `pk` ) AS field6 , COUNT( table1 . `col_varchar_nokey` ) AS field7 , COUNT( table2 . `pk` ) AS field8 , (
SELECT MAX( SUBQUERY2_t1 . `col_int_key` ) AS SUBQUERY2_field1
FROM ( t5 AS SUBQUERY2_t1 LEFT JOIN t2 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `col_int_key` ) )
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
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` ) )
WHERE ( EXISTS (
SELECT SUBQUERY3_t1 . `pk` AS SUBQUERY3_field1
FROM ( t4 AS SUBQUERY3_t1 INNER JOIN t4 AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` ) )
WHERE SUBQUERY3_t1 . `col_int_key` > table3 . `pk` AND SUBQUERY3_t1 . `pk` != table3 . `pk` ) ) AND( table1 . `pk` > 116AND table1 . `pk` < ( 116 + 175 ) OR table1 . `pk` IN (251) ) OR table1 . `col_int_nokey` = table1 . `col_int_nokey`
GROUP BY field4, field6, field9, field10
HAVING field10 = 'c' ;
field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
drop table t1,t2,t3,t4,t5; set @@optimizer_switch= default;
#launchpad BUG#609043
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
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');
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');
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
CREATE TABLE `t3` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
CREATE TABLE `t4` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
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'); SET @@optimizer_switch = 'subquery_cache=off'; /* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
GROUP BY field10 ;
COUNT( DISTINCT table2 .`col_int_key` ) (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) field10 1 NULL d 1 NULL f
Warnings:
Warning 1292 Truncated incorrect BOOLEAN value: 'f'
Warning 1292 Truncated incorrect BOOLEAN value: 'f'
Warning 1292 Truncated incorrect BOOLEAN value: 'f' SET @@optimizer_switch = 'subquery_cache=on'; /* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
GROUP BY field10 ;
COUNT( DISTINCT table2 .`col_int_key` ) (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) field10 1 NULL d 1 NULL f
Warnings:
Warning 1292 Truncated incorrect BOOLEAN value: 'f'
Warning 1292 Truncated incorrect BOOLEAN value: 'f'
Warning 1292 Truncated incorrect BOOLEAN value: 'f'
drop table t1,t2,t3,t4; set @@optimizer_switch= default;
#launchpad BUG#611625
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (1,NULL,'w');
INSERT INTO `t1` VALUES (2,7,'m');
INSERT INTO `t1` VALUES (3,9,'m');
INSERT INTO `t1` VALUES (4,7,'k');
INSERT INTO `t1` VALUES (5,4,'r');
INSERT INTO `t1` VALUES (6,2,'t');
INSERT INTO `t1` VALUES (7,6,'j');
INSERT INTO `t1` VALUES (8,8,'u');
INSERT INTO `t1` VALUES (9,NULL,'h');
INSERT INTO `t1` VALUES (10,5,'o');
INSERT INTO `t1` VALUES (11,NULL,NULL);
INSERT INTO `t1` VALUES (12,6,'k');
INSERT INTO `t1` VALUES (13,188,'e');
INSERT INTO `t1` VALUES (14,2,'n');
INSERT INTO `t1` VALUES (15,1,'t');
INSERT INTO `t1` VALUES (16,1,'c');
INSERT INTO `t1` VALUES (17,0,'m');
INSERT INTO `t1` VALUES (18,9,'y');
INSERT INTO `t1` VALUES (19,NULL,'f');
INSERT INTO `t1` VALUES (20,4,'d');
CREATE TABLE `t3` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `t3` VALUES (1,6,'r');
INSERT INTO `t3` VALUES (2,8,'c');
INSERT INTO `t3` VALUES (3,6,'o');
INSERT INTO `t3` VALUES (4,6,'c');
INSERT INTO `t3` VALUES (5,3,'d');
INSERT INTO `t3` VALUES (6,9,'v');
INSERT INTO `t3` VALUES (7,2,'m');
INSERT INTO `t3` VALUES (8,1,'j');
INSERT INTO `t3` VALUES (9,8,'f');
INSERT INTO `t3` VALUES (10,0,'n');
INSERT INTO `t3` VALUES (11,9,'z');
INSERT INTO `t3` VALUES (12,8,'h');
INSERT INTO `t3` VALUES (13,NULL,'q');
INSERT INTO `t3` VALUES (14,0,'w');
INSERT INTO `t3` VALUES (15,5,'z');
INSERT INTO `t3` VALUES (16,1,'j');
INSERT INTO `t3` VALUES (17,1,'a');
INSERT INTO `t3` VALUES (18,6,'m');
INSERT INTO `t3` VALUES (19,6,'n');
INSERT INTO `t3` VALUES (20,1,'e');
INSERT INTO `t3` VALUES (21,8,'u');
INSERT INTO `t3` VALUES (22,1,'s');
INSERT INTO `t3` VALUES (23,0,'u');
INSERT INTO `t3` VALUES (24,4,'r');
INSERT INTO `t3` VALUES (25,9,'g');
INSERT INTO `t3` VALUES (26,8,'o');
INSERT INTO `t3` VALUES (27,5,'w');
INSERT INTO `t3` VALUES (28,9,'b');
INSERT INTO `t3` VALUES (29,5,NULL);
INSERT INTO `t3` VALUES (30,NULL,'y');
INSERT INTO `t3` VALUES (31,NULL,'y');
INSERT INTO `t3` VALUES (32,105,'u');
INSERT INTO `t3` VALUES (33,0,'p');
INSERT INTO `t3` VALUES (34,3,'s');
INSERT INTO `t3` VALUES (35,1,'e');
INSERT INTO `t3` VALUES (36,75,'d');
INSERT INTO `t3` VALUES (37,9,'d');
INSERT INTO `t3` VALUES (38,7,'c');
INSERT INTO `t3` VALUES (39,NULL,'b');
INSERT INTO `t3` VALUES (40,NULL,'t');
INSERT INTO `t3` VALUES (41,4,NULL);
INSERT INTO `t3` VALUES (42,0,'y');
INSERT INTO `t3` VALUES (43,204,'c');
INSERT INTO `t3` VALUES (44,0,'d');
INSERT INTO `t3` VALUES (45,9,'x');
INSERT INTO `t3` VALUES (46,8,'p');
INSERT INTO `t3` VALUES (47,7,'e');
INSERT INTO `t3` VALUES (48,8,'g');
INSERT INTO `t3` VALUES (49,NULL,'x');
INSERT INTO `t3` VALUES (50,6,'s');
INSERT INTO `t3` VALUES (51,5,'e');
INSERT INTO `t3` VALUES (52,2,'l');
INSERT INTO `t3` VALUES (53,3,'p');
INSERT INTO `t3` VALUES (54,7,'h');
INSERT INTO `t3` VALUES (55,NULL,'m');
INSERT INTO `t3` VALUES (56,145,'n');
INSERT INTO `t3` VALUES (57,0,'v');
INSERT INTO `t3` VALUES (58,1,'b');
INSERT INTO `t3` VALUES (59,7,'x');
INSERT INTO `t3` VALUES (60,3,'r');
INSERT INTO `t3` VALUES (61,NULL,'t');
INSERT INTO `t3` VALUES (62,2,'w');
INSERT INTO `t3` VALUES (63,2,'w');
INSERT INTO `t3` VALUES (64,2,'k');
INSERT INTO `t3` VALUES (65,8,'a');
INSERT INTO `t3` VALUES (66,6,'t');
INSERT INTO `t3` VALUES (67,1,'z');
INSERT INTO `t3` VALUES (68,NULL,'e');
INSERT INTO `t3` VALUES (69,1,'q');
INSERT INTO `t3` VALUES (70,0,'e');
INSERT INTO `t3` VALUES (71,4,'v');
INSERT INTO `t3` VALUES (72,1,'d');
INSERT INTO `t3` VALUES (73,1,'u');
INSERT INTO `t3` VALUES (74,27,'o');
INSERT INTO `t3` VALUES (75,4,'b');
INSERT INTO `t3` VALUES (76,6,'c');
INSERT INTO `t3` VALUES (77,2,'q');
INSERT INTO `t3` VALUES (78,248,NULL);
INSERT INTO `t3` VALUES (79,NULL,'h');
INSERT INTO `t3` VALUES (80,9,'d');
INSERT INTO `t3` VALUES (81,75,'w');
INSERT INTO `t3` VALUES (82,2,'m');
INSERT INTO `t3` VALUES (83,9,'i');
INSERT INTO `t3` VALUES (84,4,'w');
INSERT INTO `t3` VALUES (85,0,'f');
INSERT INTO `t3` VALUES (86,0,'k');
INSERT INTO `t3` VALUES (87,1,'v');
INSERT INTO `t3` VALUES (88,119,'c');
INSERT INTO `t3` VALUES (89,1,'y');
INSERT INTO `t3` VALUES (90,7,'h');
INSERT INTO `t3` VALUES (91,2,NULL);
INSERT INTO `t3` VALUES (92,7,'t');
INSERT INTO `t3` VALUES (93,2,'l');
INSERT INTO `t3` VALUES (94,6,'a');
INSERT INTO `t3` VALUES (95,4,'r');
INSERT INTO `t3` VALUES (96,5,'s');
INSERT INTO `t3` VALUES (97,7,'z');
INSERT INTO `t3` VALUES (98,1,'j');
INSERT INTO `t3` VALUES (99,7,'c');
INSERT INTO `t3` VALUES (100,2,'f');
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (10,8,NULL); set optimizer_switch='subquery_cache=off';
SELECT (
SELECT `col_int_nokey`
FROM t3
WHERE table1 .`col_varchar_nokey` ) field13
FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
ORDER BY field13;
field13
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL set optimizer_switch='subquery_cache=on';
SELECT
(SELECT `col_int_nokey`
FROM t3
WHERE table1 .`col_varchar_nokey` ) field13
FROM t2 table1 JOIN t1 table2 ON table2 .`pk`
ORDER BY field13;
field13
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
drop table t1,t2,t3; set @@optimizer_switch= default;
# LP BUG#615760 (part 1: double transformation)
create table t1 (a int);
insert into t1 values (1),(2);
create table t2 (b int);
insert into t2 values (1),(2); set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on';
explain extended
select * from t1 where a in (select b from t2);
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2100.00 Using where
Warnings:
Note 1003/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where <cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`)))
drop table t1,t2; set @@optimizer_switch= default;
# LP BUG#615760 (part 2: incorrect heap table index flags) 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';
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (10,7,8,'v','v');
INSERT INTO `t1` VALUES (11,1,9,'r','r');
INSERT INTO `t1` VALUES (12,5,9,'a','a');
INSERT INTO `t1` VALUES (13,3,186,'m','m');
INSERT INTO `t1` VALUES (14,6,NULL,'y','y');
INSERT INTO `t1` VALUES (15,92,2,'j','j');
INSERT INTO `t1` VALUES (16,7,3,'d','d');
INSERT INTO `t1` VALUES (17,NULL,0,'z','z');
INSERT INTO `t1` VALUES (18,3,133,'e','e');
INSERT INTO `t1` VALUES (19,5,1,'h','h');
INSERT INTO `t1` VALUES (20,1,8,'b','b');
INSERT INTO `t1` VALUES (21,2,5,'s','s');
INSERT INTO `t1` VALUES (22,NULL,5,'e','e');
INSERT INTO `t1` VALUES (23,1,8,'j','j');
INSERT INTO `t1` VALUES (24,0,6,'e','e');
INSERT INTO `t1` VALUES (25,210,51,'f','f');
INSERT INTO `t1` VALUES (26,8,4,'v','v');
INSERT INTO `t1` VALUES (27,7,7,'x','x');
INSERT INTO `t1` VALUES (28,5,6,'m','m');
INSERT INTO `t1` VALUES (29,NULL,4,'c','c');
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (1,NULL,2,'w','w');
INSERT INTO `t2` VALUES (2,7,9,'m','m');
INSERT INTO `t2` VALUES (3,9,3,'m','m');
INSERT INTO `t2` VALUES (4,7,9,'k','k');
INSERT INTO `t2` VALUES (5,4,NULL,'r','r');
INSERT INTO `t2` VALUES (6,2,9,'t','t');
INSERT INTO `t2` VALUES (7,6,3,'j','j');
INSERT INTO `t2` VALUES (8,8,8,'u','u');
INSERT INTO `t2` VALUES (9,NULL,8,'h','h');
INSERT INTO `t2` VALUES (10,5,53,'o','o');
INSERT INTO `t2` VALUES (11,NULL,0,NULL,NULL);
INSERT INTO `t2` VALUES (12,6,5,'k','k');
INSERT INTO `t2` VALUES (13,188,166,'e','e');
INSERT INTO `t2` VALUES (14,2,3,'n','n');
INSERT INTO `t2` VALUES (15,1,0,'t','t');
INSERT INTO `t2` VALUES (16,1,1,'c','c');
INSERT INTO `t2` VALUES (17,0,9,'m','m');
INSERT INTO `t2` VALUES (18,9,5,'y','y');
INSERT INTO `t2` VALUES (19,NULL,6,'f','f');
INSERT INTO `t2` VALUES (20,4,2,'d','d');
SELECT table1 .`col_varchar_nokey`
FROM t2 table1 RIGHT JOIN t1 LEFT JOIN (
SELECT SUBQUERY1_t2 .*
FROM t1 SUBQUERY1_t1 LEFT JOIN t2 SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
SELECT *
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`
WHERE table3 .`col_varchar_key` IN (
SELECT `col_varchar_key`
FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ;
col_varchar_nokey
drop table t1,t2; set @@optimizer_switch= default; set optimizer_switch='subquery_cache=on';
# LP BUG#615378 (incorrect NULL result returning in Item_cache)
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_varchar_key` (`col_varchar_key`)
) DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (10,'v');
INSERT INTO `t1` VALUES (11,'r');
CREATE TABLE `t2` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_varchar_key` (`col_varchar_key`)
) DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (1,'r');
INSERT INTO `t2` VALUES (2,'c');
CREATE TABLE `t3` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_varchar_key` (`col_varchar_key`)
) DEFAULT CHARSET=latin1;
INSERT INTO `t3` VALUES (1,'w');
SELECT SUM( DISTINCT table2 . `pk` ) AS field2 ,
(SELECT SUM( SUBQUERY1_t2 . `pk` ) AS SUBQUERY1_field1
FROM t2 AS SUBQUERY1_t2 STRAIGHT_JOIN
t3 AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `pk` )
WHERE table1 . `col_varchar_key` ) AS field3
FROM ( t1 AS table1 LEFT JOIN
( t2 AS table2 STRAIGHT_JOIN
t3 AS table3 ON (table3 . `pk` = table2 . `pk` ) )
ON (table3 . `col_varchar_key` = table1 . `col_varchar_key` ) )
WHERE ( table1 . `pk` < 5 ) OR ( table1 . `col_varchar_key` IS NOT NULL)
GROUP BY field3
HAVING (field3 <= 'h'AND field2 != 4) ;
field2 field3
drop tables t1, t2, t3;
#
# Test aggregate functions as parameters to subquery cache
#
CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b));
INSERT INTO t1 VALUES
( 1, 1, 1 ),
( 1, 2, 2 ),
( 1, 3, 3 ),
( 1, 4, 6 ),
( 1, 5, 5 ),
( 1, 9, 13 ),
( 2, 1, 6 ),
( 2, 2, 7 ),
( 2, 3, 8 );
SELECT a, AVG(t1.b),
(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c
FROM t1 GROUP BY a;
a AVG(t1.b) t11c 14.00006 22.00007
DROP TABLE t1;
#
# Test of LP BUG#800696 (deleting list of Items (OR arguments)
# in optimization)
# set optimizer_switch='subquery_cache=on,in_to_exists=on';
CREATE TABLE t1 ( f3 int) ;
INSERT INTO t1 VALUES (0),(0);
CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (0),(0);
CREATE TABLE t2 ( f1 int, f2 int, f3 int) ;
INSERT INTO t2 VALUES (7,0,0);
SELECT *
FROM t2, t3
WHERE t2.f2 OR t3.f3 IN
(
SELECT t2.f2
FROM t1
WHERE t2.f1 OR t2.f3 );
f1 f2 f3 f3 7000 7000
drop tables t1, t2, t3;
#
# Test of LP BUG#872775 view with "outer references" bug
# set @@optimizer_switch= default; set optimizer_switch='subquery_cache=on';
CREATE TABLE t1 (a int) ;
CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ;
INSERT INTO t2 VALUES (1,'x'),(2,'y');
CREATE TABLE t3 (a int) ;
CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ;
INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3);
CREATE ORREPLACE VIEW v1 AS
SELECT t2.b
FROM t1
JOIN t2
WHERE t2 .c > (
SELECT t2.c FROM t3
);
SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );
pk b 269 275 280 293
drop view v1;
drop table t1,t2,t3,t4; SET optimizer_switch=@save_optimizer_switch;
# restore default set @@optimizer_switch= default;
Messung V0.5 in Prozent
¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen0.121Angebot
(Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können 2026-10-08)
¤
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.
