| Bug #56423 | Different count with SELECT and CREATE SELECT queries | ||
|---|---|---|---|
| Submitted: | 31 Aug 2010 20:03 | Modified: | 14 Dec 2010 23:11 |
| Reporter: | Sveta Smirnova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.1, 5.6.99 | OS: | Any |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | regression | ||
[31 Aug 2010 20:03]
Sveta Smirnova
[31 Aug 2010 20:08]
Sveta Smirnova
In version 5.1 workaround is using MyISAM tables. With version 5.6.99 bug exists in MyISAM as well.
[1 Sep 2010 18:40]
MySQL Verification Team
This looks like a bug that is a regression from a fix to a bug #54044
[8 Sep 2010 3:11]
Jimmy Yang
This bug has nothing to do with 54044 at all. Not sure where the link is made? Fix for 54044 is to block creating table with NULL type column, and an "error creating table" message will be printed with table not created. In this case, it is the table row count actually not correct. The error is shown with/without the fix 54044. We are investigating the real cause.
[8 Sep 2010 15:11]
Jimmy Yang
The difference of the count happens when the Create Select query missed 10 items that match the criteria.
The reason being that evaluate_join_record() (sql_select.cc) exits early without further qualify the rows in "test/listgroupmember" after it finds qualified rows in test/listsubscriber. Stack:
evaluate_join_record
sub_select
do_select
JOIN::exec
For example in this repro, we find subscriberid = 51 fits push down query for table test/listsubscriber, however it skips further qualify for table"test/listgroupmember" because the select_cond_result evaluates to false:
evaluate_join_record()
{
if (select_cond)
{
select_cond_result= test(select_cond->val_int()); <====
/* check for errors evaluating the condition */
if (join->thd->is_error())
return NESTED_LOOP_ERROR;
}
if (!select_cond || select_cond_result) <=== select_cond_result = 0
{
... further join
}
else
{
/*
The condition pushed down to the table join_tab rejects all rows
with the beginning coinciding with the current partial join.
*/
}
}
In this case, if we artificially switch select_cond_result back to true, and let it proceeds, the result will include the value 51. And in the normal "SELECT" join case, it is true too for the case of value 51.
select_cond_result is set by Item_cond_and::val_int()
/**
Evaluation of AND(expr, expr, expr ...).
...
@retval
1 If all expressions are true
@retval
0 If all expressions are false or if we find a NULL expression and
'abort_on_null' is set.
@retval
NULL if all expression are either 1 or NULL
*/
longlong Item_cond_and::val_int()
Suggest the server team take a further look into this problem.
[15 Sep 2010 7:48]
Martin Hansson
Minimal test case
Attachment: bug56423.test (application/octet-stream, text), 498 bytes.
[16 Sep 2010 12:50]
Marko Mäkelä
This was caused by the Bug #38999 fix at http://lists.mysql.com/commits/110615
[17 Sep 2010 13:45]
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/118484 3513 Martin Hansson 2010-09-17 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine updates certain bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits in the buffer. Bug no 38999 occured because the implementation of UPDATE statements compare the input buffer to the output buffer using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits in the buffer, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which counts on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 and changes the server's method of comparing records. The irrelevant bits are now masked out before comparing and memcmp is avoided altogether for NULL bits.
[17 Sep 2010 14:29]
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/118488 3513 Martin Hansson 2010-09-17 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine updates certain bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits in the buffer. Bug no 38999 occured because the implementation of UPDATE statements compare the input buffer to the output buffer using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits in the buffer, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which counts on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 and changes the server's method of comparing records. The irrelevant bits are now masked out before comparing and memcmp is avoided altogether for NULL bits.
[20 Sep 2010 13:44]
Marko Mäkelä
Thanks Martin, this looks good.
[21 Sep 2010 9:46]
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/118685 3514 Martin Hansson 2010-09-21 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine updates certain bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits in the buffer. Bug no 38999 occured because the implementation of UPDATE statements compare the input buffer to the output buffer using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits in the buffer, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which counts on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 and changes the server's method of comparing records. Unless a whole row is being written, the irrelevant bits are now masked out before comparing and memcmp is avoided altogether for NULL bits.
[24 Sep 2010 14:46]
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/119065 3518 Martin Hansson 2010-09-24 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine capable of reading only a subset of a table's columns updates corresponding bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits. Bug no 38999 occurred because the implementation of UPDATE statements compare the NULL bits using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which relies on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 in both InnoDB and InnoDB plugin and changes the server's method of comparing records. For engines that always read entire rows, we proceed as usual. For engines capable of reading only select columns, the record buffers are now compared on a column by column basis. An assertion was also added so that non comparable buffers are never read. Some relevant copy-pasted code was also consolidated in a new function.
[4 Oct 2010 10:41]
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/119814 3516 Martin Hansson 2010-10-04 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine capable of reading only a subset of a table's columns updates corresponding bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits. Bug no 38999 occurred because the implementation of UPDATE statements compare the NULL bits using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which relies on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 in both InnoDB and InnoDB plugin and changes the server's method of comparing records. For engines that always read entire rows, we proceed as usual. For engines capable of reading only select columns, the record buffers are now compared on a column by column basis. An assertion was also added so that non comparable buffers are never read. Some relevant copy-pasted code was also consolidated in a new function.
[5 Oct 2010 10:36]
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/119977 3522 Martin Hansson 2010-10-05 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine capable of reading only a subset of a table's columns updates corresponding bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits. Bug no 38999 occurred because the implementation of UPDATE statements compare the NULL bits using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which relies on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 in both InnoDB and InnoDB plugin and changes the server's method of comparing records. For engines that always read entire rows, we proceed as usual. For engines capable of reading only select columns, the record buffers are now compared on a column by column basis. An assertion was also added so that non comparable buffers are never read. Some relevant copy-pasted code was also consolidated in a new function.
[7 Oct 2010 8:14]
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/120192 3523 Martin Hansson 2010-10-07 Bug#56423: Different count with SELECT and CREATE SELECT queries This is a regression from the fix for bug no 38999. A storage engine capable of reading only a subset of a table's columns updates corresponding bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits. Bug no 38999 occurred because the implementation of UPDATE statements compare the NULL bits using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which relies on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 in both InnoDB and InnoDB plugin and changes the server's method of comparing records. For engines that always read entire rows, we proceed as usual. For engines capable of reading only select columns, the record buffers are now compared on a column by column basis. An assertion was also added so that non comparable buffers are never read. Some relevant copy-pasted code was also consolidated in a new function.
[7 Oct 2010 8:20]
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/120195 3096 Martin Hansson 2010-10-07 Null merge of 5.1 version of fix for Bug#56423.
[7 Oct 2010 8:25]
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/120197 3096 Martin Hansson 2010-10-07 [merge] Null merge of 5.1 version of fix for Bug#56423.
[7 Oct 2010 8:33]
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/120201 3232 Martin Hansson 2010-10-07 [merge] Null merge of 5.1 version of fix for Bug#56423.
[7 Oct 2010 10:02]
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/120221 3215 Martin Hansson 2010-10-07 Bug#56423: Different count with SELECT and CREATE SELECT queries This is the 5.5 version of the fix. The 5.1 version was too complicated to merge and was null merged. This is a regression from the fix for bug no 38999. A storage engine capable of reading only a subset of a table's columns updates corresponding bits in the read buffer to signal that it has read NULL values for the corresponding columns. It cannot, and should not, update any other bits. Bug no 38999 occurred because the implementation of UPDATE statements compare the NULL bits using memcmp, inadvertently comparing bits that were never requested from the storage engine. The regression was caused by the storage engine trying to alleviate the situation by writing to all NULL bits, even those that it had no knowledge of. This has devastating effects for the index merge algorithm, which relies on all NULL bits, except those explicitly requested, being left unchanged. The fix reverts the fix for bug no 38999 in both InnoDB and InnoDB plugin and changes the server's method of comparing records. For engines that always read entire rows, we proceed as usual. For engines capable of reading only select columns, the record buffers are now compared on a column by column basis. An assertion was also added so that non comparable buffers are never read. Some relevant copy-pasted code was also consolidated in a new function.
[7 Oct 2010 11:54]
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/120243 3237 Martin Hansson 2010-10-07 [merge] Merge of 5.5 version of fix for Bug#56423
[2 Nov 2010 7:08]
James Day
Martin, is there a 5.1 version of this fix?
[9 Nov 2010 19:44]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:18]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:32]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[18 Nov 2010 15:55]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[14 Dec 2010 23:11]
Paul DuBois
Noted in 5.1.53, 5.5.8 changelogs. A SELECT statement could produce a different number of rows than a CREATE TABLE ... SELECT that was supposed to select the same rows.
