diff options
| -rw-r--r-- | mysql-test/r/view.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 21 | ||||
| -rw-r--r-- | sql/opt_sum.cc | 8 |
3 files changed, 45 insertions, 1 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ab33b54bd2a..fc3e5475ae7 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2562,3 +2562,20 @@ my_sqrt 1.4142135623731 DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 (id int PRIMARY KEY); +CREATE TABLE t2 (id int PRIMARY KEY); +INSERT INTO t1 VALUES (1), (3); +INSERT INTO t2 VALUES (1), (2), (3); +CREATE VIEW v2 AS SELECT * FROM t2; +SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id; +COUNT(*) +2 +SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id; +id id +1 1 +3 3 +SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id; +COUNT(*) +2 +DROP VIEW v2; +DROP TABLE t1, t2; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 3e4797af8ef..fd9589a6f59 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2418,3 +2418,24 @@ SELECT my_sqrt FROM v1 ORDER BY my_sqrt; DROP VIEW v1; DROP TABLE t1; + +# +# Bug #18237: invalid count optimization applied to an outer join with a view +# + +CREATE TABLE t1 (id int PRIMARY KEY); +CREATE TABLE t2 (id int PRIMARY KEY); + +INSERT INTO t1 VALUES (1), (3); +INSERT INTO t2 VALUES (1), (2), (3); + +CREATE VIEW v2 AS SELECT * FROM t2; + +SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id; +SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id; + +SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id; + +DROP VIEW v2; + +DROP TABLE t1, t2; diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 4158031b9a9..aac47707d1b 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -96,8 +96,14 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) */ for (TABLE_LIST *tl= tables; tl; tl= tl->next_leaf) { + TABLE_LIST *embedded; + for (embedded= tl ; embedded; embedded= embedded->embedding) + { + if (embedded->on_expr) + break; + } + if (embedded) /* Don't replace expression on a table that is part of an outer join */ - if (tl->on_expr) { outer_tables|= tl->table->map; |