Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 506 insertions, 0 deletions

diff --git a/mysql-test/main/negation_elimination.result b/mysql-test/main/negation_elimination.result
new file mode 100644
index 00000000..7b9a76d8
--- /dev/null
+++ b/mysql-test/main/negation_elimination.result
@@ -0,0 +1,506 @@
+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	range	a	a	5	NULL	20	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	11	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	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	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	10	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	11	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	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	11	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	12	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	ref	a	a	5	const	1	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	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	11	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
+# XOR (Note: XOR is negated by negating one of the operands)
+# Should return 6,7
+SELECT * FROM t1 WHERE ((a > 5) XOR (a > 7));
+a
+6
+7
+# Should return 0..5,8..19
+SELECT * FROM t1 WHERE ((NOT (a > 5)) XOR (a > 7));
+a
+0
+1
+2
+3
+4
+5
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+SELECT * FROM t1 WHERE ((a > 5) XOR (NOT (a > 7)));
+a
+0
+1
+2
+3
+4
+5
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+SELECT * FROM t1 WHERE NOT ((a > 5) XOR (a > 7));
+a
+0
+1
+2
+3
+4
+5
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+# Should return 6,7
+SELECT * FROM t1 WHERE NOT ((NOT (a > 5)) XOR (a > 7));
+a
+6
+7
+SELECT * FROM t1 WHERE NOT ((a > 5) XOR (NOT (a > 7)));
+a
+6
+7
+# Should return 0..5,8..19
+SELECT * FROM t1 WHERE NOT ((NOT (a > 5)) XOR (NOT (a > 7)));
+a
+0
+1
+2
+3
+4
+5
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+# Should have empty result
+SELECT * FROM t1 WHERE (NULL XOR (a > 7));
+a
+SELECT * FROM t1 WHERE NOT (NULL XOR (a > 7));
+a
+# Should be simplified to "...WHERE (a XOR a)
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT ((NOT a) XOR (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` from `test`.`t1` where `test`.`t1`.`a` xor `test`.`t1`.`a`
+# Should be simplified to "...WHERE (a XOR a)
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT (a XOR (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` from `test`.`t1` where `test`.`t1`.`a` xor `test`.`t1`.`a`
+# End XOR
+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	range	a	a	5	NULL	4	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` <> 0 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` <> 0 having `test`.`t1`.`a` <> 0
+drop table t1;