MySQL Bugs: #46051: Selects with a subquery sporadically return wrong data

Bug #46051	Selects with a subquery sporadically return wrong data
Submitted:	8 Jul 2009 17:51	Modified:	7 Aug 2009 20:40
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0,5.1,5.4	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
When a query of the form:

SELECT `datetime_nokey`  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  );

is run against a database that processes other concurrent queries, it will randomly return a random number of rows.

This is similar in nature to bug #45266, but it has been fixed and pushed.

Due to the sporadic nature of this bug, it causes various false positives when testing the subquery implementation and is therefore important to fix it.

Also note that the query is not entirely valid, the subquery is not fully formed, and yet it returns rows.

How to repeat:
1. Using the Random Query Generator, create a workload against the database:

$ perl runall.pl \
  --basedir=/build/bzr/azalea-bugfixing/ \
  --grammar=conf/subquery_semijoin.yy  \
  --threads=1 \
  --queries=100000 

and disregard the output of this script.

2. In a separate console, run:

use DBI;
$| = 1;

my $dsn = 'dbi:mysql:host=127.0.0.1:port=19306:user=root:database=test';
my $query = " SELECT `datetime_nokey`  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  )";

my $dbh = DBI->connect($dsn);
my %counts;
foreach my $trial (1..150000) {
        my $sth = $dbh->prepare($query);
        $sth->execute();
        print localtime()." [$$] trial: $trial; rows ".$sth->rows()."\n";
        $counts{$sth->rows()}++;
        exit if scalar(keys %counts) > 1;
}

The script will terminate as soon as the same query has received different number of rows on two different invocations.

Suggested fix:
Either return an error or return a consistent result.

Simpler test case:

DROP TABLE IF EXISTS `C`;
CREATE TABLE `C` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
);

INSERT INTO `C` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4),
(1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7),
(5,2), (1,8), (7,0), (0,9), (9,5);

select * from `test`.`C` where (`test`.`C`.`int_key` = `test`.`C`.`int_nokey`);
SELECT *  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  );
SELECT *  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  );
SELECT *  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  );
SELECT *  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  );
SELECT *  FROM C  WHERE `int_key`  IN (  SELECT `int_nokey`  );

drop table C;

Queries supposed to be equal, but last 5 selects return complete garbage.

Tested on 5.1.37

5.0.85 is also affected.

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/78499

2804 Evgeny Potemkin	2009-07-13
      Bug#46051: Incorrectly market field caused wrong result.
      
      In a subselect all fields from outer selects are marked as dependent on
      selects they are belong to. In some cases optimizer substitutes it for an
      equivalent expression. For example "a_field IN (SELECT outer_field)" is
      substituted with "a_field = outer_field". As we moved the outer_field to the
      upper select it's not really outer anymore. But it was left marked as outer.
      If exists an index over a_field optimizer choose wrong execution plan and thus
      return wrong result.
      
      Now the Item_in_subselect::single_value_transformer function removes dependent
      marking from fields when a subselect is optimized away.
     @ mysql-test/r/subselect.result
        Added a test case for the bug#46051.
     @ mysql-test/t/subselect.test
        Added a test case for the bug#46051.
     @ sql/item_subselect.cc
        Bug#46051: Incorrectly market field caused wrong result.
        Now the Item_in_subselect::single_value_transformer function removes dependent
        marking from fields when a subselect is optimized away.

"Incorrectly market field caused wrong result": do you mean "marked"?

Ok to push.

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/78873

2804 Evgeny Potemkin	2009-07-16
      Bug#46051: Incorrectly market field caused wrong result.
      
      In a subselect all fields from outer selects are marked as dependent on
      selects they are belong to. In some cases optimizer substitutes it for an
      equivalent expression. For example "a_field IN (SELECT outer_field)" is
      substituted with "a_field = outer_field". As we moved the outer_field to the
      upper select it's not really outer anymore. But it was left marked as outer.
      If exists an index over a_field optimizer choose wrong execution plan and thus
      return wrong result.
      
      Now the Item_in_subselect::single_value_transformer function removes dependent
      marking from fields when a subselect is optimized away.
     @ mysql-test/r/subselect.result
        Added a test case for the bug#46051.
     @ mysql-test/t/subselect.test
        Added a test case for the bug#46051.
     @ sql/item_subselect.cc
        Bug#46051: Incorrectly market field caused wrong result.
        Now the Item_in_subselect::single_value_transformer function removes dependent
        marking from fields when a subselect is optimized away.

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/78945

2787 Evgeny Potemkin	2009-07-17 [merge]
      Merged fix for the bug#46051.

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/78964

2805 Evgeny Potemkin	2009-07-18
      Bug#46051: Incorrectly market field caused wrong result.
      When during the optimization an item is moved to the upper select
      the item's context left unchanged. This caused wrong result in the 
      PS/SP mode.
      The Item_ident::remove_dependence_processor now sets the context
      of the select to which the item is moved to.
     @ mysql-test/r/subselect.result
        The test case for the bug#46051 is adjusted.
     @ mysql-test/t/subselect.test
        The test case for the bug#46051 is adjusted.
     @ sql/item.cc
        Bug#46051: Incorrectly market field caused wrong result.
        The Item_ident::remove_dependence_processor now sets the context
        of the select to which the item is moved to.

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/78965

2788 Evgeny Potemkin	2009-07-18 [merge]
      Merged corrected fix for the bug#46051.

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/78976

3032 Evgeny Potemkin	2009-07-19 [merge]
      Merged fix for the bug#46051.

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/79021

3474 Evgeny Potemkin	2009-07-20 [merge]
      Merged fix for the bug#46051.

Pushed into 5.0.85 (revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (version source revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (merge vers: 5.0.85) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:epotemkin@mysql.com-20090723122141-0hixgq8rxqxfw2dm) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Noted in 5.0.85, 5.1.38, 5.4.4 changelogs.

An optimization that moved an item from a subquery to an outer query
could cause a server crash.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The 5.4 fix has been pushed to 5.4.2.