diff options
author | Alexey Kopytov <Alexey.Kopytov@Sun.com> | 2010-04-03 21:55:04 +0400 |
---|---|---|
committer | Alexey Kopytov <Alexey.Kopytov@Sun.com> | 2010-04-03 21:55:04 +0400 |
commit | cab4a776d81b2e0f67fdd40c9b1715f2284f4e4d (patch) | |
tree | 20b03f4d204d0f263b5a770bf379127619f17546 /mysql-test/t/subselect.test | |
parent | 3514d8825ca812476894a41c81dec4703a14e74d (diff) | |
parent | 92a41960454776a6f2b05b49124312cde2a9f90a (diff) | |
download | mariadb-git-cab4a776d81b2e0f67fdd40c9b1715f2284f4e4d.tar.gz |
Manual merge of mysql-5.1-bugteam into mysql-trunk-merge.
Conflicts:
Text conflict in storage/myisam/mi_delete_table.c
Diffstat (limited to 'mysql-test/t/subselect.test')
-rw-r--r-- | mysql-test/t/subselect.test | 83 |
1 files changed, 83 insertions, 0 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 50f2644ff9e..06aaf5dfb7f 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3838,3 +3838,86 @@ 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; |