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:
None 
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
Description:
SELECT query returns more rows than CREATE ... SELECT query:

SELECT  ...
+--------------+
60 rows in set (0.01 sec)

create table t1 SELECT ...
Query OK, 50 rows affected (0.16 sec)
Records: 50  Duplicates: 0  Warnings: 0

How to repeat:
See test case attached.
[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.