|
1
by brian
clean slate |
1 |
#
|
2 |
# Test of update and delete with limit |
|
3 |
#
|
|
4 |
||
5 |
--disable_warnings
|
|
6 |
drop table if exists t1; |
|
7 |
--enable_warnings
|
|
8 |
||
9 |
create table t1 (a int not null default 0 primary key, b int not null default 0); |
|
10 |
insert into t1 () values (); # Testing default values |
|
11 |
insert into t1 values (1,1),(2,1),(3,1); |
|
12 |
update t1 set a=4 where b=1 limit 1; |
|
13 |
select * from t1; |
|
14 |
update t1 set b=2 where b=1 limit 2; |
|
15 |
select * from t1; |
|
16 |
update t1 set b=4 where b=1; |
|
17 |
select * from t1; |
|
18 |
delete from t1 where b=2 limit 1; |
|
19 |
select * from t1; |
|
20 |
delete from t1 limit 1; |
|
21 |
select * from t1; |
|
22 |
drop table t1; |
|
23 |
||
24 |
create table t1 (i int); |
|
25 |
insert into t1 (i) values(1),(1),(1); |
|
26 |
delete from t1 limit 1; |
|
27 |
update t1 set i=2 limit 1; |
|
28 |
delete from t1 limit 0; |
|
29 |
update t1 set i=3 limit 0; |
|
30 |
select * from t1; |
|
31 |
drop table t1; |
|
32 |
||
33 |
# LIMIT 0 |
|
34 |
||
35 |
select 0 limit 0; |
|
36 |
||
37 |
#
|
|
38 |
# Test with DELETE, ORDER BY and limit (bug #1024) |
|
39 |
#
|
|
40 |
||
41 |
CREATE TABLE t1(id int auto_increment primary key, id2 int, index(id2)); |
|
42 |
INSERT INTO t1 (id2) values (0),(0),(0); |
|
43 |
DELETE FROM t1 WHERE id=1; |
|
44 |
INSERT INTO t1 SET id2=0; |
|
45 |
SELECT * FROM t1; |
|
46 |
DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1; |
|
47 |
# should have deleted WHERE id=2 |
|
48 |
SELECT * FROM t1; |
|
49 |
DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; |
|
50 |
SELECT * FROM t1; |
|
51 |
DROP TABLE t1; |
|
52 |
||
53 |
#
|
|
54 |
# Bug#8023 - limit on UNION with from DUAL, causes syntax error |
|
55 |
#
|
|
56 |
create table t1 (a integer); |
|
57 |
insert into t1 values (1); |
|
58 |
# both queries must return one row |
|
59 |
select 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; |
|
61 |
drop table t1; |
|
62 |
||
63 |
#
|
|
64 |
# Bug #21787: COUNT(*) + ORDER BY + LIMIT returns wrong result |
|
65 |
#
|
|
66 |
create table t1 (a int); |
|
67 |
insert into t1 values (1),(2),(3),(4),(5),(6),(7); |
|
68 |
explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; |
|
69 |
select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; |
|
70 |
explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; |
|
71 |
select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; |
|
72 |
drop table t1; |
|
73 |
# End of 4.1 tests |
|
74 |
||
75 |
#
|
|
76 |
# Bug #28464: a string argument to 'limit ?' PS |
|
77 |
#
|
|
78 |
||
79 |
prepare s from "select 1 limit ?"; |
|
80 |
set @a='qwe'; |
|
81 |
execute s using @a; |
|
82 |
set @a=-1; |
|
83 |
--error ER_WRONG_ARGUMENTS
|
|
84 |
execute s using @a; |
|
85 |
prepare s from "select 1 limit 1, ?"; |
|
86 |
--error ER_WRONG_ARGUMENTS
|
|
87 |
execute s using @a; |
|
88 |
prepare s from "select 1 limit ?, ?"; |
|
89 |
--error ER_WRONG_ARGUMENTS
|
|
90 |
execute s using @a, @a; |
|
91 |
set @a=14632475938453979136; |
|
92 |
execute s using @a, @a; |
|
93 |
set @a=-14632475938453979136; |
|
94 |
--error ER_WRONG_ARGUMENTS
|
|
95 |
execute s using @a, @a; |
|
96 |
||
97 |
--echo End of 5.0 tests
|