1drop table if exists t1,t2; 2create table t1 (a int not null auto_increment, b int not null, primary key(a)); 3insert into t1 (b) values (2),(3),(5),(5),(5),(6),(7),(9); 4select SQL_CALC_FOUND_ROWS * from t1; 5a b 61 2 72 3 83 5 94 5 105 5 116 6 127 7 138 9 14select found_rows(); 15found_rows() 168 17select SQL_CALC_FOUND_ROWS * from t1 limit 1; 18a b 191 2 20select found_rows(); 21found_rows() 228 23select SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * from t1 limit 1; 24a b 251 2 26select found_rows(); 27found_rows() 288 29select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1; 30a b 318 9 32select found_rows(); 33found_rows() 348 35select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1; 36b 372 38select found_rows(); 39found_rows() 406 41select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1; 42b c 435 3 44select found_rows(); 45found_rows() 466 47select SQL_CALC_FOUND_ROWS * from t1 left join t1 as t2 on (t1.b=t2.a) limit 2,1; 48a b a b 493 5 5 5 50select found_rows(); 51found_rows() 528 53drop table t1; 54create table t1 (a int not null primary key); 55insert into t1 values (1),(2),(3),(4),(5); 56select sql_calc_found_rows a from t1 where a in (1,2,3) order by a desc limit 0,2; 57a 583 592 60select FOUND_ROWS(); 61FOUND_ROWS() 623 63select sql_calc_found_rows a from t1 where a in (1,2,3) order by a+2 desc limit 0,2; 64a 653 662 67select FOUND_ROWS(); 68FOUND_ROWS() 693 70drop table t1; 71CREATE TABLE t1 ( 72`id` smallint(5) unsigned NOT NULL auto_increment, 73`kid` smallint(5) unsigned NOT NULL default '0', 74PRIMARY KEY (`id`), 75KEY `kid` (`kid`) 76); 77CREATE TABLE t2 ( 78id smallint(5) unsigned NOT NULL auto_increment, 79name varchar(50) NOT NULL default '', 80email varchar(50) NOT NULL default '', 81PRIMARY KEY (id), 82UNIQUE KEY e_n (email,name) 83); 84EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 85id select_type table partitions type possible_keys key key_len ref rows filtered Extra 861 SIMPLE t1 NULL system kid NULL NULL NULL 0 0.00 const row not found 871 SIMPLE t2 NULL ALL e_n NULL NULL NULL 200 100.00 Using temporary 88Warnings: 89Note 1003 /* select#1 */ select distinct sql_calc_found_rows `test`.`t2`.`email` AS `email` from `test`.`t2` where isnull(NULL) limit 10 90FLUSH STATUS; 91SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 92SHOW SESSION STATUS LIKE 'Sort_scan%'; 93Variable_name Value 94Sort_scan 0 95SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 96email 97email1 98email2 99email3 100email4 101email5 102email6 103email7 104email8 105email9 106email10 107SELECT FOUND_ROWS(); 108FOUND_ROWS() 109200 110SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL order by email LIMIT 10; 111email 112email1 113email10 114email100 115email101 116email102 117email103 118email104 119email105 120email106 121email107 122SELECT FOUND_ROWS(); 123FOUND_ROWS() 124200 125SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 126email 127email1 128email2 129email3 130email4 131email5 132email6 133email7 134email8 135email9 136email10 137SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL ORDER BY email LIMIT 10; 138email 139email1 140email10 141email100 142email101 143email102 144email103 145email104 146email105 147email106 148email107 149INSERT INTO `t1` (`id`, `kid`) VALUES ('0', '150'); 150SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 151email 152email1 153email2 154email3 155email4 156email5 157email6 158email7 159email8 160email9 161email10 162SELECT FOUND_ROWS(); 163FOUND_ROWS() 164199 165drop table t1,t2; 166CREATE TABLE `t1` ( 167`titre` char(80) NOT NULL default '', 168`numeropost` mediumint(8) unsigned NOT NULL auto_increment, 169`maxnumrep` int(10) unsigned NOT NULL default '0', 170PRIMARY KEY (`numeropost`), 171KEY `maxnumrep` (`maxnumrep`) 172) ENGINE=MyISAM ROW_FORMAT=FIXED; 173INSERT INTO t1 (titre,maxnumrep) VALUES 174('test1','1'),('test2','2'),('test3','3'); 175SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; 176titre numeropost maxnumrep 177test2 2 2 178SELECT FOUND_ROWS(); 179FOUND_ROWS() 1802 181SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0; 1821 183SELECT FOUND_ROWS(); 184FOUND_ROWS() 1851 186SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE numeropost > 1 LIMIT 0; 187titre numeropost maxnumrep 188SELECT FOUND_ROWS(); 189FOUND_ROWS() 1902 191SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 0; 192titre numeropost maxnumrep 193SELECT FOUND_ROWS(); 194FOUND_ROWS() 1953 196SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost LIMIT 0; 197titre numeropost maxnumrep 198SELECT FOUND_ROWS(); 199FOUND_ROWS() 2003 201drop table t1; 202create table t1 (id int, primary key (id)); 203insert into t1 values (1), (2), (3), (4), (5); 204select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1; 205id 2064 207select FOUND_ROWS(); 208FOUND_ROWS() 2092 210select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1; 211id 212select FOUND_ROWS(); 213FOUND_ROWS() 2140 215select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; 216id 217select FOUND_ROWS(); 218FOUND_ROWS() 2190 220drop table t1; 221CREATE TABLE t1 ( a int not null, b int not null, KEY ab(a,b) ); 222INSERT INTO t1 VALUES ( 47, 1 ); 223INSERT INTO t1 VALUES ( 70, 1 ); 224SELECT * FROM t1 225WHERE 226( 227( b =1 AND a BETWEEN 14 AND 21 ) OR 228( b =2 AND a BETWEEN 16 AND 18 ) OR 229( b =3 AND a BETWEEN 15 AND 19 ) 230); 231a b 232DROP TABLE t1; 233CREATE TABLE t1 ( a integer, u varchar(15), r integer, key uao_idx( r, a, u)); 234DELETE FROM t1 235WHERE ( r = 1 AND a IN ( 1, 2 ) AND ( u = 'w' OR u LIKE 'w/%' ) ) 236OR ( r = 1 AND a IN ( 3 ) AND ( u = 'w/U' OR u LIKE 'w/U/%' ) ) 237OR ( r = 1 AND a IN ( 1, 2, 3 ) AND ( u = 'w' ) ); 238drop table t1; 239CREATE TABLE t1 (a VARCHAR(16), UNIQUE(a)); 240INSERT INTO t1 VALUES ('1'), ('2'), ('3'); 241SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = '2' LIMIT 0, 1; 242a 2432 244SELECT FOUND_ROWS(); 245FOUND_ROWS() 2461 247DROP TABLE t1; 248CREATE TABLE t1 (a INT); 249INSERT INTO t1 VALUES (0), (0), (1), (2); 250SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = 0 GROUP BY a HAVING a > 10; 251a 252SELECT FOUND_ROWS(); 253FOUND_ROWS() 2540 255DROP TABLE t1; 256SELECT 'foo'; 257foo 258foo 259SELECT FOUND_ROWS(); 260FOUND_ROWS() 2611 262SELECT SQL_CALC_FOUND_ROWS 'foo'; 263foo 264foo 265SELECT FOUND_ROWS(); 266FOUND_ROWS() 2671 268SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0; 269foo 270SELECT FOUND_ROWS(); 271FOUND_ROWS() 2721 273SELECT FOUND_ROWS(); 274FOUND_ROWS() 2751 276SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0; 277foo 278SELECT FOUND_ROWS(); 279FOUND_ROWS() 2802 281CREATE TABLE t1 (a int, b int); 282INSERT INTO t1 VALUES (1,2), (1,3), (1,4), (1,5); 283SELECT SQL_CALC_FOUND_ROWS DISTINCT 'a' FROM t1 GROUP BY b LIMIT 2; 284a 285a 286SELECT FOUND_ROWS(); 287FOUND_ROWS() 2881 289DROP TABLE t1; 290# 291# Bug #17833261 FOUND_ROWS(): DIFFERENT RESULTS FOR LOCAL LIMIT WITH AND 292# WITHOUT OFFSET IN UNION 293# 294CREATE TABLE t1 (i INT); 295INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 296(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 2 OFFSET 2) 297UNION ALL 298(SELECT 1 FROM t1 WHERE FALSE); 299i 3003 3014 302SELECT FOUND_ROWS(); 303FOUND_ROWS() 3042 305DROP TABLE t1; 306# 307# Bug #22155786: GET NEGATIVE FOUND_ROWS() FOR UNION STMT 308# 309CREATE TABLE t1 (c1 int) ; 310INSERT INTO t1 VALUES (1), (2), (3), (4); 311(SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE false LIMIT 8, 1) 312UNION ALL 313(SELECT * FROM t1 LIMIT 4, 4); 314c1 315SELECT FOUND_ROWS(); 316FOUND_ROWS() 3170 318DROP TABLE t1; 319