# # 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'; --error 1210 execute s using @a; prepare s from "select 1 limit 1, ?"; --error 1210 execute s using @a; prepare s from "select 1 limit ?, ?"; --error 1210 execute s using @a, @a; --echo End of 5.0 tests