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