1create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; 2insert into t1 values (1, 3); 3select 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; 4count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ 51 6select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; 7Case When Count(*) < MAX_REQ Then 1 Else 0 End 81 9drop table t1; 10create table t1m (a int) engine=myisam; 11create table t1i (a int) engine=innodb; 12create table t2m (a int) engine=myisam; 13create table t2i (a int) engine=innodb; 14insert into t2m values (5); 15insert into t2i values (5); 16select min(a) from t1m; 17min(a) 18NULL 19select min(7) from t1m; 20min(7) 21NULL 22select min(7) from DUAL; 23min(7) 247 25explain select min(7) from t2m join t1m; 26id select_type table type possible_keys key key_len ref rows Extra 271 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 28select min(7) from t2m join t1m; 29min(7) 30NULL 31select max(a) from t1m; 32max(a) 33NULL 34select max(7) from t1m; 35max(7) 36NULL 37select max(7) from DUAL; 38max(7) 397 40explain select max(7) from t2m join t1m; 41id select_type table type possible_keys key key_len ref rows Extra 421 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 43select max(7) from t2m join t1m; 44max(7) 45NULL 46select 1, min(a) from t1m where a=99; 471 min(a) 481 NULL 49select 1, min(a) from t1m where 1=99; 501 min(a) 511 NULL 52select 1, min(1) from t1m where a=99; 531 min(1) 541 NULL 55select 1, min(1) from t1m where 1=99; 561 min(1) 571 NULL 58select 1, max(a) from t1m where a=99; 591 max(a) 601 NULL 61select 1, max(a) from t1m where 1=99; 621 max(a) 631 NULL 64select 1, max(1) from t1m where a=99; 651 max(1) 661 NULL 67select 1, max(1) from t1m where 1=99; 681 max(1) 691 NULL 70select min(a) from t1i; 71min(a) 72NULL 73select min(7) from t1i; 74min(7) 75NULL 76select min(7) from DUAL; 77min(7) 787 79explain select min(7) from t2i join t1i; 80id select_type table type possible_keys key key_len ref rows Extra 811 SIMPLE t2i ALL NULL NULL NULL NULL 1 NULL 821 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop) 83select min(7) from t2i join t1i; 84min(7) 85NULL 86select max(a) from t1i; 87max(a) 88NULL 89select max(7) from t1i; 90max(7) 91NULL 92select max(7) from DUAL; 93max(7) 947 95explain select max(7) from t2i join t1i; 96id select_type table type possible_keys key key_len ref rows Extra 971 SIMPLE t2i ALL NULL NULL NULL NULL 1 NULL 981 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop) 99select max(7) from t2i join t1i; 100max(7) 101NULL 102select 1, min(a) from t1i where a=99; 1031 min(a) 1041 NULL 105select 1, min(a) from t1i where 1=99; 1061 min(a) 1071 NULL 108select 1, min(1) from t1i where a=99; 1091 min(1) 1101 NULL 111select 1, min(1) from t1i where 1=99; 1121 min(1) 1131 NULL 114select 1, max(a) from t1i where a=99; 1151 max(a) 1161 NULL 117select 1, max(a) from t1i where 1=99; 1181 max(a) 1191 NULL 120select 1, max(1) from t1i where a=99; 1211 max(1) 1221 NULL 123select 1, max(1) from t1i where 1=99; 1241 max(1) 1251 NULL 126explain select count(*), min(7), max(7) from t1m, t1i; 127id select_type table type possible_keys key key_len ref rows Extra 1281 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found 1291 SIMPLE t1i ALL NULL NULL NULL NULL 1 NULL 130select count(*), min(7), max(7) from t1m, t1i; 131count(*) min(7) max(7) 1320 NULL NULL 133explain select count(*), min(7), max(7) from t1m, t2i; 134id select_type table type possible_keys key key_len ref rows Extra 1351 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found 1361 SIMPLE t2i ALL NULL NULL NULL NULL 1 NULL 137select count(*), min(7), max(7) from t1m, t2i; 138count(*) min(7) max(7) 1390 NULL NULL 140explain select count(*), min(7), max(7) from t2m, t1i; 141id select_type table type possible_keys key key_len ref rows Extra 1421 SIMPLE t2m system NULL NULL NULL NULL 1 NULL 1431 SIMPLE t1i ALL NULL NULL NULL NULL 1 NULL 144select count(*), min(7), max(7) from t2m, t1i; 145count(*) min(7) max(7) 1460 NULL NULL 147drop table t1m, t1i, t2m, t2i; 148# 149# Bug #57954: BIT_AND function returns incorrect results when 150# semijoin=on 151CREATE TABLE c ( 152pk INT, 153col_varchar_key VARCHAR(1), 154PRIMARY KEY (pk), 155KEY col_varchar_key (col_varchar_key) 156) ENGINE=InnoDB; 157INSERT INTO c VALUES (11,NULL); 158INSERT INTO c VALUES (16,'c'); 159CREATE TABLE bb ( 160pk INT, 161col_varchar_key VARCHAR(1), 162PRIMARY KEY (pk), 163KEY col_varchar_key (col_varchar_key) 164) ENGINE=InnoDB; 165INSERT INTO bb VALUES (10,NULL); 166SELECT straight_join BIT_AND(c.pk) 167FROM 168bb, c 169WHERE c.col_varchar_key='ABC' 170ORDER BY c.pk; 171BIT_AND(c.pk) 17218446744073709551615 173DROP TABLE c,bb; 174# 175# Bug #58050: BIT_OR and BIT_XOR return incorrect results when 176# semijoin=on 177# 178CREATE TABLE t1 (pk INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB; 179INSERT INTO t1 VALUES(1, 1, 1); 180CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB; 181INSERT INTO t2 VALUES (1, 1, NULL); 182SELECT t1.* FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; 183pk b c 184SELECT BIT_OR(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; 185BIT_OR(t1.b) 1860 187SELECT BIT_AND(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; 188BIT_AND(t1.b) 18918446744073709551615 190SELECT BIT_XOR(t1.b) FROM t1 JOIN t2 ON t1.c=t2.c WHERE t1.pk=1; 191BIT_XOR(t1.b) 1920 193DROP TABLE t1, t2; 194End of 5.5 tests 195