EVOLUTION-MANAGER

Edit File: group_by.result

show create table seq_1_to_15_step_2; Table Create Table seq_1_to_15_step_2 CREATE TABLE `seq_1_to_15_step_2` ( `seq` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`) ) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 select count(seq),sum(seq),1 from seq_1_to_15_step_2; count(seq) sum(seq) 1 8 64 1 # # The engine should be able to optimize the following requests # select count(*) from seq_1_to_15_step_2; count(*) 8 explain select count(*) from seq_1_to_15_step_2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select count(seq) from seq_1_to_15_step_2; count(seq) 8 explain select count(seq) from seq_1_to_15_step_2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select sum(seq) from seq_1_to_15_step_2; sum(seq) 64 explain select sum(seq) from seq_1_to_15_step_2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select count(seq),sum(seq) from seq_1_to_15_step_2; count(seq) sum(seq) 8 64 explain select count(seq),sum(seq) from seq_1_to_15_step_2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select count(seq) as c from seq_1_to_15_step_2 having c > 5; c 8 explain select count(seq) as c from seq_1_to_15_step_2 having c > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select count(seq) as c from seq_1_to_15_step_2 having c > 1000; c explain select count(seq) as c from seq_1_to_15_step_2 having c > 1000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select distinct count(*) from seq_1_to_15_step_2; count(*) 8 explain select distinct count(*) from seq_1_to_15_step_2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1; seq count(*) 1 8 3 8 5 8 7 8 9 8 11 8 13 8 15 8 explain select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY seq_1_to_15_step_2 index NULL PRIMARY 8 NULL # Using index 1 PRIMARY <derived2> ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) 2 DERIVED NULL NULL NULL NULL NULL NULL # Storage engine handles GROUP BY create view v1 as select count(*) from seq_1_to_15_step_2; select * from v1; count(*) 8 drop view v1; # # The engine can't optimize the following queries # select count(seq),sum(seq),1 from seq_1_to_15_step_2; count(seq) sum(seq) 1 8 64 1 explain select count(seq),sum(seq),1 from seq_1_to_15_step_2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index explain select count(*) from seq_1_to_15_step_2, seq_1_to_15_step_2 as t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index 1 SIMPLE t2 index NULL PRIMARY 8 NULL 8 Using index; Using join buffer (flat, BNL join) explain select count(*) from seq_1_to_15_step_2 where seq > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE seq_1_to_15_step_2 index PRIMARY PRIMARY 8 NULL 8 Using where; Using index explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index; Using temporary; Using filesort create temporary table t1 select * from seq_1_to_3; select count(NULL) from t1; count(NULL) 0 select count(NULL) from seq_1_to_3; count(NULL) 0