~drizzle-trunk/drizzle/development : contents of tests/r/negation

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

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	6	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	18	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	10	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	9	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	10	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	11	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	11	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	5	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	12	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	11	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	10	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	10	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	21	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	47	1 SIMPLE t1 range a a 5 NULL 6 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	58	1 SIMPLE t1 range a a 5 NULL 18 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	88	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	134	1 SIMPLE t1 range a a 5 NULL 9 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	148	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	195	1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	211	1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	227	1 SIMPLE t1 range a a 5 NULL 5 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	275	1 SIMPLE t1 range a a 5 NULL 12 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	292	1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	330	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	345	1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
1 by brian clean slate	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
685.4.19 by Jay Pipes Fixes negation_elimination.test. Differences in EXPLAIN output were simply due to length of column differences	388	1 SIMPLE t1 index NULL a 5 NULL 21 100.00 Using where; Using index
1 by brian clean slate	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;