release/src/router/mysql/mysql-test/t/func_group.test

   1 #
   2 # simple test of all group functions
   3 #
   4
   5 --disable_warnings
   6 drop table if exists t1,t2;
   7 --enable_warnings
   8
   9 set @sav_dpi= @@div_precision_increment;
  10 set div_precision_increment= 5;
  11 show variables like 'div_precision_increment';
  12 create table t1 (grp int, a bigint unsigned, c char(10) not null);
  13 insert into t1 values (1,1,"a");
  14 insert into t1 values (2,2,"b");
  15 insert into t1 values (2,3,"c");
  16 insert into t1 values (3,4,"E");
  17 insert into t1 values (3,5,"C");
  18 insert into t1 values (3,6,"D");
  19
  20 # Test of MySQL field extension with and without matching records.
  21 #### Note: The two following statements may fail if the execution plan
  22 #### or optimizer is changed. The result for column c is undefined.
  23 select a,c,sum(a) from t1 group by a;
  24 select a,c,sum(a) from t1 where a > 10 group by a;
  25 select sum(a) from t1 where a > 10;
  26 select a from t1 order by rand(10);
  27 select distinct a from t1 order by rand(10);
  28 select count(distinct a),count(distinct grp) from t1;
  29 insert into t1 values (null,null,'');
  30 select count(distinct a),count(distinct grp) from t1;
  31
  32 select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
  33 select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
  34 --disable_warnings
  35 select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;
  36 --enable_warnings
  37
  38 create table t2 (grp int, a bigint unsigned, c char(10));
  39 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
  40
  41 # REPLACE ... SELECT doesn't yet work with PS
  42 replace into t2 select grp, a, c from t1 limit 2,1;
  43 select * from t2;
  44
  45 drop table t1,t2;
  46
  47 #
  48 # Problem with std()
  49 #
  50
  51 CREATE TABLE t1 (id int(11),value1 float(10,2));
  52 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00);
  53 CREATE TABLE t2 (id int(11),name char(20));
  54 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');
  55 select id, avg(value1), std(value1), variance(value1) from t1 group by id;
  56 select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
  57 drop table t1,t2;
  58
  59 #
  60 # Test of bug in left join & avg
  61 #
  62
  63 create table t1 (id int not null);
  64 create table t2 (id int not null,rating int null);
  65 insert into t1 values(1),(2),(3);
  66 insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL);
  67 select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id;
  68 # Test different types with avg()
  69 select sql_small_result t2.id, avg(rating) from t2 group by t2.id;
  70 select sql_big_result t2.id, avg(rating) from t2 group by t2.id;
  71 select sql_small_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
  72 select sql_big_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
  73 drop table t1,t2;
  74
  75 #
  76 # test of count
  77 #
  78 create table t1 (a smallint(6) primary key, c char(10), b text);
  79 INSERT INTO t1 VALUES (1,'1','1');
  80 INSERT INTO t1 VALUES (2,'2','2');
  81 INSERT INTO t1 VALUES (4,'4','4');
  82
  83 select count(*) from t1;
  84 select count(*) from t1 where a = 1;
  85 select count(*) from t1 where a = 100;
  86 select count(*) from t1 where a >= 10;
  87 select count(a) from t1 where a = 1;
  88 select count(a) from t1 where a = 100;
  89 select count(a) from t1 where a >= 10;
  90 select count(b) from t1 where b >= 2;
  91 select count(b) from t1 where b >= 10;
  92 select count(c) from t1 where c = 10;
  93 drop table t1;
  94
  95 #
  96 # Test of bug in COUNT(i)*(i+0)
  97 #
  98
  99 CREATE TABLE t1 (d DATETIME, i INT);
 100 INSERT INTO t1 VALUES (NOW(), 1);
 101 SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i;
 102 SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i;
 103 DROP TABLE t1;
 104
 105 #
 106 # Another SUM() problem with 3.23.2
 107 #
 108
 109 create table t1 (
 110         num float(5,2),
 111         user char(20)
 112 );
 113 insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa');
 114 insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa');
 115 insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa');
 116 select sum(num) from t1;
 117 select sum(num) from t1 group by user;
 118 drop table t1;
 119
 120 #
 121 # Test problem with MIN() optimization in case of null values
 122 #
 123
 124 create table t1 (a1 int, a2 char(3), key k1(a1), key k2(a2));
 125 insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
 126 create table t2(a1 char(3), a2 int, a3 real, key k1(a1), key k2(a2, a1));
 127 select * from t1;
 128 # The following returned NULL in 4.0.10
 129 select min(a2) from t1;
 130 select max(t1.a1), max(t2.a2) from t1, t2;
 131 select max(t1.a1) from t1, t2;
 132 select max(t2.a2), max(t1.a1) from t1, t2;
 133
 134 explain select min(a2) from t1;
 135 explain select max(t1.a1), max(t2.a2) from t1, t2;
 136
 137 insert into t2 values('AAA', 10, 0.5);
 138 insert into t2 values('BBB', 20, 1.0);
 139 select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
 140
 141 select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
 142 select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
 143 select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
 144 select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
 145 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
 146 select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
 147 select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
 148 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
 149 drop table t1,t2;
 150
 151 #
 152 # Test of group function and NULL values
 153 #
 154
 155 CREATE TABLE t1 (a int, b int);
 156 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
 157 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 158 insert into t1 values (1,null);
 159 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 160 insert into t1 values (1,null);
 161 insert into t1 values (2,null);
 162 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 163 select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 164 insert into t1 values (2,1);
 165 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 166 select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 167 insert into t1 values (3,1);
 168 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
 169 select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
 170 explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
 171 drop table t1;
 172
 173 #
 174 # Bug #1972: test for bit_and(), bit_or() and negative values
 175 #
 176 create table t1 (col int);
 177 insert into t1 values (-1), (-2), (-3);
 178 select bit_and(col), bit_or(col) from t1;
 179 select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;
 180 drop table t1;
 181
 182 #
 183 # Bug #3376: avg() and an empty table
 184 #
 185
 186 create table t1 (a int);
 187 select avg(2) from t1;
 188 drop table t1;
 189
 190 #
 191 # Tests to check MIN/MAX query optimization
 192 #
 193
 194 # Create database schema
 195 create table t1(
 196   a1 char(3) primary key,
 197   a2 smallint,
 198   a3 char(3),
 199   a4 real,
 200   a5 date,
 201   key k1(a2,a3),
 202   key k2(a4 desc,a1),
 203   key k3(a5,a1)
 204 );
 205 create table t2(
 206   a1 char(3) primary key,
 207   a2 char(17),
 208   a3 char(2),
 209   a4 char(3),
 210   key k1(a3, a2),
 211   key k2(a4)
 212 );
 213
 214 # Populate table t1
 215 insert into t1 values('AME',0,'SEA',0.100,date'1942-02-19');
 216 insert into t1 values('HBR',1,'SEA',0.085,date'1948-03-05');
 217 insert into t1 values('BOT',2,'SEA',0.085,date'1951-11-29');
 218 insert into t1 values('BMC',3,'SEA',0.085,date'1958-09-08');
 219 insert into t1 values('TWU',0,'LAX',0.080,date'1969-10-05');
 220 insert into t1 values('BDL',0,'DEN',0.080,date'1960-11-27');
 221 insert into t1 values('DTX',1,'NYC',0.080,date'1961-05-04');
 222 insert into t1 values('PLS',1,'WDC',0.075,date'1949-01-02');
 223 insert into t1 values('ZAJ',2,'CHI',0.075,date'1960-06-15');
 224 insert into t1 values('VVV',2,'MIN',0.075,date'1959-06-28');
 225 insert into t1 values('GTM',3,'DAL',0.070,date'1977-09-23');
 226 insert into t1 values('SSJ',null,'CHI',null,date'1974-03-19');
 227 insert into t1 values('KKK',3,'ATL',null,null);
 228 insert into t1 values('XXX',null,'MIN',null,null);
 229 insert into t1 values('WWW',1,'LED',null,null);
 230
 231 # Populate table t2
 232 insert into t2 values('TKF','Seattle','WA','AME');
 233 insert into t2 values('LCC','Los Angeles','CA','TWU');
 234 insert into t2 values('DEN','Denver','CO','BDL');
 235 insert into t2 values('SDC','San Diego','CA','TWU');
 236 insert into t2 values('NOL','New Orleans','LA','GTM');
 237 insert into t2 values('LAK','Los Angeles','CA','TWU');
 238 insert into t2 values('AAA','AAA','AA','AME');
 239
 240 # Show the table contents
 241 select * from t1;
 242 select * from t2;
 243
 244 # Queries with min/max functions
 245 # which regular min/max optimization are applied to
 246
 247 explain
 248 select min(a1) from t1;
 249 select min(a1) from t1;
 250 explain
 251 select max(a4) from t1;
 252 select max(a4) from t1;
 253 explain
 254 select min(a5), max(a5) from t1;
 255 select min(a5), max(a5) from t1;
 256 explain
 257 select min(a3) from t1 where a2 = 2;
 258 select min(a3) from t1 where a2 = 2;
 259 explain
 260 select min(a1), max(a1) from t1 where a4 = 0.080;
 261 select min(a1), max(a1) from t1 where a4 = 0.080;
 262
 263 explain
 264 select min(t1.a5), max(t2.a3) from t1, t2;
 265 select min(t1.a5), max(t2.a3) from t1, t2;
 266 explain
 267 select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
 268 select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA';
 269
 270 # Queries with min/max functions
 271 # which extended min/max optimization are applied to
 272
 273 explain
 274 select min(a1) from t1 where a1 > 'KKK';
 275 select min(a1) from t1 where a1 > 'KKK';
 276 explain
 277 select min(a1) from t1 where a1 >= 'KKK';
 278 select min(a1) from t1 where a1 >= 'KKK';
 279 explain
 280 select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
 281 select max(a3) from t1 where a2 = 2 and a3 < 'SEA';
 282 explain
 283 select max(a5) from t1 where a5 < date'1970-01-01';
 284 select max(a5) from t1 where a5 < date'1970-01-01';
 285 explain
 286 select max(a3) from t1 where a2 is null;
 287 select max(a3) from t1 where a2 is null;
 288 explain
 289 select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
 290 select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q';
 291 explain
 292 select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
 293 select min(a1), max(a1) from t1 where a1 between 'A' and 'P';
 294 explain
 295 select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
 296 select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN';
 297 explain
 298 select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
 299 select max(a3) from t1 where a3 = 'MIN' and a2 = 2;
 300 explain
 301 select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
 302 select max(a3) from t1 where a3 = 'DEN' and a2 = 2;
 303
 304 explain
 305 select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
 306 select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA';
 307
 308 explain
 309 select max(a3) from t1 where a2 is null and a2 = 2;
 310 select max(a3) from t1 where a2 is null and a2 = 2;
 311
 312 explain
 313 select max(a2) from t1 where a2 >= 1;
 314 select max(a2) from t1 where a2 >= 1;
 315 explain
 316 select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
 317 select min(a3) from t1 where a2 = 2 and a3 < 'SEA';
 318
 319 explain
 320 select min(a3) from t1 where a2 = 4;
 321 select min(a3) from t1 where a2 = 4;
 322 explain
 323 select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
 324 select min(a3) from t1 where a2 = 2 and a3 > 'SEA';
 325 explain
 326 select (min(a4)+max(a4))/2 from t1;
 327 select (min(a4)+max(a4))/2 from t1;
 328 explain
 329 select min(a3) from t1 where 2 = a2;
 330 select min(a3) from t1 where 2 = a2;
 331 explain
 332 select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
 333 select max(a3) from t1 where a2 = 2 and 'SEA' > a3;
 334 explain
 335 select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
 336 select max(a3) from t1 where a2 = 2 and 'SEA' < a3;
 337 explain
 338 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
 339 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI';
 340 explain
 341 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
 342 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA';
 343 explain
 344 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
 345 select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN';
 346 explain
 347 select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
 348 select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN';
 349
 350 explain
 351 select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
 352 select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK';
 353
 354 # Queries to which max/min optimization is not applied
 355
 356 explain
 357 select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX';
 358 explain
 359 select min(a1) from t1 where a1 != 'KKK';
 360 explain
 361 select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
 362 explain
 363 select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';
 364
 365 explain
 366 select min(a4 - 0.01) from t1;
 367 explain
 368 select max(a4 + 0.01) from t1;
 369 explain
 370 select min(a3) from t1 where (a2 +1 ) is null;
 371 explain
 372 select min(a3) from t1 where (a2 + 1) = 2;
 373 explain
 374 select min(a3) from t1 where 2 = (a2 + 1);
 375 explain
 376 select min(a2) from t1 where a2 < 2 * a2 - 8;
 377 explain
 378 select min(a1) from t1  where a1 between a3 and 'KKK';
 379 explain
 380 select min(a4) from t1  where (a4 + 0.01) between 0.07 and 0.08;
 381 explain
 382 select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
 383 drop table t1, t2;
 384
 385 # Moved to func_group_innodb
 386 #--disable_warnings
 387 #create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
 388 #--enable_warnings
 389 #insert into t1 values (1, 3);
 390 #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;
 391 #select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
 392 #drop table t1;
 393
 394
 395 create table t1 (a char(10));
 396 insert into t1 values ('a'),('b'),('c');
 397 select coercibility(max(a)) from t1;
 398 drop table t1;
 399
 400 #
 401 # Bug #6658 MAX(column) returns incorrect coercibility
 402 #
 403 create table t1 (a char character set latin2);
 404 insert into t1 values ('a'),('b');
 405 select charset(max(a)), coercibility(max(a)),
 406        charset(min(a)), coercibility(min(a)) from t1;
 407 show create table t1;
 408 create table t2 select max(a),min(a) from t1;
 409 show create table t2;
 410 drop table t2;
 411 create table t2 select concat(a) from t1;
 412 show create table t2;
 413 drop table t2,t1;
 414
 415 #
 416 # aggregate functions on static tables
 417 #
 418 create table t1 (a int);
 419 insert into t1 values (1);
 420 select max(a) as b from t1 having b=1;
 421 select a from t1 having a=1;
 422 drop table t1;
 423
 424 #
 425 # Bug #3435: variance(const), stddev(const) and an empty table
 426 #
 427
 428 create table t1 (a int);
 429 select variance(2) from t1;
 430 select stddev(2) from t1;
 431 drop table t1;
 432
 433
 434 #
 435 # cleunup() of optimized away count(*) and max/min
 436 #
 437 create table t1 (a int);
 438 insert into t1 values (1),(2);
 439 prepare stmt1 from 'SELECT COUNT(*) FROM t1';
 440 execute stmt1;
 441 execute stmt1;
 442 execute stmt1;
 443 deallocate prepare stmt1;
 444 drop table t1;
 445
 446 create table t1 (a int, primary key(a));
 447 insert into t1 values (1),(2);
 448 prepare stmt1 from 'SELECT max(a) FROM t1';
 449 execute stmt1;
 450 execute stmt1;
 451 execute stmt1;
 452 deallocate prepare stmt1;
 453 drop table t1;
 454
 455 #
 456 # Bug #5406 min/max optimization for empty set
 457 #
 458
 459 CREATE TABLE t1 (a int primary key);
 460 INSERT INTO t1 VALUES (1),(2),(3),(4);
 461
 462 SELECT MAX(a) FROM t1 WHERE a > 5;
 463 SELECT MIN(a) FROM t1 WHERE a < 0;
 464
 465 DROP TABLE t1;
 466
 467 #
 468 # Bug #5555 GROUP BY enum_field" returns incorrect results
 469 #
 470
 471 CREATE TABLE t1 (
 472   id int(10) unsigned NOT NULL auto_increment,
 473   val enum('one','two','three') NOT NULL default 'one',
 474   PRIMARY KEY  (id)
 475 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 476
 477 INSERT INTO t1 VALUES
 478 (1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
 479
 480 select val, count(*) from t1 group by val;
 481 drop table t1;
 482
 483 CREATE TABLE t1 (
 484   id int(10) unsigned NOT NULL auto_increment,
 485   val set('one','two','three') NOT NULL default 'one',
 486   PRIMARY KEY  (id)
 487 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 488
 489 INSERT INTO t1 VALUES
 490 (1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
 491
 492 select val, count(*) from t1 group by val;
 493 drop table t1;
 494
 495 #
 496 # Bug #5615: type of aggregate function column wrong when using group by
 497 #
 498
 499 create table t1(a int, b datetime);
 500 insert into t1 values (1, NOW()), (2, NOW());
 501 create table t2 select MAX(b) from t1 group by a;
 502 show create table t2;
 503 drop table t1, t2;
 504
 505 #
 506 # Bug 7833:  Wrong datatype of aggregate column is returned
 507 #
 508
 509 create table t1(f1 datetime);
 510 insert into t1 values (now());
 511 create table t2 select f2 from (select max(now()) f2 from t1) a;
 512 show columns from t2;
 513 drop table t2;
 514 create table t2 select f2 from (select now() f2 from t1) a;
 515 show columns from t2;
 516 drop table t2, t1;
 517
 518 #
 519 # Bug 8893: wrong result for min/max optimization with 2 indexes
 520 #
 521
 522 CREATE TABLE t1(
 523   id int PRIMARY KEY,
 524   a  int,
 525   b  int,
 526   INDEX i_b_id(a,b,id),
 527   INDEX i_id(a,id)
 528 );
 529 INSERT INTO t1 VALUES
 530   (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
 531 SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
 532 DROP TABLE t1;
 533
 534 # change the order of the last two index definitions
 535
 536 CREATE TABLE t1(
 537   id int PRIMARY KEY,
 538   a  int,
 539   b  int,
 540   INDEX i_id(a,id),
 541   INDEX i_b_id(a,b,id)
 542 );
 543 INSERT INTO t1 VALUES
 544   (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
 545 SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
 546 DROP TABLE t1;
 547
 548
 549 #
 550 # Bug #12882    min/max inconsistent on empty table
 551 #
 552 # Test case moved to func_group_innodb
 553 #
 554 # Bug #18206: min/max optimization cannot be applied to partial index
 555 #
 556
 557 CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
 558 INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
 559 SELECT * FROM t1;
 560
 561 SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
 562 SHOW WARNINGS;
 563 SELECT MAX(b) FROM t1 WHERE b < 'pp';
 564 DROP TABLE t1;
 565
 566 CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
 567 INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
 568 SELECT MAX(b) FROM t1;
 569 EXPLAIN SELECT MAX(b) FROM t1;
 570 DROP TABLE t1;
 571
 572 CREATE TABLE t1 (id int , b varchar(512), INDEX(b(250))) COLLATE latin1_bin;
 573 INSERT INTO t1 VALUES
 574   (1,CONCAT(REPEAT('_', 250), "qq")), (1,CONCAT(REPEAT('_', 250), "zz")),
 575   (1,CONCAT(REPEAT('_', 250), "aa")), (1,CONCAT(REPEAT('_', 250), "ff"));
 576
 577 SELECT MAX(b) FROM t1;
 578 EXPLAIN SELECT MAX(b) FROM t1;
 579 DROP TABLE t1;
 580 #
 581 # Bug #16792  query with subselect, join, and group not returning proper values
 582 #
 583 CREATE TABLE t1 (a INT, b INT);
 584 INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
 585
 586 SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
 587 SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
 588 # an attempt to test all aggregate function with no table.
 589 SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),
 590        COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
 591        GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
 592 DROP TABLE t1;
 593
 594 # End of 4.1 tests
 595
 596 #
 597 # decimal-related tests
 598 #
 599 create table t2 (ff double);
 600 insert into t2 values (2.2);
 601 select cast(sum(distinct ff) as decimal(5,2)) from t2;
 602 select cast(sum(distinct ff) as signed) from t2;
 603 select cast(variance(ff) as decimal(10,3)) from t2;
 604 select cast(min(ff) as decimal(5,2)) from t2;
 605
 606 create table t1 (df decimal(5,1));
 607 insert into t1 values(1.1);
 608 insert into t1 values(2.2);
 609 select cast(sum(distinct df) as signed) from t1;
 610 select cast(min(df) as signed) from t1;
 611 select 1e8 * sum(distinct df) from t1;
 612 select 1e8 * min(df) from t1;
 613
 614 create table t3 (ifl int);
 615 insert into t3 values(1), (2);
 616 select cast(min(ifl) as decimal(5,2)) from t3;
 617
 618 drop table t1, t2, t3;
 619
 620
 621 #
 622 # BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations
 623 #
 624
 625 CREATE TABLE t1 (id int(11),value1 float(10,2));
 626 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);
 627 select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
 628 DROP TABLE t1;
 629
 630 #
 631 # BUG#8464 decimal AVG returns incorrect result
 632 #
 633
 634 CREATE TABLE t1 (col1 decimal(16,12));
 635 INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
 636 insert into t1 select * from t1;
 637 select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
 638 DROP TABLE t1;
 639
 640 #
 641 # BUG#8465 decimal MIN and MAX return incorrect result
 642 #
 643
 644 create table t1 (col1 decimal(16,12));
 645 insert into t1 values (-5.00000000001);
 646 insert into t1 values (-5.00000000001);
 647 select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
 648 delete from t1;
 649 insert into t1 values (5.00000000001);
 650 insert into t1 values (5.00000000001);
 651 select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
 652 DROP TABLE t1;
 653
 654 #
 655 # Test that new VARCHAR correctly works with COUNT(DISTINCT)
 656 #
 657
 658 CREATE TABLE t1 (a VARCHAR(400));
 659 INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a   "),
 660                           ("B"), ("b"), ("b "), ("b   ");
 661 SELECT COUNT(DISTINCT a) FROM t1;
 662 DROP TABLE t1;
 663
 664 #
 665 # Test for buf #9210: GROUP BY with expression if a decimal type
 666 #
 667
 668 CREATE TABLE t1 (a int, b int, c int);
 669 INSERT INTO t1 (a, b, c) VALUES
 670   (1,1,1), (1,1,2), (1,1,3),
 671   (1,2,1), (1,2,2), (1,2,3),
 672   (1,3,1), (1,3,2), (1,3,3),
 673   (2,1,1), (2,1,2), (2,1,3),
 674   (2,2,1), (2,2,2), (2,2,3),
 675   (2,3,1), (2,3,2), (2,3,3),
 676   (3,1,1), (3,1,2), (3,1,3),
 677   (3,2,1), (3,2,2), (3,2,3),
 678   (3,3,1), (3,3,2), (3,3,3);
 679
 680 SELECT b/c as v, a FROM t1 ORDER BY v;
 681 SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
 682 SELECT SUM(a) FROM t1 GROUP BY b/c;
 683
 684 DROP TABLE t1;
 685 set div_precision_increment= @sav_dpi;
 686
 687 #
 688 # Bug #20868: Client connection is broken on SQL query error
 689 #
 690 CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
 691 INSERT INTO t1 VALUES (1,1), (2,2);
 692
 693 CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
 694 INSERT INTO t2 VALUES (1,1), (3,3);
 695
 696 SELECT SQL_NO_CACHE
 697   (SELECT SUM(c.a) FROM t1 ttt, t2 ccc
 698    WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
 699 FROM t1 t, t2 c WHERE t.a = c.b;
 700
 701 DROP TABLE t1,t2;
 702
 703 #
 704 # Bug #10966: Variance functions return wrong data type
 705 #
 706
 707 create table t1 select variance(0);
 708 show create table t1;
 709 drop table t1;
 710 create table t1 select stddev(0);
 711 show create table t1;
 712 drop table t1;
 713
 714
 715 #
 716 # Bug#22555: STDDEV yields positive result for groups with only one row
 717 #
 718
 719 create table bug22555 (i smallint primary key auto_increment, s1 smallint, s2 smallint, e decimal(30,10), o double);
 720 insert into bug22555 (s1, s2, e, o) values (53, 78, 11.4276528, 6.828112), (17, 78, 5.916793, 1.8502951), (18, 76, 2.679231, 9.17975591), (31, 62, 6.07831, 0.1), (19, 41, 5.37463, 15.1), (83, 73, 14.567426, 7.959222), (92, 53, 6.10151, 13.1856852), (7, 12, 13.92272, 3.442007), (92, 35, 11.95358909, 6.01376678), (38, 84, 2.572, 7.904571);
 721 select std(s1/s2) from bug22555 group by i;
 722 select std(e) from bug22555 group by i;
 723 select std(o) from bug22555 group by i;
 724 drop table bug22555;
 725
 726 create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal);
 727 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
 728 select i, count(*) from bug22555 group by i;
 729 select std(s1/s2) from bug22555 where i=1;
 730 select std(s1/s2) from bug22555 where i=2;
 731 select std(s1/s2) from bug22555 where i=3;
 732 select std(s1/s2) from bug22555 where i=1 group by i;
 733 select std(s1/s2) from bug22555 where i=2 group by i;
 734 select std(s1/s2) from bug22555 where i=3 group by i;
 735 select std(s1/s2) from bug22555 group by i order by i;
 736 select i, count(*), std(o1/o2) from bug22555 group by i order by i;
 737 select i, count(*), std(e1/e2) from bug22555 group by i order by i;
 738 set @saved_div_precision_increment=@@div_precision_increment;
 739 set div_precision_increment=19;
 740 select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
 741 select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
 742 select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
 743 select i, count(*), std(s1/s2) from bug22555 group by i order by i;
 744 select i, count(*), std(o1/o2) from bug22555 group by i order by i;
 745 select i, count(*), std(e1/e2) from bug22555 group by i order by i;
 746 set div_precision_increment=20;
 747 select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
 748 select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
 749 select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
 750 select i, count(*), std(s1/s2) from bug22555 group by i order by i;
 751 select i, count(*), std(o1/o2) from bug22555 group by i order by i;
 752 select i, count(*), std(e1/e2) from bug22555 group by i order by i;
 753 set @@div_precision_increment=@saved_div_precision_increment;
 754 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
 755 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
 756 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
 757
 758 select i, count(*), std(s1/s2) from bug22555 group by i order by i;
 759 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
 760 select i, count(*), std(e1/e2) from bug22555 group by i order by i;
 761 select std(s1/s2) from bug22555;
 762 select std(o1/o2) from bug22555;
 763 select std(e1/e2) from bug22555;
 764 set @saved_div_precision_increment=@@div_precision_increment;
 765 set div_precision_increment=19;
 766 select i, count(*), std(s1/s2) from bug22555 group by i order by i;
 767 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
 768 select i, count(*), std(e1/e2) from bug22555 group by i order by i;
 769 select round(std(s1/s2), 17) from bug22555;
 770 select std(o1/o2) from bug22555;
 771 select round(std(e1/e2), 17) from bug22555;
 772 set div_precision_increment=20;
 773 select i, count(*), std(s1/s2) from bug22555 group by i order by i;
 774 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
 775 select i, count(*), std(e1/e2) from bug22555 group by i order by i;
 776 select round(std(s1/s2), 17) from bug22555;
 777 select std(o1/o2) from bug22555;
 778 select round(std(e1/e2), 17) from bug22555;
 779 set @@div_precision_increment=@saved_div_precision_increment;
 780 drop table bug22555;
 781
 782 create table bug22555 (s smallint, o double, e decimal);
 783 insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);
 784 select var_samp(s), var_pop(s) from bug22555;
 785 select var_samp(o), var_pop(o) from bug22555;
 786 select var_samp(e), var_pop(e) from bug22555;
 787 drop table bug22555;
 788
 789 create table bug22555 (s smallint, o double, e decimal);
 790 insert into bug22555 values (null,null,null),(null,null,null);
 791 select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;
 792 select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;
 793 select var_samp(e) as 'null', var_pop(e) as 'null' from bug22555;
 794 insert into bug22555 values (1,1,1);
 795 select var_samp(s) as 'null', var_pop(s) as '0' from bug22555;
 796 select var_samp(o) as 'null', var_pop(o) as '0' from bug22555;
 797 select var_samp(e) as 'null', var_pop(e) as '0' from bug22555;
 798 insert into bug22555 values (2,2,2);
 799 select var_samp(s) as '0.5', var_pop(s) as '0.25' from bug22555;
 800 select var_samp(o) as '0.5', var_pop(o) as '0.25' from bug22555;
 801 select var_samp(e) as '0.5', var_pop(e) as '0.25' from bug22555;
 802 drop table bug22555;
 803
 804
 805 #
 806 # Bug #21976: Unnecessary warning with count(decimal)
 807 #
 808
 809 create table t1 (a decimal(20));
 810 insert into t1 values (12345678901234567890);
 811 select count(a) from t1;
 812 select count(distinct a) from t1;
 813 drop table t1;
 814
 815 #
 816 # Bug #23184: SELECT causes server crash
 817 #
 818 CREATE TABLE t1 (a INT, b INT);
 819 INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
 820 INSERT INTO t1 SELECT a, b+8       FROM t1;
 821 INSERT INTO t1 SELECT a, b+16      FROM t1;
 822 INSERT INTO t1 SELECT a, b+32      FROM t1;
 823 INSERT INTO t1 SELECT a, b+64      FROM t1;
 824 INSERT INTO t1 SELECT a, b+128     FROM t1;
 825 INSERT INTO t1 SELECT a, b+256     FROM t1;
 826 INSERT INTO t1 SELECT a, b+512     FROM t1;
 827 INSERT INTO t1 SELECT a, b+1024    FROM t1;
 828 INSERT INTO t1 SELECT a, b+2048    FROM t1;
 829 INSERT INTO t1 SELECT a, b+4096    FROM t1;
 830 INSERT INTO t1 SELECT a, b+8192    FROM t1;
 831 INSERT INTO t1 SELECT a, b+16384   FROM t1;
 832 INSERT INTO t1 SELECT a, b+32768   FROM t1;
 833 SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
 834 SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
 835 SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
 836
 837 DROP TABLE t1;
 838
 839 #
 840 # Bug #27573: MIN() on an indexed column which is always NULL sets _other_
 841 # results to NULL
 842 #
 843 CREATE TABLE t1 ( a INT, b INT, KEY(a) );
 844 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
 845 EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
 846 SELECT MIN(a), MIN(b) FROM t1;
 847
 848 CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
 849 INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
 850 EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
 851 SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
 852
 853 CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
 854 INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
 855 EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
 856 SELECT MIN(a), MIN(b) FROM t3 where a = 2;
 857
 858 CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
 859 INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
 860 EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
 861 SELECT MIN(a), MIN(b) FROM t4 where a = 2;
 862 SELECT MIN(b), min(c) FROM t4 where a = 2;
 863
 864 CREATE TABLE t5( a INT, b INT, KEY( a, b) );
 865 INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
 866 EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
 867 SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
 868 SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
 869
 870 DROP TABLE t1, t2, t3, t4, t5;
 871
 872 #
 873 # Bug #31156: mysqld: item_sum.cc:918:
 874 #  virtual bool Item_sum_distinct::setup(THD*): Assertion
 875 #
 876
 877 CREATE TABLE t1 (a INT);
 878 INSERT INTO t1 values (),(),();
 879 SELECT (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ) as x FROM t1
 880   GROUP BY x;
 881 SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) );
 882
 883 DROP TABLE t1;
 884
 885 #
 886 # Bug #30715: Assertion failed: item_field->field->real_maybe_null(), file
 887 # .\opt_sum.cc, line
 888 #
 889
 890 CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b));
 891 SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
 892 DROP TABLE t1;
 893
 894 #
 895 # Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2;
 896 #
 897
 898 CREATE TABLE t1 (a INT);
 899 INSERT INTO t1 VALUES (1),(2),(3),(4);
 900
 901 SET SQL_MODE=ONLY_FULL_GROUP_BY;
 902 --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
 903 SELECT a FROM t1 HAVING COUNT(*)>2;
 904 --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
 905 SELECT COUNT(*), a FROM t1;
 906
 907 SET SQL_MODE=DEFAULT;
 908 SELECT a FROM t1 HAVING COUNT(*)>2;
 909 SELECT COUNT(*), a FROM t1;
 910
 911 DROP TABLE t1;
 912
 913 #
 914 # Bug #33133: Views are not transparent
 915 #
 916
 917 set SQL_MODE=ONLY_FULL_GROUP_BY;
 918
 919 CREATE TABLE t1 (a INT);
 920 INSERT INTO t1 VALUES (1),(2),(3),(4);
 921 CREATE VIEW v1 AS SELECT a,(a + 1) AS y FROM t1;
 922 EXPLAIN EXTENDED SELECT y FROM v1 GROUP BY v1.y;
 923
 924 DROP VIEW v1;
 925 DROP TABLE t1;
 926 SET SQL_MODE=DEFAULT;
 927
 928 #
 929 # Bug #34512: CAST( AVG( double ) AS DECIMAL ) returns wrong results
 930 #
 931
 932 CREATE TABLE t1(a DOUBLE);
 933 INSERT INTO t1 VALUES (10), (20);
 934 SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
 935
 936 DROP TABLE t1;
 937
 938 #
 939 # Bug #37348: Crash in or immediately after JOIN::make_sum_func_list
 940 #
 941
 942 CREATE TABLE derived1 (a bigint(21));
 943 INSERT INTO derived1 VALUES (2);
 944
 945
 946 CREATE TABLE D (
 947   pk int(11) NOT NULL AUTO_INCREMENT,
 948   int_nokey int(11) DEFAULT NULL,
 949   int_key int(11) DEFAULT NULL,
 950   filler blob,
 951   PRIMARY KEY (pk),
 952   KEY int_key (int_key)
 953 );
 954
 955 INSERT INTO D VALUES
 956   (39,40,4,repeat('  X', 42)),
 957   (43,56,4,repeat('  X', 42)),
 958   (47,12,4,repeat('  X', 42)),
 959   (71,28,4,repeat('  X', 42)),
 960   (76,54,4,repeat('  X', 42)),
 961   (83,45,4,repeat('  X', 42)),
 962   (105,53,12,NULL);
 963
 964 SELECT
 965   (SELECT COUNT( int_nokey )
 966    FROM derived1 AS X
 967    WHERE
 968      X.int_nokey < 61
 969    GROUP BY pk
 970    LIMIT 1)
 971 FROM D AS X
 972 WHERE X.int_key < 13
 973 GROUP BY int_nokey LIMIT 1;
 974
 975 DROP TABLE derived1;
 976 DROP TABLE D;
 977
 978 #
 979 # Bug #39656: Behaviour different for agg functions with & without where -
 980 # ONLY_FULL_GROUP_BY
 981 #
 982
 983 CREATE TABLE t1 (a INT, b INT);
 984 INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
 985
 986 SET SQL_MODE='ONLY_FULL_GROUP_BY';
 987
 988 SELECT COUNT(*) FROM t1;
 989 SELECT COUNT(*) FROM t1 where a=1;
 990
 991 --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
 992 SELECT COUNT(*),a FROM t1;
 993
 994 SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a;
 995
 996 --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
 997 SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a)
 998   FROM t1 outr;
 999
