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)

1 files changed, 5 insertions, 0 deletions

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;