diff options
Diffstat (limited to 'mysql-test/main/limit.test')
| -rw-r--r-- | mysql-test/main/limit.test | 131 |
1 files changed, 131 insertions, 0 deletions
diff --git a/mysql-test/main/limit.test b/mysql-test/main/limit.test new file mode 100644 index 00000000..668d3b74 --- /dev/null +++ b/mysql-test/main/limit.test @@ -0,0 +1,131 @@ +# +# Test of update and delete with limit +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (a int not null default 0 primary key, b int not null default 0); +insert into t1 () values (); # Testing default values +insert into t1 values (1,1),(2,1),(3,1); +update t1 set a=4 where b=1 limit 1; +select * from t1; +update t1 set b=2 where b=1 limit 2; +select * from t1; +update t1 set b=4 where b=1; +select * from t1; +delete from t1 where b=2 limit 1; +select * from t1; +delete from t1 limit 1; +select * from t1; +drop table t1; + +create table t1 (i int); +insert into t1 (i) values(1),(1),(1); +delete from t1 limit 1; +update t1 set i=2 limit 1; +delete from t1 limit 0; +update t1 set i=3 limit 0; +select * from t1; +drop table t1; + +# LIMIT 0 + +select 0 limit 0; + +# +# Test with DELETE, ORDER BY and limit (bug #1024) +# + +CREATE TABLE t1(id int auto_increment primary key, id2 int, index(id2)); +INSERT INTO t1 (id2) values (0),(0),(0); +DELETE FROM t1 WHERE id=1; +INSERT INTO t1 SET id2=0; +SELECT * FROM t1; +DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1; +# should have deleted WHERE id=2 +SELECT * FROM t1; +DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug#8023 - limit on UNION with from DUAL, causes syntax error +# +create table t1 (a integer); +insert into t1 values (1); +# both queries must return one row +select 1 as a from t1 union all select 1 from dual limit 1; +(select 1 as a from t1) union all (select 1 from dual) limit 1; +drop table t1; + +# +# Bug #21787: COUNT(*) + ORDER BY + LIMIT returns wrong result +# +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7); +explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +drop table t1; +# End of 4.1 tests + +# +# Bug #28464: a string argument to 'limit ?' PS +# + +prepare s from "select 1 limit ?"; +set @a='qwe'; +execute s using @a; +set @a=-1; +--error ER_WRONG_ARGUMENTS +execute s using @a; +prepare s from "select 1 limit 1, ?"; +--error ER_WRONG_ARGUMENTS +execute s using @a; +prepare s from "select 1 limit ?, ?"; +--error ER_WRONG_ARGUMENTS +execute s using @a, @a; +set @a=14632475938453979136; +execute s using @a, @a; +set @a=-14632475938453979136; +--error ER_WRONG_ARGUMENTS +execute s using @a, @a; + +--echo End of 5.0 tests + +# +# Bug#37075: offset of limit clause might be truncated to 0 on 32-bits server w/o big tables +# + +select 1 as a limit 4294967296,10; + +# +# Test for LIMIT X OFFSET Y +# + +CREATE TABLE t1 (a int PRIMARY KEY auto_increment); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); +SELECT a FROM t1 ORDER BY a LIMIT 10 OFFSET 1; +SELECT a FROM t1 ORDER BY a LIMIT 10 OFFSET 10; +SELECT a FROM t1 ORDER BY a LIMIT 2 OFFSET 14; +DROP TABLE t1; + +--echo End of 5.1 tests + +--echo # +--echo # mdev-16235: SELECT over a table with LIMIT 0 +--echo # + +EXPLAIN +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; + +EXPLAIN +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; + +--echo End of 5.5 tests |