mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
0a5e4a0191
Updated tests: cases with bugs or which cannot be run with the cursor-protocol were excluded with "--disable_cursor_protocol"/"--enable_cursor_protocol" Fix for v.10.5
301 lines
8.7 KiB
Text
301 lines
8.7 KiB
Text
#
|
|
# Testing of found_rows()
|
|
#
|
|
|
|
# Tests will be skipped for the view protocol otherwise
|
|
# found_rows() will return an incorrect result.
|
|
-- source include/no_view_protocol.inc
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
--enable_warnings
|
|
|
|
--disable_ps2_protocol
|
|
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` 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)
|
|
);
|
|
|
|
--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 LIMIT 10;
|
|
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();
|
|
|
|
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 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;
|
|
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
|
|
#
|
|
# Check after fix MDEV-31522
|
|
--disable_cursor_protocol
|
|
SELECT 'foo';
|
|
--enable_cursor_protocol
|
|
SELECT FOUND_ROWS();
|
|
SELECT SQL_CALC_FOUND_ROWS 'foo';
|
|
SELECT FOUND_ROWS();
|
|
SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0;
|
|
# Check after fix MDEV-31522
|
|
--disable_cursor_protocol
|
|
SELECT FOUND_ROWS();
|
|
SELECT FOUND_ROWS();
|
|
--enable_cursor_protocol
|
|
|
|
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
|
|
|
|
#
|
|
# MDEV-5549 Wrong row counter in found_rows() result
|
|
#
|
|
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);
|
|
#explain select f1 from t1,t2 where f1=f3 and f2=3 order by f1;
|
|
select f1 from t1,t2 where f1=f3 and f2=3 order by f1;
|
|
select found_rows();
|
|
drop table t1, t2;
|
|
|
|
#
|
|
# MDEV-5898 FOUND_ROWS() return incorrect value when using DISTINCT
|
|
#
|
|
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;
|
|
|
|
--disable_result_log ONCE
|
|
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();
|
|
|
|
drop table t1, t2;
|
|
|
|
#
|
|
# MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong result again
|
|
#
|
|
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;
|
|
--disable_result_log ONCE
|
|
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;
|
|
--disable_result_log ONCE
|
|
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;
|
|
drop table t1, t2;
|
|
|
|
#
|
|
# MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result
|
|
#
|
|
create table t1 (i int, v varchar(64), key (i));
|
|
|
|
--disable_query_log
|
|
let $1=150;
|
|
while ($1)
|
|
{
|
|
eval insert into t1 values ($1 % 2, 'foo');
|
|
dec $1;
|
|
}
|
|
--enable_query_log
|
|
|
|
select sql_calc_found_rows * from t1 where i = 0 order by v limit 59,2;
|
|
select found_rows();
|
|
select sql_calc_found_rows * from t1 ignore index (i) where i = 0 order by v limit 59,2;
|
|
select found_rows();
|
|
drop table t1;
|
|
|
|
#
|
|
# MDEV-9390 Function found_rows() gives incorrect result where the previous SELECT contains ORDER BY clause
|
|
#
|
|
create table t1(c1 int);
|
|
insert into t1 values(1),(2),(3),(4),(5);
|
|
select * from t1 order by c1 limit 2,1;
|
|
select found_rows();
|
|
select sql_calc_found_rows * from t1 order by c1 limit 2,1;
|
|
select found_rows();
|
|
drop table t1;
|
|
--enable_ps2_protocol
|