MySQL Bugs: #37548: result value erronously reported being NULL in certain subqueries

Bug #37548	result value erronously reported being NULL in certain subqueries
Submitted:	20 Jun 2008 15:40	Modified:	16 Sep 2008 4:24
Reporter:	Frederic Steinfels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.1.23 and up, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	null, regression, result, subquery

Description:
Since some time (do not know exactly when) but at least in versions 5.1.24 and 5.1.25 the following query is returning different values for the row 'correct' and the row 'incorrect'. When you look at the query, it should be OBVIOUS that the results must be basically the same.

How to repeat:
install the tables as supplied (privately) and run this query

 SELECT
    s.`id`,
    (SELECT CONCAT(SUBSTR(`id`,0,0),`maxweight`)+0 FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS correct,
    (SELECT `maxweight` FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS incorrect,
    REPLACE(REPLACE(CONCAT(SUBSTR(s.`name`,1,POSITION('[e]' IN s.`name`)-1),SUBSTRING_INDEX(s.`name`,'[/e]',-1)),'[d]',''),'[/d]','') AS sortname
  FROM
    (`shipmode` AS s,
    `shipzone` AS sz,
    `shipzonecountry` AS sc)
  LEFT JOIN
    `shipmaxweight` AS smw
  ON
    smw.`country_code`='CH' AND smw.`shipmode_id`=s.`id`
  WHERE
    sz.`shipmode_id`=s.`id`
  AND
    sz.`shipzonecountry_code`=sc.`code`
  AND
   (s.`minweight`<='33' OR s.`minweight` IS NULL)
  AND
   sc.`country_code`='CH'
  AND
    s.`hd`='1'
        GROUP BY s.`id`
  ORDER BY sortname

Suggested fix:
Fix :-)

This is what the query returns. Please fix mysql so that the row 'incorrect' does report the values of 'correct'

+----+---------+-----------+------------------------------------------------------------+
| id | correct | incorrect | sortname                                                   |
+----+---------+-----------+------------------------------------------------------------+
|  2 |   10000 |      NULL | Abholung in unserem Geschäft                               |
| 32 |   10000 |      NULL | Abholung in unserem Geschäft mit Einlade- und Umpackhilfe  |
| 21 |      30 |      NULL | DPD                                                        |
| 22 |    NULL |      NULL | eMail                                                      |
|  7 |     500 |      NULL | Heiminstallation 1 Person inkl Weg und 1 Stunde Arbeit     |
|  8 |     500 |      NULL | Heiminstallation 2 Person inkl Weg und 1 Stunde Arbeit     |
|  4 |     500 |      NULL | Miete Transportbus                                         |
|  5 |     500 |      NULL | Persönliche Lieferung 1 Person inkl Weg und 15min Arbeit   |
|  6 |     500 |      NULL | Persönliche Lieferung 2 Personen inkl Weg und 15min Arbeit |
| 26 |    0.25 |      NULL | Post Letter Priority                                       |
| 27 |    0.25 |      NULL | Post Letter Priority Signature                             |
| 19 |      30 |      NULL | Post Pac Economy                                           |
| 20 |      30 |      NULL | Post Pac Economy Signature                                 |
| 23 |      30 |      NULL | Post Pac Express Signature Mond                            |
| 17 |      30 |      NULL | Post Pac Priority                                          |
| 24 |      30 |      NULL | Post Pac Priority Nachnahme                                |
| 18 |      30 |      NULL | Post Pac Priority Signature                                |
| 29 |      30 |      NULL | Post Pac Signature Sperrgut                                |
|  3 |     200 |      NULL | Spediteur                                                  |
+----+---------+-----------+------------------------------------------------------------+

Simplified query:

SELECT
    s.`id`,
    (SELECT CONCAT(SUBSTR(`id`,0,0),`maxweight`)+0 FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS correct,
    (SELECT `maxweight` FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS incorrect,
    REPLACE(REPLACE(CONCAT(SUBSTR(s.`name`,1,POSITION('[e]' IN s.`name`)-1),SUBSTRING_INDEX(s.`name`,'[/e]',-1)),'[d]',''),'[/d]','') AS sortname
  FROM
    (`shipmode` AS s,
    `shipzone` AS sz)
  WHERE
    sz.`shipmode_id`=s.`id`

test case

Attachment: bug37548.test (application/octet-stream, text), 7.80 KiB.

Thank you for the report.

Verified as described using attache test case.

Workaround: alter table shipprice drop index maxweight;

Please consider wheter this bug could have been created when fixing bug 35206 which I reported and was fixed for 5.1.24

Works fine in 5.1.22, breaks with 5.1.23

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

2673 Georgi Kodinov	2008-06-24
      Bug#37548: result value erronously reported being NULL in certain subqueries
      
      When switching to indexed ORDER BY we must be sure to reset the index read
      flag if we are switching from a covering index to non-covering.

The problem here is because of a flag not being reset.
The optimizer on the subquery works as follows : 
1. it selects a covering index (b_2) and sets the flags so that the runtime would not read anything but the index.
2. It finds that it's better to use another index for sorting (d), but it doesn't reset the flag to use only the index
3. When the runtime executes the query it doesn't fill up the b and c values, so the condition of the subquery is never true and we get NULL instead of a non-null result from the subquery.

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

2673 Georgi Kodinov	2008-08-27
      Bug#37548: result value erronously reported being NULL in certain subqueries
            
      When switching to indexed ORDER BY we must be sure to reset the index read
      flag if we are switching from a covering index to non-covering.

Pushed in 5.1.28

Noted in 5.1.28 changelog.

When using indexed ORDER BY sorting, incorrect query results could be
produced if the optimizer switched from a covering index to a
non-covering index.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.7-alpha  (revid:kgeorge@mysql.com-20080827151922-k1rb2jigudvl3324) (version source revid:john.embretsen@sun.com-20080724122511-9c0oudz1xrdrs6y6) (pib:3)

Noted in 6.0.7 changelog.