Bug#41371 Select returns 1 row with condition "col is not null and col is null"

    For application compatibility reasons  MySQL converts "<autoincrement_column> IS NULL"
    predicates to "<autoincrement_column> = LAST_INSERT_ID()" in the first SELECT following an
    INSERT regardless of whether they're top level predicates or not. This causes wrong and
    obscure results when these predicates are combined with others on the same columns. Fixed
    by only doing the transformation on a single top-level predicate if a special SQL mode is
    turned on (sql_auto_is_null).
    Also made sql_auto_is_null off by default.

per-file comments:
  mysql-test/r/func_isnull.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
    test result updated

  mysql-test/t/func_isnull.test
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
    test case added

  sql/mysqld.cc
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
    sql_auto_is_null now is OFF by default.

  sql/sql_select.cc
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
    remove_eq_conds() split in two parts - one only checks the upper condition,
    the req_remove_eq_conds() recursively checks all the condition tree.

  mysql-test/extra/rpl_tests/rpl_insert_id.test
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        test fixed (set the sql_auto_is_null variable)

  mysql-test/r/mysqlbinlog.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/r/mysqlbinlog2.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/r/odbc.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/r/query_cache.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/r/user_var-binlog.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/suite/rpl/r/rpl_insert_id.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/suite/rpl/r/rpl_sp.result
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        result updated

  mysql-test/t/odbc.test
Bug#41371      Select returns 1 row with condition "col is not null and col is null"
        test fixed (set the sql_auto_is_null variable)

16 files changed, 185 insertions, 107 deletions

diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id.test b/mysql-test/extra/rpl_tests/rpl_insert_id.test
index bd815d9de02..f7cb1ff90a9 100644
--- a/mysql-test/extra/rpl_tests/rpl_insert_id.test
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test
@@ -117,6 +117,7 @@ sync_slave_with_master;
 --echo #
 
 connection master;
+set @@session.sql_auto_is_null=1;
 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
 eval create table t2(a int) engine=$engine_type;
 insert into t1 (a) values (null);
@@ -548,4 +549,5 @@ connection master;
 drop table t1, t2;
 drop procedure foo;
 SET @@global.concurrent_insert= @old_concurrent_insert;
+set @@session.sql_auto_is_null=default;
 sync_slave_with_master;
diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result
index 20ddc87ee78..79aecc7d82b 100644
--- a/mysql-test/r/func_isnull.result
+++ b/mysql-test/r/func_isnull.result
@@ -5,3 +5,8 @@ flush tables;
 select * from t1 where isnull(to_days(mydate));
 id	mydate
 drop table t1;
+CREATE TABLE t1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+INSERT INTO t1( id ) VALUES ( NULL );
+SELECT t1.id  FROM t1  WHERE (id  is not null and id is null );
+id
+DROP TABLE t1;
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index 5f32561798b..ff060f02c06 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -21,7 +21,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -67,7 +67,7 @@ DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -99,7 +99,7 @@ DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -121,7 +121,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -167,7 +167,7 @@ DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -199,7 +199,7 @@ DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -299,7 +299,7 @@ DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -349,7 +349,7 @@ DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result
index dba9bdc9d70..62df4731d47 100644
--- a/mysql-test/r/mysqlbinlog2.result
+++ b/mysql-test/r/mysqlbinlog2.result
@@ -22,7 +22,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -65,7 +65,7 @@ SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -104,7 +104,7 @@ SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -130,7 +130,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -165,7 +165,7 @@ SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -188,7 +188,7 @@ SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -218,7 +218,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -249,7 +249,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -284,7 +284,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -307,7 +307,7 @@ SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -338,7 +338,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -361,7 +361,7 @@ SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -380,7 +380,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -402,7 +402,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -448,7 +448,7 @@ SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -471,7 +471,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -493,7 +493,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -523,7 +523,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -566,7 +566,7 @@ SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -604,7 +604,7 @@ SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -630,7 +630,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -664,7 +664,7 @@ SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -687,7 +687,7 @@ SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -717,7 +717,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -747,7 +747,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -782,7 +782,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -805,7 +805,7 @@ SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -836,7 +836,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -858,7 +858,7 @@ SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -877,7 +877,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -899,7 +899,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -945,7 +945,7 @@ SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -968,7 +968,7 @@ SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -990,7 +990,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -1020,7 +1020,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result
index 5629d3dab33..3dbac12ca7c 100644
--- a/mysql-test/r/odbc.result
+++ b/mysql-test/r/odbc.result
@@ -1,4 +1,5 @@
 drop table if exists t1;
+set @@session.sql_auto_is_null=1;
 select {fn length("hello")}, { date "1997-10-20" };
 {fn length("hello")}	1997-10-20
 5	1997-10-20
@@ -7,9 +8,9 @@ insert into t1 SET A=NULL,B=1;
 insert into t1 SET a=null,b=2;
 select * from t1 where a is null and b=2;
 a	b
-2	2
 select * from t1 where a is null;
 a	b
+2	2
 explain select * from t1 where b is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
