460 lines
16 KiB
Plaintext
460 lines
16 KiB
Plaintext
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
8
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_CALC_FOUND_ROWS * from t1 limit 1;
|
|
a b
|
|
1 2
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
8
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * from t1 limit 1;
|
|
a b
|
|
1 2
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
8
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1;
|
|
a b
|
|
8 9
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
8
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1;
|
|
b
|
|
2
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
6
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1;
|
|
b c
|
|
5 3
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
6
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
8
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
EXPLAIN FORMAT=tree select SQL_CALC_FOUND_ROWS * from t1 limit 10 offset 100;
|
|
EXPLAIN
|
|
-> Limit/Offset: 10/100 row(s) (no early end due to SQL_CALC_FOUND_ROWS)
|
|
-> Table scan on t1 (cost=1.30 rows=8)
|
|
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select SQL_CALC_FOUND_ROWS * from t1 limit 10 offset 100;
|
|
a b
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select found_rows();
|
|
found_rows()
|
|
8
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
3
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
3
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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`)
|
|
) charset utf8mb4;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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)
|
|
) charset utf8mb4;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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 partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL system kid NULL NULL NULL 0 0.00 const row not found
|
|
1 SIMPLE t2 NULL index e_n e_n 404 NULL 200 100.00 NULL
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
Note 1003 /* select#1 */ select distinct sql_calc_found_rows `test`.`t2`.`email` AS `email` from `test`.`t2` where (NULL is null) limit 10
|
|
FLUSH STATUS;
|
|
SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
|
|
SHOW SESSION STATUS LIKE 'Sort_scan%';
|
|
Variable_name Value
|
|
Sort_scan 0
|
|
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
|
|
email10
|
|
email100
|
|
email101
|
|
email102
|
|
email103
|
|
email104
|
|
email105
|
|
email106
|
|
email107
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
200
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
200
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
199
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
2
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0;
|
|
1
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE numeropost > 1 LIMIT 0;
|
|
titre numeropost maxnumrep
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
2
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 0;
|
|
titre numeropost maxnumrep
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
3
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost LIMIT 0;
|
|
titre numeropost maxnumrep
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
3
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
2
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1;
|
|
id
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
0
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1;
|
|
id
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
select FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
0
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
0
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
DROP TABLE t1;
|
|
SELECT 'foo';
|
|
foo
|
|
foo
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS 'foo';
|
|
foo
|
|
foo
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0;
|
|
foo
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0;
|
|
foo
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
2
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
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
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
1
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #17833261 FOUND_ROWS(): DIFFERENT RESULTS FOR LOCAL LIMIT WITH AND
|
|
# WITHOUT OFFSET IN UNION
|
|
#
|
|
CREATE TABLE t1 (i INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
|
|
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 2 OFFSET 2)
|
|
UNION ALL
|
|
(SELECT 1 FROM t1 WHERE FALSE);
|
|
i
|
|
3
|
|
4
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
2
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #22155786: GET NEGATIVE FOUND_ROWS() FOR UNION STMT
|
|
#
|
|
CREATE TABLE t1 (c1 int) ;
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4);
|
|
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE false LIMIT 8, 1)
|
|
UNION ALL
|
|
(SELECT * FROM t1 LIMIT 4, 4);
|
|
c1
|
|
Warnings:
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
0
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
DROP TABLE t1;
|