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 13:37	Modified:	16 Nov 2010 2:01
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Server	Severity:	S7 (Test Cases)
Version:	6.0	OS:	Any
Assigned to:	Roy Lyseng	CPU Architecture:	Any

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

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

3078 Roy Lyseng	2010-04-15
      Bug#40667: subselect.test switches connection at line 1464, forgets optimization settings
      
       @ mysql-test/t/subselect.test
          One test case requires a "new" connection. When that test case is finished, close the new connection and switch back to the default one.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Changes to test suite. No changelog entry needed.