~drizzle-trunk/drizzle/development : contents of mysql-test/r/negation

~drizzle-trunk/drizzle/development : (revision 1)

drop table if exists t1;
create table t1 (a int, key (a));
insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
explain select * from t1 where not(not(a));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using where; Using index
select * from t1 where not(not(a));
a
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where not(not(not(a > 10)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
select * from t1 where not(not(not(a > 10)));
a
0
1
2
3
4
5
6
7
8
9
10
explain select * from t1 where not(not(not(a < 5) and not(a > 10)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	5	Using where; Using index
select * from t1 where not(not(not(a < 5) and not(a > 10)));
a
5
6
7
8
9
10
explain select * from t1 where not(a = 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	19	Using where; Using index
select * from t1 where not(a = 10);
a
0
1
2
3
4
5
6
7
8
9
11
12
13
14
15
16
17
18
19
explain select * from t1 where not(a != 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	5	const	1	Using index
select * from t1 where not(a != 1);
a
1
explain select * from t1 where not(a < 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	11	Using where; Using index
select * from t1 where not(a < 10);
a
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where not(a >= 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	9	Using where; Using index
select * from t1 where not(a >= 10);
a
0
1
2
3
4
5
6
7
8
9
explain select * from t1 where not(a > 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
select * from t1 where not(a > 10);
a
0
1
2
3
4
5
6
7
8
9
10
explain select * from t1 where not(a <= 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
select * from t1 where not(a <= 10);
a
11
12
13
14
15
16
17
18
19
explain select * from t1 where not(a is null);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	20	Using where; Using index
select * from t1 where not(a is null);
a
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where not(a is not null);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	5	const	1	Using where; Using index
select * from t1 where not(a is not null);
a
NULL
explain select * from t1 where not(a < 5 or a > 15);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
select * from t1 where not(a < 5 or a > 15);
a
5
6
7
8
9
10
11
12
13
14
15
explain select * from t1 where not(a < 15 and a > 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	12	Using where; Using index
select * from t1 where not(a < 15 and a > 5);
a
0
1
2
3
4
5
15
16
17
18
19
explain select * from t1 where a = 2 or not(a < 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	12	Using where; Using index
select * from t1 where a = 2 or not(a < 10);
a
2
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where a > 5 and not(a > 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	4	Using where; Using index
select * from t1 where a > 5 and not(a > 10);
a
6
7
8
9
10
explain select * from t1 where a > 5 xor a < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using where; Using index
select * from t1 where a > 5 xor a < 10;
a
0
1
2
3
4
5
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where a = 2 or not(a < 5 or a > 15);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	11	Using where; Using index
select * from t1 where a = 2 or not(a < 5 or a > 15);
a
2
5
6
7
8
9
10
11
12
13
14
15
explain select * from t1 where a = 7 or not(a < 15 and a > 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	13	Using where; Using index
select * from t1 where a = 7 or not(a < 15 and a > 5);
a
0
1
2
3
4
5
7
15
16
17
18
19
explain select * from t1 where NULL or not(a < 15 and a > 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	12	Using where; Using index
select * from t1 where NULL or not(a < 15 and a > 5);
a
0
1
2
3
4
5
15
16
17
18
19
explain select * from t1 where not(NULL and a > 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	6	Using where; Using index
select * from t1 where not(NULL and a > 5);
a
0
1
2
3
4
5
explain select * from t1 where not(NULL or a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
select * from t1 where not(NULL or a);
a
explain select * from t1 where not(NULL and a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using where; Using index
select * from t1 where not(NULL and a);
a
0
explain select * from t1 where not((a < 5 or a < 10) and (not(a > 16) or a > 17));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	11	Using where; Using index
select * from t1 where not((a < 5 or a < 10) and (not(a > 16) or a > 17));
a
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where not((a < 5 and a < 10) and (not(a > 16) or a > 17));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	15	Using where; Using index
select * from t1 where not((a < 5 and a < 10) and (not(a > 16) or a > 17));
a
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
explain select * from t1 where ((a between 5 and 15) and (not(a like 10)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
select * from t1 where ((a between 5 and 15) and (not(a like 10)));
a
5
6
7
8
9
11
12
13
14
15
delete from t1 where a > 3;
select a, not(not(a)) from t1;
a	not(not(a))
NULL	NULL
0	0
1	1
2	1
3	1
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));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	5	100.00	Using where; Using index
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a`
drop table t1;

1 by brian clean slate	1	drop table if exists t1;
	2	create table t1 (a int, key (a));
	3	insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
	4	(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
	5	explain select * from t1 where not(not(a));
	6	id select_type table type possible_keys key key_len ref rows Extra
	7	1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index
	8	select * from t1 where not(not(a));
	9	a
	10	1
	11	2
	12	3
	13	4
	14	5
	15	6
	16	7
	17	8
	18	9
	19	10
	20	11
	21	12
	22	13
	23	14
	24	15
	25	16
	26	17
	27	18
	28	19
	29	explain select * from t1 where not(not(not(a > 10)));
	30	id select_type table type possible_keys key key_len ref rows Extra
	31	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
	32	select * from t1 where not(not(not(a > 10)));
	33	a
	34	0
	35	1
	36	2
	37	3
	38	4
	39	5
	40	6
	41	7
	42	8
	43	9
	44	10
	45	explain select * from t1 where not(not(not(a < 5) and not(a > 10)));
	46	id select_type table type possible_keys key key_len ref rows Extra
	47	1 SIMPLE t1 range a a 5 NULL 5 Using where; Using index
	48	select * from t1 where not(not(not(a < 5) and not(a > 10)));
	49	a
	50	5
	51	6
	52	7
	53	8
	54	9
	55	10
	56	explain select * from t1 where not(a = 10);
	57	id select_type table type possible_keys key key_len ref rows Extra
	58	1 SIMPLE t1 range a a 5 NULL 19 Using where; Using index
	59	select * from t1 where not(a = 10);
	60	a
	61	0
	62	1
	63	2
	64	3
65	4
66	5
67	6
68	7
69	8
70	9
71	11
72	12
73	13
74	14
75	15
76	16
77	17
78	18
79	19
80	explain select * from t1 where not(a != 10);
81	id select_type table type possible_keys key key_len ref rows Extra
82	1 SIMPLE t1 ref a a 5 const 1 Using index
83	select * from t1 where not(a != 1);
84	a
85	1
86	explain select * from t1 where not(a < 10);
87	id select_type table type possible_keys key key_len ref rows Extra
88	1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index
89	select * from t1 where not(a < 10);
90	a
91	10
92	11
93	12
94	13
95	14
96	15
97	16
98	17
99	18
100	19
101	explain select * from t1 where not(a >= 10);
102	id select_type table type possible_keys key key_len ref rows Extra
103	1 SIMPLE t1 range a a 5 NULL 9 Using where; Using index
104	select * from t1 where not(a >= 10);
105	a
106	0
107	1
108	2
109	3
110	4
111	5
112	6
113	7
114	8
115	9
116	explain select * from t1 where not(a > 10);
117	id select_type table type possible_keys key key_len ref rows Extra
118	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
119	select * from t1 where not(a > 10);
120	a
121	0
122	1
123	2
124	3
125	4
126	5
127	6
128	7
129	8
130	9
131	10
132	explain select * from t1 where not(a <= 10);
133	id select_type table type possible_keys key key_len ref rows Extra
134	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
135	select * from t1 where not(a <= 10);
136	a
137	11
138	12
139	13
140	14
141	15
142	16
143	17
144	18
145	19
146	explain select * from t1 where not(a is null);
147	id select_type table type possible_keys key key_len ref rows Extra
148	1 SIMPLE t1 range a a 5 NULL 20 Using where; Using index
149	select * from t1 where not(a is null);
150	a
151	0
152	1
153	2
154	3
155	4
156	5
157	6
158	7
159	8
160	9
161	10
162	11
163	12
164	13
165	14
166	15
167	16
168	17
169	18
170	19
171	explain select * from t1 where not(a is not null);
172	id select_type table type possible_keys key key_len ref rows Extra
173	1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
174	select * from t1 where not(a is not null);
175	a
176	NULL
177	explain select * from t1 where not(a < 5 or a > 15);
178	id select_type table type possible_keys key key_len ref rows Extra
179	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
180	select * from t1 where not(a < 5 or a > 15);
181	a
182	5
183	6
184	7
185	8
186	9
187	10
188	11
189	12
190	13
191	14
192	15
193	explain select * from t1 where not(a < 15 and a > 5);
194	id select_type table type possible_keys key key_len ref rows Extra
195	1 SIMPLE t1 range a a 5 NULL 12 Using where; Using index
196	select * from t1 where not(a < 15 and a > 5);
197	a
198	0
199	1
200	2
201	3
202	4
203	5
204	15
205	16
206	17
207	18
208	19
209	explain select * from t1 where a = 2 or not(a < 10);
210	id select_type table type possible_keys key key_len ref rows Extra
211	1 SIMPLE t1 range a a 5 NULL 12 Using where; Using index
212	select * from t1 where a = 2 or not(a < 10);
213	a
214	2
215	10
216	11
217	12
218	13
219	14
220	15
221	16
222	17
223	18
224	19
225	explain select * from t1 where a > 5 and not(a > 10);
226	id select_type table type possible_keys key key_len ref rows Extra
227	1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index
228	select * from t1 where a > 5 and not(a > 10);
229	a
230	6
231	7
232	8
233	9
234	10
235	explain select * from t1 where a > 5 xor a < 10;
236	id select_type table type possible_keys key key_len ref rows Extra
237	1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index
238	select * from t1 where a > 5 xor a < 10;
239	a
240	0
241	1
242	2
243	3
244	4
245	5
246	10
247	11
248	12
249	13
250	14
251	15
252	16
253	17
254	18
255	19
256	explain select * from t1 where a = 2 or not(a < 5 or a > 15);
257	id select_type table type possible_keys key key_len ref rows Extra
258	1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index
259	select * from t1 where a = 2 or not(a < 5 or a > 15);
260	a
261	2
262	5
263	6
264	7
265	8
266	9
267	10
268	11
269	12
270	13
271	14
272	15
273	explain select * from t1 where a = 7 or not(a < 15 and a > 5);
274	id select_type table type possible_keys key key_len ref rows Extra
275	1 SIMPLE t1 range a a 5 NULL 13 Using where; Using index
276	select * from t1 where a = 7 or not(a < 15 and a > 5);
277	a
278	0
279	1
280	2
281	3
282	4
283	5
284	7
285	15
286	16
287	17
288	18
289	19
290	explain select * from t1 where NULL or not(a < 15 and a > 5);
291	id select_type table type possible_keys key key_len ref rows Extra
292	1 SIMPLE t1 range a a 5 NULL 12 Using where; Using index
293	select * from t1 where NULL or not(a < 15 and a > 5);
294	a
295	0
296	1
297	2
298	3
299	4
300	5
301	15
302	16
303	17
304	18
305	19
306	explain select * from t1 where not(NULL and a > 5);
307	id select_type table type possible_keys key key_len ref rows Extra
308	1 SIMPLE t1 range a a 5 NULL 6 Using where; Using index
309	select * from t1 where not(NULL and a > 5);
310	a
311	0
312	1
313	2
314	3
315	4
316	5
317	explain select * from t1 where not(NULL or a);
318	id select_type table type possible_keys key key_len ref rows Extra
319	1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
320	select * from t1 where not(NULL or a);
321	a
322	explain select * from t1 where not(NULL and a);
323	id select_type table type possible_keys key key_len ref rows Extra
324	1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index
325	select * from t1 where not(NULL and a);
326	a
327	0
328	explain select * from t1 where not((a < 5 or a < 10) and (not(a > 16) or a > 17));
329	id select_type table type possible_keys key key_len ref rows Extra
330	1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index
331	select * from t1 where not((a < 5 or a < 10) and (not(a > 16) or a > 17));
332	a
333	10
334	11
335	12
336	13
337	14
338	15
339	16
340	17
341	18
342	19
343	explain select * from t1 where not((a < 5 and a < 10) and (not(a > 16) or a > 17));
344	id select_type table type possible_keys key key_len ref rows Extra
345	1 SIMPLE t1 range a a 5 NULL 15 Using where; Using index
346	select * from t1 where not((a < 5 and a < 10) and (not(a > 16) or a > 17));
347	a
348	5
349	6
350	7
351	8
352	9
353	10
354	11
355	12
356	13
357	14
358	15
359	16
360	17
361	18
362	19
363	explain select * from t1 where ((a between 5 and 15) and (not(a like 10)));
364	id select_type table type possible_keys key key_len ref rows Extra
365	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
366	select * from t1 where ((a between 5 and 15) and (not(a like 10)));
367	a
368	5
369	6
370	7
371	8
372	9
373	11
374	12
375	13
376	14
377	15
378	delete from t1 where a > 3;
379	select a, not(not(a)) from t1;
380	a not(not(a))
381	NULL NULL
382	0 0
383	1 1
384	2 1
385	3 1
386	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));
387	id select_type table type possible_keys key key_len ref rows filtered Extra
388	1 SIMPLE t1 index NULL a 5 NULL 5 100.00 Using where; Using index
389	Warnings:
390	Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a`
391	drop table t1;