#At file:///home/anurag/mysqlsrc/mysql-5.1-bugteam-47904/ based on revid:olav@stripped
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.
modified:
mysql-test/r/subselect.result
mysql-test/t/subselect.test
sql/item.cc
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-11-03 16:23:05 +0000
+++ b/mysql-test/r/subselect.result 2009-12-09 11:29:43 +0000
@@ -4578,3 +4578,92 @@ SELECT 1 FROM t1 GROUP BY
1
DROP TABLE t1;
End of 5.1 tests.
+Set up test tables.
+CREATE TABLE t1 (
+t1_id INT UNSIGNED,
+PRIMARY KEY(t1_id)
+) Engine=MyISAM;
+INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 SELECT * FROM t1;
+CREATE TABLE t3 (
+t3_id INT UNSIGNED AUTO_INCREMENT,
+t1_id INT UNSIGNED,
+amount DECIMAL(16,2),
+PRIMARY KEY(t3_id),
+KEY(t1_id)
+) Engine=MyISAM;
+INSERT INTO t3 (t1_id, t3_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
+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
+;
+t1_id total_amount
+1 100.00
+2 200.00
+3 0.00
+4 400.00
+5 0.00
+SELECT * FROM (the same inner query)
+Produces correct results.
+SELECT * FROM (
+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
+) AS t;
+t1_id total_amount
+1 100.00
+2 200.00
+3 0.00
+4 400.00
+5 0.00
+Now make t2.t1_id part of a key.
+ALTER TABLE t2 ADD PRIMARY KEY(t1_id);
+Same inner query by itself.
+Still correct results.
+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;
+t1_id total_amount
+1 100.00
+2 200.00
+3 0
+4 400.00
+5 0
+SELECT * FROM (the same inner query), now with indexes on the LEFT JOIN
+SELECT * FROM (
+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
+) AS t;
+t1_id total_amount
+1 100.00
+2 200.00
+3 0.00
+4 400.00
+5 0.00
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2009-11-03 16:23:05 +0000
+++ b/mysql-test/t/subselect.test 2009-12-09 11:29:43 +0000
@@ -3560,3 +3560,86 @@ SELECT 1 FROM t1 GROUP BY
DROP TABLE t1;
--echo End of 5.1 tests.
+
+#
+# Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index
+#
+
+--echo Set up test tables.
+CREATE TABLE t1 (
+ t1_id INT UNSIGNED,
+
+ PRIMARY KEY(t1_id)
+) Engine=MyISAM;
+
+INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5);
+
+CREATE TABLE t2 SELECT * FROM t1;
+
+CREATE TABLE t3 (
+ t3_id INT UNSIGNED AUTO_INCREMENT,
+ t1_id INT UNSIGNED,
+ amount DECIMAL(16,2),
+
+ PRIMARY KEY(t3_id),
+ KEY(t1_id)
+) Engine=MyISAM;
+
+INSERT INTO t3 (t1_id, t3_id, amount)
+ VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00);
+
+--echo This is the 'inner query' running by itself.
+--echo Produces correct results.
+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
+;
+
+--echo SELECT * FROM (the same inner query)
+--echo Produces correct results.
+SELECT * FROM (
+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
+) AS t;
+
+--echo Now make t2.t1_id part of a key.
+ALTER TABLE t2 ADD PRIMARY KEY(t1_id);
+
+--echo Same inner query by itself.
+--echo Still correct results.
+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;
+
+--echo SELECT * FROM (the same inner query), now with indexes on the LEFT JOIN
+SELECT * FROM (
+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
+) AS t;
+
+
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2009-11-24 15:30:21 +0000
+++ b/sql/item.cc 2009-12-09 11:29:43 +0000
@@ -3503,7 +3503,7 @@ void Item_copy_decimal::copy()
{
my_decimal *nr= item->val_decimal(&cached_value);
if (nr && nr != &cached_value)
- memcpy (&cached_value, nr, sizeof (my_decimal));
+ my_decimal2decimal (nr, &cached_value);
null_value= item->null_value;
}
Attachment: [text/bzr-bundle] bzr/anurag.shekhar@sun.com-20091209112943-b6oqil4iyrcmzf44.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (anurag.shekhar:3253)Bug#47904 | Anurag Shekhar | 9 Dec |
