~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
#
2
# Test negation elimination
3
#
4
5
--disable_warnings
6
drop table if exists t1;
7
--enable_warnings
8
9
create table t1 (a int, key (a));
10
insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
11
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
12
13
explain select * from t1 where not(not(a));
14
select * from t1 where not(not(a));
15
explain select * from t1 where not(not(not(a > 10)));
16
select * from t1 where not(not(not(a > 10)));
17
explain select * from t1 where not(not(not(a < 5) and not(a > 10)));
18
select * from t1 where not(not(not(a < 5) and not(a > 10)));
19
explain select * from t1 where not(a = 10);
20
select * from t1 where not(a = 10);
21
explain select * from t1 where not(a != 10);
22
select * from t1 where not(a != 1);
23
explain select * from t1 where not(a < 10);
24
select * from t1 where not(a < 10);
25
explain select * from t1 where not(a >= 10);
26
select * from t1 where not(a >= 10);
27
explain select * from t1 where not(a > 10);
28
select * from t1 where not(a > 10);
29
explain select * from t1 where not(a <= 10);
30
select * from t1 where not(a <= 10);
31
explain select * from t1 where not(a is null);
32
select * from t1 where not(a is null);
33
explain select * from t1 where not(a is not null);
34
select * from t1 where not(a is not null);
35
explain select * from t1 where not(a < 5 or a > 15);
36
select * from t1 where not(a < 5 or a > 15);
37
explain select * from t1 where not(a < 15 and a > 5);
38
select * from t1 where not(a < 15 and a > 5);
39
40
explain select * from t1 where a = 2 or not(a < 10);
41
select * from t1 where a = 2 or not(a < 10);
42
explain select * from t1 where a > 5 and not(a > 10);
43
select * from t1 where a > 5 and not(a > 10);
44
explain select * from t1 where a > 5 xor a < 10;
45
select * from t1 where a > 5 xor a < 10;
46
47
explain select * from t1 where a = 2 or not(a < 5 or a > 15);
48
select * from t1 where a = 2 or not(a < 5 or a > 15);
49
explain select * from t1 where a = 7 or not(a < 15 and a > 5);
50
select * from t1 where a = 7 or not(a < 15 and a > 5);
51
52
explain select * from t1 where NULL or not(a < 15 and a > 5);
53
select * from t1 where NULL or not(a < 15 and a > 5);
54
explain select * from t1 where not(NULL and a > 5);
55
select * from t1 where not(NULL and a > 5);
56
explain select * from t1 where not(NULL or a);
57
select * from t1 where not(NULL or a);
58
explain select * from t1 where not(NULL and a);
59
select * from t1 where not(NULL and a);
60
61
explain select * from t1 where not((a < 5 or a < 10) and (not(a > 16) or a > 17));
62
select * from t1 where not((a < 5 or a < 10) and (not(a > 16) or a > 17));
63
explain select * from t1 where not((a < 5 and a < 10) and (not(a > 16) or a > 17));
64
select * from t1 where not((a < 5 and a < 10) and (not(a > 16) or a > 17));
65
explain select * from t1 where ((a between 5 and 15) and (not(a like 10)));
66
select * from t1 where ((a between 5 and 15) and (not(a like 10)));
67
68
delete from t1 where a > 3;
69
select a, not(not(a)) from t1;
70
explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like "1"), not (a not in (1,2)), not(a != 2) from t1 where not(not(a)) having not(not(a));
71
72
drop table t1;
73
74
# End of 4.1 tests