MySQL Bugs: #40667: subselect.test switches connection at line 1464, forgets optimization settings

Bug #40667	subselect.test switches connection at line 1464, forgets optimization settings
Submitted:	12 Nov 2008 14:37	Modified:	2 Feb 17:56
Reporter:	Roy Lyseng
Status:	Closed
Category:	Tests: Server	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Roy Lyseng	Target Version:	6.0
Triage:	Triaged: D3 (Medium)

Description:
subselect.test is included from a number of other tests, such as subselect_no_semijoin.
However, at line 1464 the test switches to a new connection, so that the current
optimizer settings are ignored for the rest of the test script.

How to repeat:
Run subselect_no_semijoin test. Observe EXPLAIN output that indicates that a semijoin has
been executed.

Suggested fix:
Close the new connection when it is no longer needed, then switch back to the default
connection.

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

2700 Roy Lyseng	2008-11-17
      Bug#40667: subselect.test switches connection at line 1464, forgets optimization
settings
      
      One test case requires a "new" connection. When that test case is finished, close
the new connection
      and switch back to the default one.

The posted patch uncovers a problem that the result of several queries is different. It
seems we get incorrect result whenever materialization strategy is used.

The posted patch uncovers a problem that the result of several queries is different. It
seems we get incorrect result whenever materialization strategy is used.

Patch to fix this bug. To be applied after BUG#36752 is fixed.

Attachment: bug40667-fix-after-bug36752.diff (, text), 14.28 KiB.

If I apply the fix for BUG#36752, then modify subselect.test file as was done in Roy's
patch, then the result differences are in EXPLAIN outputs only.

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

2934 Satya B	2008-11-20
      Fix of BUG#40667 - Archive tables joined on primary return no result
      
      Problem
      -------
      Select queries on archive tables when joined on their primary keys
      returns no results(empty set)
      
      How it was solved
      -----------------
      Archive storage doesn't inform the handler about the fetched record 
      status when it is found. Fixed the archive storage engine to update
      the record status when it fetches successfully
      
      TestCase
      --------
      Modified archive.test testcase to test queries on tables joined by
      primary key

FYI:

BUG#36752: "subquery materialization produces wrong results when comparing different
types"

Has been pushed into 6.0-opt, so you can try now the combined effect of
this bug fix and that of BUG#36752.

Apologies, I have written wrong bug no in commit header for my patch ,when my bug no is
40677. 

Please discard the patch committed on 20th Nov 2008.

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

2702 Roy Lyseng	2008-11-26
      Bug#40667: subselect.test switches connection at line 1464, forgets optimization
settings

Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20081229085854-ui755edl9x4xomen)
(version source revid:sergefp@mysql.com-20081126143611-sh4x9pv9vmbnm00v) (merge vers:
6.0.9-alpha) (pib:6)

Changing status to reflect the reality: this has been pushed some time ago