create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; insert into t1 values (1, 3); select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ 1 select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; Case When Count(*) < MAX_REQ Then 1 Else 0 End 1 drop table t1; create table t1m (a int) engine=myisam; create table t1i (a int) engine=innodb; create table t2m (a int) engine=myisam; create table t2i (a int) engine=innodb; insert into t2m values (5); insert into t2i values (5); select min(a) from t1m; min(a) NULL select min(7) from t1m; min(7) NULL select min(7) from DUAL; min(7) 7 explain select min(7) from t2m join t1m; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away select min(7) from t2m join t1m; min(7) NULL select max(a) from t1m; max(a) NULL select max(7) from t1m; max(7) NULL select max(7) from DUAL; max(7) 7 explain select max(7) from t2m join t1m; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away select max(7) from t2m join t1m; max(7) NULL select 1, min(a) from t1m where a=99; 1 min(a) 1 NULL select 1, min(a) from t1m where 1=99; 1 min(a) 1 NULL select 1, min(1) from t1m where a=99; 1 min(1) 1 NULL select 1, min(1) from t1m where 1=99; 1 min(1) 1 NULL select 1, max(a) from t1m where a=99; 1 max(a) 1 NULL select 1, max(a) from t1m where 1=99; 1 max(a) 1 NULL select 1, max(1) from t1m where a=99; 1 max(1) 1 NULL select 1, max(1) from t1m where 1=99; 1 max(1) 1 NULL select min(a) from t1i; min(a) NULL select min(7) from t1i; min(7) NULL select min(7) from DUAL; min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select min(7) from t2i join t1i; min(7) NULL select max(a) from t1i; max(a) NULL select max(7) from t1i; max(7) NULL select max(7) from DUAL; max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select max(7) from t2i join t1i; max(7) NULL select 1, min(a) from t1i where a=99; 1 min(a) 1 NULL select 1, min(a) from t1i where 1=99; 1 min(a) 1 NULL select 1, min(1) from t1i where a=99; 1 min(1) 1 NULL select 1, min(1) from t1i where 1=99; 1 min(1) 1 NULL select 1, max(a) from t1i where a=99; 1 max(a) 1 NULL select 1, max(a) from t1i where 1=99; 1 max(a) 1 NULL select 1, max(1) from t1i where a=99; 1 max(1) 1 NULL select 1, max(1) from t1i where 1=99; 1 max(1) 1 NULL explain select count(*), min(7), max(7) from t1m, t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t1m, t1i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t1m, t2i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t1m, t2i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t2m, t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2m system NULL NULL NULL NULL 1 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t2m, t1i; count(*) min(7) max(7) 0 NULL NULL drop table t1m, t1i, t2m, t2i; # # Bug #57954: BIT_AND function returns incorrect results when # semijoin=on CREATE TABLE c ( pk INT, col_varchar_key VARCHAR(1), PRIMARY KEY (pk), KEY col_varchar_key (col_varchar_key) ) ENGINE=InnoDB; INSERT INTO c VALUES (11,NULL); INSERT INTO c VALUES (16,'c'); CREATE TABLE bb ( pk INT, col_varchar_key VARCHAR(1), PRIMARY KEY (pk), KEY col_varchar_key (col_varchar_key) ) ENGINE=InnoDB; INSERT INTO bb VALUES (10,NULL); SELECT straight_join BIT_AND(c.pk) FROM bb, c WHERE c.col_varchar_key='ABC' ORDER BY c.pk; BIT_AND(c.pk) 18446744073709551615 DROP TABLE c,bb; # # Bug #58050: BIT_OR and BIT_XOR return incorrect results when # semijoin=on # CREATE TABLE t1 (pk INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB; INSERT INTO t1 VALUES(1, 1, 1); CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB; INSERT INTO t2 VALUES (1, 1, NULL); SELECT t1.* FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; pk b c SELECT BIT_OR(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; BIT_OR(t1.b) 0 SELECT BIT_AND(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; BIT_AND(t1.b) 18446744073709551615 SELECT BIT_XOR(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; BIT_XOR(t1.b) 0 DROP TABLE t1, t2; # # Bug#12713907: STRANGE OPTIMIZE & WRONG RESULT UNDER ORDER BY # COUNT(*) LIMIT. # CREATE TABLE t1 ( id BIGINT(20) , member_id_to INT(11) , r_date DATE , PRIMARY KEY (id,r_date), KEY r_date_idx (r_date), KEY t1_idx01 (member_id_to) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (107924526,518491,'2011-05-01'), (107924527,518491,'2011-05-01'), (107924534,518491,'2011-06-21'), (107924535,518491,'2011-06-21'), (107924542,1601319,'2011-06-21'), (107924543,1601319,'2011-06-21'), (107924544,1601319,'2011-06-21'), (107924545,1601319,'2011-06-21'); SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date = '2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1; member_id_to COUNT(*) 518491 2 DROP TABLE t1; # # MDEV-4269: crash when grouping by values() # SELECT @@storage_engine INTO @old_engine; set storage_engine=innodb; create table y select 1 b; select 1 from y group by b; 1 1 select 1 from y group by values(b); 1 1 drop table y; SET storage_engine=@old_engine; # # Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN # CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT, KEY(b, c, a(765))) ENGINE=INNODB; INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0); SELECT MIN(c) FROM t1 GROUP BY b; MIN(c) 0 EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL b 263 NULL 4 Using index for group-by DROP TABLE t1; End of 5.5 tests