@@ -25,3 +26,4 @@ SELECT sql_no_cache a, last_insert_id() FROM t1;
 a	last_insert_id()
 1	1
 DROP TABLE t1;
+set @@session.sql_auto_is_null=default;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 89057603c3d..6c6d357a462 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -342,19 +342,18 @@ create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i)
 insert into mysqltest.t1 (a) values (1);
 select * from mysqltest.t1 where i is null;
 i	a
-1	1
 create table t1(a int);
 select * from t1;
 a
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	1
+Qcache_queries_in_cache	2
 select * from mysqltest.t1;
 i	a
 1	1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	2
+Qcache_queries_in_cache	3
 drop database mysqltest;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result
index 05efea79fe7..b9587bedc05 100644
--- a/mysql-test/r/user_var-binlog.result
+++ b/mysql-test/r/user_var-binlog.result
@@ -22,7 +22,7 @@ SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/;
 use test/*!*/;
 SET TIMESTAMP=10000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
index 8daed8d5c25..f0519a9d724 100644
--- a/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
+++ b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
@@ -16,7 +16,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET TIMESTAMP=10000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
index b7edf7fedb8..7214d07c783 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
@@ -16,7 +16,7 @@ SET @`v`:=_ucs2 0x006100620063 COLLATE `ucs2_general_ci`/*!*/;
 use test/*!*/;
 SET TIMESTAMP=10000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/suite/rpl/r/rpl_insert_id.result b/mysql-test/suite/rpl/r/rpl_insert_id.result
index e171e247b6c..f0be00c9966 100644
--- a/mysql-test/suite/rpl/r/rpl_insert_id.result
+++ b/mysql-test/suite/rpl/r/rpl_insert_id.result
@@ -97,6 +97,7 @@ drop table t1;
 #
 # Bug#14553: NULL in WHERE resets LAST_INSERT_ID
 #
+set @@session.sql_auto_is_null=1;
 create table t1(a int auto_increment, key(a)) engine=myisam;
 create table t2(a int) engine=myisam;
 insert into t1 (a) values (null);
@@ -531,3 +532,4 @@ id	last_id
 drop table t1, t2;
 drop procedure foo;
 SET @@global.concurrent_insert= @old_concurrent_insert;
+set @@session.sql_auto_is_null=default;
diff --git a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
index 2cb316c6a1f..b5adcefe373 100644
--- a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
+++ b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
@@ -159,7 +159,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -181,7 +181,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -290,7 +290,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -321,7 +321,7 @@ ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result
index e2946bb487a..29de7e8fe82 100644
--- a/mysql-test/suite/rpl/r/rpl_sp.result
+++ b/mysql-test/suite/rpl/r/rpl_sp.result
@@ -619,7 +619,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET TIMESTAMP=t/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test
index 6218efb882f..424f6dc6640 100644
--- a/mysql-test/t/func_isnull.test
+++ b/mysql-test/t/func_isnull.test
@@ -13,3 +13,15 @@ select * from t1 where isnull(to_days(mydate));
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug #41371    Select returns 1 row with condition "col is not null and col is null"
+#
+
+CREATE TABLE t1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
+INSERT INTO t1( id ) VALUES ( NULL );
+SELECT t1.id  FROM t1  WHERE (id  is not null and id is null );
+DROP TABLE t1;
+
+# End of 5.1 tests
+
diff --git a/mysql-test/t/odbc.test b/mysql-test/t/odbc.test
index 6a754bb32a7..f1d60e15bc8 100644
--- a/mysql-test/t/odbc.test
+++ b/mysql-test/t/odbc.test
@@ -3,6 +3,8 @@
 drop table if exists t1;
 --enable_warnings
 
+set @@session.sql_auto_is_null=1;
+
 #
 # Test some ODBC compatibility
 #
@@ -32,3 +34,5 @@ SELECT sql_no_cache a, last_insert_id() FROM t1;
 DROP TABLE t1;
 
 # End of 4.1 tests
+
+set @@session.sql_auto_is_null=default;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index ee31339c77e..c432b3d9e0a 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -7792,7 +7792,7 @@ static int mysql_init_variables(void)
   log_error_file_ptr= log_error_file;
   lc_messages_dir_ptr= lc_messages_dir;
   mysql_data_home= mysql_real_data_home;
