#At file:///home/hf/work/mysql_common/41371/ based on revid:vvaintroub@stripped
2938 Alexey Botchkov 2009-11-03
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)
modified:
mysql-test/extra/rpl_tests/rpl_insert_id.test
mysql-test/r/func_isnull.result
mysql-test/r/mysqlbinlog.result
mysql-test/r/mysqlbinlog2.result
mysql-test/r/odbc.result
mysql-test/r/query_cache.result
mysql-test/r/user_var-binlog.result
mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result
mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
mysql-test/suite/rpl/r/rpl_insert_id.result
mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
mysql-test/suite/rpl/r/rpl_sp.result
mysql-test/t/func_isnull.test
mysql-test/t/odbc.test
sql/mysqld.cc
sql/sql_select.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_insert_id.test'
--- a/mysql-test/extra/rpl_tests/rpl_insert_id.test 2008-10-08 11:23:53 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test 2009-11-03 13:54:41 +0000
@@ -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;
=== modified file 'mysql-test/r/func_isnull.result'
--- a/mysql-test/r/func_isnull.result 2002-05-16 20:35:09 +0000
+++ b/mysql-test/r/func_isnull.result 2009-11-03 13:54:41 +0000
@@ -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;
=== modified file 'mysql-test/r/mysqlbinlog.result'
--- a/mysql-test/r/mysqlbinlog.result 2009-09-30 02:31:25 +0000
+++ b/mysql-test/r/mysqlbinlog.result 2009-11-03 13:54:41 +0000
@@ -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 *//*!*/;
=== modified file 'mysql-test/r/mysqlbinlog2.result'
--- a/mysql-test/r/mysqlbinlog2.result 2008-04-02 09:49:22 +0000
+++ b/mysql-test/r/mysqlbinlog2.result 2009-11-03 13:54:41 +0000
@@ -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 *//*!*/;
=== modified file 'mysql-test/r/odbc.result'
--- a/mysql-test/r/odbc.result 2006-07-10 13:44:05 +0000
+++ b/mysql-test/r/odbc.result 2009-11-03 13:54:41 +0000
@@ -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()
a last_insert_id()
1 1
DROP TABLE t1;
+set @@session.sql_auto_is_null=default;
=== modified file 'mysql-test/r/query_cache.result'
--- a/mysql-test/r/query_cache.result 2009-09-10 09:18:29 +0000
+++ b/mysql-test/r/query_cache.result 2009-11-03 13:54:41 +0000
@@ -342,19 +342,18 @@ create table mysqltest.t1 (i int not nul
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
=== modified file 'mysql-test/r/user_var-binlog.result'
--- a/mysql-test/r/user_var-binlog.result 2008-04-02 09:49:22 +0000
+++ b/mysql-test/r/user_var-binlog.result 2009-11-03 13:54:41 +0000
@@ -22,7 +22,7 @@ SET @`a b`:=_latin1 0x68656C6C6F COLLATE
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 *//*!*/;
=== modified file 'mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result'
--- a/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result 2009-05-31 05:44:41 +0000
+++ b/mysql-test/suite/binlog/r/binlog_row_ctype_ucs.result 2009-11-03 13:54:41 +0000
@@ -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 *//*!*/;
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result 2008-10-23 19:27:09 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result 2009-11-03 13:54:41 +0000
@@ -16,7 +16,7 @@ SET @`v`:=_ucs2 0x006100620063 COLLATE `
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 *//*!*/;
=== modified file 'mysql-test/suite/rpl/r/rpl_insert_id.result'
--- a/mysql-test/suite/rpl/r/rpl_insert_id.result 2008-10-08 11:23:53 +0000
+++ b/mysql-test/suite/rpl/r/rpl_insert_id.result 2009-11-03 13:54:41 +0000
@@ -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;
=== modified file 'mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result'
--- a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result 2008-04-02 09:49:22 +0000
+++ b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result 2009-11-03 13:54:41 +0000
@@ -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 *//*!*/;
=== modified file 'mysql-test/suite/rpl/r/rpl_sp.result'
--- a/mysql-test/suite/rpl/r/rpl_sp.result 2009-10-22 22:30:28 +0000
+++ b/mysql-test/suite/rpl/r/rpl_sp.result 2009-11-03 13:54:41 +0000
@@ -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 *//*!*/;
=== modified file 'mysql-test/t/func_isnull.test'
--- a/mysql-test/t/func_isnull.test 2005-07-28 00:22:47 +0000
+++ b/mysql-test/t/func_isnull.test 2009-11-03 13:54:41 +0000
@@ -13,3 +13,15 @@ select * from t1 where isnull(to_days(my
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
+
=== modified file 'mysql-test/t/odbc.test'
--- a/mysql-test/t/odbc.test 2006-07-10 13:27:03 +0000
+++ b/mysql-test/t/odbc.test 2009-11-03 13:54:41 +0000
@@ -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()
DROP TABLE t1;
# End of 4.1 tests
+
+set @@session.sql_auto_is_null=default;
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2009-11-02 22:24:24 +0000
+++ b/sql/mysqld.cc 2009-11-03 13:54:41 +0000
@@ -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);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-10-28 10:07:30 +0000
+++ b/sql/sql_select.cc 2009-11-03 13:54:41 +0000
@@ -9119,18 +9119,26 @@ optimize_cond(JOIN *join, COND *conds, L
/**
- 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, It
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, It
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@stripped) */
+ /*
+ 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, It
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@stripped) */
- 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
Attachment: [text/bzr-bundle] bzr/holyfoot@mysql.com-20091103135441-x9oxk7q1ljqbvs4o.bundle
| Thread |
|---|
| • bzr commit into mysql-5.5-next-mr-bugfixing branch (holyfoot:2938)Bug#41371 | Alexey Botchkov | 3 Nov |
