Bug #11487 crash, SELECT with IN and subquery
Submitted: 21 Jun 2005 17:45 Modified: 23 Jun 2005 18:05
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0 OS:
Assigned to: Igor Babaev CPU Architecture:Any

[21 Jun 2005 17:45] Matthias Leich
I have a testcase where the following simple SELECT with
IN and subquery
   SELECT values_id FROM t0
   WHERE values_id IN (SELECT values_id FROM t2
                       WHERE select_id = 0 OR select_id = 1);
crashes the MySQL server. 
t2 has a PRIMARY KEY(select_id,values_id) and 
contains => 4 records.
The crash disappers, when I 
- remove the PRIMARY KEY of t2     or
- delete one record of t2                 or
- simplify the subquery WHERE clause to select_id = 0 
Therefore I guess it could be an optimizer problem.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1972, 2005-06-21

Version 4.1 ChangeSet@1.2306, 2005-06-21 does
not show this crash.

How to repeat:
Please use the attached testcase ml017.test with
same additional condition variations.
  copy it to mysql-test/t
  touch r/ml017.result     # Produce a dummy file with 
                                   # expected results
  ./mysql-test-run ml017
  inspect r/ml017.reject    # The protocol of the execution.
[21 Jun 2005 17:46] Matthias Leich

Attachment: ml017.test (application/test, text), 2.21 KiB.

[23 Jun 2005 9:08] 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:

[23 Jun 2005 11:15] Igor Babaev
  1.1986 05/06/23 02:08:30 igor@rurik.mysql.com +3 -0
    Fixed buf #11487.
    Added a call of  QUICK_RANGE_SELECT::init to the
    QUICK_RANGE_SELECT::reset method. Without it the second
    evaluation of a subquery employing the range access failed.
  subselect.result, subselect.test:
    Added a test case for bug #11487.

The fix will appear in 5.0.8.
[23 Jun 2005 18:05] Mike Hillyer
Documented in 5.0.8 changelog:

<listitem><para>Multiple range accesses in a subquery cause server crash. (Bug #11487)</para></listitem>