-  thd_startup_options= (OPTION_AUTO_IS_NULL | OPTION_BIN_LOG |
+  thd_startup_options= (OPTION_BIN_LOG |
                         OPTION_QUOTE_SHOW_CREATE | OPTION_SQL_NOTES);
   protocol_version= PROTOCOL_VERSION;
   what_to_log= ~ (1L << (uint) COM_TIME);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 01b21dd7e2d..1d19ba7e96e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9119,18 +9119,26 @@ optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list,
 
 
 /**
-  Remove const and eq items.
+  Handles the reqursive job for remove_eq_conds()
 
-  @return
-    Return new item, or NULL if no condition @n
-    cond_value is set to according:
-    - COND_OK     : query is possible (field = constant)
-    - COND_TRUE   : always true	( 1 = 1 )
-    - COND_FALSE  : always false	( 1 = 2 )
+  Remove const and eq items. Return new item, or NULL if no condition
+  cond_value is set to according:
+  COND_OK    query is possible (field = constant)
+  COND_TRUE  always true	( 1 = 1 )
+  COND_FALSE always false	( 1 = 2 )
+
+  SYNPOSIS
+    remove_eq_conds()
+    thd 			THD environment
+    cond                        the condition to handle
+    cond_value                  the resulting value of the condition
+
+  RETURN
+    *COND with the simplified condition
 */
 
-COND *
-remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
+static COND *
+internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
 {
   if (cond->type() == Item::COND_ITEM)
   {
@@ -9144,7 +9152,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
     Item *item;
     while ((item=li++))
     {
-      Item *new_item=remove_eq_conds(thd, item, &tmp_cond_value);
+      Item *new_item=internal_remove_eq_conds(thd, item, &tmp_cond_value);
       if (!new_item)
 	li.remove();
       else if (item != new_item)
@@ -9193,6 +9201,86 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
   else if (cond->type() == Item::FUNC_ITEM &&
 	   ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
   {
+    Item_func_isnull *func=(Item_func_isnull*) cond;
+    Item **args= func->arguments();
+    if (args[0]->type() == Item::FIELD_ITEM)
+    {
+      Field *field=((Item_field*) args[0])->field;
+      /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */
+      /*
+        datetime_field IS NULL has to be modified to
+        datetime_field == 0
+      */
+      if (((field->type() == MYSQL_TYPE_DATE) ||
+           (field->type() == MYSQL_TYPE_DATETIME)) &&
+          (field->flags & NOT_NULL_FLAG) && !field->table->maybe_null)
+      {
+	COND *new_cond;
+	if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
+	{
+	  cond=new_cond;
+          /*
+            Item_func_eq can't be fixed after creation so we do not check
+            cond->fixed, also it do not need tables so we use 0 as second
+            argument.
+          */
+	  cond->fix_fields(thd, &cond);
+	}
+      }
+    }
+    if (cond->const_item())
+    {
+      *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
+      return (COND*) 0;
+    }
+  }
+  else if (cond->const_item())
+  {
+    *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
+    return (COND*) 0;
+  }
+  else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
+  {						// boolan compare function
+    Item *left_item=	((Item_func*) cond)->arguments()[0];
+    Item *right_item= ((Item_func*) cond)->arguments()[1];
+    if (left_item->eq(right_item,1))
+    {
+      if (!left_item->maybe_null ||
+	  ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)
+	return (COND*) 0;			// Compare of identical items
+    }
+  }
+  *cond_value=Item::COND_OK;
+  return cond;					// Point at next and level
+}
+
+
+/**
+  Remove const and eq items. Return new item, or NULL if no condition
+  cond_value is set to according:
+  COND_OK    query is possible (field = constant)
+  COND_TRUE  always true	( 1 = 1 )
+  COND_FALSE always false	( 1 = 2 )
+
+  SYNPOSIS
+    remove_eq_conds()
+    thd 			THD environment
+    cond                        the condition to handle
+    cond_value                  the resulting value of the condition
+
+  NOTES
+    calls the inner_remove_eq_conds to check all the tree reqursively
+
+  RETURN
+    *COND with the simplified condition
+*/
+
+COND *
+remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
+{
+  if (cond->type() == Item::FUNC_ITEM &&
+      ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
+  {
     /*
       Handles this special case for some ODBC applications:
       The are requesting the row that was just updated with a auto_increment
@@ -9235,52 +9323,16 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
           clear for next row
         */
         thd->substitute_null_with_insert_id= FALSE;
+
+        *cond_value= Item::COND_OK;
+        return cond;
       }
-      /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */
-      else if (((field->type() == MYSQL_TYPE_DATE) ||
-		(field->type() == MYSQL_TYPE_DATETIME)) &&
-		(field->flags & NOT_NULL_FLAG) &&
-	       !field->table->maybe_null)
-      {
-	COND *new_cond;
-	if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
-	{
-	  cond=new_cond;
-          /*
-            Item_func_eq can't be fixed after creation so we do not check
-            cond->fixed, also it do not need tables so we use 0 as second
-            argument.
-          */
-	  cond->fix_fields(thd, &cond);
-	}
-      }
-    }
-    if (cond->const_item())
-    {
-      *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
-      return (COND*) 0;
-    }
-  }
-  else if (cond->const_item())
-  {
-    *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
-    return (COND*) 0;
-  }
-  else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
-  {						// boolan compare function
-    Item *left_item=	((Item_func*) cond)->arguments()[0];
-    Item *right_item= ((Item_func*) cond)->arguments()[1];
-    if (left_item->eq(right_item,1))
-    {
-      if (!left_item->maybe_null ||
-	  ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)
-	return (COND*) 0;			// Compare of identical items
     }
   }
-  *cond_value=Item::COND_OK;
-  return cond;					// Point at next and level
+  return internal_remove_eq_conds(thd, cond, cond_value); // Scan all the condition
 }
 
+
 /* 
   Check if equality can be used in removing components of GROUP BY/DISTINCT