diff options
| author | Jon Olav Hauglid <jon.hauglid@sun.com> | 2010-06-06 13:19:29 +0200 |
|---|---|---|
| committer | Jon Olav Hauglid <jon.hauglid@sun.com> | 2010-06-06 13:19:29 +0200 |
| commit | 142a162c665704ce5b4d5b671d05a068661c088a (patch) | |
| tree | def1ebab09889a8f09a9fcd074c8a672689376af /mysql-test/r/subselect.result | |
| parent | cbf4019a1b3ec82bdcd9a65db114908a920fb01c (diff) | |
| parent | 60a9d9bbb94ac03159bcc2d75b649abb1c9dc956 (diff) | |
| download | mariadb-git-142a162c665704ce5b4d5b671d05a068661c088a.tar.gz | |
manual merge from mysql-trunk-bugfixing
Conflicts:
Text conflict in mysql-test/r/archive.result
Contents conflict in mysql-test/r/innodb_bug38231.result
Text conflict in mysql-test/r/mdl_sync.result
Text conflict in mysql-test/suite/binlog/t/disabled.def
Text conflict in mysql-test/suite/rpl_ndb/r/rpl_ndb_binlog_format_errors.result
Text conflict in mysql-test/t/archive.test
Contents conflict in mysql-test/t/innodb_bug38231.test
Text conflict in mysql-test/t/mdl_sync.test
Text conflict in sql/sp_head.cc
Text conflict in sql/sql_show.cc
Text conflict in sql/table.cc
Text conflict in sql/table.h
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 89 |
1 files changed, 89 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index b23556887c1..e3aa87876ed 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4899,3 +4899,92 @@ WHERE t1.a = d1.a; ERROR 42S22: Unknown column 'd1.a' in 'where clause' 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; |