1# 2# Testing of found_rows() 3# 4--disable_warnings 5drop table if exists t1,t2; 6--enable_warnings 7 8create table t1 (a int not null auto_increment, b int not null, primary key(a)); 9insert into t1 (b) values (2),(3),(5),(5),(5),(6),(7),(9); 10select SQL_CALC_FOUND_ROWS * from t1; 11select found_rows(); 12select SQL_CALC_FOUND_ROWS * from t1 limit 1; 13select found_rows(); 14select SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * from t1 limit 1; 15select found_rows(); 16select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1; 17select found_rows(); 18select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1; 19select found_rows(); 20select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1; 21select found_rows(); 22select SQL_CALC_FOUND_ROWS * from t1 left join t1 as t2 on (t1.b=t2.a) limit 2,1; 23select found_rows(); 24drop table t1; 25 26# 27# Test SQL_CALC_FOUND_ROWS optimization when used with one table and filesort 28# 29 30create table t1 (a int not null primary key); 31insert into t1 values (1),(2),(3),(4),(5); 32select sql_calc_found_rows a from t1 where a in (1,2,3) order by a desc limit 0,2; 33select FOUND_ROWS(); 34select sql_calc_found_rows a from t1 where a in (1,2,3) order by a+2 desc limit 0,2; 35select FOUND_ROWS(); 36drop table t1; 37 38# 39# Test of SQL_CALC_FOUND_ROWS with DISTINCT 40# 41 42CREATE TABLE t1 ( 43`id` smallint(5) unsigned NOT NULL auto_increment, 44`kid` smallint(5) unsigned NOT NULL default '0', 45PRIMARY KEY (`id`), 46KEY `kid` (`kid`) 47); 48 49CREATE TABLE t2 ( 50 id smallint(5) unsigned NOT NULL auto_increment, 51 name varchar(50) NOT NULL default '', 52 email varchar(50) NOT NULL default '', 53 PRIMARY KEY (id), 54 UNIQUE KEY e_n (email,name) 55); 56 57disable_query_log; 58let $1=200; 59let $2=0; 60while ($1) 61{ 62 inc $2; 63 eval INSERT INTO t2 VALUES ($2,'name$2','email$2'); 64 dec $1; 65} 66enable_query_log; 67 68--let $query=SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10 69--eval EXPLAIN $query 70FLUSH STATUS; 71--disable_result_log 72--eval $query 73--enable_result_log 74SHOW SESSION STATUS LIKE 'Sort_scan%'; 75--eval $query 76SELECT FOUND_ROWS(); 77 78SELECT 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; 79SELECT FOUND_ROWS(); 80 81SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 82SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL ORDER BY email LIMIT 10; 83INSERT INTO `t1` (`id`, `kid`) VALUES ('0', '150'); 84 85SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; 86SELECT FOUND_ROWS(); 87 88drop table t1,t2; 89 90# 91# Test bug when using range optimization 92# 93 94CREATE TABLE `t1` ( 95 `titre` char(80) NOT NULL default '', 96 `numeropost` mediumint(8) unsigned NOT NULL auto_increment, 97 `maxnumrep` int(10) unsigned NOT NULL default '0', 98 PRIMARY KEY (`numeropost`), 99 KEY `maxnumrep` (`maxnumrep`) 100) ENGINE=MyISAM ROW_FORMAT=FIXED; 101INSERT INTO t1 (titre,maxnumrep) VALUES 102('test1','1'),('test2','2'),('test3','3'); 103SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; 104SELECT FOUND_ROWS(); 105SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0; 106SELECT FOUND_ROWS(); 107SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE numeropost > 1 LIMIT 0; 108SELECT FOUND_ROWS(); 109SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 0; 110SELECT FOUND_ROWS(); 111SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost LIMIT 0; 112SELECT FOUND_ROWS(); 113drop table t1; 114 115# 116# Test problem with impossible WHERE (Bug #1468) 117# 118 119create table t1 (id int, primary key (id)); 120insert into t1 values (1), (2), (3), (4), (5); 121select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1; 122select FOUND_ROWS(); 123select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1; 124select FOUND_ROWS(); 125select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; 126select FOUND_ROWS(); 127drop table t1; 128 129# 130# Other bugs with range optimization 131# 132 133# bug #2448 134 135CREATE TABLE t1 ( a int not null, b int not null, KEY ab(a,b) ); 136INSERT INTO t1 VALUES ( 47, 1 ); 137INSERT INTO t1 VALUES ( 70, 1 ); 138SELECT * FROM t1 139WHERE 140( 141 ( b =1 AND a BETWEEN 14 AND 21 ) OR 142 ( b =2 AND a BETWEEN 16 AND 18 ) OR 143 ( b =3 AND a BETWEEN 15 AND 19 ) 144); 145DROP TABLE t1; 146 147# bug #2698 148 149CREATE TABLE t1 ( a integer, u varchar(15), r integer, key uao_idx( r, a, u)); 150DELETE FROM t1 151WHERE ( r = 1 AND a IN ( 1, 2 ) AND ( u = 'w' OR u LIKE 'w/%' ) ) 152 OR ( r = 1 AND a IN ( 3 ) AND ( u = 'w/U' OR u LIKE 'w/U/%' ) ) 153 OR ( r = 1 AND a IN ( 1, 2, 3 ) AND ( u = 'w' ) ); 154drop table t1; 155 156# 157# Bug #3738: we have a ref key 158# 159 160CREATE TABLE t1 (a VARCHAR(16), UNIQUE(a)); 161INSERT INTO t1 VALUES ('1'), ('2'), ('3'); 162SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = '2' LIMIT 0, 1; 163SELECT FOUND_ROWS(); 164DROP TABLE t1; 165 166# 167# Bug #3845: group by, having and empty result 168# 169 170CREATE TABLE t1 (a INT); 171INSERT INTO t1 VALUES (0), (0), (1), (2); 172SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = 0 GROUP BY a HAVING a > 10; 173SELECT FOUND_ROWS(); 174DROP TABLE t1; 175 176# 177# Bug #6089: queries which don't use any tables 178# 179 180SELECT 'foo'; 181SELECT FOUND_ROWS(); 182SELECT SQL_CALC_FOUND_ROWS 'foo'; 183SELECT FOUND_ROWS(); 184SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0; 185SELECT FOUND_ROWS(); 186SELECT FOUND_ROWS(); 187 188SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0; 189SELECT FOUND_ROWS(); 190 191# 192# Bug #7945: group by + distinct with constant expression + limit 193# 194 195CREATE TABLE t1 (a int, b int); 196INSERT INTO t1 VALUES (1,2), (1,3), (1,4), (1,5); 197SELECT SQL_CALC_FOUND_ROWS DISTINCT 'a' FROM t1 GROUP BY b LIMIT 2; 198SELECT FOUND_ROWS(); 199DROP TABLE t1; 200 201# End of 4.1 tests 202