Bug #41371 | Select returns 1 row with condition "col is not null and col is null" | ||
---|---|---|---|
Submitted: | 10 Dec 2008 18:36 | Modified: | 7 Mar 2010 18:16 |
Reporter: | Eric GuimarĂ£es | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 4.1.22, 5.0.51, 5.0.72, 5.1.30, 6.0.8 | OS: | Linux |
Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[10 Dec 2008 18:36]
Eric GuimarĂ£es
[10 Dec 2008 19:38]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 4.1.22, 5.0.72, 5.1.30, 6.0.8: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.0.72-enterprise-gpl-nt MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE b ( -> id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.20 sec) mysql> INSERT INTO b( id ) VALUES ( NULL ); Query OK, 1 row affected (0.03 sec) mysql> SELECT b.id FROM b WHERE (id is not null and id is null ); +----+ | id | +----+ | 1 | +----+ 1 row in set (0.02 sec) mysql> SELECT b.id FROM b WHERE (id is not null and id is null ); Empty set (0.00 sec) mysql> SELECT b.id FROM b WHERE (id is not null and id is null ); Empty set (0.00 sec)
[4 Feb 2009 10:04]
Hajo Skwirblies
We experienced the behavior also with selects on different tables. Reproduced on: 5.0.45 (amd64),5.0.67 (amd64/i686) On i686 the problem ocurrs only on the first "select after insert" per session. On amd64 every "select after insert" returns the wrong set with increasing ids. CREATE DATABASE IF NOT EXISTS bugtest; USE bugtest; DROP TABLE IF EXISTS one; CREATE TABLE one ( `id` bigint(20) NOT NULL auto_increment, `isdeleted` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM; DROP TABLE IF EXISTS two; CREATE TABLE two ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM; INSERT INTO `two` (`id`, `name`) VALUES (1, 'eins'), (2, 'zwei'), (3, 'drei'); INSERT INTO one SET isdeleted='0'; SELECT id, name FROM two WHERE id IS NULL; +----+------+ | id | name | +----+------+ | 1 | eins | +----+------+
[17 Jun 2009 12:42]
V Venkateswaran
According to the documentation in the following link (http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-functionality.html#connec...) Certain ODBC applications (including Delphi and Access) may have trouble obtaining the auto-increment value using the previous examples. In this case, try the following statement as an alternative: SELECT * FROM tbl WHERE auto IS NULL; where auto is an auto-increment column. This special case needs to work only when the condition is alone in the where clause (no AND/OR) and is at top-level. Hence the behavior needs to be restricted to the following case SELECT * FROM <table> WHERE <autoinc> IS NULL So the following query returning a column is a bug mysql> SELECT b.id FROM b WHERE (id is not null and id is null ); +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) But on the other hand the below is not a bug and is expected behaviour SELECT id, name FROM two WHERE id IS NULL; +----+------+ | id | name | +----+------+ | 1 | eins | +----+------+ ----------------------------------------------------------------------------------- | Related Code | | ------------ | | | | from sql_select.cc (remove_eq_conds) function, | | | | Pls see the following comment in the function | | | | Handles this special case for some ODBC applications: | | The are requesting the row that was just updated with a auto_increment | | value with this construct: | | | | SELECT * from table_name where auto_increment_column IS NULL | | This will be changed to: | | SELECT * from table_name where auto_increment_column = LAST_INSERT_ID | | | | This function basically is used to recognize obvious equalities and | | inequalities like 1=1, 1=2 etc. This function also checks for the IS NULL | | condition as a special case. | | | | The function performs the above query transformation for the first | | select after an insert. | ------------------------------------------------------------------------------------
[21 Jun 2009 1:30]
V Venkateswaran
Item Tree for the where condition --------------------------------- The Item tree for the condition (id is not null and id is null ) of the query: SELECT b.id FROM b WHERE (id is not null and id is null ); can be represented as below. COND__ITEM (Item_func::COND_AND_FUNC) | | | | ------------------------------- | | | | Item_func::ISNOTNULL_FUNC Item_func::ISNULL_FUNC COND_ITEM contains a list of Items (2 items), one for the condition "is not null" represented by Item_func::ISNOTNULL_FUNC and one for the condition "is null" represented by Item_func::ISNULL_FUNC. Parsing the Item Tree to remove obvious equality and unequality conditions -------------------------------------------------------------------------- The remove_eq_conds functions parses and replaces the obvious conditions in the item tree to increase the efficiency of the query evaluation process. For e.g. The condition 1=1 will be replaced by COND_TRUE The condition 1=2 will be replaced by COND_FALSE The condition age > 40 will be replaced by COND_OK The function "remove_eq_conds" works by recursively parsing the tree. For example, in the above case we pass the root pointer "COND__ITEM (Item_func::COND_AND_FUNC)" to the remove_eq_conds function. This function then calls itself on "Item_func::ISNOTNULL_FUNC" and "Item_func::ISNULL_FUNC" using the argument list that is part of the root(List_iterator<Item> li(*((Item_cond*) cond)->argument_list());). Replacing the "is null" item ---------------------------- The following condition else if (cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC) is used to check for the "is null" condition and if present is replaced with a check for the last inserted autoincrement value in the following code. COND *new_cond; if ((new_cond= new Item_func_eq(args[0], new Item_int("last_insert_id()", thd->read_first_successful_insert_id_in_prev_stmt(), MY_INT64_NUM_DECIMAL_DIGITS)))) { 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); } Performing query transformation only in the case of "select * from <table> where <auto>' is NULL queries --------------------------------------------------------------------------------------------------------- The easiest way of doing this would be to move the query transformation code into a separate function and call this query transformation code before every call to remove_eq_conds.
[21 Jun 2009 1:33]
V Venkateswaran
rough patch for doing restrictive query transformation
Attachment: 41371.diff.txt (text/plain), 6.99 KiB.
[21 Jun 2009 1:36]
V Venkateswaran
The file 41371.diff.txt contains a rough patch that moves the auto is NULL transformation into a separate function. The above patch produces the following correct cases case: 1 mysql> CREATE TABLE b (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO b( id ) VALUES ( NULL ); Query OK, 1 row affected (0.00 sec) mysql> SELECT b.id FROM b WHERE (id is not null and id is null ); Empty set (0.00 sec) case: 2 mysql> CREATE TABLE b (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO b( id ) VALUES ( NULL ); Query OK, 1 row affected (0.00 sec) mysql> SELECT b.id FROM b WHERE id is null; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
[3 Sep 2009 12:48]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82328 2709 Alexey Botchkov 2009-09-03 Bug#41371 Select returns 1 row with condition "col is not null and col is null" For the AUTO_INCREMENT field, the IS NULL condition is turned into 'col == LAST_INSERT_ID'. Fixed by preventing that transformation for any other conditions than simple 'col IS NULL'. 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/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.
[24 Oct 2009 11:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88084 2709 Alexey Botchkov 2009-10-24 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.
[3 Nov 2009 4:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89022 2935 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)
[3 Nov 2009 15:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89154 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)
[4 Nov 2009 10:26]
Alexey Botchkov
mysql-next-mr-bugfixing
[20 Nov 2009 12:57]
Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:holyfoot@mysql.com-20091104085357-p6tcgw8t5adbzxmn) (merge vers: 6.0.14-alpha) (pib:13)
[1 Dec 2009 20:14]
Paul DuBois
Noted in 6.0.14 changelog. For application compatibility reasons, when sql_auto_is_null is 1, MySQL converts auto_inc_col IS NULL to auto_inc_col = LAST_INSERT_ID(). However, this was being done regardless of whether the predicate was alone or at the top level. Now it occurs only when it is a single top-level level predicate. In conjunction with this bug fix, the default value of the sql_auto_is_null system variable has been changed from 1 to 0, which may cause incompatibilities with existing applications. Setting report to NDI pending push to 5.6.x.
[11 Dec 2009 6:04]
Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:45]
Paul DuBois
Noted in 5.6.0 changelog.
[6 Mar 2010 10:52]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[7 Mar 2010 18:16]
Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.
[27 Sep 2010 22:33]
Roel Van de Paar
Bug #47005 was marked as duplicate since apparently it was fixed by this one.