diff options
| -rw-r--r-- | mysql-test/r/derived_view.result | 58 | ||||
| -rw-r--r-- | mysql-test/t/derived_view.test | 45 | ||||
| -rw-r--r-- | sql/table.cc | 2 |
3 files changed, 104 insertions, 1 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 8cc53d7932d..be6050ab48e 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -645,3 +645,61 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away DROP VIEW v1; DROP TABLE t1; +# +# LP bug #800535: GROUP BY query with nested left join +# and a derived table in the nest +# +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (a int NOT NULL); +INSERT INTO t2 VALUES (1), (2); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (3,3), (4,4); +EXPLAIN EXTENDED +SELECT t.a FROM t1 LEFT JOIN +(t2 t JOIN t3 ON t3.b > 5) ON t.a >= 1 +GROUP BY t.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE t ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` `t` join `test`.`t3`) on(((`test`.`t`.`a` >= 1) and (`test`.`t3`.`b` > 5))) where 1 group by `test`.`t`.`a` +SELECT t.a FROM t1 LEFT JOIN +(t2 t JOIN t3 ON t3.b > 5) ON t.a >= 1 +GROUP BY t.a; +a +NULL +EXPLAIN EXTENDED +SELECT t.a FROM t1 LEFT JOIN +(( SELECT * FROM t2 ) t JOIN t3 ON t3.b > 5) ON t.a >= 1 +GROUP BY t.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` >= 1) and (`test`.`t3`.`b` > 5))) where 1 group by `test`.`t2`.`a` +SELECT t.a FROM t1 LEFT JOIN +(( SELECT * FROM t2 ) t JOIN t3 ON t3.b > 5) ON t.a >= 1 +GROUP BY t.a; +a +NULL +CREATE VIEW v1 AS SELECT * FROM t2; +EXPLAIN EXTENDED +SELECT t.a FROM t1 LEFT JOIN +(v1 t JOIN t3 ON t3.b > 5) ON t.a >= 1 +GROUP BY t.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` >= 1) and (`test`.`t3`.`b` > 5))) where 1 group by `test`.`t2`.`a` +SELECT t.a FROM t1 LEFT JOIN +(v1 t JOIN t3 ON t3.b > 5) ON t.a >= 1 +GROUP BY t.a; +a +NULL +DROP VIEW v1; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 7433b83b8a4..f4fc17bd3b2 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -277,3 +277,48 @@ SELECT MAX(b) FROM v1 WHERE a=7 AND b<75; DROP VIEW v1; DROP TABLE t1; + + +--echo # +--echo # LP bug #800535: GROUP BY query with nested left join +--echo # and a derived table in the nest +--echo # + +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (1), (2); + +CREATE TABLE t2 (a int NOT NULL); +INSERT INTO t2 VALUES (1), (2); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (3,3), (4,4); + +EXPLAIN EXTENDED +SELECT t.a FROM t1 LEFT JOIN + (t2 t JOIN t3 ON t3.b > 5) ON t.a >= 1 + GROUP BY t.a; +SELECT t.a FROM t1 LEFT JOIN + (t2 t JOIN t3 ON t3.b > 5) ON t.a >= 1 + GROUP BY t.a; + +EXPLAIN EXTENDED +SELECT t.a FROM t1 LEFT JOIN + (( SELECT * FROM t2 ) t JOIN t3 ON t3.b > 5) ON t.a >= 1 + GROUP BY t.a; +SELECT t.a FROM t1 LEFT JOIN + (( SELECT * FROM t2 ) t JOIN t3 ON t3.b > 5) ON t.a >= 1 + GROUP BY t.a; + +CREATE VIEW v1 AS SELECT * FROM t2; + +EXPLAIN EXTENDED +SELECT t.a FROM t1 LEFT JOIN + (v1 t JOIN t3 ON t3.b > 5) ON t.a >= 1 + GROUP BY t.a; +SELECT t.a FROM t1 LEFT JOIN + (v1 t JOIN t3 ON t3.b > 5) ON t.a >= 1 + GROUP BY t.a; + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + diff --git a/sql/table.cc b/sql/table.cc index bf8e0e46661..d61ab15e957 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4537,7 +4537,7 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref, Force creation of nullable item for the result tmp table for outer joined views/derived tables. */ - if (view->outer_join) + if (view->table && view->table->maybe_null) item->maybe_null= TRUE; /* Save item in case we will need to fall back to materialization. */ view->used_items.push_back(item); |