MySQL Bugs: #47904: Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index

Bug #47904	Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index
Submitted:	7 Oct 2009 22:20	Modified:	20 Jun 2010 22:41
Reporter:	Daniel Grace	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.37-2-log (Debian), 5.4	OS:	Linux
Assigned to:	Anurag Shekhar	CPU Architecture:	Any
Tags:	subquery left join index

Description:
In some situations, a query that queries from a derived table (i.e. SELECT ... FROM (SELECT ...)) will produce incorrect results when the following conditions are present:

- The table subquery contains a derived query ((SELECT ... ) AS column)
- The derived query can potentially produce zero rows or a single NULL (i.e. no rows matched, or an aggregate function such as SUM() running over zero rows))
- The table subquery joins at least two tables.
- The join condition involves an index.

It is worth noting that the table subquery, ran by itself as a standalone SELECT... , works fine and produces correct data.  However, CREATE TABLE ... SELECT... and INSERT ... SELECT both fail in the exact same manner as SELECT * FROM (SELECT...)

This problem is not present in the latest community build of 5.0.x.

How to repeat:
-- Set up test tables.
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
	foo_id INT UNSIGNED,

	PRIMARY KEY(foo_id)
) Engine=MyISAM;

INSERT INTO foo (foo_id) VALUES (1), (2), (3), (4), (5)
;

DROP TABLE IF EXISTS foo2;
CREATE TABLE foo2 SELECT * FROM foo;
	
DROP TABLE IF EXISTS bar;
CREATE TABLE bar (
	bar_id INT UNSIGNED AUTO_INCREMENT,
	foo_id INT UNSIGNED,
	amount DECIMAL(16,2),

	PRIMARY KEY(bar_id),
	KEY(foo_id)
) Engine=MyISAM;

INSERT INTO bar (foo_id, bar_id, amount) VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00)
;

