Bug #33743 nested subqueries, unique index, wrong result
Submitted: 8 Jan 2008 16:33 Modified: 20 Nov 2010 23:27
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:6.0 OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Triage: D2 (Serious)

[8 Jan 2008 16:33] Matthias Leich
Description:
NIST TEST 0099 in dml022 gives a wrong result with MySQL 6.0.
This failure could be called a regression because 
this test was (and is) successful with MySQL5.0 and 5.1 since
a long time. Unfortunately this test was not applied to
MySQL 6.0.
 
Snip from the protocol of the test:
SELECT * FROM t1;
EMPNUM	EMPNAME	GRADE	CITY
E1	Alice	12	Deale
E2	Betty	10	Vienna
E3	Carmen	13	Vienna
E4	Don	12	Deale
E5	Ed	13	Akron

CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
EMPNAME
Alice         
Alice   <-- There are more records with the value
Alice   <-- 'Alice' than within the base table t1.
Betty
Don

The NIST test expects here to get three rows with
the values 'Alice', 'Betty' and 'Don'.

Further experiments show that we get a correct
result set if the index t1_IDX is not unique or
if the unique index does not exist.

My environment:
- mysql-6.0-build ChangeSet@1.2785, 2008-01-02
- mysql-5.1-build ChangeSet@1.2653, 2008-01-02
  (no bug !!)
- compile-pentium-debug-max
- Linux (OpenSuSE 10.3) 64 Bit
- Intel Core2Duo T7500 64 Bit

How to repeat:
Please use the attached testcase ml010.test.
The file with expected results ml010.result
contains the result sets I expect.
[8 Jan 2008 16:36] Matthias Leich
test case

Attachment: ml010.test (application/octet-stream, text), 2.65 KiB.

[8 Jan 2008 16:36] Matthias Leich
expected results

Attachment: ml010.result (application/octet-stream, text), 2.13 KiB.

[26 Apr 2008 2:10] 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/46059

ChangeSet@1.2627, 2008-04-26 06:09:22+04:00, sergefp@mysql.com +5 -0
  BUG#33743 "nested subqueries, unique index, wrong result"
  Fix a lot of problems found by the NIST test:
  - sj_table_is_included(): Make the check if an sj-outer table is functionally 
    dependent on other sj-outer tables actually work: don't rely on 
    join_tab->ref.depend_map, it can be wrong because of equality propagation,
    do check tab->ref.items array instead.
  - setup_semijoin_dups_elimination(): 
    = Don't use InsideOut strategy if inner tables are interleaved with 
      outer (it is unusable)
    = When setting up FirstMatch strategy for a case with multiple inner 
      tables, set tab->do_firstmatch only for the last inner table.
  - get_bound_sj_equalities(): fix typo
  - best_access_path(): Don't consider InsideOut strategy when all subquery's
    outer references are bound.
    already bound.
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[3 Jun 2008 19:11] Sergey Petrunya
Note for the changelog:

Fixed a number of problems in new subquery optimization code. The effect of those problems was that MySQL could pick incorrect query plan when using InsideOut and/or FirstMatch subquery optimizations, which in turn would cause wrong query results.
[4 Jun 2008 7:46] Paul Dubois
Noted in 6.0.6 changelog.
[16 Aug 2010 6:32] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:04] Bugs System
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)
[20 Nov 2010 23:27] Paul Dubois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:13] Paul Dubois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.