diff options
Diffstat (limited to 'mysql-test/main/select_found.result')
| -rw-r--r-- | mysql-test/main/select_found.result | 365 |
1 files changed, 365 insertions, 0 deletions
diff --git a/mysql-test/main/select_found.result b/mysql-test/main/select_found.result new file mode 100644 index 00000000000..2c37c2d28d8 --- /dev/null +++ b/mysql-test/main/select_found.result @@ -0,0 +1,365 @@ +drop table if exists t1,t2; +create table t1 (a int not null auto_increment, b int not null, primary key(a)); +insert into t1 (b) values (2),(3),(5),(5),(5),(6),(7),(9); +select SQL_CALC_FOUND_ROWS * from t1; +a b +1 2 +2 3 +3 5 +4 5 +5 5 +6 6 +7 7 +8 9 +select found_rows(); +found_rows() +8 +select SQL_CALC_FOUND_ROWS * from t1 limit 1; +a b +1 2 +select found_rows(); +found_rows() +8 +select SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * from t1 limit 1; +a b +1 2 +select found_rows(); +found_rows() +8 +select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1; +a b +8 9 +select found_rows(); +found_rows() +8 +select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1; +b +2 +select found_rows(); +found_rows() +6 +select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1; +b c +5 3 +select found_rows(); +found_rows() +6 +select SQL_CALC_FOUND_ROWS * from t1 left join t1 as t2 on (t1.b=t2.a) limit 2,1; +a b a b +3 5 5 5 +select found_rows(); +found_rows() +8 +drop table t1; +create table t1 (a int not null primary key); +insert into t1 values (1),(2),(3),(4),(5); +select sql_calc_found_rows a from t1 where a in (1,2,3) order by a desc limit 0,2; +a +3 +2 +select FOUND_ROWS(); +FOUND_ROWS() +3 +select sql_calc_found_rows a from t1 where a in (1,2,3) order by a+2 desc limit 0,2; +a +3 +2 +select FOUND_ROWS(); +FOUND_ROWS() +3 +drop table t1; +CREATE TABLE t1 ( +`id` smallint(5) unsigned NOT NULL auto_increment, +`kid` smallint(5) unsigned NOT NULL default '0', +PRIMARY KEY (`id`), +KEY `kid` (`kid`) +); +CREATE TABLE t2 ( +id smallint(5) unsigned NOT NULL auto_increment, +name varchar(50) NOT NULL default '', +email varchar(50) NOT NULL default '', +PRIMARY KEY (id), +UNIQUE KEY e_n (email,name) +); +EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 Const row not found; Using temporary +1 SIMPLE t2 ALL NULL NULL NULL NULL 200 +SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +email +email1 +email2 +email3 +email4 +email5 +email6 +email7 +email8 +email9 +email10 +SELECT FOUND_ROWS(); +FOUND_ROWS() +200 +SELECT 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; +email +email1 +email10 +email100 +email101 +email102 +email103 +email104 +email105 +email106 +email107 +SELECT FOUND_ROWS(); +FOUND_ROWS() +200 +SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +email +email1 +email2 +email3 +email4 +email5 +email6 +email7 +email8 +email9 +email10 +SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL ORDER BY email LIMIT 10; +email +email1 +email10 +email100 +email101 +email102 +email103 +email104 +email105 +email106 +email107 +INSERT INTO `t1` (`id`, `kid`) VALUES ('0', '150'); +SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +email +email1 +email2 +email3 +email4 +email5 +email6 +email7 +email8 +email9 +email10 +SELECT FOUND_ROWS(); +FOUND_ROWS() +199 +drop table t1,t2; +CREATE TABLE `t1` ( +`titre` char(80) NOT NULL default '', +`numeropost` mediumint(8) unsigned NOT NULL auto_increment, +`maxnumrep` int(10) unsigned NOT NULL default '0', +PRIMARY KEY (`numeropost`), +KEY `maxnumrep` (`maxnumrep`) +) ENGINE=MyISAM ROW_FORMAT=FIXED; +INSERT INTO t1 (titre,maxnumrep) VALUES +('test1','1'),('test2','2'),('test3','3'); +SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; +titre numeropost maxnumrep +test2 2 2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0; +1 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE numeropost > 1 LIMIT 0; +titre numeropost maxnumrep +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 0; +titre numeropost maxnumrep +SELECT FOUND_ROWS(); +FOUND_ROWS() +3 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost LIMIT 0; +titre numeropost maxnumrep +SELECT FOUND_ROWS(); +FOUND_ROWS() +3 +drop table t1; +create table t1 (id int, primary key (id)); +insert into t1 values (1), (2), (3), (4), (5); +select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1; +id +4 +select FOUND_ROWS(); +FOUND_ROWS() +2 +select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1; +id +select FOUND_ROWS(); +FOUND_ROWS() +0 +select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; +id +select FOUND_ROWS(); +FOUND_ROWS() +0 +drop table t1; +CREATE TABLE t1 ( a int not null, b int not null, KEY ab(a,b) ); +INSERT INTO t1 VALUES ( 47, 1 ); +INSERT INTO t1 VALUES ( 70, 1 ); +SELECT * FROM t1 +WHERE +( +( b =1 AND a BETWEEN 14 AND 21 ) OR +( b =2 AND a BETWEEN 16 AND 18 ) OR +( b =3 AND a BETWEEN 15 AND 19 ) +); +a b +DROP TABLE t1; +CREATE TABLE t1 ( a integer, u varchar(15), r integer, key uao_idx( r, a, u)); +DELETE FROM t1 +WHERE ( r = 1 AND a IN ( 1, 2 ) AND ( u = 'w' OR u LIKE 'w/%' ) ) +OR ( r = 1 AND a IN ( 3 ) AND ( u = 'w/U' OR u LIKE 'w/U/%' ) ) +OR ( r = 1 AND a IN ( 1, 2, 3 ) AND ( u = 'w' ) ); +drop table t1; +CREATE TABLE t1 (a VARCHAR(16), UNIQUE(a)); +INSERT INTO t1 VALUES ('1'), ('2'), ('3'); +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = '2' LIMIT 0, 1; +a +2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (0), (1), (2); +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = 0 GROUP BY a HAVING a > 10; +a +SELECT FOUND_ROWS(); +FOUND_ROWS() +0 +DROP TABLE t1; +SELECT 'foo'; +foo +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS 'foo'; +foo +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0; +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0; +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,2), (1,3), (1,4), (1,5); +SELECT SQL_CALC_FOUND_ROWS DISTINCT 'a' FROM t1 GROUP BY b LIMIT 2; +a +a +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +DROP TABLE t1; +create table t1 (f1 int primary key, f2 tinyint) engine=myisam; +insert t1 values (10,3),(11,2),(12,3); +create table t2 (f3 int primary key) engine=myisam; +insert t2 values (11),(12),(13); +select f1 from t1,t2 where f1=f3 and f2=3 order by f1; +f1 +12 +select found_rows(); +found_rows() +1 +drop table t1, t2; +create table t1 (a1 int auto_increment primary key, c1 int); +insert t1 (a1) values (null); +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +insert t1 (a1) select null from t1; +update t1 set c1=a1 % 2; +create table t2 (a2 int, b2 int, c2 char(16) default '', primary key (a2, b2)); +insert t2 select a1, 1, 'ok' from t1; +insert t2 select a1, 2, 'ko' from t1; +insert t2 select a1, 3, 'ko' from t1; +insert t2 select a1, 4, 'ok' from t1; +insert t2 select a1, 5, 'ok' from t1; +select sql_calc_found_rows distinct a1,c2 from t1 join t2 on a2=a1 +where a1 <= 256 and c1=0 and c2='ok' order by a1 desc limit 46; +select found_rows(); +found_rows() +128 +drop table t1, t2; +create table t1 (i1 int, v1 int, primary key(i1,v1)); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 (i2 int primary key, v2 int); +insert into t2 values (1,5),(2,5),(3,10); +select 1 as res from t1 left join t2 on i1 = i2 where v2 = 5 or v1 = 5 or v1 = 10 order by v1; +res +1 +1 +select sql_calc_found_rows 1 as res from t1 left join t2 on i1 = i2 where v2 = 5 or v1 = 5 or v1 = 10 order by v1 limit 1; +select found_rows() as count; +count +2 +select sql_calc_found_rows 1 as res from t1 left join t2 on i1 = i2 where v2 = 5 order by v1 limit 1; +select found_rows() as count; +count +2 +drop table t1, t2; +create table t1 (i int, v varchar(64), key (i)); +select sql_calc_found_rows * from t1 where i = 0 order by v limit 59,2; +i v +0 foo +0 foo +select found_rows(); +found_rows() +75 +select sql_calc_found_rows * from t1 ignore index (i) where i = 0 order by v limit 59,2; +i v +0 foo +0 foo +select found_rows(); +found_rows() +75 +drop table t1; +create table t1(c1 int); +insert into t1 values(1),(2),(3),(4),(5); +select * from t1 order by c1 limit 2,1; +c1 +3 +select found_rows(); +found_rows() +3 +select sql_calc_found_rows * from t1 order by c1 limit 2,1; +c1 +3 +select found_rows(); +found_rows() +5 +drop table t1; |