1000 SELECT COUNT(*) FROM t1 a JOIN t1 outr
1001   ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a);
1002
1003 SET SQL_MODE=default;
1004 DROP TABLE t1;
1005
1006
1007 ###
1008 --echo End of 5.0 tests
1009
1010 --echo #
1011 --echo # BUG#47280 - strange results from count(*) with order by multiple
1012 --echo #             columns without where/group
1013 --echo #
1014
1015 --echo #
1016 --echo # Initialize test
1017 --echo #
1018
1019 CREATE TABLE t1 (
1020   pk INT NOT NULL,
1021   i INT,
1022   PRIMARY KEY (pk)
1023 );
1024 INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
1025
1026 --echo #
1027 --echo # Start test
1028 --echo # All the following queries shall return 1 record
1029 --echo #
1030
1031 --echo
1032 --echo # Masking all correct values {11...13} for column i in this result.
1033 --replace_column 2 #
1034 SELECT MAX(pk) as max, i
1035 FROM t1
1036 ORDER BY max;
1037
1038 --echo
1039 EXPLAIN
1040 SELECT MAX(pk) as max, i
1041 FROM t1
1042 ORDER BY max;
1043
1044 --echo
1045 --echo # Only 11 is correct for collumn i in this result
1046 SELECT MAX(pk) as max, i
1047 FROM t1
1048 WHERE pk<2
1049 ORDER BY max;
1050
1051 --echo #
1052 --echo # Cleanup
1053 --echo #
1054 DROP TABLE t1;
1055
1056 --echo #
1057 --echo # Bug#43668: Wrong comparison and MIN/MAX for YEAR(2)
1058 --echo #
1059 create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime);
1060 insert into t1 values
1061   (98,1998,19980101,"1998-01-01 00:00:00"),
1062   (00,2000,20000101,"2000-01-01 00:00:01"),
1063   (02,2002,20020101,"2002-01-01 23:59:59"),
1064   (60,2060,20600101,"2060-01-01 11:11:11"),
1065   (70,1970,19700101,"1970-11-11 22:22:22"),
1066   (NULL,NULL,NULL,NULL);
1067 select min(f1),max(f1) from t1;
1068 select min(f2),max(f2) from t1;
1069 select min(f3),max(f3) from t1;
1070 select min(f4),max(f4) from t1;
1071 select a.f1 as a, b.f1 as b, a.f1 > b.f1 as gt,
1072        a.f1 < b.f1 as lt, a.f1<=>b.f1 as eq
1073 from t1 a, t1 b;
1074 select a.f1 as a, b.f2 as b, a.f1 > b.f2 as gt,
1075        a.f1 < b.f2 as lt, a.f1<=>b.f2 as eq
1076 from t1 a, t1 b;
1077 select a.f1 as a, b.f3 as b, a.f1 > b.f3 as gt,
1078        a.f1 < b.f3 as lt, a.f1<=>b.f3 as eq
1079 from t1 a, t1 b;
1080 select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt,
1081        a.f1 < b.f4 as lt, a.f1<=>b.f4 as eq
1082 from t1 a, t1 b;
1083 select *, f1 = f2 from t1;
1084 drop table t1;
1085
1086 --echo #
1087 --echo # Bug #54465: assert: field_types == 0 || field_types[field_pos] ==
1088 --echo #             MYSQL_TYPE_LONGLONG
1089 --echo #
1090
1091 CREATE TABLE t1 (a INT);
1092 INSERT INTO t1 VALUES (1), (2);
1093
1094 SELECT MAX((SELECT 1 FROM t1 ORDER BY @var LIMIT 1)) m FROM t1 t2, t1
1095        ORDER BY t1.a;
1096
1097 DROP TABLE t1;
1098
1099 --echo #
1100 --echo #  Bug#58030 crash in Item_func_geometry_from_text::val_str
1101 --echo #
1102
1103 --disable_result_log
1104
1105 SELECT MAX(TIMESTAMP(RAND(0)));
1106 SELECT MIN(TIMESTAMP(RAND(0)));
1107
1108 --echo #
1109 --echo #  Bug#58177 crash and valgrind warnings in decimal and protocol sending functions...
1110 --echo #
1111
1112 SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1113 SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1114 SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1115 SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1116 SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa');
1117
1118 --enable_result_log
1119
1120
1121 --echo #
1122 --echo # Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS
1123 --echo #
1124
1125 CREATE TABLE t1 (a BIGINT UNSIGNED);
1126 INSERT INTO t1 VALUES (18446668621106209655);
1127 SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
1128 DROP TABLE t1;
1129
1130 --echo #
1131 --echo # Bug #11766270  59343: YEAR(4): INCORRECT RESULT AND VALGRIND WARNINGS WITH MIN/MAX, UNION
1132 --echo #
1133
1134 CREATE TABLE t1(f1 YEAR(4));
1135 INSERT INTO t1 VALUES (0000),(2001);
1136 --enable_metadata
1137 (SELECT MAX(f1) FROM t1) UNION (SELECT MAX(f1) FROM t1);
1138 --disable_metadata
1139 DROP TABLE t1;
1140
1141
1142 --echo #
1143 --echo End of 5.1 tests
1144