Bug #33743 nested subqueries, unique index, wrong result

• Submitted: 8 Jan 2008 16:33
• Modified: 20 Nov 2010 23:27
• Reporter: Matthias Leich
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 6.0
• OS: Any
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[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, [e-mail] +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:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:04] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-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.