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