--source include/count_sessions.inc

--connect (con1, localhost, root,,)
--connect (con2, localhost, root,,)


create table t1(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, KEY(b));

--disable_query_log
--let $i=1
while ($i < 250) {
--eval insert into t1 values (NULL, $i)
--inc $i
}
--enable_query_log

insert into t1 select NULL,b from t1;
insert into t1 select NULL,b from t1;
insert into t1 select NULL,b from t1;
insert into t1 select NULL,b from t1;
insert into t1 select NULL,b from t1;

select count(*) from t1;

create table t2(b INT NOT NULL PRIMARY KEY, c INT);

--disable_query_log
--let $i=1
while ($i < 250) {
--eval insert into t2 values ($i, $i)
--inc $i
}
--enable_query_log


create table t2_src(b INT, c INT);
INSERT INTO t2_src SELECT * FROM t2;
INSERT INTO t2_src SELECT * FROM t2;
INSERT INTO t2_src SELECT * FROM t2;
INSERT INTO t2_src SELECT * FROM t2;
INSERT INTO t2_src SELECT * FROM t2;
INSERT INTO t2_src SELECT * FROM t2;

create table t3(b INT NOT NULL AUTO_INCREMENT PRIMARY KEY, d INT);

--disable_query_log
--let $i=1
while ($i < 250) {
--eval insert into t3 values (NULL, $i)
--inc $i
}
--enable_query_log

connection con1;
begin;
replace into t2 select * from t2_src;

connection con2;
#explain select b, count(*) as count_bs  from t1 left join t2 using (b) left join t3 using (b) group by b;
#explain format=tree select b, count(*) as count_bs  from t1 left join t2 using (b) left join t3 using (b) group by b;
#show status like '%Handler%';
--let $handler_key_start=query_get_value(SHOW STATUS LIKE 'Handler_read_key',Value,1)
set debug='+d,ib_sel';
select b, count(*) as count_bs  from t1 left join t2 using (b) left join t3 using (b) group by b;
set debug='-d,ib_sel';
--let $handler_key_end=query_get_value(SHOW STATUS LIKE 'Handler_read_key',Value,1)
--expr $res= $handler_key_end - $handler_key_start
#show status like '%Handler%';
assert($res <= 500); #250 rows from t2, 250 rows from t3

connection con1;
commit;

connection default;

--disconnect con1
--disconnect con2

--source include/wait_until_count_sessions.inc


DROP TABLE t1,t2,t3;