From ebc168be65c43dffc56fab179f362d0355853d3e Mon Sep 17 00:00:00 2001
From: Daniel Black <daniel@linux.ibm.com>
Date: Mon, 17 Dec 2018 19:31:11 +1100
Subject: [PATCH] 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 COUNT(*) FROM t1 WHERE a<a` will show:

EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
id     select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1      SIMPLE  NULL    NULL    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   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
Warnings:
Note   1003    /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where 1
---
 mysql-test/r/explain.result | 104 ++++++++++++++++++++++++++++++++++++
 mysql-test/t/explain.test   |  38 +++++++++++++
 sql/sql_optimizer.cc        |   3 +-
 3 files changed, 144 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index f325af4ffaa..4e5d7894208 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -769,3 +769,107 @@ Warnings:
 Note	1003	delete from `test`.`t1` where (0 and <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
 DROP TABLE t1, t2;
 # End of test for Bug#23209903
+#
+# 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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+SELECT COUNT(*) FROM t1 WHERE a!=a;
+COUNT(*)
+0
+EXPLAIN SELECT * FROM t1 WHERE a>a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+SELECT COUNT(*) FROM t1 WHERE a>a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where 0
+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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where 0
+SELECT COUNT(*) FROM t1 WHERE a!=a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where 0
+SELECT COUNT(*) FROM t1 WHERE a>a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where 0
+SELECT COUNT(*) FROM t1 WHERE a<a;
+COUNT(*)
+0
+#
+# Modifing expression doesn't hit "Impossible WHERE" clause
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where 1
+SELECT COUNT(*) FROM t1 WHERE not (a!=a);
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a-1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`a` > (`test`.`t1`.`a` - 1))
+SELECT COUNT(*) FROM t1 WHERE a>a-1;
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a+1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`a` < (`test`.`t1`.`a` + 1))
+SELECT COUNT(*) FROM t1 WHERE a<a+1;
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a-1<a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` - 1) < `test`.`t1`.`a`)
+SELECT COUNT(*) FROM t1 WHERE a-1<a;
+COUNT(*)
+3
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	66.67	Using where
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (not((`test`.`t1`.`a` like `test`.`t1`.`a`)))
+SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
+COUNT(*)
+0
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE a XOR a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	44.44	Using where
+Warnings:
+Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`a` xor `test`.`t1`.`a`)
+SELECT COUNT(*) FROM t1 WHERE a XOR a;
+COUNT(*)
+0
+DROP TABLE t1;
diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test
index 3a33da8db8d..96a20322863 100644
--- a/mysql-test/t/explain.test
+++ b/mysql-test/t/explain.test
@@ -469,3 +469,41 @@ DROP TABLE t1, t2;
 
 --echo # End of test for Bug#23209903
 
+--echo #
+--echo # 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;
+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 # Modifing expression doesn't hit "Impossible WHERE" clause
+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;
diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc
index e40c29e37cf..1270e7e45df 100644
--- a/sql/sql_optimizer.cc
+++ b/sql/sql_optimizer.cc
@@ -9449,7 +9449,8 @@ static bool internal_remove_eq_conds(THD *thd, Item *cond, Item **retcond,
     Item *left_item = down_cast<Item_func *>(cond)->arguments()[0];
     Item *right_item = down_cast<Item_func *>(cond)->arguments()[1];
     if (left_item->eq(right_item, 1)) {
-      if (!left_item->maybe_null ||
+      if (*cond_value == Item::COND_FALSE ||
+          !left_item->maybe_null ||
           down_cast<Item_func *>(cond)->functype() == Item_func::EQUAL_FUNC) {
         *retcond = NULL;
         return false;  // Compare of identical items