MDEV-8553: Impossible where for a!=a, a<a, a>a

For a table column `a`, the above expressions logically
equate to false in all cases.

With this patch the optimizer knows about this and queries
like:

SELECT * FROM t1 WHERE a!=a

no longer need to evaluate a!=a for every row.

The same applies if the expression was `a<a`, or `a>a`

An `EXPLAIN SELECT COOUNT(*) FROM t1 WHERE a<a` will show:

id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE

Similarly `NOT (a!=a)` is always true.

EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);
id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away

2 files changed, 124 insertions, 0 deletions

diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result
index a3a702609c9..1a76f438d48 100644
--- a/mysql-test/main/type_int.result
+++ b/mysql-test/main/type_int.result
@@ -280,5 +280,87 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 DROP TABLE t1;
 #
+# MDEV-8554 Expect "Impossible WHERE" for never true values like a!=a, a<a, a>a
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN SELECT * FROM t1 WHERE a!=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 COUNT(*) FROM t1 WHERE a!=a;
+COUNT(*)
+0
+EXPLAIN SELECT * FROM t1 WHERE a>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 COUNT(*) FROM t1 WHERE a>a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<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 COUNT(*) FROM t1 WHERE a<a;
+COUNT(*)
+0
+ALTER TABLE t1 MODIFY a TINYINT NOT NULL;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a!=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 COUNT(*) FROM t1 WHERE a!=a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>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 COUNT(*) FROM t1 WHERE a>a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<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 COUNT(*) FROM t1 WHERE a<a;
+COUNT(*)
+0
+#
+# MDEV-8554 Modifing expression doesn't hit "Impossible WHERE" clause
+#
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT COUNT(*) FROM t1 WHERE not (a!=a);
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a-1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT COUNT(*) FROM t1 WHERE a>a-1;
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a+1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT COUNT(*) FROM t1 WHERE a<a+1;
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a-1<a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT COUNT(*) FROM t1 WHERE a-1<a;
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a XOR a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT COUNT(*) FROM t1 WHERE a XOR a;
+COUNT(*)
+0
+DROP TABLE t1;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test
index 8d56f94388c..96b07bb8759 100644
--- a/mysql-test/main/type_int.test
+++ b/mysql-test/main/type_int.test
@@ -206,6 +206,48 @@ EXPLAIN SELECT * FROM t1 WHERE a=200;
 EXPLAIN SELECT * FROM t1 WHERE a<=>200;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-8554 Expect "Impossible WHERE" for never true values like a!=a, a<a, a>a
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+
+EXPLAIN SELECT * FROM t1 WHERE a!=a;
+SELECT COUNT(*) FROM t1 WHERE a!=a;
+EXPLAIN SELECT * FROM t1 WHERE a>a;
+SELECT COUNT(*) FROM t1 WHERE a>a;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
+SELECT COUNT(*) FROM t1 WHERE a<a;
+
+ALTER TABLE t1 MODIFY a TINYINT NOT NULL;
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a!=a;
+SELECT COUNT(*) FROM t1 WHERE a!=a;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a;
+SELECT COUNT(*) FROM t1 WHERE a>a;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
+SELECT COUNT(*) FROM t1 WHERE a<a;
+
+--echo #
+--echo # MDEV-8554 Modifing expression doesn't hit "Impossible WHERE" clause
+--echo #
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);
+SELECT COUNT(*) FROM t1 WHERE not (a!=a);
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a-1;
+SELECT COUNT(*) FROM t1 WHERE a>a-1;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a+1;
+SELECT COUNT(*) FROM t1 WHERE a<a+1;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a-1<a;
+SELECT COUNT(*) FROM t1 WHERE a-1<a;
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
+SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a XOR a;
+SELECT COUNT(*) FROM t1 WHERE a XOR a;
+
+DROP TABLE t1;
 
 --echo #
 --echo # End of 10.4 tests