-- This is the 'inner query' running by itself.
-- Produces correct results.
SELECT
	foo.foo_id,
	IFNULL((SELECT SUM(amount) FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	LEFT JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
;

-- SELECT * FROM (the same inner query)
-- Produces correct results.
SELECT * FROM (
SELECT
	foo.foo_id,
	IFNULL((SELECT SUM(amount) FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	LEFT JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
) AS t;

-- Now make foo2.foo_id part of a key.
ALTER TABLE foo2 ADD PRIMARY KEY(foo_id);

-- Same inner query by itself.
-- Still correct results.
SELECT
	foo.foo_id,
	IFNULL((SELECT SUM(amount) FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	LEFT JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
;

-- SELECT * FROM (the same inner query), now with indexes on the LEFT JOIN
-- INCORRECT results - The rows that would be 0.00 (because the inner subselect returns NULL) instead copy the column value from the previous row.
SELECT * FROM (
SELECT
	foo.foo_id,
	IFNULL((SELECT SUM(amount) FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	LEFT JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
) AS t;

-- And if you sort the output?
SELECT * FROM (
SELECT
	foo.foo_id,
	IFNULL((SELECT SUM(amount) FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	LEFT JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
ORDER BY
	foo.foo_id DESC
) AS t;

-- No aggregate function call?
SELECT * FROM (
SELECT
	foo.foo_id,
	IFNULL((SELECT amount FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	LEFT JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
ORDER BY
	foo.foo_id DESC
) AS t;

-- Different type of join?
SELECT * FROM (
SELECT
	foo.foo_id,
	IFNULL((SELECT amount FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	INNER JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
) AS t;

-- What about creating a table from the results?
DROP TABLE IF EXISTS baz;
CREATE TABLE baz SELECT
	foo.foo_id,
	IFNULL((SELECT amount FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	INNER JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
;
SELECT * FROM baz;
-- What about INSERT ... SELECT
TRUNCATE baz;
INSERT INTO baz SELECT
	foo.foo_id,
	IFNULL((SELECT amount FROM bar WHERE bar.foo_id=foo.foo_id), 0) AS total_amount
FROM
	foo
	INNER JOIN foo2 ON foo2.foo_id=foo.foo_id
GROUP BY
	foo.foo_id
;
SELECT * FROM baz;

-- Clean up our test data
DROP TABLE foo;
DROP TABLE foo2;
DROP TABLE bar;
DROP TABLE baz;

Thank you for the bug report.

Verified as described on 5.4 too.

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

3244 Anurag Shekhar	2009-12-04
      Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN 
                 on index
      
      'my_decimal' class has two members which can be used to access the 
      value. The member variable buf (inherited from parent class decimal_t) 
      is set to member variable buffer so that both are pointing to same value.
      
      This makes use of memcpy to clone my_decimal as buffer is declared as
      an array and memcpy results in copying the data from the source object
      but the buf now points to the buffer of source class and any update
      of buf results in updating the source object, while array buffer continues
      to hold the original value at the time memcpy.
      
      Latter when the new object (which now holds a corrupt value) is cloned
      again using proper cloning function 'my_decimal2decimal' the buf pointer
      is fixed result in loss of the current value.
      
      Using my_decimal2decimal instead of memcpy in Item_copy_decimal::copy()
      fixed this problem.
     @ mysql-test/r/subselect.result
        Updated result file after addding test case for bug#47904.
     @ mysql-test/t/subselect.test
        Added test case for bug#47904.
     @ sql/item.cc
        Memcopy shouldn't be used to clone my_decimal. Use my_decimal2decimal
        instead.

Path approved with suggestions to rephrase some comments

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

3253 Anurag Shekhar	2009-12-09
      Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN 
               on index
      
      'my_decimal' class has two members which can be used to access the 
      value. The member variable buf (inherited from parent class decimal_t) 
      is set to member variable buffer so that both are pointing to same value.
      
      Item_copy_decimal::copy() uses memcpy to clone 'my_decimal'. The member
      buffer is declared as an array and memcpy results in copying the values
      of the array, but the inherited member buf, which should be pointing at
      the begining of the array 'buffer' starts pointing to the begining of 
      buffer in original object (which is being cloned). Further updates on 
      'my_decimal' updates only the inherited member 'buf' but leaves 
      buffer unchanged.
      
      Later when the new object (which now holds a inconsistent value) is cloned
      again using proper cloning function 'my_decimal2decimal' the buf pointer
      is fixed resulting in loss of the current value.
      
      Using my_decimal2decimal instead of memcpy in Item_copy_decimal::copy()
      fixed this problem.
     @ mysql-test/r/subselect.result
        Updated result file after addding test case for bug#47904.
     @ mysql-test/t/subselect.test
        Added test case for bug#47904.
     @ sql/item.cc
        Memcopy shouldn't be used to clone my_decimal. Use my_decimal2decimal
        instead.

Looks ok.

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

3441 Anurag Shekhar	2010-04-02
      Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN 
             on index
      
      'my_decimal' class has two members which can be used to access the 
      value. The member variable buf (inherited from parent class decimal_t) 
      is set to member variable buffer so that both are pointing to same value.
      
      Item_copy_decimal::copy() uses memcpy to clone 'my_decimal'. The member
      buffer is declared as an array and memcpy results in copying the values
      of the array, but the inherited member buf, which should be pointing at
      the begining of the array 'buffer' starts pointing to the begining of 
      buffer in original object (which is being cloned). Further updates on 
      'my_decimal' updates only the inherited member 'buf' but leaves 
      buffer unchanged.
      
      Later when the new object (which now holds a inconsistent value) is cloned
      again using proper cloning function 'my_decimal2decimal' the buf pointer
      is fixed resulting in loss of the current value.
      
      Using my_decimal2decimal instead of memcpy in Item_copy_decimal::copy()
      fixed this problem.
     @ mysql-test/r/subselect.result
        Updated result file after addding test case for bug#47904.
     @ mysql-test/t/subselect.test
        Added test case for bug#47904.
     @ sql/item.cc
        Memcopy shouldn't be used to clone my_decimal. Use my_decimal2decimal
        instead.

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

4021 Anurag Shekhar	2010-04-02 [merge]
      merging fix for bug#47904

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

4023 Sergey Glukhov	2010-04-05 [merge]
      5.1-bugteam->mysql-pe merge.
      test result update(Bug#47904).
     @ mysql-test/r/having.result
        5.1-bugteam->mysql-pe merge
     @ mysql-test/r/subselect_no_mat.result
        test result update(Bug#47904)
     @ mysql-test/r/subselect_no_opts.result
        test result update(Bug#47904)
     @ mysql-test/r/subselect_no_semijoin.result
        test result update(Bug#47904)
     @ mysql-test/t/having.test
        5.1-bugteam->mysql-pe merge

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:anurag.shekhar@sun.com-20100401200536-zncgi34ni34guha8) (merge vers: 5.1.46) (pib:16)

Documented bugfix as follows in the 5.1.46 changelog:

        A query that read from a derived table (of the form SELECT ...
        FROM (SELECT ...)) produced incorrect results when the following
        conditions were present:

              The table subquery contained a derived query ((SELECT ...) 
              AS column).

              The derived query could potentially produce zero rows or a
              single NULL (that is, no rows matched, or the query used
              an aggregate function such as SUM() running over zero
              rows).

              The table subquery joined at least two tables.

              The join condition involved an index.

Set NM status -- waiting for 5.5. merge.

There is something really strange in the test case in the patch.

This query is run twice:

SELECT
t1.t1_id,
IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount
FROM
t1
LEFT JOIN t2 ON t2.t1_id=t1.t1_id
GROUP BY
t1.t1_id;

Once without a primary key on t2, once with.

These two queries produce different output!

t1_id	total_amount
1	100.00
2	200.00
3	0.00
4	400.00
5	0.00

versus

t1_id	total_amount
1	100.00
2	200.00
3	0
4	400.00
5	0

Note the zeros are output as 0.00 in one case and as 0 in the other. That
surely looks like a bug?

It seems as if the result type of IFNULL(amount,0) in one case is
decimal(16,2), and in the other case is int? I'm not 100% sure from the
documentation which is correct, but surely it should not depend on the
presense or absense of a primary key?

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100403175504-n95vgc3liknhj3fn) (merge vers: 5.5.4-m3) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)