1# 2# Test of update and delete with limit 3# 4 5--disable_warnings 6drop table if exists t1; 7--enable_warnings 8 9create table t1 (a int not null default 0 primary key, b int not null default 0); 10insert into t1 () values (); # Testing default values 11insert into t1 values (1,1),(2,1),(3,1); 12update t1 set a=4 where b=1 limit 1; 13select * from t1; 14update t1 set b=2 where b=1 limit 2; 15select * from t1; 16update t1 set b=4 where b=1; 17select * from t1; 18delete from t1 where b=2 limit 1; 19select * from t1; 20delete from t1 limit 1; 21select * from t1; 22drop table t1; 23 24create table t1 (i int); 25insert into t1 (i) values(1),(1),(1); 26delete from t1 limit 1; 27update t1 set i=2 limit 1; 28delete from t1 limit 0; 29update t1 set i=3 limit 0; 30select * from t1; 31drop table t1; 32 33# LIMIT 0 34 35select 0 limit 0; 36 37# 38# Test with DELETE, ORDER BY and limit (bug #1024) 39# 40 41CREATE TABLE t1(id int auto_increment primary key, id2 int, index(id2)); 42INSERT INTO t1 (id2) values (0),(0),(0); 43DELETE FROM t1 WHERE id=1; 44INSERT INTO t1 SET id2=0; 45SELECT * FROM t1; 46DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1; 47# should have deleted WHERE id=2 48SELECT * FROM t1; 49DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; 50SELECT * FROM t1; 51DROP TABLE t1; 52 53# 54# Bug#8023 - limit on UNION with from DUAL, causes syntax error 55# 56create table t1 (a integer); 57insert into t1 values (1); 58# both queries must return one row 59select 1 as a from t1 union all select 1 from dual limit 1; 60(select 1 as a from t1) union all (select 1 from dual) limit 1; 61drop table t1; 62 63# 64# Bug #21787: COUNT(*) + ORDER BY + LIMIT returns wrong result 65# 66create table t1 (a int); 67insert into t1 values (1),(2),(3),(4),(5),(6),(7); 68explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; 69select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; 70explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; 71select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; 72drop table t1; 73# End of 4.1 tests 74 75# 76# Bug #28464: a string argument to 'limit ?' PS 77# 78 79prepare s from "select 1 limit ?"; 80set @a='qwe'; 81execute s using @a; 82set @a=-1; 83--error ER_WRONG_ARGUMENTS 84execute s using @a; 85prepare s from "select 1 limit 1, ?"; 86--error ER_WRONG_ARGUMENTS 87execute s using @a; 88prepare s from "select 1 limit ?, ?"; 89--error ER_WRONG_ARGUMENTS 90execute s using @a, @a; 91set @a=14632475938453979136; 92execute s using @a, @a; 93set @a=-14632475938453979136; 94--error ER_WRONG_ARGUMENTS 95execute s using @a, @a; 96 97--echo End of 5.0 tests 98 99# 100# Bug#37075: offset of limit clause might be truncated to 0 on 32-bits server w/o big tables 101# 102 103select 1 as a limit 4294967296,10; 104 105# 106# Test for LIMIT X OFFSET Y 107# 108 109CREATE TABLE t1 (a int PRIMARY KEY auto_increment); 110INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); 111INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(); 112SELECT a FROM t1 ORDER BY a LIMIT 10 OFFSET 1; 113SELECT a FROM t1 ORDER BY a LIMIT 10 OFFSET 10; 114SELECT a FROM t1 ORDER BY a LIMIT 2 OFFSET 14; 115DROP TABLE t1; 116 117--echo End of 5.1 tests 118 119--echo # 120--echo # mdev-16235: SELECT over a table with LIMIT 0 121--echo # 122 123EXPLAIN 124SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; 125SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; 126 127EXPLAIN 128SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; 129SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; 130 131--echo End of 5.5 tests 132